ChatGPT

How to Use ChatGPT for Excel: Clean Data and Check Totals

How do I use ChatGPT to clean up data for Excel?

Give ChatGPT the column names and five rules: the date format, the allowed category values, what counts as a duplicate, the amount format and the payment values. Then paste your raw data. Don't rely on any total ChatGPT reports; paste the table into Excel, calculate with SUM and SUMIF, and make sure amounts aren't stored as text.

Key points

  1. A good cleanup prompt names the columns and sets five rules: date format, allowed category values, duplicates, amount format and payment values.
  2. Always recalculate ChatGPT's totals in Excel with SUM and SUMIF instead of copying its number.
  3. Excel's SUM skips numbers stored as text without showing an error, so a wrong total can look perfectly normal.

ChatGPT is very good at turning messy notes into tidy rows. It’s less reliable at adding them up. So split the job: let ChatGPT do the reshaping, and let Excel do the math. The example below uses eight lines of made-up expense notes.

The raw notes

3/2 coffee w/ client 14.50 card
Mar 3 - office supplies $42.10 (Staples)
3/2 coffee w/ client 14.50 card
2026-03-05 software sub 29
3/7 train to Boston 61.00
3/7 lunch 18.25 cash
3/9 printer ink 42.1
march 10 software sub 29.00

Note the mix of date formats, the possible duplicate coffee line, and “29” vs “29.00” for what may be the same charge.

The five-part prompt

Turn these notes into a table with exactly these columns:
Date | Description | Category | Amount | Payment

Rules:
1. Date: YYYY-MM-DD. Assume the year is 2026.
2. Category: only Meals, Travel, Supplies or Software.
3. Duplicates: same date, same description and same amount = duplicate.
   Don't delete duplicates. Add a column "Possible duplicate" with Yes/No.
4. Amount: number only, two decimals, no currency symbol.
5. Payment: card, cash or blank if not stated. Don't guess.

After the table, list any line you weren't sure about and why.
Don't calculate totals; I'll do that in Excel.

Each rule heads off a common mistake. Without rule 2, you get “Food”, “Meal” and “Lunch” as three separate categories. Without rule 3, ChatGPT may quietly delete a row that was a real second purchase. Without “don’t guess” in rule 5, it fills blanks with plausible values.

Check the result in Excel

Paste the table into Excel, with amounts in column D and categories in column C, then:

  1. Grand total: =SUM(D2:D9). For this example it should come to 250.45.
  2. Per category: =SUMIF(C2:C9,"Software",D2:D9) and so on for each category. The category totals should add up to the grand total.
  3. Row count: =COUNTA(A2:A9) should match the number of lines you started with, which is 8 here.
  4. Numbers really are numbers: =COUNT(D2:D9) counts only numeric cells, so it should also be 8. A lower number means some amounts were pasted as text.

For the sample notes, the correct grand total is 250.45: Meals 47.25, Supplies 84.20, Software 58.00 and Travel 61.00. If ChatGPT flagged the second coffee as a possible duplicate and you decide to drop it, the total becomes 235.95.

If any of these don’t match, find the row that’s off before you touch the formula.

The silent error: numbers stored as text

Microsoft’s documentation says SUM ignores text values. If an amount comes in as text, for example 42.10 with a trailing space or '29, SUM leaves it out and shows no error. The total is just smaller than it should be.

Signs to watch for:

  • Amounts that line up on the left of the cell instead of the right.
  • A small green triangle in the corner of the cell.
  • =ISNUMBER(D2) returning FALSE.

To fix them, select the cells and use the warning icon’s Convert to Number option, or use =VALUE(D2) in a helper column.

Which ChatGPT plan you need

Pasting text works fine on the free plan. If you regularly upload large spreadsheets or use data analysis, paid plans raise those limits. See ChatGPT plans explained. If you’d rather build the sheet inside an AI app, Claude has its own spreadsheet tool, Claude Sheets, covered in the Claude guide.

FAQ

Can ChatGPT edit my Excel file directly?

You can upload a spreadsheet and ask ChatGPT to analyze or reshape it. Whatever comes back, open the result in Excel and check the totals yourself.

Why is my SUM total too low after pasting?

Some amounts were probably pasted as text, often because of a stray space, an apostrophe or a currency symbol. SUM ignores text values, so the total shrinks without an error. Convert them to numbers and the total updates.

Do I need ChatGPT Plus for this?

No. Pasting text and getting a table back works on Free. Paid plans raise the limits on file uploads and data analysis, which helps if you process large files often.

Is it safe to paste bank or customer data into ChatGPT?

Strip or mask names, account numbers and anything personal first. The method works just as well with masked data.

Sources

Checked on November 17, 2026.

  1. SUM function (Microsoft Support) (support.microsoft.com)
  2. SUMIF function (Microsoft Support) (support.microsoft.com)
  3. Convert numbers stored as text to numbers (Microsoft Support) (support.microsoft.com)
  4. What is ChatGPT Go? (OpenAI Help Center) (help.openai.com)

Change log

  • : First published with a fictional 8-row example.

This article summarizes each provider's official materials. We have no affiliation with these companies and use no affiliate links. Your checkout and account pages are the final word on prices and limits. Ads & affiliate disclosure · Editorial policy