Most spreadsheet problems are not difficult. They are tedious. You know what you want the data to do. You just cannot remember whether it is VLOOKUP or XLOOKUP, or why your IF statement is returning FALSE on every row except the ones you want it to.
AI is good at this. Describe the columns you have and the result you want, and you get a working formula with an explanation. That is faster than searching a help forum and better than copy-pasting something you do not understand.
The tool, and how to set it up
Use Claude (claude.ai) for formula generation, data cleaning approaches and analysis questions. You type, it answers, you paste the formula into your spreadsheet. No setup beyond an account.
If you are in Excel with Microsoft 365, Copilot is available directly in the ribbon. If you are in Google Sheets, Gemini is built into the Help menu and formula bar. Both work in context with your actual data, which is an advantage for simple tasks. For anything structural, where you need to think through the approach before touching the file, Claude in a separate tab is more useful.
Do not upload your spreadsheet file unless you have checked that sharing it is appropriate. For most formula questions, you do not need to.
How to do it
1. Describe your columns, not the function you think you need.
The most common mistake is starting with "how do I use INDEX MATCH to..." when you should start with "I have a product code in column A and a price list on Sheet 2, I want to pull the price into column B." Claude finds the right function from the description. You do not need to know the function name first.
2. Ask for the formula and the explanation.
Always ask for both. The explanation is what you will need when the formula breaks in three months and you have forgotten how it works. Ask for edge cases too: what happens if the lookup value is not found, if the cell is blank, if there is a duplicate.
3. For data cleaning, describe the mess.
"I have names in column A formatted as 'Smith, John' and I need them split into First Name (B) and Last Name (C) in Google Sheets" is enough. AI handles split-name formulas, date format fixes (the US-date-in-a-UK-file problem is a classic), removing leading or trailing spaces, deduplication approaches, and conditional formatting rules.
4. For analysis, ask for the approach before the formula.
"I need a summary of total spend by department by month. My data is in columns A to E. What is the best way to do this in Excel?" You might get a pivot table suggestion, a SUMIFS approach, or a dynamic array formula depending on what your data looks like. Once you have agreed on the approach, ask for the formula.
5. Paste a small sample when the structure is unusual.
A few rows of example data (with real values replaced by dummy values) will get you a more accurate formula than a verbal description of a complex layout. Three to five rows with your actual column headers is usually enough.
The prompt
I have a spreadsheet with these columns:
- A: [column name and description]
- B: [column name and description]
- C: [column name and description]
I want to: [describe the result you need in plain English].
I'm using [Excel / Google Sheets].
Give me the formula, explain how it works, and tell me the edge cases I should watch for (blanks, duplicates, text in number columns, etc.).
Adapt as needed. For cleaning tasks, replace "I want to" with "The problem is that..." and describe what is wrong with the data.
How to QA it
Before you paste the formula down an entire column, test it on five rows where you already know the correct answer. Pick rows that represent different scenarios: the normal case, a blank cell, a zero value, a text value in what should be a number column.
The classic failure mode is a formula that is right on clean rows and silently wrong on messy ones. VLOOKUP returning the first match when there are duplicates. An average that ignores errors instead of flagging them. A date calculation that works in the US date format and gives nonsense in UK format. None of these produce an error message. They just produce a wrong number, quietly.
For any total, spot-check it. Add up a subset manually (or with a simple SUM on a filtered view) and confirm the AI-generated formula matches. This takes two minutes and will catch the majority of problems before they reach a report.
If the formula returns an error, paste the error back into Claude with the formula and the data in that cell. It will usually identify the problem immediately.
How to stay safe
Do not paste real personal or sensitive data into any consumer AI tool. That means no salary data, no customer records, no employee performance data, no financial figures that are not already public. The formula does not need the real data. Replace values with dummy numbers and realistic fake names before pasting anything as an example.
For numbers that are going into a report, a board pack, or anywhere a decision will be made from them, verify every total before it leaves your hands. AI can be confidently wrong about a calculation. It can also generate a formula that works on your sample data and breaks on a different data structure in the same file. The formula is a starting point. Your judgement about whether the output is plausible is the check.
If you are using Copilot or Gemini in-app and the tool can see your actual spreadsheet, treat it the same way you would treat giving a colleague access to the file. Make sure the data in that file is appropriate to share.
Start with a formula question you have been putting off. Describe the columns, describe the result, ask for the explanation. You will have a working formula and an understanding of it in under two minutes.