AI promises to make tedious work easier, but I wanted to know whether it could deliver in real Excel projects. Rather than asking Claude for formulas or snippets of code, I tested whether it could handle three types of automation: creating a workbook from scratch, building a reusable reporting system, and developing a tool that analyzes existing spreadsheets. The goal was to see how much of the work Claude could handle and where I would still need to step in.

Building a complete Excel workbook

An entire file generated from a single prompt

For my first test, I wanted to see whether Claude could automate the process of building a complete Excel workbook rather than just help with individual pieces of VBA. I asked it to create an employee onboarding workbook with the underlying tables, automation features, and dashboard.

The result was more detailed than I expected

The result was impressively complete. After importing Claude’s BAS file into a blank macro-enabled workbook and running the macro, Excel created every worksheet, converted the datasets into Excel tables, added formulas, applied data validation and conditional formatting, built a dashboard, and linked everything together with navigation buttons.

Some smaller details stood out too. Claude formatted columns correctly, removed dashboard gridlines, wrapped complex INDEX/MATCH formulas in IFERROR, and added color-coded sheet tabs for easier navigation.

The VBA needed a few fixes

The only coding issue was a small VBA syntax error caused by escaped quotation marks. Excel highlighted the problem immediately, and after I reported the error back to Claude, it generated a corrected version of the VBA. Importing the updated module resolved the issue.

Most of the remaining changes were cosmetic. I deleted the default blank worksheet, resized a few rows and columns, repositioned the overlapping dashboard charts, reformatted the dashboard metrics into card-style summaries, refined the conditional formatting colors, and updated the table colors to match their worksheet tabs. I also replaced the hard-coded data validation lists with range-based lists.

Looking back, most of those refinements reflected gaps in my prompt rather than shortcomings in Claude’s coding. I hadn’t specified the dashboard layout, how data validation should be managed, or how task statuses should be color-coded. If I were to run the prompt again, I’d include these details for a more complete result.

Automating a complete reporting workflow

A reusable PDF reporting system

After seeing Claude build an entire Excel workbook for my first automation, I wanted to test whether it could take an existing dataset and automate a repetitive reporting workflow. After creating a sales table containing 500 rows of sample data, a report template, and a log sheet, I asked Claude to create a macro that identified each salesperson, generated a PDF report, saved it, and recorded the output.

This result surprised me the most

Once the VBA was working, the results were impressive. The generated PDF reports followed my template exactly, and the filenames were clear and consistent. The macro correctly pulled each salesperson’s data, calculated their totals, created individual reports, and recorded each output in the report log.

The biggest surprise was that this wasn’t just a one-time shortcut. After the initial run, I added a new row to the sales data and ran the macro again. It detected the updated data, generated the additional report, saved it alongside the existing PDFs, and added the new entry to the report log. That completely changed the value of the automation. Instead of running once, I now had a reusable reporting system.

Claude helped me fix problems

The first version didn’t work perfectly straight away. When I ran the macro, it stopped with a “Bad file name or number” error before creating any reports. However, after feeding the error message back to Claude, it rewrote the file-handling section to check the workbook location more carefully and create the output folder safely. I then imported the updated VBA module, and the macro ran successfully.

I also noticed that the generated reports displayed currency using my local UK settings rather than US dollars. Claude adjusted the VBA to apply an explicit US currency format, ensuring the PDFs displayed dollar values regardless of the computer’s regional settings.

These fixes were relatively minor compared with what the macro achieved. Claude handled the complicated parts—analyzing the workbook structure, generating reports, creating PDFs, and maintaining a log—but the testing process still mattered.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.

Building a reusable workbook analyzer

A one-click Excel inspection tool

Inspecting an unfamiliar workbook can take time. Which sheets are hidden? Where are formulas coming from? Are there external links, tables, charts, or PivotTables?

For my final test, I moved from creating workbooks to understanding them. I asked Claude to build a reusable VBA tool that I could store in my Personal Macro Workbook (PERSONAL.XLSB) and run on any workbook I opened, creating a report that showed its structure, objects, and potential issues.

A complex task became a five-second process

This was the automation that felt most like a genuine Excel utility. When I ran the macro, Claude created a new Workbook Analysis sheet that brought together information that is normally spread across Excel’s interface, including workbook structure, tables, charts, PivotTables, formulas, validation rules, and conditional formatting rules.

I also tested whether this was a one-time report or a reusable tool. When I added another table to the workbook and clicked the macro in my QAT again, the Workbook Analysis sheet updated with the new table information. When I deleted the table and reran the analysis, the report updated once more. As a result, I wasn’t just creating a snapshot of one workbook—I had a tool I could run whenever I needed to inspect a spreadsheet.

Issues were only minor

Overall, this automation required fewer adjustments than the previous two.

The only issue I found was with named ranges. Claude successfully identified them in the workbook, but some dynamic array-related names returned errors like _xlfn.SINGLE and _xlfn.UNIQUE, prefixes that can appear when newer Excel functions aren’t being interpreted correctly. Another named range returned #VALUE!.

There was also an external link warning when opening the test workbook because I had deliberately included an external reference. However, the analyzer still correctly identified that external link in the final report.

Compared with the complexity of what the macro was doing, these were minor issues. Claude created a reusable Excel inspection tool that would have taken me significantly longer to build manually.

AI can speed up Excel work, but it still needs a human touch

Claude didn’t replace my Excel knowledge, but it helped me build tools I would have spent much longer creating manually. The biggest lesson was that AI works best when you define the goal clearly, test the output, and refine anything that doesn’t work. That same testing approach also helped me compare ChatGPT and Gemini when I asked them to help me build an Excel dashboard, where I found that the tool providing the clearest instructions and most precise output ultimately needed the least manual refinement.

Prompt 1:

Create VBA that builds a complete employee onboarding workbook from scratch. The workbook should contain separate worksheets for Employees, Equipment, Training, Tasks, and Dashboard. Format each dataset as an Excel table with clear headers and sample formulas where appropriate. Add data validation drop-down lists for fields like Department and Status, apply conditional formatting to highlight overdue training and outstanding tasks, and create a dashboard summarizing key metrics with charts. Add a navigation menu on the Dashboard with hyperlinks to each worksheet. The macro should create everything automatically when run, and if the workbook already contains these sheets, ask whether to overwrite them before continuing.

Prompt 2:

I have uploaded an Excel workbook containing a sales data table, a report template, and a report log sheet. Please inspect the workbook structure before writing the VBA. Create a VBA macro that generates personalized sales reports from the workbook. The macro should identify every unique salesperson in the SalesData table. For each salesperson, it should filter their records, populate the Report_Template sheet with their name and sales metrics, export the completed report as a PDF, and save it into a folder called Sales Reports. If the folder does not exist, create it automatically. Use the salesperson’s name as part of the filename. After each report is created, record the salesperson, filename, creation date, and status in the Report_Log sheet. The macro should handle names containing spaces and special characters, prevent accidental overwriting of existing PDFs, and display a summary message when all reports have been completed.

Prompt 3:

I want to create a reusable VBA tool that I can store in my Personal Macro Workbook (PERSONAL.XLSB) and run on any Excel workbook I open. Please create a macro called “AnalyzeWorkbook” that inspects the currently active workbook and creates a new worksheet called “Workbook Analysis” containing a structured report of its contents. The macro must not modify the workbook being analyzed. It should only read information from the active workbook and create the analysis report. The report should include the following sections. Workbook overview: workbook name; file path; date analyzed; number of worksheets; number of visible worksheets; number of hidden worksheets. Worksheet inventory: For every worksheet, list worksheet name; visibility status; used range address; number of rows used; number of columns used. Excel tables: For every table in the workbook, list worksheet name; table name; table range; number of rows; number of columns. PivotTables: For every PivotTable, list worksheet name; PivotTable name; location. Charts: For every chart, list worksheet name; chart name; chart type. Named ranges: For every named range, list name; refers to range/formula; scope (workbook or worksheet). Formula analysis: Identify cells containing formula errors; formulas that reference other worksheets; formulas containing external workbook references. Data validation: Identify cells containing data validation rules and list worksheet name; cell/range; validation type; validation criteria. Conditional formatting: Identify: worksheet name; applied range; rule type; formatting requirements; create clear section headings. Format the output as a readable report. Use bold headings and adjust column widths automatically. Freeze the top row. Apply filters where appropriate. Make the report easy to review after running the macro. Technical requirements: The macro must run from PERSONAL.XLSB. It must analyze whichever workbook is currently active. It must not rely on hard-coded workbook names or sheet names. It must handle workbooks with no tables, charts, PivotTables, named ranges, or other objects without failing. Use error handling so one unsupported object does not stop the entire analysis. Provide the VBA code as a complete “.bas” module that I can import into PERSONAL.XLSB.