ChatGPT for Excel: Formulas, Tables, Analysis
If you describe the task you want done to ChatGPT or Claude in plain language, it will write the Excel formula, VBA macro, or data-cleaning steps for you. You don’t need to know technical terms like VLOOKUP, INDEX-MATCH, or pivot table logic — you say “compare the lists on these two tabs, give me the ones that appear in both,” and you get the formula. The same goes for chart suggestions, data-cleaning steps, and macros for repetitive tasks. On this page I walk through how it works with a real formula request, which tasks it’s genuinely useful for, and what you need to watch out for.
What It’s Used For
If you tell tools like ChatGPT and Claude what you want to accomplish instead of just saying “do this Excel task,” they’ll handle most of the tasks below for you. Excel is just one part of this automation — for the broader picture of which tasks you can automate with AI, see the AI Automation Guide.
| Task | Example request |
|---|---|
| Writing complex formulas | “Compare the lists on these two tabs, give me the ones that appear in both” |
| Data cleaning | “Convert the dates in this column to a single format, remove duplicates” |
| Building pivot tables | “Pull a monthly sales summary from this data, broken down by product” |
| Chart suggestions | “Which chart type suits this data better, and why?” |
| Writing VBA/macros | “Turn this weekly tab-merging task I do into a macro” |
You don’t need to memorize formulas like VLOOKUP, INDEX-MATCH, XLOOKUP, or SUMIFS — describe what you want and it produces the right formula, and even tells you which one better fits your situation.
A Concrete Example: A Formula to Compare Two Lists
Example request: “I have separate customer lists on tab A and tab B, and I want to find the customers that appear on both.” In response, ChatGPT typically produces a formula like this:
=IF(COUNTIF(SheetB!A:A, A2)>0, "On both", "Only on A")
For larger lists, or when you need to compare multiple columns together, it suggests an XLOOKUP-based alternative:
=IFERROR(XLOOKUP(A2, SheetB!A:A, SheetB!A:A, "Missing"), "Missing")
When asking for the formula, you also need to specify details like which column is on which tab, whether there’s a header row, and case sensitivity — the more precisely you describe it, the more accurate the formula. If you need to do this kind of comparison regularly, at scale, or across multiple files, check out my Comparing Two Excel Lists service.
Data Cleaning, Pivot Tables, and Chart Suggestions
For data-cleaning requests, you get the best results by describing step by step what needs to happen: when you say “this column has ‘Istanbul,’ ‘istanbul,’ and ‘IST’ — convert them all to one form,” it gives you both the formula and the order in which to apply it. Large, messy datasets need a multi-turn process rather than a single one-off prompt — if you need this done regularly, check out my Excel Data Cleaning service.
When building a pivot table, you can ask which field should go in rows, which in values, and which filters will help. For chart selection, it can tell you — based on your data — whether a line chart, a stacked column, or a pie chart is more appropriate, and which settings to choose. If you need to turn these two into a weekly/monthly report, check out my Excel Report Preparation service.
Writing VBA/Macros
You can turn a repetitive task you do by hand every week — merging tabs, formatting, saving files to a specific folder — into a macro. The more concretely you describe your request (which tab, which column, what happens under which condition), the less editing the macro needs afterward.
Example request: “Write a macro that merges 5 different Excel files that come in every month into a single sheet, keeping the header row only once.” ChatGPT produces VBA code for this request; you paste the code into the Visual Basic editor from Excel’s Developer tab and run it. If you need to automate this kind of task regularly, across multiple files, check out my Excel Automation service.
What to Watch Out For
The formula ChatGPT produces doesn’t always work correctly — especially with nested formulas, or errors stemming from the fact that Turkish Excel uses a semicolon (;) instead of a comma (,) as the argument separator. Don’t paste every generated formula straight into a live spreadsheet without testing it on a small sample first.
Second, and more important: data privacy. Think before pasting sensitive data — a customer list, a price quote, a payroll sheet — directly into ChatGPT. Using fake/sample data to test a formula is usually enough — the formula’s logic stays the same, only the data changes.
Third: using a formula ChatGPT produced without understanding why it works means nobody will be able to update it later. At the very least, ask it to summarize the formula’s logic in one sentence and note it in the file. If you want to clarify the difference between a one-off prompt and a repeatable, organizational process, see my Prompt vs Skill vs Agent piece.
Speeding Up Excel Work With AI While Managing 43 Accounts
While managing 43 ad accounts on my own, I frequently build Excel/Google Sheets tables to compare weekly budget, conversion, and cost data. Instead of manually comparing 43 accounts one by one, I get ChatGPT to write the formula that finds which accounts are above target CPA, plus the weekly summary table — I could write the formula myself too, but this cuts a job that takes minutes down to seconds.
What I’ve noticed: AI builds the skeleton of the formula quickly, but since it doesn’t know my account structure, my column names, or exceptions (like some accounts using a different currency), the first generated formula almost never works exactly as-is — it takes one or two rounds of correction. So the expectation of “a perfect table from one prompt” isn’t realistic; what saves time is that the correction rounds are much faster than doing it all by hand.
You Can Ask Me for This Work
If it’s a one-off Excel formula, ChatGPT alone is probably enough. But if it’s a recurring report, merging multiple files, or a process that needs to be automated with a macro, you can ask me for this work — it’s remote, hourly-billed work that typically takes 2 hours to 2 weeks. You can write via the contact page, or check out my Excel Automation service directly.
Frequently Asked Questions
Does ChatGPT always write correct Excel formulas?
No. Errors are common, especially with nested formulas or the difference in Turkish Excel’s separator rules (semicolon vs. comma). Don’t paste a generated formula into a live spreadsheet without testing it on a small sample first.
What data shouldn’t I paste into ChatGPT?
Think before pasting sensitive data directly — a customer list, a price quote, a payroll sheet. Fake/sample data is usually enough to test a formula’s logic; you don’t need real data.
Is ChatGPT better, or Excel’s own AI feature (Copilot)?
Since Copilot works directly inside the file, it writes the formula into the cell itself and automatically sees the context (column headers, data type). ChatGPT is more flexible and can be used for free, but you have to copy and paste the formula yourself. Either way, you need to verify the formula it produces.
What information do I need to give ChatGPT to write a VBA macro?
Describe as clearly as possible which tabs/files it’ll work with, what should happen under which condition, and your existing column/header names. The more concretely you describe it, the less editing the macro needs.