DEV Community

Cover image for 7 ChatGPT prompts that turn messy spreadsheets into clean reports
Richard C
Richard C

Posted on

7 ChatGPT prompts that turn messy spreadsheets into clean reports

#chatgpt #productivity #spreadsheets #excel

Most spreadsheets don't start clean. Dates come in three formats, someone types "office supplies" while someone else types "Office Supplies", and the same row gets pasted twice. ChatGPT can help you tidy that up and turn it into a report, but only if you give it clear instructions and check what comes back.

Below are seven copy-paste prompts, all run against the same small dataset. For each one I've worked out what a correct answer should look like, so you can compare what ChatGPT gives you against known-good numbers. Chat tools can and do get arithmetic wrong, so always double-check their numbers against spreadsheet formulas (there are some at the end).

The example dataset

Example data (made up for this tutorial, not real transactions). It has 10 rows with deliberate problems: mixed date formats, mixed currency formats, a duplicate row with different formatting, an exact duplicate row, a blank amount, a blank customer and inconsistent category names.

# Date Customer Category Amount
1 2026-09-03 Acme Office Supplies $120.00
2 09/05/2026 Brightside office supplies 85.5
3 Sept 7 2026 Cole & Co Software $1,200.00
4 2026-09-07 Cole & Co ␣software␣ 1200
5 09/12/2026 Dunmore Travel USD 340
6 2026-09-15 Acme Office supplies (blank)
7 Sep 18 2026 Evergreen TRAVEL $95.25
8 09/20/2026 Brightside Software 4,999.00
9 2026-09-22 (blank) Travel 410
10 2026-09-03 Acme Office Supplies $120.00

(␣ marks a stray space. Row 4's category is " software " with spaces on both sides.)

The same data as CSV, ready to paste:

Date,Customer,Category,Amount
2026-09-03,Acme,Office Supplies,$120.00
09/05/2026,Brightside,office supplies,85.5
Sept 7 2026,Cole & Co,Software,"$1,200.00"
2026-09-07,Cole & Co, software ,1200
09/12/2026,Dunmore,Travel,USD 340
2026-09-15,Acme,Office supplies,
Sep 18 2026,Evergreen,TRAVEL,$95.25
09/20/2026,Brightside,Software,"4,999.00"
2026-09-22,,Travel,410
2026-09-03,Acme,Office Supplies,$120.00
Enter fullscreen mode Exit fullscreen mode

Dates in the MM/DD/YYYY style are US format here (09/05/2026 is September 5). Tell ChatGPT that explicitly, or it may guess.

Prompt 1: Clean and standardise 

Here is a CSV of transactions. Clean it and return the result as a CSV.
Rules:
- Convert every date to YYYY-MM-DD. Dates like 09/05/2026 are US format (month/day/year).
- Convert Amount to a plain number with two decimals: no $, no "USD", no thousands separators.
- Trim spaces from Category and use Title Case (e.g. "Office Supplies").
- If Customer is blank, write "(unknown)". If Amount is blank, leave it blank. Do not guess values.
- Keep all rows, including duplicates, in the original order.
Then list every change you made, row by row.

[paste CSV here]
Enter fullscreen mode Exit fullscreen mode

What a correct answer should look like (10 rows, same order):

Date,Customer,Category,Amount
2026-09-03,Acme,Office Supplies,120.00
2026-09-05,Brightside,Office Supplies,85.50
2026-09-07,Cole & Co,Software,1200.00
2026-09-07,Cole & Co,Software,1200.00
2026-09-12,Dunmore,Travel,340.00
2026-09-15,Acme,Office Supplies,
2026-09-18,Evergreen,Travel,95.25
2026-09-20,Brightside,Software,4999.00
2026-09-22,(unknown),Travel,410.00
2026-09-03,Acme,Office Supplies,120.00
Enter fullscreen mode Exit fullscreen mode

Check that row 6's amount is still blank. A common mistake is filling it with 0 or a guess.

Prompt 2: Remove duplicates and flag them 

Using the cleaned CSV, find rows that are exact duplicates of an earlier row
(same Date, Customer, Category and Amount after cleaning).
- Add a Status column: "Duplicate" for the repeat, "Unique" for everything else.
- Tell me how many duplicates there are and which original row numbers they are.
- Then give me the de-duplicated table (Unique rows only).

[paste cleaned CSV here]
Enter fullscreen mode Exit fullscreen mode

What a correct answer should look like:

  • 2 duplicates: row 4 (repeat of row 3, which only looked different before cleaning) and row 10 (exact repeat of row 1).
  • 8 unique rows remain: rows 1, 2, 3, 5, 6, 7, 8 and 9.

Prompt 3: Summarise by category 

Using only the Unique rows, give me a summary by Category:
- number of rows
- total Amount
- average Amount (ignore blank amounts; do not treat them as 0)
Show the working for each total (list the amounts you added).
Also give the grand total.
Enter fullscreen mode Exit fullscreen mode

What a correct answer should look like:

Category Rows Total Average
Office Supplies 3 205.50 (120.00 + 85.50, one blank) 102.75
Software 2 6,199.00 (1,200.00 + 4,999.00) 3,099.50
Travel 3 845.25 (340.00 + 95.25 + 410.00) 281.75
Grand total 8 7,249.75

If the Office Supplies average comes back as 68.50, the blank was counted as 0 (205.50 / 3). That's the most likely error here.

Prompt 4: Build a pivot-style summary table 

Build a pivot-style table from the Unique rows:
- rows = Customer, columns = Category, values = sum of Amount
- include row totals and column totals
- show blank cells as "-" rather than 0
Return it as a Markdown table.
Enter fullscreen mode Exit fullscreen mode

What a correct answer should look like:

Customer Office Supplies Software Travel Total
(unknown) - - 410.00 410.00
Acme 120.00 - - 120.00
Brightside 85.50 4,999.00 - 5,084.50
Cole & Co - 1,200.00 - 1,200.00
Dunmore - - 340.00 340.00
Evergreen - - 95.25 95.25
Total 205.50 6,199.00 845.25 7,249.75

Acme's Office Supplies total is 120.00, not 240.00, because the duplicate row is excluded. Acme's row 6 has no amount.

Prompt 5: Write the spreadsheet formulas 

My cleaned data is in A1:E11 with headers Date, Customer, Category, Amount, Status.
Rows 2-11 are data. Write Google Sheets formulas (that also work in Excel) for:
1. A Status column (E) marking the second and later copies of a row as "Duplicate", otherwise "Unique".
2. Count, total and average Amount per category, using only Unique rows, with the category name in G2:G4.
3. Grand total, number of duplicates, number of blank amounts, and the largest amount.
Give exact cell references and explain each formula in one sentence.
Enter fullscreen mode Exit fullscreen mode

What a correct answer should look like. These are formulas I tested in a spreadsheet with this data. Put "Office Supplies", "Software" and "Travel" in G2:G4.

E2:  =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2)>1,"Duplicate","Unique")   (fill down to E11)

H2:  =COUNTIFS($C$2:$C$11,G2,$E$2:$E$11,"Unique")                  (fill down to H4)
I2:  =SUMIFS($D$2:$D$11,$C$2:$C$11,G2,$E$2:$E$11,"Unique")         (fill down to I4)
J2:  =AVERAGEIFS($D$2:$D$11,$C$2:$C$11,G2,$E$2:$E$11,"Unique")     (fill down to J4)

I6:  =SUM(I2:I4)                                  → 7249.75   grand total
I7:  =COUNTIF(E2:E11,"Duplicate")                 → 2
I8:  =COUNTIFS(D2:D11,"",E2:E11,"Unique")         → 1         blank amounts
I9:  =MAXIFS(D2:D11,E2:E11,"Unique")              → 4999
I10: =COUNTIF(E2:E11,"Unique")                    → 8
Enter fullscreen mode Exit fullscreen mode

H2:J4 should give 3 / 205.50 / 102.75, 2 / 6199 / 3099.50 and 3 / 845.25 / 281.75. The E2 formula uses an expanding range ($A$2:A2), so only the later copy is marked Duplicate. AVERAGEIFS ignores blank cells, which is why Office Supplies averages 102.75.

The answer you get may be written differently and still be right. Paste it into your sheet and check it gives these numbers.

Prompt 6: Spot anomalies 

Look at the Unique rows and list anything that looks unusual or needs a human check:
- amounts far larger or smaller than the rest
- missing values
- anything else inconsistent
For each, give the row number, what's unusual, and a question I should ask about it.
Don't change or remove anything.
Enter fullscreen mode Exit fullscreen mode

What a correct answer should look like. It should at least flag:

  • Row 8: Brightside, Software, 4,999.00. That's more than 4× the next largest amount (1,200.00) and about 69% of the grand total. Worth confirming it isn't a typo for 49.99.
  • Row 6: Acme, Office Supplies, blank amount. Find the missing figure.
  • Row 9: Travel, 410.00, blank customer. Find out who it was for.

It might also mention that two rows were duplicates, which is fine. If it flags the 1,200.00 Cole & Co row as the outlier instead of row 8, push back.

Prompt 7: Write a short plain-English report 

Write a short report (under 150 words) for a busy manager about this September data,
using only the Unique rows. Include: total spend, the biggest category with its share
of the total, the number of transactions, and the data issues found (duplicates removed,
missing values, anything unusual). Use plain language, no jargon.
Only use numbers you can show from the data.
Enter fullscreen mode Exit fullscreen mode

What a correct answer should look like. The wording will vary, but these numbers should all be in it and match:

  • Total: 7,249.75 across 8 transactions (after removing 2 duplicates).
  • Biggest category: Software, 6,199.00, about 85.5% of the total. Then Travel 845.25 (11.7%) and Office Supplies 205.50 (2.8%).
  • One large item (4,999.00) makes up most of the Software total.
  • One missing amount (Acme, Office Supplies) and one missing customer (a 410.00 Travel charge).

If the report's percentages don't add up to roughly 100%, or the total isn't 7,249.75, check the numbers before you send it.

Practical tips

  • Don't paste sensitive data. Remove or replace names, account numbers, emails, addresses and anything confidential before pasting into any chat tool. Check your company's policy first.
  • Verify totals with formulas. Treat a chat answer as a draft. Recalculate the totals with SUMIFS/COUNTIFS (like the ones in Prompt 5) and make sure they match.
  • Ask for the working. "Show the amounts you added" makes mistakes much easier to spot.
  • State your assumptions. Date format, currency and how to treat blanks change the answer, so put them in the prompt.
  • Work on a copy. Keep the original file untouched so you can always compare.

If you'd like more ready-made prompts for other parts of running a business, 50 AI Prompts for Busy Small Business Owners is pay what you want, from $1. It's a 4-page PDF (plus the Markdown source) with 50 copy-paste prompts across marketing (13), email (12), customer service (12) and planning & operations (13). Examples include review replies, late-payment reminders, a 30-day social calendar, SOPs and pricing checks. Each prompt has fill-in [BRACKETS] for your own details, and they work in any chat tool. The output still needs your review.

Related tool: the full prompt pack has more fill-in prompts like these for everyday small business tasks.
50 AI Prompts for Busy Small Business Owners

By Richard C · AIL Studio

Related from AIL Studio

Top comments (0)