How to Use ChatGPT for Excel Formulas and Analysis

Every Excel workshop I run, someone asks a version of the same question: "Sagnik, I know what result I need — I just don't know which formula to use." That's exactly the problem ChatGPT solves brilliantly. You describe what you want in plain English, and it hands you a formula ready to paste into your spreadsheet.

I've been running Excel and AI integration workshops for corporate teams across India for several years now, and ChatGPT has become one of the most democratising tools I've seen land in the hands of non-technical professionals. It doesn't replace the need to understand Excel — but it dramatically lowers the barrier to doing sophisticated work. In this guide I'll walk you through exactly how to use it effectively, with real prompts, real formulas, and the honest limitations you need to know about.

Why ChatGPT Is a Surprisingly Capable Excel Assistant

ChatGPT has been trained on an enormous volume of technical documentation, Stack Overflow threads, forum discussions, and tutorial content — which means it has absorbed a vast working knowledge of Excel functions, their syntax, their quirks, and common patterns for combining them.

What makes it particularly useful in practice is that it doesn't just regurgitate function signatures. It reasons about your specific problem. Give it context about your spreadsheet layout and it will adapt its answer to your column letters, your data types, and your intended output. Ask a follow-up question and it remembers what you told it earlier in the conversation.

Here's what ChatGPT genuinely does well for Excel work:

• Formula generation from plain English — describe the outcome, get the formula
• Explaining formulas you inherited — paste in a formula someone else wrote and ask what it does
• Suggesting error handling — wrapping formulas in IFERROR, IFNA, and similar guards
• Writing VBA macros — even if you've never opened the Visual Basic editor
• Data cleaning strategies — step-by-step approaches to restructuring messy imports
• Building on your existing formula — paste what you have, explain what's broken, get a fix

It's not infallible, and I'll cover the pitfalls later. But as a starting point for complex work, it consistently impresses my workshop participants.

The Art of Prompting ChatGPT for Excel

The single biggest factor in getting a useful response from ChatGPT is the quality of your prompt. Vague questions produce vague answers. Specific, structured prompts produce formulas you can use immediately.

Here's the prompting framework I teach in my corporate Excel AI sessions:

"I have an Excel spreadsheet where [describe your columns and what data they contain]. I need a formula in [target cell/column] that [describe exactly what result you want, including any conditions]. I'm using [Excel version or Microsoft 365]."

The version detail matters more than people realise. Functions like XLOOKUP, LET, and LAMBDA are only available in Microsoft 365 and Excel 2021+. If you're on Excel 2016 or 2019, ChatGPT needs to know that so it doesn't hand you a formula your version can't evaluate.

Here are a few prompting techniques that consistently yield better results:

• Paste a sample of your data — even 5 rows of dummy data gives ChatGPT the context it needs to write precise formulas with correct column references
• State what you've already tried — "I tried VLOOKUP but it only returns the first match" tells ChatGPT exactly where to steer you
• Ask for alternatives — follow up with "Can you give me a version that doesn't use an array formula?" or "Is there a simpler approach?"
• Request an explanation — always ask ChatGPT to explain the formula it gives you; this is how you build your own knowledge rather than staying dependent on it

Writing Complex Formulas with ChatGPT

Let's get into real examples. These are the kinds of formula requests that typically take professionals 15–30 minutes of Googling and trial-and-error — and ChatGPT handles them in seconds.

Multi-Condition Summing

Suppose you have a sales report: Column A is the sales rep's name, Column B is the product category, Column C is the region, and Column D is the revenue. You want to sum all revenue where the rep is "Priya" and the region is "West".

Prompt: "Sum all values in column D where column A equals 'Priya' and column C equals 'West'."

=SUMIFS(D:D, A:A, "Priya", C:C, "West")

ChatGPT will also note you can replace the hardcoded strings with cell references, making the formula dynamic — which is exactly what I'd push you toward in a workshop setting.

Extracting Text with Conditions

You have a column of email addresses and need to extract just the domain name (everything after the @ symbol).

Prompt: "Extract the domain from email addresses in column A — everything after the @ symbol."

=MID(A2, FIND("@", A2) + 1, LEN(A2) - FIND("@", A2))

On Microsoft 365, ChatGPT might offer the cleaner TEXTAFTER alternative:

=TEXTAFTER(A2, "@")

This is exactly why specifying your Excel version matters — you might be handed a more powerful function you didn't even know existed. For a full view of which lookup and text functions to use when, the VLOOKUP vs XLOOKUP comparison is worth reading alongside this.

Ranking with Ties Handled Correctly

Standard RANK breaks ties arbitrarily. Ask ChatGPT for a formula that assigns the same rank to tied values and skips the next rank number accordingly, and it will give you RANK.EQ — or walk you through a COUNTIF-based approach for older versions.

=RANK.EQ(B2, $B$2:$B$100, 0)

For a deeper exploration of the formulas that handle these kinds of nuanced professional requirements, see the 15 advanced Excel formulas guide.

Cleaning and Transforming Data with ChatGPT's Help

One of the most time-consuming parts of real-world Excel work isn't the analysis — it's getting the data into a usable shape first. ChatGPT is a genuinely excellent partner for this.

In my workshops, I regularly see data that arrives from legacy systems or third-party exports with problems like: leading and trailing spaces, inconsistent capitalisation, dates stored as text, numbers formatted as text, merged cells that break everything downstream, and columns that need splitting or combining.

For each of these, ChatGPT can generate both formula-based fixes and step-by-step instructions for using Excel's built-in tools like Text to Columns or Flash Fill. Some useful prompts:

• "Remove all leading and trailing spaces from column B and convert it to proper case."
• "Column C has dates stored as text in the format DD-MM-YYYY. Give me a formula to convert them to proper Excel dates."
• "Split a full name in column A into first name and last name in separate columns."
• "Column D has numbers formatted as text — some have commas as thousands separators. Convert them to numeric values."

For the dates-as-text problem, ChatGPT typically gives you something like:

=DATEVALUE(TEXT(A2,"DD-MM-YYYY"))

-- or, more directly for DD-MM-YYYY text strings:
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

For a broader strategy on tackling messy imported data, the post on cleaning messy data in Excel fast pairs well with using ChatGPT as your formula generator.

Generating VBA Macros Without Knowing VBA

This is where ChatGPT genuinely levels the playing field. VBA — Excel's built-in automation language — has a steep syntax curve. Most professionals never learn it because the upfront investment feels too high relative to their day-to-day needs. ChatGPT removes that barrier almost entirely.

You describe what you want to automate in plain English, and ChatGPT writes the macro. You paste it into the Visual Basic Editor (Alt + F11, then Insert → Module) and run it. That's it.

Here's an example. Prompt: "Write a VBA macro that loops through all rows in Sheet1, and if the value in column C is less than zero, highlights that entire row in red."

Sub HighlightNegativeRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    For i = 2 To lastRow
        If ws.Cells(i, 3).Value < 0 Then
            ws.Rows(i).Interior.Color = RGB(255, 0, 0)
        Else
            ws.Rows(i).Interior.ColorIndex = xlNone
        End If
    Next i
End Sub

ChatGPT will also explain what each section of the macro does, which is valuable if you need to modify it later or explain it to a colleague. A couple of practical tips for getting good macros out of ChatGPT:

• Always specify your sheet name and which columns or ranges are involved
• Mention whether the macro should run once or be triggered by an event (like opening the file or changing a cell)
• Ask ChatGPT to add error handling so the macro doesn't crash silently on unexpected data
• Test on a copy of your file first — macros that modify data can't be undone with Ctrl+Z

ChatGPT vs Claude vs Microsoft Copilot for Excel

Since my workshops often cover multiple AI tools, I get this comparison question constantly. Here's an honest summary based on my hands-on experience with all three:

Capability	ChatGPT	Claude	Microsoft Copilot
Formula generation	Excellent	Excellent	Good (in-app)
VBA macro writing	Excellent	Excellent	Limited
Live spreadsheet access	Via file upload (GPT-4o)	Via file upload	Native, no upload needed
Explaining existing formulas	Very good	Very good	Good
Data analysis on your file	Strong with Code Interpreter	Good	Strong (native integration)
Availability	Standalone (browser/app)	Standalone (browser/app)	Requires Microsoft 365
Cost	Free tier + paid plans	Free tier + paid plans	Included with M365 Business

My practical advice: if your organisation already has Microsoft 365 Business or Enterprise, Copilot's native integration means you don't have to copy-paste between a chat window and Excel. But for formula generation and macro writing outside of Excel — particularly when you're working on a complex problem and want back-and-forth conversation — ChatGPT and Claude are both outstanding. The right tool depends on your workflow, not a ranking.

Common Pitfalls and How to Avoid Them

I want to be upfront about where ChatGPT can let you down, because I've seen workshop participants run into these and lose confidence in the tool when a few simple habits would have prevented the issue.

1. Formula hallucinations — ChatGPT occasionally invents function names or syntax that doesn't exist in Excel. Always test every formula it gives you before putting it into production use. If you get a #NAME? error, paste the formula back and ask ChatGPT why it's failing.
2. Version mismatches — Functions like XLOOKUP, FILTER, UNIQUE, LET, and LAMBDA are not available in Excel 2016 or 2019. If you don't specify your version, ChatGPT may default to Microsoft 365 syntax. Always state your version upfront.
3. Array formula handling — In older Excel versions, some formulas need to be entered with Ctrl+Shift+Enter to work as array formulas. ChatGPT usually mentions this, but if a formula returns a wrong result, ask specifically: "Does this need to be entered as an array formula?"
4. Regional separator settings — If your Excel uses semicolons instead of commas as argument separators (common in European and some Asian locale settings), tell ChatGPT: "I use semicolons as separators in my Excel." It will adjust accordingly.
5. Blind trust in macros — Always read through a macro before running it, even briefly. ChatGPT-generated macros are generally safe but occasionally contain logic errors, particularly around range assumptions. Test on a copy of your file.
6. Not asking follow-up questions — The biggest waste I see is people taking the first response and walking away. ChatGPT works best as a conversation. Ask it to simplify the formula. Ask it to explain what each argument does. Ask it to handle blank cells. This iterative approach is where the real value is.

Putting It All Together

ChatGPT won't replace knowing Excel — and I'd argue you shouldn't want it to. Understanding why a formula works makes you faster, more adaptable, and more capable when AI tools aren't available or give you a wrong answer. What ChatGPT does is collapse the time between "I know what I need" and "I have a formula that does it."

The professionals I see getting the most value from it are those who use it as a thinking partner: they describe their problem clearly, ask for the formula, ask ChatGPT to explain it, then deliberately try to understand the logic. After a few months of working this way, they've absorbed a working knowledge of functions they'd never have bothered to learn from scratch.

Start simple. Take the formula you hate most — the one you Google every single time — and ask ChatGPT to write it for you and explain it. Build from there. Within a few sessions, you'll have a workflow that makes you meaningfully faster at everything Excel-related.

Related Posts

• How to Use Google Gemini for Excel Formulas and Data
• How to Use Claude AI to Write Excel Formulas Instantly
• Using Claude AI to Generate Excel Macros and VBA
• Excel Copilot for Data Analysis — A Practical Guide