Can ChatGPT write Excel formulas correctly?

Yes, ChatGPT is quite good at writing Excel formulas when you describe what you need clearly. It handles VLOOKUP, INDEX/MATCH, SUMIFS, nested IFs, and array formulas well. Always test the output in a copy of your spreadsheet first — occasionally it misses a nuance specific to your data structure.

Does ChatGPT work with Google Sheets too?

Yes. Most Excel formulas work in Google Sheets with minor syntax differences. ChatGPT can write formulas specifically for Google Sheets if you mention it in your prompt. Functions like QUERY, IMPORTRANGE, and ARRAYFORMULA are Google Sheets-specific and ChatGPT handles them well.

Is it safe to use ChatGPT-generated VBA macros?

ChatGPT-generated VBA is generally safe for standard operations — formatting, data manipulation, automation. Before running any macro, read through it and understand what it does. Never run a macro from an unknown source on a file containing sensitive data without reviewing the code. Test on a copy first.

ChatGPT for Excel: Automate Spreadsheets in Seconds

I've watched accountants spend four hours building a formula that ChatGPT can generate in 30 seconds. That's not a slight — it's just how fast the landscape has changed. I used to be that accountant, honestly, wrestling with nested IF statements and VLOOKUP edge cases at 9pm.

Now I describe what I need in plain English and get a working formula back in moments. This guide gives you over 10 actual prompts, with the exact Excel formulas they produce, plus a VBA macro example that shows how far this can go.

Whether you're doing month-end close, building financial models, or just trying to clean up a messy data export, these prompts will change how you interact with Excel.

Why ChatGPT Handles Excel So Well

Excel's formula language is formal and structured — exactly the kind of thing language models are trained on extensively. Unlike creative writing, there's a right answer to "write a SUMIFS formula that totals column C where column A equals 'East' and column B is after 1/1/2026." Either it works or it doesn't.

That objectivity makes it easy to verify ChatGPT's output. Paste the formula, check if it returns the right number. If not, paste the error back and ask it to fix the formula. This feedback loop is faster than searching Stack Overflow or Excel forums.

10 Copy-Paste Excel Formula Prompts

Prompt 1: Multi-Condition Lookup

"Write an Excel formula that looks up a value from Sheet2 where two conditions must match: column A in Sheet2 equals the value in A2 of the current sheet, and column B in Sheet2 equals the value in B2. Return the corresponding value from column C of Sheet2."

Output:

=INDEX(Sheet2!C:C, MATCH(1, (Sheet2!A:A=A2)*(Sheet2!B:B=B2), 0))

Enter as an array formula with Ctrl+Shift+Enter in older Excel versions.

Prompt 2: Dynamic Running Total

"Write an Excel formula for a running total in column D where each row adds the current row's value in column C to the sum of all previous rows. The data starts at row 2."

Output:

=SUM($C$2:C2)

Drag this down and it creates a cumulative total automatically.

Prompt 3: Extract Month and Year for Pivot Grouping

"I have dates in column A formatted as MM/DD/YYYY. Write a formula for column B that returns just the month name (like 'January') and a formula for column C that returns the year as a 4-digit number."

Output:

=TEXT(A2,"MMMM")
=YEAR(A2)

Prompt 4: Flag Duplicates Across Two Columns

"I have employee names in column A and department names in column B. Write a formula that returns 'Duplicate' if the combination of name and department appears more than once in the dataset, and 'Unique' otherwise."

Output:

=IF(COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2)>1,"Duplicate","Unique")

When I first ran this on a 4,000-row HR export, it found 23 duplicates a manual review had missed. The model's ability to think about the data structure rather than just the syntax is genuinely impressive.

Prompt 5: Age Calculation with Error Handling

"Write an Excel formula to calculate someone's age in years from a birthdate in column A. If column A is empty, return a blank instead of an error."

Output:

=IF(A2="","",DATEDIF(A2,TODAY(),"Y"))

Prompt 6: Weighted Average

"I have values in column B and weights in column C. Write a formula for the weighted average of column B, where each value is multiplied by its weight in column C, then divided by the sum of all weights."

Output:

=SUMPRODUCT(B2:B100,C2:C100)/SUM(C2:C100)

Prompt 7: Dynamic Drop-Down List Based on Another Cell

"Write the Excel data validation formula for a drop-down in column B that shows different options depending on what's selected in column A. If A2 says 'Marketing', show options from a named range called MarketingList. If A2 says 'Sales', show options from SalesList."

This one requires a named range setup plus an indirect formula — ChatGPT explains both the formula and the setup steps, which is where it goes beyond just syntax.

Output (Data Validation formula):

=INDIRECT(A2&"List")

Then name your ranges "MarketingList" and "SalesList" accordingly.

Prompt 8: Text Cleaning — Remove Extra Spaces and Standardize Case

"I have messy text in column A with inconsistent spacing and capitalization. Write a formula that trims extra spaces and converts the text to proper case (first letter of each word capitalized)."

Output:

=PROPER(TRIM(A2))

Short formula, huge impact on messy data imports.

Prompt 9: XLOOKUP with Fallback Value

"Write an XLOOKUP formula that searches for the value in A2 in a table on Sheet3 column A, returns the value from Sheet3 column D, and returns 'Not Found' if there's no match."

Output:

=XLOOKUP(A2,Sheet3!A:A,Sheet3!D:D,"Not Found")

If you're still using VLOOKUP, this upgrade alone saves hours per year in formula debugging.

Prompt 10: Conditional Formatting Formula for Overdue Dates

"Write the conditional formatting formula for a rule that highlights a row in red if the date in column E is more than 30 days in the past and the status in column F is not 'Complete'."

Output:

=AND($E2<TODAY()-30,$F2<>"Complete")

Apply to the full row range in conditional formatting settings.

VBA Macro Example: Auto-Format Monthly Report

This is where ChatGPT really earns its keep for accountants. Writing VBA from scratch requires knowing the object model — Workbook, Worksheet, Range, Cell. ChatGPT knows all of it.

The prompt:

"Write a VBA macro for Excel that does the following: loops through all sheets in the workbook except 'Summary', applies currency formatting ($#,##0.00) to columns C through F, bolds the header row (row 1), auto-fits all column widths, and adds a solid light blue fill to row 1. The sheet names will vary."

Output:

Sub FormatAllSheets()
    Dim ws As Worksheet
    Dim lastCol As Integer

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            ' Apply currency format to columns C through F
            ws.Columns("C:F").NumberFormat = "$#,##0.00"

            ' Bold header row
            ws.Rows(1).Font.Bold = True

            ' Light blue fill on header row
            ws.Rows(1).Interior.Color = RGB(173, 216, 230)

            ' Auto-fit all columns
            ws.Cells.EntireColumn.AutoFit
        End If
    Next ws

    MsgBox "Formatting complete!", vbInformation
End Sub

I used a version of this to format a 14-tab monthly close workbook. What used to take 25 minutes of manual formatting now runs in 8 seconds. That's not an exaggeration.

For more ways to automate workflows, our ChatGPT plugins guide covers tools that connect ChatGPT directly to your data sources.

Data Analysis Prompts

Beyond formulas and macros, ChatGPT is useful for analyzing data you paste directly into the chat.

Describe your dataset and ask for analysis:

"Here's a CSV export of our monthly sales data [paste data]. What patterns do you see? Which products have declining month-over-month sales? Flag anything unusual."

ChatGPT with data analysis (formerly Code Interpreter) can actually process uploaded spreadsheets, run calculations, and return charts. For accountants, this is a faster path to exploratory analysis than building pivot tables from scratch.

According to Microsoft's research on AI in productivity tools, AI assistance in spreadsheet tasks reduces formula error rates significantly compared to manual entry — which matches my experience using these prompts on real financial data.

Debugging Formulas You Already Have

One underused ChatGPT skill: paste a broken formula and ask why it's not working.

Prompt:

"This Excel formula is returning a #VALUE! error and I can't figure out why: =SUMIFS(C:C,A:A,E2,B:B,'>='&F2). My dates in column B are formatted as text, not actual date values. How do I fix this?"

ChatGPT will diagnose the text-date issue, explain why it causes the error, and rewrite the formula with a DATEVALUE conversion. This is faster than forum searching, and the explanation actually teaches you something.

Tips for Getting Better Outputs

Tell ChatGPT your Excel version. Some functions (XLOOKUP, FILTER, UNIQUE) only work in Excel 2019+ and Microsoft 365. If you're on an older version, say so and it will use compatible alternatives.

Paste a sample of your data. Describing your columns abstractly works, but pasting 5 rows of actual data (removing any sensitive values) produces formulas that match your exact structure.

Ask for explanations. Adding "and explain what each part of the formula does" to your prompt turns every formula into a learning moment. After a few months of this, your own formula skills improve significantly.

See also our ChatGPT vs Claude comparison if you're curious how different models handle Excel tasks — there are real differences in how they approach complex formula logic.

Conclusion

ChatGPT has changed Excel work for me in a way I didn't expect — not by replacing the skill, but by removing the frustration from the repetitive parts. The hours I used to spend googling formula syntax or debugging VBA now go into actually interpreting results and making decisions.

The prompts in this guide are a starting point, not an endpoint. Once you understand the pattern — describe your data structure, specify the exact output you want, mention your Excel version — you can generate virtually any formula or macro you need.

Try Prompt 1 and Prompt 4 first. Those cover the two scenarios that slow down most data work: multi-condition lookups and duplicate detection. After that, the VBA example is worth your time even if you've never written a macro in your life.

For more AI productivity guides, check out our prompt engineering guide — the same principles that make these Excel prompts work apply to every ChatGPT use case.