How to Automate Boring Spreadsheet Work with AI

Every office has That Spreadsheet. The one that takes four hours to update every month. The one with 47 tabs and a formula so complex that if the person who wrote it leaves, nobody can maintain it. The one that makes you question your career choices every second Tuesday.

AI can help with That Spreadsheet. Not by replacing it (spreadsheets are immortal, they'll outlast us all) but by automating the bits that make you want to put your head through the monitor.

I spent years doing data work professionally before AI tools could do this stuff. I'm slightly bitter about it, honestly. The things that used to take me half a day now take fifteen minutes. Progress is wonderful and also personally offensive.

Writing formulas

This is the most immediately useful thing AI does for spreadsheets.

Instead of spending twenty minutes trying to remember the syntax for VLOOKUP (or arguing about whether to use INDEX MATCH instead), you just describe what you want:

"Write an Excel formula that looks up the value in column A of Sheet1, finds it in column B of Sheet2, and returns the corresponding value from column D of Sheet2."

ChatGPT or Claude will give you the formula. You paste it in. It works. Five seconds instead of twenty minutes.

This works for everything from simple SUMIFs to complex nested formulas that would make a grown adult weep. I once asked Claude to write a formula that calculated weighted averages across multiple conditions with dynamic date ranges and it produced something I couldn't have written myself in under an hour. It worked first time. I was simultaneously impressed and existentially threatened.

The key: describe what you want in plain English. Don't try to use Excel terminology unless you're confident in it. "Add up all the sales for January where the region is North" works perfectly.

Data cleaning

This is where AI saves the most time and where i used to spend the most painful hours of my career.

Got a column of names in different formats? (JOHN SMITH, john smith, Smith, John, smith john...) Got dates in three different formats? Got addresses that need standardising? Got duplicates that aren't quite duplicates?

You have two options:

Option 1: Use ChatGPT's Advanced Data Analysis. Upload your CSV or Excel file. Describe the problem. "Clean up the name column so all names are in 'First Last' format with proper capitalisation. Remove duplicates based on email address, keeping the most recent entry." It writes Python code, runs it, and gives you a clean file back. You don't need to know Python. You don't even see the code if you don't want to.

Option 2: Ask for formulas. "Write an Excel formula to convert text in A1 to proper case" or "Write a formula to extract the domain from an email address." Apply the formula down the column. Done.

Option 1 is better for large, messy datasets. Option 2 is better for ongoing templates where you want the cleaning built into the spreadsheet.

Analysis and insights

Upload your data to ChatGPT or Claude and ask questions:

• "What are the main trends in this data?"
• "Which products have the highest profit margin?"
• "Show me the month-over-month growth rate"
• "Are there any outliers that seem unusual?"
• "Create a pivot table showing sales by region and quarter"

The AI will analyse the data and often produce visualisations. Charts, tables, summary statistics. Things that would take you an hour to create in Excel, done in seconds.

A word of caution: always verify the analysis. AI can miscalculate, misinterpret column headers, or make assumptions about your data that aren't correct. Treat it as a first pass, not a final answer.

Building reports

Monthly reporting is one of those tasks that's simultaneously important and soul-crushingly repetitive. The same report, with updated numbers, formatted the same way, sent to the same people.

Here's a workflow that works:

1. Export your updated data as CSV
2. Upload to ChatGPT Advanced Data Analysis
3. "Generate a monthly sales report with: executive summary, key metrics vs last month, top 5 products, regional breakdown, and any notable trends. Format it as a professional summary."
4. Copy the output into your report template
5. Review and edit

The first time you set this up takes a bit of back and forth. After that, it's copy-paste-review-send. What used to take three hours takes thirty minutes.

Google Sheets AI features

If you use Google Sheets, there are AI features built right in:

• Help me organise: Generates tables and templates from descriptions
• Formula suggestions: Start typing and Sheets suggests formulas
• Smart fill: Detects patterns and fills data automatically

These aren't as powerful as uploading to ChatGPT, but they're right there in the sheet. No switching between apps. For quick tasks that you want to learn this weekend, Google Sheets AI is a good starting point.

Excel Copilot

Microsoft's Copilot in Excel can:

• Generate formulas from natural language descriptions
• Create charts and pivot tables
• Highlight patterns and outliers
• Answer questions about your data

It's getting better quickly. Six months ago it was a bit hit-and-miss. Now it handles most routine tasks reliably. If your organisation pays for Copilot, use it for spreadsheets. That's where it genuinely shines.

The "but what about complex spreadsheets" question

If your spreadsheet is genuinely complex, with VBA macros, multiple data sources, complex dependencies, and business logic that's evolved over years... AI can still help, but differently.

Ask it to explain the existing formulas. "What does this formula do?" is one of the most underrated prompts. Understanding what's already there is often harder than building something new.

Ask it to simplify. "This formula is [paste formula]. Can you rewrite it to do the same thing more simply?" Sometimes the answer is yes and the result is something a normal human can maintain.

Ask it to document. "Create documentation for this spreadsheet explaining what each tab does, what the key formulas are, and how data flows between sheets." This is the single most useful thing you can do for your colleagues (and your future self).

For more on building workflows without coding, there's a separate guide.

The one thing to do today: take one formula you've been struggling with or one data cleaning task you've been putting off. Open ChatGPT or Claude. Describe it in plain English. Paste in the result. Congratulate yourself on the time you just saved, and try not to think about all the hours you spent learning VLOOKUP.