MICKAI® Trust AgentFree AI courses

applied · Level 2

Automate a spreadsheet workflow with AI, checks and an audit trail

Clean a messy expenses sheet with formulas an assistant suggests, then prove every change with control totals, validation checks and a change log.

By Mickarle Wagstaff-Irons - Micky Irons

  • Level 2Getting going
  • 120 min
  • 6 chapters
  • Free PDF, no account
Read a sample Download free PDF (PDF, 1.8 MB)
The Assemblyapplied / 02

Start with the essentials

The short answer

To automate a spreadsheet job with an AI assistant safely, keep the original untouched, share only invented sample rows and treat every suggested formula as a draft. Check it against control totals you worked out independently, spot-check rows and test odd inputs. Record each change in a change log, then reconcile row counts and totals before and after.

What you will learn

  • You will be able to set up a workbook that keeps the original data untouched and imported as text.
  • You will be able to ask an assistant for a formula using invented sample rows only, never personal or confidential data.
  • You will be able to check a formula with control totals, spot checks and odd inputs before trusting it.
  • You will be able to keep a change log and a reconciliation that show how the before and after totals connect.
  • You will be able to recognise silent type conversion, day and month swaps, rounding gaps, hidden rows, wrong ranges and invented functions.

Who it is for

Office workers who use spreadsheets and have tried an AI assistant, and who want to automate a routine clean-up without losing track of what changed. You need to be able to enter a formula and fill it down a column. No coding is needed.

Before you start

  • Comfort entering a simple formula, such as a SUM, and filling it down a column. A spreadsheet program: the examples were run in LibreOffice Calc and use functions that Excel and Google Sheets also list. The free AI toolkit workbook covers choosing an assistant and checking its privacy settings.

Read a sample · Chapter 03 of 06

03

Ask an assistant, then make it prove itself

Treat every formula an assistant drafts as untested until it passes your checks.

A good request gives the layout, the rules and invented rows, and asks for weak spots:

text · 7 lines
Sheet Raw has columns ref, date, category, amount, all stored as text.
Dates look like 2026-03-02 or 02/03/2026 (day/month/year).
Invented rows:
EX-9001,2026-03-02,Meals,12.50
EX-9002,13/03/2026,Travel, 38.00
Write a formula for Clean!B2 that turns Raw!B2 into a date.
Explain it and list inputs that would break it.

Suppose the reply is this illustrative formula:

text · 1 line
=IF(MID(Raw!B2,5,1)="-",DATE(VALUE(LEFT(Raw!B2,4)),VALUE(MID(Raw!B2,6,2)),VALUE(RIGHT(Raw!B2,2))),DATE(VALUE(RIGHT(Raw!B2,4)),VALUE(LEFT(Raw!B2,2)),VALUE(MID(Raw!B2,4,2))))

It reads well: a dash in fifth place means one layout, otherwise the other. Check it three ways.

  1. Spot-check a row you can read. Raw row 2 holds 02/03/2026, 2 March. The formula gives 3 February 2026.
  2. Test an edge case. Row 13 holds 13/03/2026. The formula gives 3 January 2027: there is no month 13, so DATE rolled forward, which all three vendors document and Google calls silent.
  3. Compare with a control. Every date should be in March 2026. For 20 of the 26 rows you will keep, it is not.

The second DATE reads month then day, the US order. Nothing raised an error; only checking found it.

Try it yourself · Activity 03

15 min

Catch the swap

Check the assistant's formula, then fix it.

  1. Enter it in Clean cell B2, fill down to B31 and format column B as a date.
  2. Compare rows 2, 3 and 13 with Raw.
  3. Swap LEFT(Raw!B2,2) and MID(Raw!B2,4,2) in the second DATE, fill down again and re-check.
  4. Optional: send the prompt above to an assistant you use and test its answer the same way.

Without row 13, which check would still catch the error?

Worked answer

Before the fix, row 2 gives 3 February 2026, row 3 (written 2026-03-02) is right, row 13 gives 3 January 2027, and blank rows 7 and 20 show an error (Err:502 in LibreOffice) until the next chapter. After the swap, rows 2 and 13 give 2 and 13 March 2026. Without row 13, the March check still catches row 2's 3 February. Any assistant's answer must pass all three checks.

Keep learning

The complete workbook

This workbook takes one small, invented expenses file with typical mess and cleans it with formulas of the kind an AI assistant suggests. You will check each formula against control totals and odd inputs, flag rows rather than delete them, and finish with a change log and a reconciliation that connects the before and after totals. Every formula was run in LibreOffice Calc.

  1. 01
    Set up safely: a copy, a raw sheet and invented data

    Before any formula, protect the original and decide what an assistant may see.

    In the workbook · 1 exercise
  2. 02
    Control totals: measure before you change anything

    A control total is a figure worked out independently that later results must match. Formula mistakes rarely raise an error; control totals make them visible.

    In the workbook · 1 exercise
  3. 03
    Ask an assistant, then make it prove itself

    Treat every formula an assistant drafts as untested until it passes your checks.

    Read here · 1 exercise
  4. 04
    Clean with formulas you can trace

    Build Clean beside Raw, row for row, so every clean value points back to its source.

    In the workbook · 1 exercise
  5. 05
    Log, validate and reconcile

    Three records let someone else check your work: what changed, what is valid and whether the totals connect.

    In the workbook · 1 exercise
  6. 06
    Traps that look fine

    Most spreadsheet errors do not announce themselves. Each trap below was run on the practice file.

    In the workbook · 1 exercise

Also inside: a 9-point checklist, a glossary of 11 terms and 10 questions and answers to test yourself. 6 hands-on exercises, each with a worked answer at the back where the workbook gives one.

Download all 6 chapters as PDF (PDF, 1.8 MB)

No login, no card, no account. Before the download we ask you to follow Mickai (two quick links). Free to download and use for personal learning, study groups and inside your own team. Please do not resell the workbooks or republish them as your own. Link people to trust-agent.ai instead.

Test yourself

Questions and answers

Can I paste my real spreadsheet into an AI assistant?

Not if it holds personal or confidential data. The UK National Cyber Security Centre has recommended keeping sensitive information out of queries to public large language models, and data protection law expects you to use only the personal data you need. Give the assistant the column headers and a few invented rows in the same format, and check your organisation's AI policy first.

Is replacing names with codes enough to make data safe to share?

Not on its own. The ICO says pseudonymised data is still personal data for anyone who holds the information that links the codes back to people. A sample of real rows is still real data too. Invented rows in the right format are the safer way to get help with a formula.

Why import everything as text instead of just opening the file?

Opening a CSV lets the program convert values automatically, and Microsoft documents that Excel does this, for example by dropping leading zeros. A date read in the wrong order is then wrong before you start, with no error. Importing as text keeps the raw sheet identical to the file, so every conversion happens in a formula you can see and check.

What is a control total, and where do I get one?

A figure from outside your formulas that your results must match: the record count and total a system reports with an export, a figure from an earlier report, or a small group you add by hand. Record them before you write any formula, so no formula can influence them.

Why did SUM return 0 on my amounts?

Because they are stored as text. SUM ignores text values, as Microsoft's documentation says, and gives no error. Compare COUNT, which counts numbers, with COUNTA, which counts cells that are not empty. If they differ on a column that should hold only numbers, convert with VALUE and check the result against your control total.

How do I stop dates being read the wrong way round?

Import dates as text and build them with DATE from the parts you know, day then month for UK dates. Then spot-check a row whose day is above 12 and count dates outside the expected period. DATE silently rolls impossible values forward, so a swapped month of 13 becomes January of the next year rather than an error.

Should I delete duplicate and blank rows?

Flag them instead. A status column marking each row keep, duplicate or blank leaves the evidence in place, lets the reconciliation count what was excluded and lets someone else check your decision. Before excluding a duplicate, compare the whole row, not just the reference.

Does a reconciliation of zero prove my sheet is right?

No. It proves that nothing was added or lost without explanation. It cannot tell whether a row you kept should have been excluded: a duplicate you failed to flag simply moves from one line to another and the difference stays zero. Combine it with validation checks, spot checks and, for work that matters, an independent reviewer.

The assistant used a function my spreadsheet does not recognise. What now?

Treat it as a warning sign. Assistants sometimes use functions that do not exist, or that exist in one program only. Look it up in your program's own function list. If it is not there, ask for a version that uses only listed functions, then test that version like any other formula.

Do these formulas work in Excel and Google Sheets?

Every function used appears in Microsoft's Excel function list and Google's Sheets function list, but the formulas were run only in LibreOffice Calc 26.2.5.2, so test them on the practice file in your own program first. In LibreOffice, set Formula syntax to Excel A1 so that sheet references such as Raw!A2 work as printed.

When you have finished

Get your certificate of completion

Type your name and download a certificate for this workbook as a PDF, ready to print or to add to LinkedIn. It is made on your own device, so your name is never sent to us. It is a self-declared certificate, not an accredited qualification.

Make my certificate

Learn the language

Key terms

CSV
Comma-separated values: a plain text table in which each line is a row and commas separate the cells.
Control total
A figure from outside your formulas, such as a record count or a hand-added sum, that later results must match.
Reconciliation
Showing that the before figure, minus each logged change, equals the after figure, with no unexplained difference.
Change log
A record of every change made to the data: what, where, when, who, why, and how it was checked.
Validation check
A formula that tests whether each row meets the rules, such as a positive amount and a date in the right month.
Edge case
An unusual input, such as a missing value or a day above 12, used to test whether a formula still behaves.

6 of the workbook's 11 terms. The complete glossary is in the workbook.

Follow the evidence

Sources and checks

Facts last checked: .

Examples in this workbook were run on: LibreOffice Calc 26.2.5.2 on Windows 11 Pro, run headless through its bundled Python 3.12.13 UNO bridge (text import, typed formulas, fill down, hidden rows, filter); Python 3.12.10 with openpyxl 3.1.5 for an .xlsx cross-check and exact decimal arithmetic (2026-09-26).

These workbooks use AI assistance. See how the workbooks are made.

  1. Excel functions (alphabetical)Microsoft Support
  2. Google Sheets function listGoogle Docs Editors Help
  3. Functions by Category (Calc)LibreOffice Help, The Document Foundation
  4. Set automatic data conversionsMicrosoft Support
  5. Import or export text (.txt or .csv) filesMicrosoft Support
  6. Text Import WizardMicrosoft Support
  7. Text ImportLibreOffice Help, The Document Foundation
  8. Importing and Exporting CSV FilesLibreOffice Help, The Document Foundation
  9. Formula optionsLibreOffice Help, The Document Foundation
  10. SUM functionMicrosoft Support
  11. COUNT functionMicrosoft Support
  12. COUNTGoogle Docs Editors Help
  13. VALUE functionMicrosoft Support
  14. DATE functionMicrosoft Support
  15. DATEGoogle Docs Editors Help
  16. DATELibreOffice Help, The Document Foundation
  17. SUBTOTAL functionMicrosoft Support
  18. SUBTOTALGoogle Docs Editors Help
  19. Mathematical Functions (SUBTOTAL)LibreOffice Help, The Document Foundation
  20. ChatGPT and large language models: what's the risk?National Cyber Security Centre (UK)
  21. Principle (c): Data minimisationInformation Commissioner's Office (UK)
  22. PseudonymisationInformation Commissioner's Office (UK)
  23. The AQuA BookGOV.UK (Government Analysis Function and partners)

Created by Mickarle Wagstaff-Irons - Micky Irons with the Mickai team. Published by Mickai LTD. Last updated 26 September 2026.

NextKeep going

Where to go next