ChatGPT for Excel: Write Formulas, Analyze Data, and Automate Tasks

Excel formulas used to require memorizing cryptic syntax or endless Googling. Now you can just ask ChatGPT.

Whether you need a complex VLOOKUP, want to analyze sales data, or need to automate repetitive tasks with macros, ChatGPT can help. Here’s how to use it effectively.

What ChatGPT Can Do for Excel

Let’s be clear about capabilities:

ChatGPT Excels At:

• ✅ Writing formulas from plain English descriptions
• ✅ Explaining what complex formulas do
• ✅ Debugging formulas that aren’t working
• ✅ Suggesting better approaches to problems
• ✅ Creating VBA macros and scripts
• ✅ Analyzing uploaded spreadsheets (free and Plus)
• ✅ Teaching Excel concepts

ChatGPT Cannot:

• ❌ Edit your Excel file directly
• ❌ Connect to live Excel data
• ❌ Run inside Excel (that’s Copilot)
• ❌ Access your computer’s files automatically

Bottom line: ChatGPT is your Excel consultant. You still copy-paste the formulas yourself.

---

Getting Started: Three Ways to Use ChatGPT for Excel

Method 1: Describe Your Problem (Works for Everyone)

Simply tell ChatGPT what you’re trying to do:

You: “I have sales data in column A (product names) and column B (sales amounts). I need a formula to sum all sales for products containing ‘Premium’ in the name.”

ChatGPT:

=SUMIF(A:A,"*Premium*",B:B)

This works with the free tier. Just describe your spreadsheet structure clearly.

Method 2: Upload Your Spreadsheet (Free and Plus)

Both free and Plus users can upload .xlsx files directly:

1. Click the attachment icon in ChatGPT
2. Select your Excel file
3. Ask questions about your actual data

Example prompts after uploading:

• “Summarize this data”
• “Find any anomalies or outliers”
• “Create a formula to calculate monthly growth”
• “What trends do you see?”

ChatGPT sees your actual column names, data types, and values — giving much more accurate help.

Note: Free users can upload up to 3 files per day (~50 MB for spreadsheets, 512 MB for other files). Plus users get significantly higher limits.

Method 3: Screenshot Your Spreadsheet

Prefer not to upload your file? Take a screenshot instead:

1. Screenshot the relevant part of your spreadsheet
2. Upload the image to ChatGPT
3. Ask your question

ChatGPT can read the structure and give formula help based on what it sees. This is also useful when you’re working with sensitive data you’d rather not upload as a file.

---

Formula Help: From Basic to Advanced

Simple Formulas

“Sum column B”

=SUM(B:B)

“Average of cells B2 to B100”

=AVERAGE(B2:B100)

“Count non-empty cells in column A”

=COUNTA(A:A)

ChatGPT handles these instantly, but the real value is in complex formulas.

VLOOKUP and INDEX-MATCH

“I need to look up a price from a separate table. Product name is in A2, price table is in Sheet2 columns A and B”

=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

“Same thing but the lookup column isn’t the first column”

=INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))

ChatGPT will explain when to use VLOOKUP vs INDEX-MATCH and why.

Conditional Formulas

“If sales > 1000, show ‘High’, if > 500 show ‘Medium’, otherwise ‘Low’”

=IF(B2>1000,"High",IF(B2>500,"Medium","Low"))

“Sum sales only where region is ‘East’ and month is ‘January‘“

=SUMIFS(C:C,A:A,"East",B:B,"January")

Text Manipulation

“Extract the first name from a full name in A2”

=LEFT(A2,FIND(" ",A2)-1)

“Combine first name in A2 and last name in B2 with a space”

=A2&" "&B2

Or using CONCAT (useful when joining ranges):

=CONCAT(A2," ",B2)

Date Formulas

“Calculate age from birthdate in A2”

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

“Get the last day of the month for a date in A2”

=EOMONTH(A2,0)

“Find the number of business days between two dates”

=NETWORKDAYS(A2,B2)

Array Formulas (The Powerful Stuff)

“Find the second largest value in column B”

=LARGE(B:B,2)

“Sum the top 5 values in column B”

=SUM(LARGE(B2:B100,{1,2,3,4,5}))

“Count unique values in column A”

Modern Excel (365/2021+):

=ROWS(UNIQUE(A2:A100))

Older Excel:

=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

---

Real-World Examples

Example 1: Sales Commission Calculator

Your situation: “I have a sales table with columns: Salesperson (A), Sales Amount (B), Region (C). Commission is 5% for sales under $10,000, 7% for $10,000-$50,000, and 10% for above $50,000.”

ChatGPT’s formula:

=IF(B2>50000,B2*0.1,IF(B2>=10000,B2*0.07,B2*0.05))

Bonus: Ask ChatGPT to explain the formula, and it will break down each part.

Example 2: Dynamic Dashboard Metrics

Your situation: “I need to calculate Month-over-Month growth percentage. Current month sales in B2, previous month in B3.”

ChatGPT’s formula:

=(B2-B3)/B3*100

Or formatted as percentage:

=(B2-B3)/B3

(Then format cell as percentage)

Handle division by zero:

=IF(B3=0,"N/A",(B2-B3)/B3)

Example 3: Data Cleanup

Your situation: “Column A has names in all caps. I need proper capitalization.”

ChatGPT’s formula:

=PROPER(A2)

Your situation: “Remove extra spaces from messy data in A2”

=TRIM(A2)

Your situation: “Extract numbers from text like ‘Order #12345’”

=TEXTJOIN("",TRUE,IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

(This is where ChatGPT really shines — who wants to figure that out manually?)

Example 4: Conditional Formatting Formula

Your situation: “I want to highlight rows where sales are below average”

ChatGPT’s answer:

1. Select your data range
2. Conditional Formatting → New Rule → Use a formula
3. Enter: =$B2<AVERAGE($B:$B)
4. Choose your highlight color

ChatGPT gives you the formula AND the steps to apply it.

---

Creating VBA Macros with ChatGPT

Need to automate repetitive tasks? ChatGPT writes VBA code too.

Example: Auto-Format New Data

Your request: “Write a macro that formats any new data I paste: bold headers, auto-fit columns, add borders”

ChatGPT’s VBA:

Sub FormatNewData()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    ' Find the used range
    Dim dataRange As Range
    Set dataRange = ws.UsedRange

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

    ' Auto-fit all columns
    dataRange.Columns.AutoFit

    ' Add borders
    With dataRange.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

    MsgBox "Formatting complete!"
End Sub

How to use it:

1. Press Alt + F11 to open VBA editor
2. Insert → Module
3. Paste the code
4. Close editor
5. Run with Alt + F8

Example: Batch Process Files

Your request: “Write a macro to combine all .xlsx files from a folder into one sheet”

ChatGPT will generate a complete VBA script that:

• Opens a folder picker dialog
• Loops through all Excel files
• Copies data to a master sheet
• Handles headers properly

This kind of automation used to require hiring a developer or hours of learning VBA.

---

Analyzing Data with ChatGPT

Upload and Explore

After uploading a spreadsheet (available on both free and Plus tiers), try these prompts:

Initial exploration:

• “What’s in this spreadsheet?”
• “Summarize the key metrics”
• “What’s the date range of this data?”

Finding insights:

• “What trends do you see?”
• “Are there any outliers or anomalies?”
• “Which product category performs best?”

Specific analysis:

• “Calculate the correlation between marketing spend and sales”
• “What’s the average order value by region?”
• “Show me month-over-month growth”

Creating Visualizations

Ask ChatGPT:

• “Create a bar chart of sales by region”
• “Show me a trend line of monthly revenue”
• “Make a pie chart of market share”

ChatGPT will generate charts using its built-in data analysis feature. You can download the chart or ask for modifications.

---

Pro Tips for Better Results

1. Be Specific About Your Data

Bad: “Write a formula for commission”

Good: “Write a formula for commission. Sales amount is in column C, starting row 2. Commission is 5% for sales under $1000, 8% for $1000-$5000, 12% above $5000.”

2. Mention Your Excel Version

Some functions only work in newer Excel:

Include this: “I’m using Excel 365” or “I’m using Excel 2019”

ChatGPT will avoid suggesting functions your version doesn’t support.

3. Describe the Expected Output

Good: “I need a formula that returns TRUE if both conditions are met, FALSE otherwise”

This prevents ambiguity about what you want.

4. Ask for Explanations

Add: “Explain how this formula works step by step”

Understanding the formula helps you modify it later and builds your Excel skills.

5. Request Error Handling

Add: “Include error handling for blank cells and division by zero”

ChatGPT will wrap your formula in appropriate IFERROR or IF statements.

6. Test Edge Cases

After getting a formula, ask:

• “What happens if the cell is empty?”
• “What if there’s no match?”
• “Will this work with negative numbers?”

---

ChatGPT vs Microsoft Copilot for Excel

Both can help with Excel, but they work differently:

Feature	ChatGPT	Copilot in Excel
Works inside Excel	No	Yes
Edits cells directly	No	Yes
Formula explanations	Excellent	Good
Complex formula help	Excellent	Very Good
VBA macro creation	Excellent	Limited
Learning/teaching	Excellent	Good
Live data analysis	No	Yes
Price	$20/mo (Plus)	Included in M365 Personal/Family; $30/mo (Copilot for Business)
Free option	Yes	Yes (Copilot Chat — limited)

Important: Copilot in Excel requires a Microsoft 365 subscription (Personal, Family, or Business). The standalone Copilot Pro plan was discontinued in late 2025. Free Copilot Chat is rolling out inside M365 apps with basic AI features, though premium capabilities like editing cells directly still require a paid plan.

When to Use Each:

Use ChatGPT when:

• You need to understand how a formula works
• You’re creating complex or nested formulas
• You need VBA macros
• You want detailed explanations
• You’re on a budget (free tier works for file uploads and formula help)

Use Copilot when:

• You want AI directly in Excel
• You need to analyze live data without uploading
• You want one-click formula insertion
• You use Microsoft 365 heavily already

Use both when:

• Learn with ChatGPT, execute with Copilot
• Use ChatGPT for complex formulas, Copilot for quick tasks

For a detailed comparison, see our Microsoft Copilot Review.

---

Common Excel Tasks: Quick Reference

Here are prompts you can copy-paste for common tasks:

Lookups

Write an INDEX-MATCH formula to look up [value] from [source column]
and return the corresponding value from [result column].

Conditional Sums

Write a SUMIFS formula to sum [sum column] where [condition column 1]
equals [value 1] AND [condition column 2] is greater than [value 2].

Date Calculations

Write a formula to calculate the number of [days/months/years]
between the date in [cell 1] and [cell 2/today].

Text Extraction

Write a formula to extract [first X characters / everything after
the @ symbol / numbers only] from [cell].

Data Validation

Write a formula for data validation that only allows [specific
criteria, e.g., dates in the future, numbers between X and Y].

Pivot Table Alternative

Write a formula to summarize [column] by [category column],
showing [sum/average/count] for each category.

---

Limitations to Know

ChatGPT Can Get Formulas Wrong

Always test formulas before using them on important data:

• Try with a small sample first
• Verify the output makes sense
• Check edge cases (empty cells, zeros, negative numbers)

Complex Spreadsheets Need Context

For intricate spreadsheets with multiple sheets and references:

• Upload the file for best results (available on free and Plus)
• Or describe the structure in detail
• Include sample data in your prompt

Some Functions Are Version-Specific

ChatGPT might suggest:

• XLOOKUP (Excel 365/2021+ only)
• FILTER (Excel 365/2021+ only)
• Dynamic arrays (Excel 365/2021+ only)

Mention your version to avoid compatibility issues.

No Real-Time Connection

ChatGPT can’t:

• See changes you make after uploading
• Update formulas automatically
• Connect to your live workbooks

You’ll always copy-paste manually.

---

Getting Started Today

1. Try a simple formula first

   • Open ChatGPT (free works)
   • Ask: “Write an Excel formula to [your task]”
   • Copy the result to Excel and test

2. Build complexity gradually

   • Start with basic formulas
   • Move to conditional logic
   • Try lookup functions
   • Experiment with VBA

3. Upload a real spreadsheet

   • Works on both free and Plus (free allows up to 3 files/day)
   • Use a copy, not your original
   • Ask for analysis and suggestions
   • Request specific formulas for your columns

4. Learn while you solve

   • Always ask “explain this formula”
   • Build your Excel knowledge
   • Eventually you’ll need ChatGPT less

---

Related Articles

• ChatGPT Mastery Guide — Everything you need to know about ChatGPT
• Copilot in Word Tutorial — AI in Microsoft Office
• Microsoft Copilot Review — Is Copilot worth it?
• AI Tools Guide — All AI tools for work compared
• Best AI Tools for Office Work — Curated productivity tools