Using AI with Excel and Spreadsheets: Analysis You Can Verify

Using AI with Excel and Spreadsheets: Analysis You Can Verify

To use AI with Excel safely, ask the AI for formulas, structures and analysis plans, not finished numbers, and check every step in the spreadsheet itself. Work with a sample that has no personal data, or with only the column names and data types. That way the AI speeds up the work and you can still explain where each result came from.

Key takeaways

• AI is good at writing formulas, explaining other people's formulas and suggesting an analysis plan. It becomes a risk when it hands you a total without showing how it got there.
• Always ask for the formula or the step, never just the result. Excel should run the calculation, not the language model.
• Before pasting anything into a chat, remove or replace personal and sensitive financial data. Often the AI only needs the structure of the table.
• Every analysis needs an independent check: a control total, a test on a few rows, and a person who understands the business looking at the result.
• Record what was asked, what was answered and what was checked. Without that record, nobody can repeat or audit the analysis later.

Where AI genuinely helps in a spreadsheet

The safest way to use AI with spreadsheets is as a formula-writing assistant. You describe what you want in plain language, such as "sum column D when the region is South and the month is October", and you get a SUMIFS formula ready to test. The gain is not having to remember the syntax, and the risk is low because you can see the formula and the result in the cell.

Another useful application is explanation. Almost every company has an inherited spreadsheet with nested formulas nobody understands anymore. You can paste a formula (without real data) and ask the AI to describe, step by step, what it does. This helps with documentation and with finding old errors, such as ranges that do not cover every row.

AI also helps with planning. Given a description of your columns, it can suggest how to organize a pivot table, which indicators to calculate, which quality checks to run before analyzing (duplicates, dates in the wrong format, empty cells) and how to structure separate tabs for data, calculation and results.

The use that demands the most care is asking the AI to "analyze" an entire spreadsheet and return conclusions. Language models can get arithmetic wrong, skip rows or invent a plausible explanation for a pattern that does not exist. When the tool runs real code on the file, the risk drops, but checking the work is still your job. The practical rule: the AI proposes, Excel calculates, you validate.

How to protect sensitive data before any prompt

Business spreadsheets often mix tax IDs, emails, phone numbers, salaries, contract values and customer data. Pasting that into a public chat without thinking is the most common mistake and the easiest to avoid. Before any prompt, decide what the AI really needs to see. In most formula cases it needs only the column names, the format of the values and two or three fictional examples.

A simple preparation routine:

• Copy the structure of the spreadsheet into a new file, keeping only the headers.
• Replace names and document numbers with invented values in the same format, such as "Customer 01" and "000.000.000-00".
• Swap real financial values for test numbers that still let you verify the logic.
• Remove hidden tabs, comments and metadata that may carry internal information.
• Confirm which AI tool the company has approved and under what conditions it handles the data you send.

The last point is about governance, not technique. The company needs a clear rule on which tools may receive which types of data. If that does not exist yet, start with a company policy for employee ChatGPT use, which helps define what to allow and what to ban. When a spreadsheet contains personal data, Brazil's data protection law (LGPD) applies to the processing, including processing done by an external tool. The website of the ANPD, Brazil's data protection authority, is the official reference for guidance on the topic, and decisions about specific cases should go through whoever handles privacy at your company or through legal counsel.

There is also the option of running the analysis in a controlled environment, such as a business account with suitable terms of use or a tool built into a suite the company already subscribes to. This does not remove the need to minimize data. The less the AI receives, the smaller the damage if something goes wrong.

Step by step: generate a formula you can verify

This is the workflow we recommend for any new formula. It works with any AI assistant and with Excel or Google Sheets.

• Step one: describe the goal in one sentence, with the exact column names and the tab where they live. State your Excel version or say it is Google Sheets, because functions like XLOOKUP do not always exist in older versions.
• Step two: describe the data format. Are dates stored as dates or as text? Do values use a decimal comma? Are there empty cells or stray spaces?
• Step three: ask for the formula and also an explanation of each part, with an example input and the expected output.
• Step four: test the formula on a copy of the spreadsheet, on five or six rows whose result you calculate by hand.
• Step five: test edge cases: an empty cell, a zero value, text where a number should be, an item that does not exist in the lookup table.
• Step six: extend the formula to all rows and compare a control total against an independent source, such as the report from the originating system.
• Step seven: record the goal, the formula, the date and who checked it on a documentation tab.

One important detail in step four: work out the expected result before you look at what the formula returned. If you look at the result first, you tend to believe it is right. Also be wary of formulas that work but hide errors, such as IFERROR wrapped around everything. It can turn a real problem into a silent zero. Ask the AI to handle only the expected errors and let the others show up.

How to ask for analysis without getting a loose number

When the goal is to understand the data, not just write a formula, the way you ask changes everything. Instead of "which product sells the most?", ask for a method: "describe how to calculate the product with the highest revenue per month using a pivot table, with the row, column and value fields, and list the quality checks I should run first".

The difference is that a method can be executed and inspected by you. A direct answer can only be accepted or rejected. Three habits make an analysis verifiable:

• Ask for the reasoning in short numbered steps, so each step corresponds to a cell, a helper column or a pivot table you can open.
• Ask the AI to list the assumptions it made, such as "I assumed returns are stored as negative values". Wrong assumptions are the most common source of wrong conclusions.
• Ask for checkpoints: totals that must match, row counts before and after a filter, plausible minimum and maximum values.

Use visible helper columns instead of one giant formula. One column to clean the text, another to convert the date, another to classify the row. Each can be inspected separately, and whoever inherits the spreadsheet can follow the path. If the analysis becomes a monthly routine, turn it into a documented process, as we describe in bank reconciliation with AI, where the same logic appears: automate what is repetitive and check what has financial consequences.

When the data arrives from PDFs, invoices or exported reports, the extraction step has its own errors. The article on how to extract data from PDFs with AI explains how to define fields, rules and human review before the information reaches the spreadsheet.

Checklist before you use the result

Before taking any number into a meeting, a proposal or a purchasing decision, run through this list:

• Does the number of rows processed match the number of rows in the source?
• Is there an independent control total (from the system, the bank statement, the previous report) that matches the result?
• Do the five largest and five smallest values make sense to someone who knows the business?
• Were empty cells, duplicates and out-of-period dates handled explicitly rather than ignored?
• Was every new formula tested on edge cases?
• Are the assumptions of the analysis written down somewhere in the spreadsheet?
• Could a second person redo the result using only the documentation?
• If the decision affects customers or employees, is there human review before any action?

The last item deserves attention. When a spreadsheet analysis becomes the criterion for denying credit, a discount or service to someone, it enters the territory of automated decisions. The article on LGPD automated decision-making covers the right to review and why a person must be able to understand and contest the criterion.

Illustrative scenario: the monthly sales report

Imagine a small distributor whose manager builds a sales-by-region report every month from a system export. This is an illustrative scenario, unrelated to any real company.

The manager creates a copy with the headers and ten fictional rows. They ask the AI for a formula that sums sales by region and month, and another that flags orders with no region code. The AI returns SUMIFS for the total and a check using IF and COUNTIF for orders without a region. The manager tests both on the ten fictional rows, working out the expected result first. One of the formulas fails when the region has a trailing space, and the AI suggests a helper column using TRIM, which the manager adopts.

On the real spreadsheet, the manager applies the formulas and compares the grand total with the total the system itself shows. If they match, the work moves on. If not, the column of orders without a region shows where to look. Finally, the manager notes on a documentation tab what was done. The following month, anyone on the team can repeat the process in minutes, and at no point did the AI see a customer name or a real value.

How we apply map, limit, pilot and transfer

The method we use in AI projects works for spreadsheets too. First, map: which spreadsheets support important decisions, who maintains them, where the data comes from and what the current result is without AI, which will serve as the reference. Then, limit: define which data may leave the company environment, in which tool and with which type of action allowed (suggesting a formula yes, changing the production spreadsheet no).

Next, pilot a single real workflow, such as the monthly report, with a record of what was asked and checked, and a success criterion agreed before you start. Finally, transfer: document the procedure for the team and decide whether to expand, adjust or stop. To structure the pilot, the piece on guardrails for AI agents shows how to think about input, output and action controls, and the NIST AI Risk Management Framework is a public reference for organizing risk management. To understand risks specific to applications built on language models, such as leakage of sensitive information and overreliance on the output, see the OWASP Top 10 for LLM applications.

Frequently asked questions

Is it safe to paste my entire spreadsheet into ChatGPT or another AI? Generally it is not the best path. Spreadsheets tend to contain personal and financial data, and the AI rarely needs it to write a formula. Send only the structure and fictional data, and confirm which tool the company has approved before sending any real information.

Can AI get the math wrong in a spreadsheet? Yes. Language models are not calculators and can get sums wrong, skip rows or invent explanations. That is why the most reliable path is to ask for the formula, let Excel calculate, and check the result against an independent total and a few cases tested by hand.

Do I need advanced Excel skills to use AI in this process? You do not need advanced knowledge, but you need to understand enough to check the work. If you cannot explain what a formula does or test a simple case, the risk of accepting an error is high. Ask the AI to explain each part and start with small tasks.

How do I document AI use in a spreadsheet the team will reuse? Create a documentation tab with the goal of the analysis, the main formulas, the assumptions, the date, who checked it and which control total was used. If possible, also keep the request made to the AI. This lets you repeat the process next month and makes any future audit easier.

Sources

• NIST: AI Risk Management Framework
• OWASP: Top 10 for LLM Applications
• ANPD: Autoridade Nacional de Proteção de Dados