Numbers and lists: spreadsheets with AI
Formulas explained, messy lists cleaned, a chart from a table — without memorising anything.
A cleaned-up sheet with one formula you understand and one chart that says something.
Lesson 8 of 12 · Part 2: Make things
Numbers and lists: spreadsheets with AI
Spreadsheets are where many people lose an evening. AI helps in two ways. Ask any assistant to write or explain a formula — it is good at this. Or, with a Google AI plan that includes Gemini in Sheets, ask the side panel to build a table, add a column or make a chart, and use the =AI() function to sort or summarise text in each row.
The idea
Ask for the formula, and why
“Write a Google Sheets formula that… and explain each part.” Understanding the formula is what lets you fix it next month.
Describe your columns
It cannot see your sheet unless it is in Sheets. Tell it: “Column A is names, B is dates as 12/03/2026, C is amounts in rupees.”
Anonymise first
Before you paste or upload, replace names with codes — Student 1, Customer 2. The analysis is the same; the risk is gone.
Step by step
- 1
Pick a real list
Fee receipts, stock, a reading log, attendance totals. Copy it into a new Google Sheet and replace names with codes.
- 2
Ask for a formula
Describe the columns and what you want calculated. Paste the formula and test it on three rows you can check by hand.
- 3
Clean the mess
Ask how to fix inconsistent spellings, split a column, or remove duplicates — or, with Gemini in Sheets, ask the side panel to do it.
- 4
Make one chart
Ask what chart would show the main pattern, and why. Make it. Give it a title that says the finding, not the topic.
Prompts to copy
Change the parts in [brackets], and the details to fit your own day.
Teacher
In Google Sheets, column A has student codes, columns B to F have five test scores out of 20. Write a formula for column G that gives each student’s average as a percentage, rounded to one decimal place. Explain each part of the formula.
Shop
My stock sheet has item names in A, quantity in B and reorder level in C. Write a formula for D that shows “ORDER” when quantity is at or below the reorder level, otherwise blank. Then tell me how to colour those rows red automatically.
Anyone
Here is a table (names replaced with codes). What are the three most important patterns in it? Suggest one chart that shows the most important one, and the title it should have.
Try it
Your task
Take one real list, anonymise it, add one working formula you can explain, and one chart with a title that states the finding.
When you are done: Spreadsheets stop being a wall: you can ask for what you need and understand what you got.
Check yourself
Think of your answer first, then open it. Nothing is marked or saved.
The formula works on the first row. Is it right?Show answer
Not yet — test it on two or three more rows you can check by hand, including an unusual one (a blank, a zero).
Why replace names with codes before using AI?Show answer
The patterns are the same, and no one’s private marks, fees or purchases leave your hands.
Keep the trust
Codes, not names.
A sheet of children’s marks or customers’ purchases is personal data. The AI does not need to know who anyone is to add up their numbers.