Spreadsheets and Data

Spreadsheets are where a lot of working life happens, and where a lot of silent errors hide. AI can clean a messy column, explain a formula that nobody remembers writing and suggest an analysis. It can also compute something wrong with total confidence, and it must never receive sensitive data it should not see. In this lesson you will learn what to ask, how to protect your data and how to verify results.

Research gave you information. Now we turn to numbers and tables. The central rule of this lesson is short: AI helps you work with data, but you verify the calculations, and you protect the data.

Julien's messy export

Julien, the accountant, exports a list of client invoices from a software. The file has inconsistent date formats, names written in different ways, some empty cells and a column mixing amounts and comments. Cleaning it by hand would take an hour. He wonders if an assistant could do it, but the file contains client names and amounts, which fall under professional secrecy and the GDPR. He must decide how to use AI, not just whether.

First, protect the data

Before any prompt, apply your data traffic light from chapter 1. Tables often hide sensitive data in places you do not look: a comments column, a hidden sheet, a file name. There are three safe strategies.

1. Anonymize or pseudonymize: replace names with codes (Client A, Client B), remove identifiers, round or scale amounts if exact values are not needed for the task. Keep the key that links codes to real people on your own side.
2. Send structure, not content: describe the columns and give two or three invented rows; ask for a formula or a method, then apply it yourself on the real file.
3. Use an approved tool that your organization has cleared for this type of data, when the real data is needed.

Remember that removing the name is not always enough: a combination of postcode, date of birth and job can identify a person. When in doubt, share less.

Also check how your tool handles files: some analyze a spreadsheet by running calculations, others only read it as text. The second mode is much more error-prone with large tables.

What AI does well with data

• Cleaning: standardizing date formats, splitting a full-name column, spotting duplicates and inconsistent spellings.
• Classifying: assigning categories to free-text lines (for example, types of expense), with a list of categories you provide.
• Formulas: writing a formula from a description, or explaining one that you inherited.
• Analysis ideas: suggesting which comparisons, groupings or charts would answer your question.
• Reading a result: describing what a table shows in plain language.
• Documenting: writing instructions for how a sheet works, so that a colleague can maintain it.

A ready-to-use prompt: a formula from a description

I use a spreadsheet application. My sheet has these columns (invented example data, no real names):
A: Invoice date (format [e.g. dd/mm/yyyy])
B: Client code
C: Amount excl. tax
D: Status (Paid / Pending / Late)

I want a formula that [what you want, e.g. adds up the amounts of Late invoices for client code "C012" in the current year].
Give me: (1) the formula, (2) a step-by-step explanation of how it works, (3) one small test case with expected result so I can check it, (4) two common mistakes to watch out for with this formula.
Tell me which spreadsheet function names might differ depending on the language of my application.

Note the request for a test case with the expected result. It lets you verify the formula on a small example you can calculate by hand before applying it to the real file.

A ready-to-use prompt: clean and classify a column

Here are 15 lines from a column of free-text expense descriptions (anonymized):
[paste lines]

Classify each line into exactly one of these categories: [list of categories you define, plus "Other"].
Present the result as a table: original text / category / confidence (high, medium, low) / short reason.
Do not create new categories. If a line is ambiguous, mark it "Other" and say why.

Test on a small sample, check the result, refine the categories, and only then apply the method to the full list, still verifying a sample.

Verifying calculations

A language model is a text predictor, not a calculator. It can reason about numbers, but it can also make arithmetic mistakes, miscount rows or misread a column. Some tools run real calculations in the background; others do not. Either way, verify.

• Recompute independently: run the formula in the spreadsheet itself and compare totals.
• Test on a known case: check a few rows by hand.
• Check the order of magnitude: is the total plausible?
• Check edge cases: empty cells, text in numeric columns, duplicates, dates in different formats.
• Check the count: do the number of rows before and after match, and do you know why any rows are missing?
• Keep the original: always work on a copy and keep the source data untouched.

If the assistant presents a conclusion ("sales fell in the third quarter"), look at the numbers behind it yourself. Check whether it compares like with like, and ask what other explanation could fit the same data.

Synthesis and charts

An assistant can summarize a table in words and suggest the right chart, even if it cannot always draw it. Ask it to describe the chart you should build: the type, the axes, the title, what to highlight. Then build it from your own data in your spreadsheet. A good chart title states the message: "Late invoices peak at the end of each quarter" rather than "Invoices by month".

A ready-to-use prompt: from table to insight

Below is a small table of anonymized data. Columns: [describe columns and units].
[paste table]

Question I want to answer: [e.g. which categories contribute most to late payments?].
Please: (1) describe in plain language what the table shows; (2) tell me which calculation or grouping would answer my question and how to do it in a spreadsheet; (3) propose the best chart type, with axes, title and what to highlight; (4) list 3 limitations or alternative explanations I should consider before drawing conclusions.
Do not claim figures that you cannot compute from the table.

Bad use versus good use

Bad use	Good use
Uploading the full client invoice export to an unapproved tool.	Replacing names with codes, or sending only the column structure and a few invented rows.
Copying the total given by the assistant into a report.	Recomputing it in the spreadsheet and checking a sample by hand.
Applying a complicated formula you do not understand.	Asking for a step-by-step explanation and a test case before using it.

Your turn: formula, test, explain

Exercise. Take a spreadsheet you use (or build one with ten invented rows). (1) Write down a calculation you need regularly and ask for the formula with the prompt above. (2) Test it on your small example: compute the expected result by hand first. (3) Ask the assistant to explain the formula as if to a new colleague, and paste that explanation into a "How this sheet works" tab. (4) Write down what you would never put in a prompt from this file.

See a solution

Julien builds a copy of his invoice table with invented clients "A" to "D" and ten rows. He wants the total of late invoices for client A. He computes by hand that it should be 2,400 (three invoices of 800). The formula proposed by the assistant returns 2,400 on his test, then 1,600 when he adds a row with the status written "late" in lower case: his data had inconsistent capitalization. He cleans the status column first, and then applies the formula on the real file.

The "never in a prompt" list: client names, tax numbers, bank details, any comment fields describing personal situations. The exercise showed him that the real value was not the formula itself but the test and the edge cases.