Spreadsheets and data basics
AI turns ‘I don’t know the formula’ into ‘done’. Great for formulas and tidying data — as long as you check the maths and protect the data.
🎬 Watch the video lesson, then work through the full written module below.
Your goals
- Get and understand spreadsheet formulas.
- Clean and reformat messy text data.
- Protect confidential data and check the maths.
Formulas, explained
Describe what you want: “Excel formula to sum column B where column A is ‘paid’”. Or paste a formula and ask “what does this do?”. AI is a patient tutor for Excel and Google Sheets.
Clean and convert
AI is excellent at tedious text jobs: split full names into first/last, standardise dates, turn a messy list into a tidy table, reformat addresses. Paste the mess, describe the tidy result.
A great AI spreadsheet job is…
Two hard rules
Check maths · guard data
1. AI can miscalculate — always sanity-check totals. 2. Never paste personal or confidential data into public AI tools (more in Module 7).
Beyond formulas
AI is a data assistant, not just a formula lookup:
- “Suggest how to structure this data for a pivot table.”
- “Give me a step-by-step recipe to clean this column.”
- “Write a formula to flag duplicates / extract the domain from an email.”
- “Explain what this spreadsheet is trying to calculate.”
Debug with AI
Hit a #REF! or a formula that won't work? Paste it and the error and ask “why is this failing and how do I fix it?” It's a patient tutor that explains the fix, so you learn, not just copy.
Golden rule from this module: use dummy data when working out the method; never paste real personal or confidential data into a public tool.
Solve a real spreadsheet job
Your task (5–10 min)
Use your own (non-sensitive) sheet or dummy data.
- Describe a calculation you need in plain English.
- Get the Excel/Sheets formula + ask it to explain each part.
- Try a cleaning task: “split this into first/last name.”
- Sanity-check the result yourself.
- Save the formula pattern for next time.
The messy contact list
Sana has 500 rows of jumbled contacts to tidy. She’s tempted to paste the lot — including phone numbers and emails — into a public AI tool.
What should they do? Think, then reveal.
Reveal the answer
Tidy the format, not the personal data.
For the method, she asks AI with dummy data or just the steps. She does the actual clean in her spreadsheet — real personal data never goes into a public AI tool.
Cleaning messy names + an annotated formula
Messy sample (5 dummy rows)
jOHN smith
Smith, Jane
bob JONES
MARY o’brien
a. patel
Tidied by AI
John Smith
Jane Smith
Bob Jones
Mary O’Brien
A. Patel
The formula it gave — each part explained
=PROPER(TRIM(A2))
TRIM(A2)— removes the extra spaces around the name.PROPER(…)— capitalises the first letter of each word.
Test it on a few rows you can check by eye before trusting the whole column.
End-of-module quiz
6 questions. You need 4 to pass. Retake as often as you like.
Question 1
AI is genuinely useful for…
Question 2
With numbers from AI you should…
Question 3
Confidential/personal data and public AI…
Question 4
Safe to paste into a public AI tool?
Question 5
AI gives you =SUMIF(A:A,"paid",B:B) and you don't understand it. Best next step?
Question 6
You want AI's help cleaning a customer list. The safe method is:
Remember this
- AI writes and explains formulas — a patient Excel/Sheets tutor.
- Brilliant for cleaning and reformatting messy text.
- Check the maths; never paste confidential data into public AI.
Finish all 8 modules and pass each quiz to earn your AI Skills for Work certificate of completion — shareable on your CV and LinkedIn. CPD accreditation is being arranged.