AI for data analysis: get real answers from a CSV without being an analyst

Upload, do not paste

Every major assistant will take a spreadsheet now. ChatGPT runs Python on an uploaded file in a sandbox and lets you download the results as CSV or Excel. Claude reads uploaded CSV and Excel files, runs code to compute over them, and renders charts next to the conversation. Gemini does the same in the app and also works directly inside Google Sheets. Julius is built for nothing else: upload, ask, get tables and charts, ask again.

The one habit that matters more than tool choice: upload the file, do not paste the rows into the chat box. A pasted table lands in the model's context window, where a long one gets truncated or skimmed, and the model "reads" the numbers the way it reads prose. An uploaded file gets processed by code, which means every row is counted and the arithmetic is done by a computer instead of a language model. Most of the horror stories about AI data analysis come from pasting.

If your data is already in Excel or Google Sheets, you have a third option: Copilot in Excel and Gemini in Sheets work on the sheet in place. That skips the export step, and the results land as real formulas, tables, and charts you can keep.

Step 1: profile the file before asking anything

Do not open with your real question. Open with a profile request, because half of the mistakes downstream come from the model guessing what a column means.

You are a careful data analyst. I have uploaded [FILE NAME], an export from [SYSTEM, e.g. our invoicing software]. Before we analyze anything, profile the file:

1. Row count and column list, with the data type you detected for each column.
2. For each column, one plain-English sentence on what you think it contains, and flag any column you are unsure about.
3. Date range covered, and any gaps or duplicate dates.
4. Columns with blanks, and how many blanks in each.
5. Numbers stored as text (currency symbols, commas, percent signs), and any negative values that look odd.
6. The five most common values in each text column.

Do not summarize or draw conclusions yet. Ask me any question you need answered about the columns before we continue.

Read the answer slowly. If the model calls a column "Total" and you know it is the pre-tax subtotal, correct it now. If it reports 4,312 rows and your spreadsheet shows 4,318, find out why before anything else. That six-row gap is usually a header block, blank lines, or a footer total row that got treated as data, and it will poison every number after this point.

Step 2: clean it in the chat, then keep the cleaned file

Cleaning is where these tools save the most time, because the work is tedious and the instructions are easy to state. Typical asks:

• Convert the Total column from text like "$1,240.00" to numbers.
• Parse the Date column as dates; the format is month/day/year.
• Standardize the Region column: "NE", "Northeast", and "North East" are the same thing.
• Remove exact duplicate rows and tell me how many you removed.
• Split "Customer" into company name and contact name where there is a comma.
• Drop the two footer rows that contain totals.

Ask for a summary of every change with counts ("removed 14 duplicate rows; converted 4,318 totals; 3 could not be parsed and are listed below"). Then download the cleaned file and save it next to the original. Never overwrite the original. If the chat expires or the tool loses the file, you want to start from the cleaned version, not repeat the work.

Step 3: ask questions the right way

Three rules make the difference between an answer you can use and one you have to redo.

One question per message. "What are our top customers, which categories are growing, and where are we discounting too much" produces three shallow answers. Ask them one at a time and build on the results.

Name the columns exactly. "Total revenue by month" is ambiguous when the file has Subtotal, Tax, and Total. Say "sum the Total column, grouped by the month of Invoice date." You can be less formal once you have seen the model use the right columns twice.

Always ask what was excluded. Append "state any rows you excluded and why" to analytical questions. Models silently drop rows with blank dates, unparseable numbers, or outliers, and a total that quietly omits the biggest invoice of the year is worse than no total.

Useful phrasings:

• "Show the calculation, then the result." You get the code or formula logic, which you can sanity check even without reading code.
• "Give me the result as a table with one row per [CATEGORY] and columns for [METRICS]."
• "What would you want to know before answering this?" when your own question is fuzzy.

Step 4: pivots and charts

A pivot table is a grid of one category against another with a number in each cell. Ask for it in those words: "Pivot: rows are Product category, columns are month, values are the sum of Total. Add a grand total row and column." Then check that the grand total matches the sum of the Total column from your profile step. If it does not, something was excluded.

For charts, state the chart type and what goes on each axis. "Line chart, one line per Product category, x axis is month, y axis is sum of Total" is unambiguous. Ask for a bar chart when comparing categories at one point in time, a line chart when showing change over time, and a table when the reader needs the actual numbers. Charts from these tools are good enough for an internal deck; for a board slide, rebuild in Excel or Sheets from the same numbers.

Step 5: sanity checks that catch most mistakes

Run these before you repeat any number to another person.

1. Row count. The file has N rows. Does the analysis account for N rows, or N minus a stated exclusion?
2. Grand total. Open the original in Excel or Sheets and put a SUM on the Total column. The model's grand total must match to the cent.
3. Three spot rows. Pick a customer and a month, filter the sheet yourself, and compare to the pivot cell.
4. Rephrase and re-ask. Ask the same question in different words in a new message. Two different answers means one of them is wrong; find out which.
5. Ask for the columns used. "Which columns did you use for that answer?" catches a model that quietly switched from Total to Subtotal.
6. Direction check. If the tool says a category grew and your gut says it shrank, do not assume the tool is right. Filter the sheet and look.

The three traps

Bad totals. Numbers stored as text get skipped in sums. Blank cells get treated as zero in an average, which drags it down. A footer row labeled "Total" gets added into the total. Percentages get averaged instead of recalculated from the underlying counts. Every one of these produces a plausible number, which is why the grand-total check exists.

Wrong joins. The moment you upload a second file ("here is the customer list, add the account manager to each invoice"), you have a join, and joins are where numbers silently double. If the customer list has two rows for the same customer (an old and a new address, say), every invoice for that customer now appears twice and that customer's revenue doubles. Before joining, ask: "Is the [KEY COLUMN] unique in the second file? List any duplicates." After joining, check the row count again. It should equal the invoice file's row count exactly, unless you asked for something different.

Hallucinated columns and values. A model can refer to a "Margin" column that does not exist, because most sales files have one. It can fill a chart with values it computed from a pattern rather than from the data, especially when the file was pasted and truncated. It can also produce an analysis that looks complete while quietly working from the first few hundred rows. The defense is the profile step (you know what columns exist), uploading rather than pasting, and asking for the calculation behind any number that will leave the room.

Worked example: fourteen months of sales, one afternoon

Suppose you run a twelve-person distribution business and export every invoice from your accounting system for the last fourteen months. Columns: Invoice date, Invoice number, Customer, Product category, Quantity, Unit price, Discount, Total, Sales rep, Region. About 4,300 rows. You want to know where growth is coming from and whether discounting is getting out of hand.

Message 1 is the profile prompt above. The model reports 4,318 rows, flags that Discount is stored as text with a percent sign, notes 22 rows with a blank Region, and asks whether Total is pre- or post-discount. You answer: post-discount, and blank Regions should be labeled "Unknown".

Message 2: "Convert Discount to a decimal number. Fill blank Region with Unknown. Confirm the row count is still 4,318. Then give me the sum of Total for the whole file." You check that sum against a SUM in Excel. It matches.

Message 3: "Pivot: rows are Product category, columns are calendar month of Invoice date, values are sum of Total, with a grand total row and column. Then tell me which three categories grew the most between the first three months and the last three months, in dollars and in percent." The grand total matches Message 2. Two categories are carrying all of the growth.

Message 4: "For each Sales rep, show the average Discount weighted by Total, by quarter. Flag any rep whose weighted discount rose by more than two percentage points across the period. Show how you calculated the weighted average." One rep's discount rate has climbed every quarter. The calculation shown confirms it was weighted by revenue, not a simple average.

Message 5: "Line chart: one line per Product category, x axis month, y axis sum of Total. Then a second chart: bar chart of weighted Discount by Sales rep for the most recent quarter."

Message 6: "Which customers had at least [DOLLAR AMOUNT] in the first half of the period and less than half that in the second half? List them with both totals. State any rows you excluded."

You now have a growth story, a discount problem with a name on it, and a win-back list, and you have checked the two numbers that everything else depends on. Save the cleaned file and the chat.

Moving the answer into Excel or Sheets

A chat answer is a snapshot. Next month's export will need the same analysis, so move the parts you will repeat into the spreadsheet.

The fastest path: ask the model to describe the pivot in spreadsheet terms ("Rows: Product category. Columns: Months (Invoice date). Values: Sum of Total.") and build it yourself with Insert, then PivotTable in Excel, or Insert, then Pivot table in Sheets. It takes two minutes once you know the fields. For calculated columns, ask for the formula: "Give me an Excel formula for the weighted discount per rep that I can put in a helper column, and explain what each part does."

If you have Copilot in Excel, format the data as a table first (select the range, then Ctrl+T); Copilot works far better on a table than on a loose range, and it can add the pivot and the chart in place. Gemini in Sheets can write the formulas and build a chart from a plain-English description. For a walkthrough of both, see AI for Excel and spreadsheets.

Whatever path you take, the sanity checks apply again. A formula the model wrote is still a formula you have to test on three rows.

Privacy before you upload

A sales export usually contains customer names, emails, and sometimes payment details. Before uploading to any AI tool, delete the columns the analysis does not need (email, phone, address, card details) and, where practical, replace customer names with IDs. Use your organization's approved business or team plan rather than a personal account, because the business tiers of the major vendors do not use your inputs for training under their standard terms. If the data includes health, payroll, financial account, or legal records, stop and get compliance sign-off first; consumer AI tools are not the place for regulated data, and AI privacy at work covers what to check.

Next steps

• Keep going in the spreadsheet with AI for Excel and spreadsheets.
• Grab ready-made prompts from data and spreadsheets prompts.
• Compare the tools: ChatGPT, Claude, Gemini, and Julius.
• Learn why models produce confident wrong numbers in AI hallucinations explained and how to catch them in how to fact-check AI output.
• Terms worth knowing: context window, hallucination, and PII.
• If this is your job, see AI for data analysts and AI for financial analysts.