Spreadsheet support: formulas, cleaning and checking with AI

Spreadsheets are where office work quietly gets technical. Someone left you a workbook full of formulas nobody understands, the supplier export has dates in three formats, and your manager wants spend by supplier for the quarter by Friday. AI is good at this kind of help: it writes formulas from a plain description, explains what an inherited formula does, and finds errors a tired eye would miss. It is also capable of adding up wrongly, misreading a column or quietly "fixing" data it shouldn't touch. The routine that makes it safe is simple: ask it to show its working, keep the original data, and check a sample by hand.

This guide follows Leah Bennett, Operations Assistant at Fernway Group (our fictional practice company), and Maya Roberts, the Office Manager.

Worked example: the supplies orders log

Leah's first decision is about the data itself. The "ordered by" column holds colleagues' names, and the analysis doesn't need it. She deletes that column from a copy before any AI tool sees the file. Fernway's approved tool is Copilot, signed in with her work account, so she works there. The spend figures are internal, so a personal account would be the wrong place even without the names.

Spot the problems first

Before any totals, she asks for an audit. Analysing messy data gives confident wrong answers.

This is our office supplies orders log. Before any analysis, check it for data problems and list them with row numbers:

supplier names that look like the same supplier spelled differently; dates in inconsistent formats or outside July to September; blank quantity, unit cost or total cells; rows where quantity times unit cost plus VAT doesn't match the total; possible duplicate rows.

Calculate the checks rather than estimating. Don't change anything yet. Tell me how many rows you checked.

The list comes back with: one supplier spelled three ways ("Pennywell Stationers", "Pennywell Stationers Ltd", "PENNYWELL"), eleven dates in American month-first format, four blank totals, two rows where the total doesn't match, and one likely duplicate. It says it checked 238 rows. Leah's file has 241 rows of data. That gap matters: three rows were skipped, and she finds they sit below a blank row the tool treated as the end of the data. She removes the blank row and runs the audit again.

A totals row or a blank row inside the data catches tools out in the same way. The spreadsheet it misread walks through a real-looking example.

Clean without losing the original

Next she cleans, with two rules: never overwrite the original columns, and get a list of every change.

She checks the mapping. One is wrong: the tool merged "Brightledge Office" with "Brightside Print", which are two different suppliers. She corrects it by hand. Without the mapping list, that merge would have hidden inside a total.

The formula

Now the summary. Leah could ask the tool to calculate totals directly, and in a file-analysis tool that is fine if it runs code. But Maya will want to update this every quarter, so a formula in the sheet is more useful.

I use [Excel / Google Sheets]. My data has: dates in column A, totals including VAT in column G, and cleaned supplier names in column I. The data is in rows 2 to 242.

On a summary sheet, supplier names are in column A starting at A2. Write a formula for B2 that adds up the totals for that supplier where the date is between 1 July 2026 and 30 September 2026. I'll put the start and end dates in cells E1 and E2.

Explain what each part of the formula does in one line each, and tell me any way it could give the wrong answer.

The tool returns a SUMIFS formula with a plain explanation of each part and a warning: if any dates are stored as text, those rows won't be counted. That is exactly the issue from the American-format dates, so Leah fixes those first.

Check a sample by hand

The summary shows Pennywell as the biggest supplier at about a third of spend. Before this goes to Maya, Leah checks:

• Filter and add. She filters the original data to Pennywell in July and adds the totals with the status bar sum. It matches the formula.
• Spot-check rows. She picks five rows at random and checks each total against quantity, unit cost and VAT.
• Reconcile. The supplier totals add up to the grand total of the whole column for the period.

This takes ten minutes. It is the part that makes the numbers safe to use in a decision.

Explaining a spreadsheet you inherited

Many office spreadsheets were built years ago by someone who has since left. AI is a patient explainer.

I've inherited a spreadsheet and don't understand this formula. I use [Excel / Google Sheets].

[paste the formula, and describe what's in the cells it refers to]

Explain in plain English what it calculates, step by step. List every cell or range it depends on. Then tell me what would make it give a wrong answer, such as blank cells, text instead of numbers or rows added below the range.

In Copilot in Excel or Gemini in Sheets, if your licence includes them, you can ask about the open workbook directly. In a general assistant, paste the formula and a description of the columns, not the whole file. The Copilot in Excel and Gemini in Sheets and Drive lessons show the in-app features step by step.

Ask it to calculate, and to show its working

A language model predicting a total is guessing. A tool that runs code on your file, or a formula in your sheet, is calculating. For anything with numbers, you want calculation.

Whatever the tool, the request is the same: "calculate, don't estimate; show me how; tell me how many rows you used". A result you can trace is a result you can check.

For practice on a messier, text-heavy dataset, try Analyse survey feedback. For a practice file with known errors, download the Fernway sales export and see whether your tool finds the region typos and the wrong totals.

When not to use AI

Data safety

Spreadsheets are where personal data hides in plain sight: staff lists, absence records, customer contacts, pay, home addresses. Never upload a spreadsheet containing personal data to a personal AI account. Even in an approved tool, delete the columns the task doesn't need before you start. Removing names doesn't always make data anonymous: a small team, a job title and a date can identify someone. If the task can be done with made-up rows that have the same shape, use those to build the formula, then apply it to the real sheet yourself. Your organisation's policy and approved tools still take priority.

What to check

Common mistakes

• Trusting a total the tool worked out in its head. Ask it to calculate with code or give you a formula.
• Letting it overwrite the raw data. Always clean into new columns and keep the original.
• Not checking row counts. A skipped block of rows gives a tidy, wrong answer.
• Pasting a formula without understanding it. If you can't explain it, you can't spot when it breaks.
• Forgetting to say which program. Excel and Google Sheets differ in places, and even Excel versions differ. Say what you use.
• Uploading the whole file when a description would do. For a formula, the column names and two made-up rows are usually enough.
• Personal data in personal accounts. The most common and most avoidable data mistake in office AI use.