AI for Data and Spreadsheets

How to get an AI assistant to write your formulas, tidy your data and answer questions about your numbers — and the two habits that keep you safe: recomputing the key figures yourself and never uploading data you should not share.

Kak Ros runs a small kedai runcit in Seremban. Every month she types her takings into a spreadsheet, and every month the same questions nag at her. Is the business growing? Which month was really the best? Should she stock more drinks before Hari Raya? The numbers are all there, in a grid she has been filling for years. What she lacks is not data but a patient helper who knows spreadsheets. That is exactly the job an AI assistant can do well — provided she knows where it is strong, where it slips, and what it must never see.

Three ways AI helps with numbers

People use AI with spreadsheets in three quite different ways, and it pays to know which one you are doing, because each fails differently.

=fxThe formula tutorYou keep the data; AI writes and explains formulas

You describe your columns and what you want, and the assistant writes a formula, a pivot-table recipe or a set of steps. Your data never leaves your computer. The risk is a formula that is subtly wrong for your layout — so you test it on a few rows you can check by eye.

PYThe analyst that runs codeYou upload a file; the tool computes

Several assistants can now open a spreadsheet or CSV file, write a small program behind the scenes, run it and report results and charts. This is far more reliable for arithmetic than a model "doing sums in its head". The risks are privacy (the file is now on someone else's server) and silent misreading of columns.

?!The thinking partnerAI helps you ask better questions

Before any calculation, an assistant is useful for working out what to ask: which comparison matters, what could explain a dip, what chart would show it honestly. Here it needs no data at all — only a description of what you have.

Formulas and pivot tables, explained

The single most useful habit is to describe your sheet precisely: the column letters, what each holds, the first data row, and the result you want. A vague request gets a generic formula that will not fit your layout.

Vague
How do I add up sales in Excel?
Better

I use Google Sheets. Column A has dates (e.g. 03/03/2026), column B has a product category (Minuman, Makanan, Rokok, Lain-lain), and column C has the sale amount in RM. Data starts in row 2 and ends around row 900.

Write one formula that totals column C for category "Minuman" in March 2026 only. Explain each part of the formula in plain words, and tell me one way it could give a wrong answer.

A good answer will be something like =SUMIFS(C2:C900, B2:B900, "Minuman", A2:A900, ">="&DATE(2026,3,1), A2:A900, "<"&DATE(2026,4,1)), followed by an explanation. The last line of the prompt matters: asking how a formula might fail often surfaces the real trap — here, dates typed as text instead of real dates, which the formula will silently skip.

Pivot tables summarise a long list into a compact table: total sales by month and category, say. They are perfect for AI-assisted learning because the assistant cannot click for you, but it can give you exact steps for your version of Excel or Google Sheets and explain what "Rows", "Columns" and "Values" mean. Ask it to explain why each field goes where it goes, and you will need to ask only once.

Try this prompt
I have a sales list with columns Date, Category and Amount (RM). Walk me through building a pivot table in Microsoft Excel that shows total Amount for each Category (rows) by Month (columns). Number the steps, tell me what to do if dates appear one per day instead of grouped by month, and explain what each pivot area does.

Let the tool run the code

Consensus A large language model predicts text; it does not calculate the way a spreadsheet does. Asked to add up forty numbers in its reply, it may produce a total that looks right and is not. That is why the major assistants added a code-execution feature: when you upload a file, the model writes a short program (usually in Python), runs it in a sandbox and reports what the program computed.

You will find this under different names — "data analysis" in ChatGPT, an analysis or code tool in Claude, Gemini inside Google Sheets, Copilot and Python inside Microsoft Excel As of 2026. Names, limits and which plans include them change often, so check what your own tool offers. Three habits apply to all of them:

Cleaning messy data

Real spreadsheets are messy, and messy data gives confident wrong answers. Tap each tile to see a common problem and how to ask AI for help with it.

The pattern in every tile is the same: ask AI to find and list problems; keep the decision to change data for yourself. And always work on a copy of the file, never the only original.

Worked example: Kak Ros's six months

Here is a small, made-up dataset — monthly takings for the first half of 2026:

MonthSales (RM)
January18,200
February19,600
March (Hari Raya month)26,400
April17,900
May18,800
June19,300

Suppose an assistant, answering without running code, replies: "Total sales were RM120,200, an average of RM21,200 a month. March was your best month at about 30% of half-year sales, and sales grew 6% from January to June."

Before acting on it, Kak Ros spends two minutes checking with a calculator or a =SUM():

Two of four figures were wrong, and nothing in the tone of the answer hinted which. Then comes the more interesting question — what the numbers mean. Comparing March with January is misleading, because March held the festive season. A fairer question is whether March 2026 beat the Raya month of 2025, and whether "ordinary" months are rising. That is where AI as a thinking partner shines:

Try this prompt
I run a small grocery shop in Malaysia. I have monthly sales totals for two years. Hari Raya falls in a different month each year. What comparisons should I make to tell whether my business is really growing, rather than just seeing festive peaks? Suggest three questions, the calculation for each, and one chart for each. Do not invent any numbers.

Good questions and honest charts

The quality of an analysis depends far more on the question than on the tool. Good questions are specific (which months, which products), comparative (against what?) and tied to a decision ("should I stock more drinks in Ramadan?"). Ask AI to restate your question before answering it; misunderstandings show up at once.

For charts, a few rules prevent most mistakes, and an assistant will apply them if you ask:

Try this prompt
Using the uploaded sales file, make one line chart of monthly totals. Start the y-axis at zero, label both axes with units, title it with the period covered, and mark the Hari Raya month. Then tell me in two sentences what the chart shows and one thing it does not show.

Check the arithmetic, protect the data

Two disciplines turn AI-assisted analysis from risky to dependable. The first is checking. Even professionals' spreadsheets go wrong: in 2013 a student, Thomas Herndon, found that an influential economics paper by Carmen Reinhart and Kenneth Rogoff had a spreadsheet formula that left five countries out of an average. If an expert's own formula can miss rows, so can an AI's. Recompute every number you will act on, quote or send to someone else — the total, the average and anything surprising.

The second is privacy. Uploading a file to an AI tool usually means sending it to the provider's servers. Before you do:

Key point

Malaysia's Personal Data Protection Act 2010 applies to personal data handled in commercial transactions. Customer lists are personal data; uploading them to an unapproved tool can breach both company policy and the law. When in doubt, take the personal data out.

Quick check

Why is a built-in data-analysis feature that runs code more reliable for totals than a plain chat reply?

Language models predict text; a program computes. Code can still be pointed at the wrong column, so check which columns it used.

Your sales file includes customer names and phone numbers. You only want monthly totals. What should you do first?

Data the analysis does not need should not leave your computer. "Ignore it" still sends it.

An AI says your six-month average is RM21,200. The total is RM120,200. Is it right?

120,200 ÷ 6 ≈ 20,033. A ten-second check catches it.

Which chart choice is misleading?

Bars encode size by length; a truncated axis exaggerates differences.

// Pause and reflect

  1. Which spreadsheet task at work, study or home costs you the most time each month? Is it a formula problem, a cleaning problem or a question problem?
  2. What data in your files would you have to remove before uploading anything — and is uploading allowed at all where you work?
  3. Which figure in your last report would you be most embarrassed to get wrong? How would you recheck it?

Your notes are saved privately in this browser.

Try this

Type Kak Ros's six months into a new spreadsheet (no personal data involved). Ask an assistant for formulas for the total, the average, each month's share and January-to-June growth; paste them in and compare with the worked example. Then ask it for a chart and check the chart against the rules above. Note one thing the AI got wrong or left ambiguous.

Key terms on this page

Sources & further reading