NexframeAI
AI Productivity

ChatGPT for Excel: Formulas, Clean-Up and Analysis

Learn how to use ChatGPT for Excel: write XLOOKUP, SUMIFS and IF formulas, clean messy data, analyse uploaded sheets and keep business data private.

NEXFRAME AI 12 min read
ChatGPT for Excel: Formulas, Clean-Up and Analysis

You have a spreadsheet open, you know roughly what you want from it, and you are stuck on the formula. Maybe you need to pull prices from another sheet, total sales by month, or fix a column where half the dates are text. You could spend 40 minutes on forums, or you could describe the problem in plain English and get a working formula in seconds.

That is where using ChatGPT for Excel really shines. It will not replace knowing your data, but it is very good at turning "I want to add up transfers for the Ikeja branch in March" into a formula you can paste straight into a cell. It can also explain formulas you inherited, suggest clean-up steps, and analyse a file you upload.

This guide covers the prompts that work, the formulas you will use most (XLOOKUP, SUMIFS, IF, TEXT and friends), how to clean messy data, how to analyse an uploaded file, and the privacy checks you should run before sharing any business sheet. We finish with a worked example from a Lagos shop and answers to common questions.

Why Use ChatGPT for Excel at All?

Excel has hundreds of functions, and most people use about ten. The gap is not intelligence; it is vocabulary. You know what you want the sheet to do, but you do not know that the function is called SUMIFS or that it takes its arguments in a particular order.

ChatGPT closes that gap in three ways:

  • Translation: you describe the outcome in plain English, and it returns the formula.

  • Explanation: you paste a formula you do not understand, and it breaks it down piece by piece.

  • Analysis: you upload a file, and it summarises, groups, charts and spots patterns.

It works on the free plan, though limits on file uploads and advanced analysis are tighter. If you are deciding whether to pay, our comparison of ChatGPT Go vs Plus explains which tier makes sense for regular spreadsheet work.

Microsoft also builds Copilot into Excel for Microsoft 365 subscribers, and Google has Gemini in Sheets. The prompts in this guide work in any of them, but we will focus on ChatGPT because it is the tool most of our readers already have open.

How to Ask ChatGPT for an Excel Formula

The quality of the formula depends almost entirely on how well you describe your sheet. "Give me a formula to add sales" gets a generic answer. A precise prompt gets something you can paste.

Describe Your Columns Exactly

Tell ChatGPT the sheet name, the column letters, what each column holds, and where the data starts and ends. Mention your Excel version, because XLOOKUP and some newer functions are not available in Excel 2016 or older.

I use Excel [Microsoft 365 / Excel 2019 / Google Sheets].
My sheet is called [Sales]. Data is in rows 2 to [500].
Column A: Date (real dates, dd/mm/yyyy)
Column B: Product name
Column C: Category
Column D: Quantity
Column E: Unit price in naira
Column F: Payment method (Cash, Transfer, POS)
I want a formula in [cell H2] that [describe the result you want].
Give me the formula, then explain each part in one line.

Ask for the Explanation, Not Just the Answer

Always ask for a short explanation. It helps you spot when ChatGPT has misunderstood your layout, and it means you learn the function instead of copying it blindly. Our guide to writing better prompts covers this habit in more depth.

Give It a Test Case

Add one line such as "Row 5 should return ₦45,000." If the formula does not produce that number, you know immediately that something is wrong.

The Formulas You Will Use Most

Here are the functions that come up again and again, with example prompts and the kind of formula ChatGPT returns. Adjust the ranges to match your own sheet.

XLOOKUP: Find a Value From Another Table

Use XLOOKUP when you have a product code in one sheet and need the price, supplier or stock level from another.

Prompt: "In my Sales sheet, column B has product names. My Prices sheet has product names in column A and unit prices in column C. Write a formula for E2 that pulls the price, and shows 'Not found' if the product is missing."

=XLOOKUP(B2, Prices!A:A, Prices!C:C, "Not found")

If you are on an older version, ask for the INDEX and MATCH equivalent instead.

SUMIFS: Add Up With Conditions

SUMIFS totals a column only where other columns meet your conditions. It is the workhorse of small-business reporting.

Prompt: "Total column G (sales value) where column F is 'Transfer' and the date in column A is in March 2026."

=SUMIFS(G:G, F:F, "Transfer", A:A, ">="&DATE(2026,3,1), A:A, "<"&DATE(2026,4,1))

COUNTIFS and AVERAGEIFS follow the same pattern, so once you understand this one, you can ask for the others.

IF and IFS: Label Rows Automatically

Prompt: "In H2, show 'High' if G2 is ₦100,000 or more, 'Medium' if it is ₦30,000 or more, otherwise 'Low'."

=IFS(G2>=100000, "High", G2>=30000, "Medium", TRUE, "Low")

TEXT: Format Numbers and Dates for Reports

TEXT turns numbers and dates into readable labels, which is useful for summaries and WhatsApp-ready messages.

="Sales on "&TEXT(A2,"dd mmm yyyy")&": ₦"&TEXT(G2,"#,##0")

That returns something like "Sales on 14 Mar 2026: ₦45,000".

Other Useful Requests

  • UNIQUE and SORT: "Give me a sorted list of every product sold."

  • FILTER: "Show only rows where payment is POS and quantity is over 5."

  • TEXTBEFORE and TEXTAFTER: "Split 'Tolu Adeyemi' into first and last name."

  • Conditional formatting rules: "Write a rule that highlights rows where stock is below 10."

Cleaning Messy Data With ChatGPT

Most spreadsheet pain comes from dirty data rather than difficult formulas. Names typed three different ways, dates stored as text, phone numbers with and without +234, and extra spaces everywhere.

Ask for a Clean-Up Plan First

Paste ten or fifteen sample rows (with personal details removed) and ask ChatGPT to list every problem it sees before fixing anything.

Here are 15 sample rows from my sheet (personal details removed):
[paste rows]
List every data quality problem you can see, such as inconsistent spelling,
dates stored as text, extra spaces, duplicates or mixed units.
For each problem, suggest the simplest fix in Excel (formula, Flash Fill,
Find and Replace, or Remove Duplicates). Do not rewrite the data yet.

Common Fixes ChatGPT Will Suggest

  • TRIM and CLEAN to remove extra spaces and hidden characters.

  • PROPER to standardise names ("chiamaka OBI" becomes "Chiamaka Obi").

  • DATEVALUE or Text to Columns to turn text dates into real dates.

  • SUBSTITUTE to strip "₦" or commas from numbers stored as text.

  • Remove Duplicates on the Data tab, after you decide which columns define a duplicate.

Standardise Categories

If one staff member types "Phone case", another "phone cases" and another "Case", ask ChatGPT to suggest a mapping table, then use XLOOKUP to apply it. This is safer than letting the AI rewrite thousands of rows directly, because you can review the mapping in one place.

Analysing an Uploaded Spreadsheet

ChatGPT can read Excel and CSV files you upload, run calculations on them using code behind the scenes, and return summaries, tables and charts. This is the fastest way to get an overview of a sheet you did not build.

A Good First Prompt

I have uploaded my sales file for [period].
1. Describe the structure: sheets, columns, row count, date range.
2. Flag any data problems before analysing.
3. Then give me: total sales, sales by month, top 10 products by value,
   and the split by payment method.
Show your numbers in tables and tell me which columns you used for each.

Check the Numbers Before You Trust Them

AI analysis is fast, but it is not infallible. It can misread a column, include a total row twice, or quietly drop rows it could not parse. Pick two or three figures and check them yourself with a SUMIFS or a pivot table. Our explainer on AI hallucination explains why confident-sounding answers can still be wrong.

Turn the Analysis Back Into Excel

Once you like an insight, ask: "Give me the Excel formulas or pivot table steps to reproduce this in my own file." That way your report updates next month without re-uploading anything.

Privacy Cautions Before You Upload

A spreadsheet often holds more sensitive information than people realise: customer phone numbers, addresses, staff salaries, bank details, supplier prices.

  • Remove or mask personal data. Replace names with codes (Customer 001) and delete phone numbers, emails, account numbers and BVNs before uploading.

  • Use sample rows for formula help. You rarely need the full file to get a formula. Ten fake rows with the same structure are enough.

  • Check your data settings. In ChatGPT's Data Controls you can turn off the option that lets your chats be used to improve models. Business and Enterprise plans handle data differently by default, so check what your plan says.

  • Follow your employer's rules. Many companies forbid uploading internal files to consumer AI tools. If yours has an AI policy, read it first.

  • Remember the hidden trade-offs. Free tools are not free of costs; our guide to the hidden costs of free AI tools covers what you give up.

Worked Example: A Lagos Shop's Sales Sheet

Chiamaka runs a phone accessories shop in Computer Village, Ikeja. Her two sales staff log every sale in a shared Excel sheet: date, product, category, quantity, unit price in naira, payment method and staff name. After six months the sheet has about 2,400 rows, and she wants to know which products actually make money and how much comes in by transfer versus cash.

Step 1: Clean. She copies 15 rows into ChatGPT (no customer details are in the sheet) and asks for a list of problems. ChatGPT spots that "Tecno charger", "tecno Charger " and "Charger - Tecno" all appear, that some prices are typed as "₦3,500" text rather than numbers, and that a few dates are in mm/dd format. She uses TRIM and PROPER, a SUBSTITUTE formula to convert prices to numbers, and a small mapping table to standardise product names.

Step 2: Formulas. She adds a Total column (=D2*E2), then asks for a SUMIFS that totals transfers per month. Her summary sheet now shows that most of her income arrives by transfer, which she reconciles against her bank statement each week.

Step 3: Upload and analyse. She uploads the cleaned file and asks for the top ten products by value and by quantity. ChatGPT shows that screen protectors sell the most units, but power banks bring in the most money. She checks the power bank total with her own SUMIFS, and it matches.

Step 4: Margins. She adds a cost price column and asks for a margin formula. To sanity-check one product, she runs the numbers through our free profit margin calculator.

Step 5: Make it repeatable. She asks ChatGPT to write a one-page checklist her staff can follow when logging sales, so next month's sheet starts clean. Our SOP workflow shows how to turn checklists like this into a proper standard operating procedure.

The whole exercise took her an afternoon. If she paid for a higher plan with more file analysis, at roughly ₦8,000 to ₦32,000 a month depending on tier (about US$5 to US$20 at around ₦1,550 per dollar, and only an estimate, so check the current price in your app), it would pay for itself the first time it stopped her reordering a slow-selling product.

Common Mistakes to Avoid

  • Vague prompts. "Fix my formula" without the formula, the error and the layout wastes everyone's time.

  • Not mentioning your Excel version. XLOOKUP, FILTER and TEXTBEFORE will show #NAME? errors in older versions.

  • Pasting whole columns of personal data. Use sample or masked rows.

  • Trusting uploaded analysis without checking. Verify at least two numbers by hand.

  • Mixing up regional settings. Some Excel setups use semicolons instead of commas between arguments. If a formula fails, ask ChatGPT to convert it.

  • Hard-coding numbers. Ask for formulas that reference cells (a target in J1, for example) so you can change inputs without editing every formula.

  • Skipping the explanation. If you cannot explain the formula, you cannot fix it when the sheet changes.

Final Thoughts

ChatGPT for Excel is best thought of as a patient colleague who knows every function by heart. You still decide what the numbers mean and whether they are right. Start with one annoying task this week, such as a SUMIFS report or a clean-up job, and save the prompts that work so you can reuse them.

If your spreadsheets are mostly about money in and money out, our guide to AI bookkeeping for small businesses is a natural next step.

Frequently Asked Questions

Can ChatGPT edit my Excel file directly?

It can read an uploaded file, make changes using code and give you a new file to download. However, it does not work inside your open workbook the way Copilot in Excel does. For live editing, copy the formulas it gives you into your own sheet.

Is ChatGPT for Excel free to use?

Yes, the free plan can write and explain formulas. File uploads and data analysis are available on the free plan with tighter limits; paid plans such as Go and Plus raise them. Check the current limits in your app.

Why does the formula ChatGPT gave me return an error?

Usually the ranges do not match your layout, your Excel version lacks the function, or your region uses semicolons as separators. Paste the exact error message and your column layout back into the chat and ask it to fix the formula.

Does it work with Google Sheets?

Yes. Most formulas are identical, and ChatGPT can adjust the ones that differ, such as array formulas. Just say "Google Sheets" in your prompt.

Is it safe to upload my business spreadsheet?

It can be, if you remove personal and sensitive data first and check your data settings. Never upload customer bank details, BVNs or staff salary information to a consumer AI tool.

Can ChatGPT make charts and pivot tables?

It can create charts from uploaded data and describe the exact steps to build a pivot table in Excel. For anything you will reuse, ask for the steps so the chart lives in your own file.

Should I use ChatGPT or Copilot in Excel?

If you have Microsoft 365 with Copilot, it works inside your workbook, which is convenient. ChatGPT is often better for explaining concepts and for people without a Copilot licence. Many people use both.

Share

Comments (0)

Sign in to post a comment.

  • Be the first to comment.