AI for Excel and CSV: how to get verifiable analysis and useful charts

A manager uploads a quarter of sales exports to an AI assistant and asks, “Find insights and build charts.” A minute later, confident conclusions and attractive charts appear. But the total does not match the accounting system, and one “trend” turns out to be the result of dates being read incorrectly.

The short answer: spreadsheet analysis depends on more than the model. It also depends on the environment in which it runs. Can the service accept the file, calculate results with code, show its steps and let you repeat the calculation? It is impossible to name the “best model for spreadsheet analysis” honestly without running the same test on your data. Reliability starts with checking the file structure, units and control totals; charts come later.

The main rule is that every figure in a conclusion must come from a reproducible method—a script, formula or independent pivot table—not be “calculated” by the model in its prose. After reading, you will be able to inspect a source file, get a repeatable calculation, choose a chart for the question and compare tools against criteria rather than impressions.

Clarify the business question

A mismatch between an AI answer and an Excel total can appear before any calculation: one side counts orders while the other counts product lines. The metrics will not match. Agree on definitions before uploading a file.

Clarify:

• Revenue or cash received. The value of shipped orders and the value of payments received are different.
• Order or order line. An order with three products may occupy three rows. Row count and order count differ.
• Period. Calendar month or fiscal month; creation date or payment date.
• Currency. Are all amounts in one currency? If not, which exchange rate should be used?
• Exclusions. Cancelled orders, test records and returns.

Write these definitions in one paragraph and include it with each prompt. The model does not know how your company defines revenue and may choose a plausible definition unless you specify one.

Check the source file

Before asking for conclusions, check whether the file itself was read correctly. Treat this as a separate step and inspect its result before looking at charts.

Check:

• Encoding and delimiter. A CSV with text in the wrong encoding can turn into unreadable characters. A semicolon where a comma is expected may merge every column into one.
• Dates. Is the format consistent throughout the file, and how was it parsed?
• Missing values. How many key fields are blank, and do blanks mean zero, unknown or an export error?
• Repeated records. Check for repeated keys, but first establish whether a row represents an order, a line item or an event. A repeated order_id in a line-item file is not necessarily a duplicate.
• Units. Hryvnias or thousands of hryvnias; individual items or packs.
• Personal fields. Customer names, phone numbers and addresses. Remove them before upload if they are not needed. Use only a service that company policy permits for this data.

A useful first request is to have the AI describe the file: row and column counts, data types, missing values, and the minimum and maximum dates. If the earliest date in a quarterly export is in 1970, something was parsed incorrectly.

For recurring exports, automate these checks with data tests. Some tools include four general tests: uniqueness in a column, no missing values, membership in an allowed list, and a matching record in another table. A test identifies rows that break its rule; if there are none, that particular test passed. Passing four rule checks does not prove a business metric is correct, so also reconcile it against an accounting source.

Get a reproducible calculation

A language model that “does the math in its head”—directly in the response text—can make an arithmetic mistake even when the explanation sounds convincing. Run calculations with code or formulas instead.

Some file-analysis environments can run Python code, work with uploaded tables and show intermediate results. Review the code, outputs and assumptions before using the conclusion. Capabilities depend on the environment, model, plan and settings. Scanned PDFs and table images may yield unreliable exact values, and a large or complex file may not be fully processed. Do not assume that the calculation environment can access the internet or that Excel formulas and external workbook links are handled in a particular way; test your file and check the service’s rules.

Ask any tool to:

• show the code or formula for each calculation;
• report row counts before and after each filter;
• save intermediate tables so you can open and inspect them;
• state its assumptions about missing values, duplicates and dates.

Example for learning: a duplicate and a date

Consider this small educational order table in hryvnias:

Total before checking duplicates: 100 + 200 + 200 + 150 = UAH 650.

Total after removing a confirmed duplicate. In this educational table, each row represents one order. If the accounting system confirms that both B records refer to the same order, remove one repeat: 100 + 200 + 150 = UAH 450. The difference is UAH 200: the uncleaned total is about 44% higher than the correct one (200 ÷ 450 ≈ 0.44), all because of one row.

Important: remove a repeated record only after checking the file’s granularity and confirming it against a reliable key and accounting source. If each row represents a product line, a repeated order_id may be valid. Equal amounts do not prove duplication either; two separate orders can cost the same.

The date trap. The value 03/04/2026 is ambiguous. Depending on the date convention, it could mean 3 April or 4 March. If the tool reads it as 4 March, order C leaves April and the monthly chart no longer reflects reality.

Imagine a daily order-total chart made before these checks. In this example it will show an extra UAH 200 on 2 April. If C is interpreted as 4 March, the April total falls by another UAH 150. The chart could then support a claim about a daily peak or monthly decline that does not exist in the accounting data.

Here is reproducible Python logic using pandas. This code is specifically for the educational orders.csv above: the columns are named order_id, date and amount, the row order matches the example, and the accounting source has confirmed that the third row repeats the second:

import pandas as pd

df = pd.read_csv("orders.csv", sep=",", encoding="utf-8")
print(len(df), df["amount"].sum())          # 4 rows, 650

candidates = df[df.duplicated(["order_id", "date", "amount"], keep=False)]
print(candidates)                            # candidate rows, not a deletion decision

df_clean = df.drop(index=2).copy()           # only the confirmed third example row
print(len(df_clean), df_clean["amount"].sum())  # 3 rows, 450

df_clean["date"] = pd.to_datetime(df_clean["date"], format="%d.%m.%Y", errors="coerce")
print(df_clean[df_clean["date"].isna()])     # rows with a different date format

This is teaching code. For another file, check the column names, amount type, row unit, index of the confirmed repeat and date format. Row C’s date becomes missing (NaT) and must be investigated manually; it must not be silently dropped from the total. In a real system, remove records using a confirmed event identifier, not a row position.

Reconcile the result

Even a reproducible calculation should be checked independently. A few simple methods:

• Control total. The period total should match a known value, such as an accounting report or payment-system total.
• A few manual examples. Pick three to five rows and trace them from the source file to the result.
• Independent pivot table. Build the same aggregation in Excel and compare it with the AI result.
• Unexpected outliers. Investigate a sharp peak or drop before including it in a conclusion. It may reflect a business event or a data error.

Errors that sound convincing

The most dangerous analysis errors do not look like errors. Here are common examples:

All examples in the table are educational. The common issue is that a conclusion follows logically from the data, but the data or definitions were not what they seemed.

When results differ, investigate in this order: filters, duplicates, value types, missing data, period and metric definition. If the mismatch remains, check the source and aggregation logic with the metric owner.

Choose a chart for the question

A chart should answer a specific question, not decorate a report. Basic rules:

Label units, period and data source on every chart. “Sales” without saying whether it means hryvnias or units, or which period it covers, is easy to misread.

Causation deserves separate care. If sales rose in months with more advertising, that is a pattern in the data, not proof that advertising caused the increase. Seasonality, a new product or competitors’ actions may have contributed. A causal claim needs a separate hypothesis test, such as a comparison with a control period or group and consideration of other factors. AI often suggests causal explanations, so treat “X caused Y” as a prompt to investigate.

Choose a tool by testing it

People often try to answer “Which model is best for Excel?” by looking at rankings. Public tests address a narrower question: they cover particular files, tasks and tool versions, not every model on a specific business’s data.

A small in-house comparison is more useful:

1. Same export. Use a real but de-identified file with a known correct answer.
2. Same questions. Write five to ten questions, including traps involving duplicates, ambiguous dates and missing values.
3. Accuracy. Do the numbers match the control totals?
4. Reproducibility. Does the tool show code or formulas, and can the calculation be repeated?
5. Usability. How much time and effort does a verified result take?
6. Data and access. Does company policy permit uploading this data to the service?
7. Limits. File size, sheet count, Excel formula handling and plan limits.
8. Price. The cost of the plan required for your workload.

The comparison applies to your file and the date of the test. Tools change, so repeat it after significant updates.

What to change in your process

1. Write down the definitions of key metrics: revenue, order, period, currency and exclusions.
2. Before analysis, get a file profile: rows, columns, types, missing values and date range.
3. Check duplicates by key and date formats; remove only confirmed repeats.
4. Require code or a formula for every calculation and row counts after each filter.
5. Reconcile totals with a control value, manual examples and a pivot table.
6. Choose a chart for the question and label units, period and source.
7. Compare two or three tools on the same de-identified file with known answers.

Frequently asked questions