Spreadsheets and Data: Small Tables, Clean Labels, Dates and Missing Values

Spreadsheets are where a lot of everyday work lives: orders, expenses, survey answers, task lists. They are also where data is messiest: labels typed five different ways, dates in three formats, empty cells, amounts with currency symbols. A chat assistant can help you tidy and understand a small table quickly. It can also quietly "fix" what it should have flagged, and it is not a calculator. This page teaches you to ask for cleaning and analysis in a way that keeps you in control and makes every change checkable.

It builds on Classifying and Checking: Categories, Sentiment, Tables and Missing Data and on the checking habit from Research and Source Checking: Citations, Claims and Verification. If you want the data skills behind it, see SQL and Data Handling for AI: Beginner. All the data below is invented. Never paste real customer, patient or employee data into a chat assistant unless your organisation allows it: use a sample or replace names and IDs first.

What you will learn

• How to ask for a table's columns to be explained, and how to ask questions about a small pasted table.
• How to request a table with exact column names, and how to clean labels, dates, amounts and units without silent changes.
• How to deal with missing values by listing them, not guessing them.
• How to turn notes and results into a status report or executive summary, and how to keep observed trends apart from guessed explanations.
• How to check every result against the original data, including a quick recount.

Know your data before you ask

A model only sees what you paste (or upload, if your app supports files: check what your app supports). It does not know what your column names mean in your company, and it cannot see the rest of a large file. For anything beyond a small table, paste a header and a few typical rows, or work with a sample.

Summarise a spreadsheet's columns and meaning

Before analysing a file you did not create, ask the model to describe each column. This is a quick way to find what you do not understand yet. The key rule: separate what the data shows from what the model is guessing.

Weak prompt:

What is in this spreadsheet?

Better prompt:

Below are the header and three rows of an orders spreadsheet. For each column give:
name, likely meaning, data type, and what you are unsure about.
Mark each meaning as "clear from the data" or "my guess". Do not invent columns.

ord_id,dt,cust_ref,cat,qty,price,stat
1001,2026-03-02,C-17,Office Supplies,10,4.50,shipped
1002,2026-03-04,C-22,Furniture,1,189.00,shipped
1003,2026-03-04,C-17,Office Supplies,25,4.50,returned

The better prompt names what you want per column and forces an honest split between evidence and guess.

Example reply (illustrative; yours will differ):

ord_id   | order identifier, number     | clear from the data
dt       | date, ISO format (YYYY-MM-DD)| clear; unclear if order date or ship date
cust_ref | customer code, text          | clear that it repeats (C-17); meaning of code is my guess
cat      | product category, text       | clear
qty      | quantity, whole number       | clear; unit of measure unknown
price    | probably unit price, decimal | my guess: 10 x 4.50 fits a unit price; currency unknown
stat     | order status, text           | clear: shipped / returned; other values may exist

What to verify: the guesses are the useful part. Answer them from your own knowledge or ask the file's owner, then paste the answers back ("price is unit price in USD") so later questions use correct definitions.

Ask questions about a small table pasted into chat

For small tables (a few dozen rows at most), you can paste the data and ask direct questions. Put the table first, label it, and ask for the working, not only the answer.

Prompt:

Use only the table below. Answer each question and show which rows you used.
Q1. Which region had the highest total revenue over both months?
Q2. What is the total number of units across all rows?
Q3. What is South's revenue per unit over both months, to 2 decimals?
If the table cannot answer a question, say so.

region | month | units | revenue_usd
North | Jan | 120 | 5400
North | Feb | 135 | 6210
South | Jan | 90 | 4320
South | Feb | 80 | 3840
East | Jan | 150 | 6300
East | Feb | 165 | 7260
West | Jan | 70 | 3430
West | Feb | 95 | 4560

Example reply (illustrative; yours will differ):

Q1. East. Rows: East Jan 6300 + East Feb 7260 = 13560. North is 11610, South 8160, West 7990.
Q2. 905 units (120+135+90+80+150+165+70+95).
Q3. South: revenue 4320 + 3840 = 8160; units 90 + 80 = 170; 8160 / 170 = 48.00.

These answers are correct (you can confirm them: total revenue is 41,320 and total units 905). Models do make slips when adding many numbers. A typical failure pattern is a dropped row: for example, a total of 810 units, which is 905 minus the last row's 95. That is why you asked for the rows used. Add the numbers yourself, or paste the table into a spreadsheet and use SUM. For anything that matters, treat the model's arithmetic as a draft and recompute it.

Specify column names for a requested table

When you want text turned into a table, name the columns, their order, the allowed values and what to put when information is missing. Otherwise the model invents headings and drifts between replies.

Weak prompt:

Make a table of these room bookings.

Better prompt:

Turn the three notes into a table with exactly these columns, in this order:
room | booked_by | day | start | end | purpose
Use 24-hour times (HH:MM). If a value is not in the note, write UNKNOWN. Do not guess.
Output only the table.

1. Room B booked by Lena, Tuesday 14:00-15:30, design review.
2. Aiko has the small room on Wednesday from 09:00 for the supplier call.
3. Room C, Thursday, Mateo, weekly planning.

Example reply (illustrative; yours will differ):

room    | booked_by | day       | start   | end     | purpose
Room B  | Lena      | Tuesday   | 14:00   | 15:30   | design review
UNKNOWN | Aiko      | Wednesday | 09:00   | UNKNOWN | supplier call
Room C  | Mateo     | Thursday  | UNKNOWN | UNKNOWN | weekly planning

Note that note 2 says "the small room", which is not a room name, so UNKNOWN is the right answer. A weaker reply would have written "Room A". Check three things every time: the column names and order are exactly yours, no value appears that is not in the notes, and the number of rows equals the number of notes.

Cleaning data: labels, missing values, dates and units

Cleaning has one rule: no silent changes. Ask for the changed data and a record of what changed, so you can review it.

Clean inconsistent category labels

The same category appears as "Office Supplies", "office supplies" and "Ofice Supplies". Ask for a mapping table from every original label to one clean label, plus counts before and after. Labels the model cannot decide must be left for you.

Prompt:

Below is a list of 14 category labels, one per line. Allowed clean labels:
Office Supplies, Furniture, Electronics.
Make a mapping table: original label | clean label | counts of that original label.
Fix only spelling, capital letters and extra spaces. If a label is not clearly one of the
allowed labels, put NEEDS DECISION and do not choose. Finish with the total count per
clean label and check that the counts add up to 14.

Office Supplies
office supplies
Office Supply
OFFICE SUPPLIES
Ofice Supplies
Furniture
furniture
Furnitures
Funiture
Electronics
electronics
Stationery
Office Supplies
Electronics

Example reply (illustrative; yours will differ):

Office Supplies (6): Office Supplies x2, office supplies, Office Supply, OFFICE SUPPLIES,
                     Ofice Supplies
Furniture (4): Furniture, furniture, Furnitures, Funiture
Electronics (3): Electronics x2, electronics
NEEDS DECISION (1): Stationery - could belong to Office Supplies, not certain
Check: 6 + 4 + 3 + 1 = 14.

What to verify: recount the original list yourself, check the counts add up, and look at each merge. "Office Supply" to "Office Supplies" is a safe fix. "Stationery" is a business decision, and the model was right to leave it. Cleaned labels should replace the old ones only after you approve the mapping.

Identify missing values without guessing replacements

Models like to be helpful, so they fill gaps with plausible numbers. For data that is a mistake. Ask for a list of missing cells, and forbid replacements. Also say what counts as missing: blank, "N/A", "-" and "unknown". A zero is a value, not a gap, though it may be worth a question.

Prompt:

List every missing value in the table below. Missing means blank, N/A, - or unknown.
Output: order_id | column | what is there now. Then give the count of missing cells and
the count of rows affected. Do not fill, estimate or correct anything. Separately,
list any value that is valid but looks odd (for example a zero) as "check".

order_id,qty,unit_price,ship_date
2001,10,4.50,2026-03-05
2002,,4.20,2026-03-06
2003,5,N/A,2026-03-06
2004,0,4.80,-
2005,12,4.50,2026-03-09
2006,8,,

Example reply (illustrative; yours will differ):

2002 | qty        | blank
2003 | unit_price | N/A
2004 | ship_date  | -
2006 | unit_price | blank
2006 | ship_date  | blank
Missing cells: 5. Rows affected: 4 (2002, 2003, 2004, 2006).
Check: order 2004 has qty 0 (valid value, but a shipped order with zero quantity is odd).

Count it yourself: five cells, four rows. The same table gives an average quantity of 7.0 over the five known values (10, 5, 0, 12 and 8 add to 35), not over six rows. Whenever an average or total is requested from data with gaps, ask the model to say how many values it used. You decide what to do with the gaps: ask the owner, leave them out and say so, or use a documented rule.

Standardise date formats in supplied data

Dates are a classic trap. "03/04/2026" is 3 April in some countries and 4 March in others, and no model can know which you mean. Ask for ISO format (YYYY-MM-DD) and tell the model to flag, not guess, anything that is ambiguous.

Prompt:

Convert each date to ISO format YYYY-MM-DD. Output: original | ISO | note.
Rules: if a date can be read two ways, write AMBIGUOUS and give both readings.
If it cannot be converted without more information, write UNRESOLVED and say what is missing.
If you assume the century of a two-digit year, say so in the note.

2026-03-14
14 Mar 2026
March 15, 2026
16/03/2026
03/04/2026
2026.03.18
19-Mar-26
next Friday

Example reply (illustrative; yours will differ):

2026-03-14      | 2026-03-14 | already ISO
14 Mar 2026     | 2026-03-14 |
March 15, 2026  | 2026-03-15 |
16/03/2026      | 2026-03-16 | day first, because 16 cannot be a month
03/04/2026      | AMBIGUOUS  | 2026-03-04 or 2026-04-03
2026.03.18      | 2026-03-18 |
19-Mar-26       | 2026-03-19 | assumed 2026 for "26"
next Friday     | UNRESOLVED | needs the date it was written

What to verify: look at every AMBIGUOUS and UNRESOLVED row first. Then notice one subtle point: if a whole column mixes formats, "16/03/2026" suggests the writer used day first, but that does not prove it for "03/04/2026". If you know the source's convention, state it in the prompt ("dates from this system are day/month/year").

Extract currency, quantity and unit information

Free text such as "12 kg" or "$30 each" hides three fields: an amount, a currency or unit, and sometimes a count. Ask the model to split them into columns, to keep the original text, and not to convert anything unless you ask.

Prompt:

Split each line into columns: original | amount | currency | quantity | unit | note.
Use currency codes (USD, EUR). If the currency is not clear from the text, write UNKNOWN
(for example "$" is used by several currencies). Do not convert currencies or units.
Remove thousands separators from numbers. Leave a cell empty if it does not apply.

USD 1,200.50
€45
$30 each
12 kg
500 g
3 boxes x 24 pieces
2.5 L

Example reply (illustrative; yours will differ):

USD 1,200.50        | 1200.50 | USD     |   | |
€45                 | 45      | EUR     |   | |
$30 each            | 30      | UNKNOWN |   | | "each" means per item; quantity not given
12 kg               |         |         | 12  | kg |
500 g               |         |         | 500 | g  | not converted
3 boxes x 24 pieces |         |         | 3   | boxes | 24 pieces per box
2.5 L               |         |         | 2.5 | L  |

Check that numbers match the original text exactly (1,200.50 became 1200.50), and that no currency was guessed. "3 boxes x 24 pieces" also tempts a calculation: the total is 3 x 24 = 72 pieces, but only if that is what the line means, and you did not ask for it. If you do ask for conversions later (500 g is 0.5 kg), ask for them in a separate column so the original stays visible.

From notes and results to decisions

Convert meeting notes into a status report

Meeting notes mix facts, guesses and wishes. Give the model a template, and tell it what to do when information is missing: especially owners and dates, which models are tempted to invent.

Prompt:

Turn these meeting notes into a status report with these sections, in this order:
Done | In progress | Blocked | Decisions | Actions (owner, due date) | Open questions.
Use only the notes. If an owner or date is not stated, write UNASSIGNED or NO DATE.
Do not add a green/amber/red rating. Put anything unclear under Open questions.

Project Atlas sync, 2026-10-06. Present: Mateo, Aiko, Priya, Kwame.
- Login page: Aiko says done, tested on 2 browsers, not yet on phones.
- Payment page: Mateo is waiting for sandbox keys from the bank, no date yet. Might slip.
- Priya will send the revised pricing text by Friday (10-09).
- Decided: launch stays 2026-11-02 unless the payment keys are not here by 10-16.
- The load test needs to be booked. Nobody volunteered.
- Kwame: "budget is fine so far" (no figures given).

Example reply (illustrative; yours will differ):

Done: Login page (tested on 2 browsers).
In progress: Pricing text (revised version due 2026-10-09).
Blocked: Payment page - waiting for sandbox keys from the bank, no date given.
Decisions: Launch stays 2026-11-02 unless payment keys are missing on 2026-10-16.
Actions: Send revised pricing text - Priya - 2026-10-09
         Book the load test - UNASSIGNED - NO DATE
Open questions: Login page on phones? Budget figures (only "fine so far")? Date for the keys?

What to verify: every owner and date should trace to a line in the notes ("Friday (10-09)" became 2026-10-09; 9 October 2026 is a Friday). The model should not decide that "Login page: done" is complete when phone testing is missing. It belongs under Open questions as well, and a good prompt makes that visible.

Draft an executive summary from provided results

An executive summary is for a reader with little time. Ask for the conclusion first, a tight length, and numbers only from your data. If the model calculates anything, the calculation must appear so you can check it.

Prompt:

Write an executive summary for a support manager, at most 80 words.
Start with the main conclusion. Use only the results below and only numbers that matter.
If you calculate a number, put the calculation in brackets. End with one limitation.

Support results, Priya's team, Pune (invented data):
Month | Tickets | Avg first response (hours) | Satisfaction (%)
Jul | 410 | 6.2 | 82
Aug | 385 | 5.1 | 84
Sep | 352 | 4.4 | 87

Example reply (illustrative; yours will differ):

Support improved between July and September. Tickets fell from 410 to 352 (410 - 352 = 58
fewer), first responses got faster (6.2 to 4.4 hours) and satisfaction rose from 82% to 87%.
Limitation: only three months of data, and the results do not show why the changes happened.

Check each number against the table, recompute the bracketed calculation (410 - 352 = 58), and read the last sentence: it should admit what the data cannot tell. That limitation is not decoration. It protects your reader from over-trusting a short summary.

Separate observed trends from possible explanations

The summary above stops at "does not show why". Readers (and models) love to add a cause: "because of the new help centre". The data does not contain a new help centre; a cause that is not in the data is a hypothesis. Ask the model to keep two lists.

Prompt:

Using the support results above, make a table with three parts.
Part 1, Observed: each trend, with the exact numbers from the table.
Part 2, Possible explanations: label each one HYPOTHESIS, say it is not in the data,
and say what data would test it.
Do not state any explanation as a fact.

Example reply (illustrative; yours will differ):

Observed: Tickets fell each month: 410, 385, 352. Response time fell: 6.2, 5.1, 4.4 hours.
          Satisfaction rose: 82, 84, 87 percent.
HYPOTHESIS 1: fewer problems with the product. Not in the data. Test: ticket topics by month.
HYPOTHESIS 2: faster replies raised satisfaction. Not in the data. Test: satisfaction of
              tickets answered fast versus slow within the same month.
HYPOTHESIS 3: customers used self-service help instead. Not in the data. Test: help-page visits.

Two moves make this work: the label (a hypothesis cannot sneak into the report as a fact) and the test (it tells you what to look for next). Keep this habit for the whole course: observation, then interpretation, then what would check it.

Where this shows up at work

• Finance and operations: cleaning exported lists (suppliers, categories, dates) before they go into a report or database.
• Project management: turning meeting notes into a status report that does not invent owners or dates.
• Analysis and reporting: getting a first read on an unfamiliar file, drafting an executive summary, and showing management what the data supports.
• Building AI features: the same instructions (exact columns, UNKNOWN instead of guesses, a change record) become the prompt in a data-cleaning step, where the output is then checked by code.

Common mistakes

• Trusting the model's arithmetic. Totals, averages and counts can be wrong, with a confident tone. The fix: ask for the rows used, recompute with a spreadsheet, and compare.
• Letting it fill gaps. A made-up price or date looks like real data. The fix: say "do not fill, estimate or correct", and ask for a list of gaps.
• Silent cleaning. Changed labels with no record cannot be reviewed. The fix: ask for original-to-clean mapping and counts before and after.
• Guessing ambiguous dates and currencies. "03/04/2026" and "$" have more than one meaning. The fix: state your convention, or require AMBIGUOUS / UNKNOWN.
• Presenting explanations as findings. The fix: separate observed facts from hypotheses, and say how each hypothesis could be tested.
• Pasting private data. The fix: use samples or replace names and IDs, and follow your organisation's rules.

Try it yourself

1. Easy. Clean this list of order statuses with a mapping table and counts (allowed labels: Shipped, Delivered, Cancelled): Shipped, shipped, SHIPPED, Delivered, "delivered " (trailing space), Deliverd, Cancelled, canceled, Canceled. Check the counts add up to 9.
2. Medium. Priya in Pune pasted four invoice lines: "Invoice 7 - 14 Mar 2026 - INR 12,500", "Invoice 8 - 2026-03-20 - INR 8,250.50", "Invoice 9 - 21/03/2026 - 4,000", "Invoice 10 - 25 Mar 2026 - INR 1,000". Ask for a table with columns invoice, date (ISO), amount, currency. Then ask for the total of the INR invoices. Use no guesses for any invoice with a missing currency.
3. Hard. Take the six-row order table from the missing-values section. Ask the model for (a) the average quantity and (b) the average unit price, each with the number of values used, and for what it did with the gaps. Then ask it to write a three-sentence summary for your manager that includes one observation and one clearly labelled hypothesis. Recompute (a) and (b) yourself.

Answers

Easy. A correct mapping: Shipped (3: Shipped, shipped, SHIPPED); Delivered (3: Delivered, "delivered ", Deliverd); Cancelled (3: Cancelled, canceled, Canceled). Counts: 3 + 3 + 3 = 9. Pass checklist: a mapping from every original to a clean label; counts before and after; the total is 9; nothing was dropped; "Cancelled" and "canceled" are two spellings of one word, so merging them is safe. If your assistant marked one as NEEDS DECISION, that is acceptable but over-careful.

Medium. A strong prompt names the four columns, asks for ISO dates, says to write UNKNOWN for missing currency and not to convert anything, and asks for the INR total separately with the invoices used. Expected table: 7 | 2026-03-14 | 12500 | INR; 8 | 2026-03-20 | 8250.50 | INR; 9 | 2026-03-21 | 4000 | UNKNOWN (also: 21/03/2026 is day first because 21 cannot be a month); 10 | 2026-03-25 | 1000 | INR. INR total over invoices 7, 8 and 10: 12,500 + 8,250.50 + 1,000 = 21,750.50. Invoice 9 (4,000) must not be added to it or called INR; the model should say the total excludes it. Pass checklist: the columns are exact; dates match; currency is UNKNOWN for invoice 9; the total is 21,750.50 and the invoices used are listed; you recomputed it.

Hard. Correct values: the known quantities are 10, 5, 0, 12 and 8 (five values, sum 35), so the average is 7.0. The known prices are 4.50, 4.20, 4.80 and 4.50 (four values, sum 18.00), so the average is 4.50. The model should say it left the gaps out and report the value counts (5 and 4). Pass checklist: no gap was filled; value counts were stated; both averages match your own calculation (if they do not, the model's arithmetic was wrong: say so and ask again with the working); the summary contains one number-based observation and a sentence beginning "Hypothesis" that is not presented as a finding.

Summary

• Ask the model to explain columns first, and separate "clear from the data" from "my guess".
• For tables, name exact columns and order, and require UNKNOWN instead of guesses.
• Clean with a record: mapping tables, counts before and after, no silent changes. Flag ambiguous dates and currencies instead of resolving them.
• List missing values; do not fill them. Say how many values any average used.
• Executive summaries and status reports use only your data; observed trends and hypotheses are kept apart.
• Models make arithmetic mistakes. Recompute totals, averages and counts in a spreadsheet.

Next: Reports and Business Analysis: Calculations, Templates and Checked Totals takes the numbers further: showing and checking calculations, percentages versus percentage points, fixed report templates and reconciling conflicting figures.