Spreadsheets and Data with AI: Analysis You Can Check

An analyst asks an assistant for the average order value across two regions. It suggests averaging the two regional averages. The spreadsheet shows a clean number, the chart looks fine, and the figure goes into a presentation. It is wrong. Nothing about the answer looked wrong, because arithmetic mistakes do not look like anything.

Spreadsheets and data are an area where AI can save real time, and where a quiet error can survive all the way into a decision. The way through is a division of labour: the AI proposes a method; a tool computes; you check.

• What AI does well and badly with data.
• A safe workflow for formulas, cleaning and analysis.
• A runnable example of the "average of averages" mistake and how a simple check catches it.
• A list of common analysis traps to ask about.
• How to protect private data.

Some products can run code on files you upload, which is different from the model computing in its head. The code can be inspected and re-run, which makes the results more trustworthy. Even then, read the code, check the assumptions, and test.

1. Describe the data, do not dump it. Give column names, types and three or four sample rows. This is enough for formulas and methods, and it avoids exposing everything.
2. State the question precisely. "Total revenue per region for October" beats "analyse this".
3. Ask for the method and the formula, with an explanation of each part.
4. Test on a tiny example where you know the right answer.
5. Apply it, and keep your original data untouched in a separate sheet or file.
6. Reconcile. Check that totals, counts and ranges make sense (see below).
7. Ask for assumptions and pitfalls, and decide which matter.
8. Write the conclusion yourself, using AI for wording only after you trust the numbers.

I have a sheet with columns: A order_id, B region (text), C order_date (dd/mm/yyyy,stored as text), D value (number, in rupees). Sample rows:1001, North, 03/10/2026, 12001002, South, 03/10/2026, 4501003, North, 05/10/2026, 980Question: total value per region for October 2026.Give me a formula for Google Sheets or Excel (say which you assume), explaineach part, and list any problems the text dates might cause.

You get a formula, an explanation, and importantly a warning that text dates may need converting first. Test it on these three rows, where you can total by hand.

After anything AI-assisted touches your data, run these quick checks:

• Row counts: same number of rows before and after cleaning, or a known, explained difference.
• Totals reconcile: the sum of the parts equals the whole. Region totals add up to the grand total.
• Spot-check three rows by hand, including the first and last.
• Min, max and blanks: scan the extremes for impossible values (negative ages, a date in the year 3026).
• Types: numbers stored as text will quietly drop out of sums.
• Sensible magnitude: does the result match your rough expectation?

Two regions: North had 10 orders worth Rs 1,000 in total, and South had 90 orders worth Rs 18,000. What is the average order value?

A tempting assistant answer: "North averages Rs 100 per order and South averages Rs 200, so the overall average is Rs 150." The arithmetic is correct and the method is wrong. The program computes both ways.

# File: average_of_averages.pyimport csvimport ioDATA = """region,orders,total_valueNorth,10,1000South,90,18000"""rows = list(csv.DictReader(io.StringIO(DATA)))# The shortcut an assistant might suggest: average the two regional averages.regional_averages = [float(r["total_value"]) / int(r["orders"]) for r in rows]average_of_averages = sum(regional_averages) / len(regional_averages)# The correct overall average order value: total value divided by total orders.total_value = sum(float(r["total_value"]) for r in rows)total_orders = sum(int(r["orders"]) for r in rows)true_average = total_value / total_ordersprint("regional averages:", regional_averages)print("average of averages:", average_of_averages)print("true average order value:", true_average)

regional averages: [100.0, 200.0]average of averages: 150.0true average order value: 190.0

The shortcut gives Rs 150 and the true figure is Rs 190, a difference of Rs 40, or about 21 percent of the true figure. Averaging the averages gives North's 10 orders the same weight as South's 90. Because most orders came from South, the real average must sit much closer to South's Rs 200.

How a check catches it. A reconciliation test: multiply the claimed overall average by the total orders, and compare with total value. 150 times 100 orders is 15,000, but the actual total is 19,000. They do not match, so the method is wrong. This "does it add back up?" test takes seconds and works on many kinds of mistakes.

Not every data problem is a calculation. Many are about cleaning, where the quiet errors come from assumptions.

Suppose a column called order_date contains these values: 03/10/2026, 3 Oct 2026, 2026-10-03 and an empty cell. You ask an assistant for a formula that turns everything into proper dates.

It will probably offer a reasonable formula. Before you apply it to thousands of rows, ask yourself four questions, and ask the assistant to answer them too:

1. Which convention is 03/10/2026? In some countries it means 3 October and in others 10 March. A formula that guesses one way will convert every ambiguous date without complaint. Check the source of the data, and look for a value like 25/10/2026 that can only be read one way.
2. What happens to the blank? It might become zero, which a spreadsheet may display as a date in 1900 or 1970. A blank should stay blank or be flagged.
3. What happens to values the formula cannot parse? Silent conversion to an error value, or to the wrong date, is the main risk.
4. How will you know it worked? Count the rows before and after, count how many dates parsed, and look at the earliest and latest result. A date in the year 1900 or in 2062 is a sign of failure.

The reconciliation checks from earlier in this article apply directly. If you had 5,000 rows and 4,910 parsed, you know 90 need attention, which is far better than not knowing.

Prompt for the review step.

Here is my date column pattern (examples: 03/10/2026, 3 Oct 2026, 2026-10-03, blank). The datacomes from [SOURCE], and I believe the convention is day/month/year. Write a formula orshort script that converts them to proper dates, leaves blanks blank, and flags any value itcannot parse instead of guessing. Then list what could still go wrong.

The instruction "flags any value it cannot parse instead of guessing" turns a hidden failure into a visible list, which is the pattern behind most reliable data work.

These are good prompts for a review step: "List the ways this analysis could mislead, given my data."

AI is useful for suggesting possible explanations ("sales dropped in week 3, possibly due to a holiday, a stock-out or a pricing change") because it can draw on patterns from many situations. Those are hypotheses. You test them with your data and your knowledge of the business. When an assistant writes "sales fell because of the holiday", ask: "What evidence in this data supports that, and what else could explain it?" If the data cannot say, the sentence should say "may" or be removed.

• Keep the original untouched and work on a copy.
• Ask for a change log: "List every kind of change you are proposing, with an example, before applying anything."
• Prefer formulas or scripts you can inspect to opaque "fix it" actions.
• Check rows that changed: sample ten before and after.
• Beware of over-cleaning, such as deleting "outliers" that were real, or standardising names in a way that merges different people.

Data often contains personal or business-sensitive information, such as customer names, phone numbers, salaries or sales figures that your organisation treats as confidential.

• For formulas and methods, sample rows with fake or anonymised values are enough.
• Remove or mask identifiers (names, phone numbers, emails, ID numbers) when you do need to share real structure.
• Use your organisation's approved tool for real data, and check its data terms.
• Never paste credentials, API keys or database connection strings.

Privacy and Security goes through these in more detail.

• Asking the model to total a pasted table and trusting the answer.
• Applying a formula to the full sheet without a small test.
• Not reconciling after a cleanup.
• Accepting the AI's explanation of a trend as if it were an analysis.
• Sharing a whole sensitive spreadsheet when sample rows would do.
• Presenting a chart without reading its axes.

1, North, 01/10/2026, 1002, South, 01/10/2026, 2003, North, 02/10/2026, 50

Columns: employee_id (e.g. E001), manager (e.g. M01), salary (number), departmentSample rows: E001, M01, 500000, Sales / E002, M01, 650000, Sales

• Next: Brainstorming, Planning and Decision Support with AI covers the judgment side of work.
• For using AI to learn SQL and analysis, see SQL for Beginners: Learn to Query a Database With Real Examples and Data Analyst Roadmap: Skills, Tools and Projects to Get Your First Job.
• For the full verification routine, see How to Fact-Check AI-Generated Answers.