AI and Excel: Dashboard Without Losing Formulas

To turn a messy Excel workbook into a readable dashboard with an AI agent, there is one thing you must do before anything else: describe the file's structure in the same detail you would use when briefing a new analyst. Without that description, the agent will guess - and it will guess wrong.

Why Excel Breaks Under Automatic Analysis

A multi-sheet workbook is a web of dependencies. One sheet pulls data from another via references like ='Sales'!B12, a third sheet calculates a total from the first two, and a fourth is a lookup table nobody has touched in two years but that everything else depends on.

When an AI agent receives that file without any explanation, it sees numbers but not meaning. It does not know that the "Revenue" column on the "Retail" sheet is gross revenue before VAT, while the same column on the "Wholesale" sheet is already the net amount. If the agent adds them together directly, the total will be wrong. The report will look polished, the numbers will appear clean, and any decision based on them will be built on a mistake.

That is why the first step is not "upload the file and build a dashboard" - it is working through the structure together.

Inventory the File Before Any Calculations

Make a copy of the original workbook and work only from that copy. Leave the original untouched - it is your safety net if something goes wrong. Build the dashboard on a separate new sheet inside the copy, or in a separate file entirely, but never on top of the source data.

Before handing the file to the agent, write a short plain-language description covering:

how many sheets the workbook contains and what each one holds;
which fields are the key ones - for example, "sale date", "SKU", "amount";
where the data comes from: entered manually, exported from a CRM, imported from another system;
whether any sheets serve as lookup tables that feed the rest;
whether the same label can mean different things on different sheets.

That last point is the most dangerous. If "revenue" means gross sales on one sheet and margin on another, the agent needs to know that before it starts calculating. Otherwise you will be rebuilding everything from scratch.

Sensitive data - personal customer information, commercial terms with counterparties - should be removed from the file before you share it, or replaced with anonymised labels. This is basic data hygiene.

How to State the Task in Plain Language

An AI agent understands natural language. You do not need to know programming or any special syntax. Here is a fictional example of how to brief an agent on a sales workbook:

"I have an Excel workbook with three main sheets: 'Monthly Sales', 'Returns', and 'Revenue After Returns'. The 'Monthly Sales' sheet has columns for date, account manager, region, SKU, quantity, and amount excluding VAT. The 'Returns' sheet has the same columns plus a reason for the return. The 'Revenue After Returns' sheet calculates totals using a formula that references the first two sheets. I need a dashboard showing: monthly sales as a line chart, the top 5 account managers by revenue, and the return rate as a share of total volume. The period is the current year. Before you start calculating, please confirm: is 'revenue' in the column heading on the 'Revenue After Returns' sheet the same figure as 'amount' on the 'Monthly Sales' sheet, or a different metric?"

Notice the final request. The agent should ask a clarifying question before it calculates, not assume an answer on its own. If the agent starts working silently without checking ambiguous terms, that is a signal to add the context yourself and ask it to stop.

Checking the Output Step by Step

When the agent delivers a result, do not accept it immediately. Checking does not guarantee the output is error-free, but it catches the most common problems.

Cross-reference totals against the original. Pick two or three figures from the dashboard - total revenue for the quarter, for instance - and compare them with what the source file shows. Any discrepancy needs an explanation, not rounding.

Spot-check a sample of formulas manually. Not all of them - just a few: one simple formula, one with a condition (for example, SUMIF, which sums only the rows that meet a specified criterion), and one with a cross-sheet reference. Open the cell, read the formula, and confirm it points to the correct range.

Change one input value in the copy of the file and watch what happens. If dependent cells recalculate and the result looks logical, that piece of the logic works. If nothing changes, or the wrong thing changes, check the formula, the calculation mode, any pivot tables involved, and the range references. The cause is not always a hardcoded number. Changing one input tests only part of the logic, not the whole file.

If the agent has filled empty cells with "approximate" values or added rows that were not in the original, those need to go. The dashboard should reflect real data. If the agent cannot explain where a specific number came from, that is a reason to investigate, not to accept the result.

Agree on the Dashboard Layout Before Building It

Before the agent starts assembling visualisations - charts, pivot tables, slicers - agree on the layout. You can do this in plain text: "I want three sections: total revenue at the top, a monthly chart in the middle, and a manager table on the right." The agent will propose a version, you will adjust it, and only then does it start building.

Revising a layout in words is far easier than rebuilding a finished dashboard.

Also specify which metrics should recalculate when the data changes and which are fixed. The annual sales target is a fixed number - it does not need to be a formula. Actual performance against that target is dynamic and should update automatically. Ask the agent to provide the formulas for all calculated totals and check them as described above.

Acceptance Criteria: When Is the Dashboard Done?

The dashboard is ready when four conditions are met.

First: any metric that was already calculated in the source matches the source when the same filters and time period are applied. For new metrics, the source range, formula, and a method for manual verification are documented.

Second: formulas reference actual cells and do not contain hardcoded numbers where a calculation is expected.

Third: when an input value changes, dependent cells recalculate correctly.

Fourth: new calculated metrics are derived from the specified source data, and any "approximate" values the agent may have added are explicitly removed.

If any one condition is not met, the dashboard goes back for revision. This is the standard at which a report is safe to present to management or use as a basis for decisions.

For more on building broader automation processes with AI agents, see the business automation section. If you want to think through how to train your team on AI tools, take a look at the training section.

FAQ

Is it safe to give the agent a file with real customer data?

It is better not to, without anonymising it first. Replace names and contact details with placeholder labels - "Client 1", "Client 2" - or remove personal columns before sharing. They are usually unnecessary for sales analysis anyway.

What if the agent confidently delivers a result but the numbers do not match the original?

Do not accept the result. Ask the agent to show exactly where a specific figure came from: which sheet, which range. If it cannot explain the source, that is a reason to investigate manually rather than trust the output.

Does the dashboard have to be on a separate sheet?

Yes. If the dashboard sits on top of the source data, any accidental edit can corrupt the source. A separate sheet - or a separate file - is a simple safeguard against that.

The agent suggested restructuring the source data. Should I agree?

Only if you understand exactly what it is proposing to change, and only if you have an unmodified copy of the original. Restructuring can break formulas that reference specific ranges. Save the copy first, then experiment.

If you want to work through a specific file or set up a reporting workflow, reach out on Telegram @shimaoz or by email at hello@majento.ai.

Source

Majento consultations and training courses.