Have you ever frozen up in front of a spreadsheet? "I want to sum by this condition, but which function do I use?" "The formula I finally built is glowing red with errors." "The numbers are lined up, but I can't tell what they actually say." AI knocks these walls of "I'm bad at functions" and "I can't analyze" right down. You don't need to memorize function names or relearn statistics. Just describe what you want to do in plain language and it builds the formula, tidies the data, and even returns hints about trends. In this chapter, we'll look concretely at how someone who isn't an analysis specialist can make AI their "spreadsheet partner."
The goal: ask in plain words and move forward on both formulas and analysis
Get past "I don't understand functions" with AI
Much of what has tripped you up in spreadsheets came from "the goal is in your head, but you can't translate it into the software's language." "I want to sum only the rows that match a condition in column A" is a clear enough sentence, but is that SUMIF or SUMIFS, and in what argument order? — and there your hands freeze. AI takes over this "translation" entirely. You state your goal in plain language, and AI converts it into a formula, a procedure, or a graph. The era of memorizing functions is, effectively, over. What matters is just being able to explain "what you want to produce" in your own words — that's all.
Searching for function names, puzzling over argument order, freezing when an error's meaning is unclear. Time melts away just "looking it up."
Say what you want to do, and the formula, the fix, and even how to present it come back. The human can focus on "what do I want to know."
💡 Any tool is fine. Excel or Google Sheets — when you ask AI, just add a word: "in Excel" or "in Sheets." Even if the function names differ slightly, AI tells you how to write it in both. If you want to go deeper on analysis in general, also see how to do data analysis with AI.
Have functions and formulas built from your words
First, the biggest headache: building formulas. The trick is not to try to guess the function name. Just plainly describe "what's in which column and what you want to produce" and that's enough. AI picks the best function and returns it in a form you can paste straight in. Classics like pulling a value from another table (VLOOKUP or the newer XLOOKUP), counting or summing only rows that match a condition (COUNTIF / SUMIF), and combining multiple conditions are all AI's forte.
Please build a formula in Excel.
・Column A has "region" and column B has "sales amount" (data from row 2 down, last row unknown).
・I want to sum the sales amount only for rows where "region" is "West."
Include the formula and a short explanation of what each part does.
The key is to state concretely "what's in each column." Write the column contents and the result you want, and AI assembles the right function — like SUMIF — to fit your table.
Another thing that comes up constantly at work is "pulling a corresponding value from another table." Look up a product name from a product code, a department from an employee number — that kind of matching. You can ask for this in words too.
In a spreadsheet, based on the "product code" in column C of the order sheet (Sheet1), I want to pull the product name from the product master (Sheet2, column A = code, column B = product name).
Show me how to write it with both XLOOKUP and VLOOKUP, and add a line on which is safer for a beginner.
Asking for "both ways to write it" and "which is safer" means you learn the reasoning behind choosing between them, not just get a formula — that's the good part of AI.
And when a formula trips you up, there's fixing the error. Displays like #N/A (not found), #REF! (reference deleted), and #VALUE! (type mismatch) are the points that discourage beginners the most, but explaining them is exactly where AI is strongest. The fastest route is to paste the erroring formula as-is and ask "why is this error appearing and how do I fix it?"
✅ The trick with errors is to paste "the whole original." Along with the error name, hand over the problematic formula itself plus "what I was trying to do," and AI pinpoints the cause accurately. Ask not just for "the fixed formula" but also "why it was wrong," and next time you'll be able to handle it yourself.
Aggregating and cleaning — tidy up messy data
Collected data is usually "not quite usable as-is." The same company written inconsistently, the same person entered twice, dates in mismatched formats. This prep work (data cleansing) gets much easier when you do it while having AI teach you the steps — because you can ask "how do I do this" in words.
Ask AI the steps to sort and filter — "in descending order of amount," "show only rows matching a condition" — and you won't get lost in the menus.
The problem of the same row entered twice. You can be taught the "steps to remove duplicates" or a formula to find them.
Line up variations in full-width/half-width, spaces, and abbreviations. You can discuss replacement rules and functions together.
What's more, the star of aggregation — the pivot table — tends to get avoided as "looks hard," but the idea is simple. "What goes in the rows, what goes in the columns, and what do I want to aggregate?" — decide this in words, and AI walks you through how to build it. Say "I want a list of sales totals by region and by month," for example, and it shows you the pivot setup steps in order.
In Excel, from order data (columns: date / region / product category / amount),
I want a list of "sales totals by region and by product category."
Teach me how to build it with a pivot table, in click-by-click order, for a beginner.
Add "in click-by-click order" and "for a beginner," and it comes back as an operating guide with less jargon. If you get stuck, just describe what's on your screen and ask again.
⚠️ Tables on paper, PDF, or images need to be turned into data first. If what you have is a printout or screenshot, you first need to read the text and turn it into a table. For those steps, how to extract text from images with AI OCR is a good reference. Aggregation and analysis only come alive once you have clean data.
Grasp the trends — discuss what can be said
Once the data is in order, it's finally time to think about "so, what can actually be said?" This is the heart of analysis, and where many people feel "this is beyond me." But with AI as your partner, just paste a table or CSV and ask "tell me what you notice" and you get a foothold for thinking. It turns a string of numbers into insights in words.
Have it sum up in a sentence "where it's growing and where it's falling," to use as a starting point for the discussion.
Have it pick out "values extremely large or small compared to the rest." Useful for catching entry mistakes too.
Form a rough hypothesis on "do A and B seem related?" It's easier to decide which direction to dig into.
Below is sales data by month and by region (table pasted after this).
1. What trends can you read overall? Sum up in 3 points.
2. If there are values clearly off from the rest (outliers), point them out.
3. Suggest angles that would be worth looking into further.
Avoid stating numbers as definite; present them only as "hypotheses."
The final line, "avoid definite statements, present as hypotheses," matters. AI's reading is a starting point, not a conclusion. Build the stance of verifying "why can we say that?" yourself, right into the prompt.
🚫 Don't take AI's analysis at face value. AI can point out "there seems to be a correlation," but it doesn't guarantee that's a genuinely meaningful relationship. It can return interpretations that sound plausible but are actually thinly grounded. Treat AI's insights as "hints on which direction to investigate," and always verify important judgments against the source data.
Charts and visualization — showing it so it lands
Numbers don't speak for themselves. "Which chart you show them in" greatly changes how the message lands. But which to choose — bar, line, pie, scatter — is surprisingly hard to decide. You can consult AI here too. Say "what you want to convey" and it proposes the chart type that fits.
When you compare magnitudes across items. The classic for comparing by region or by product.
When showing change over time. Like a monthly sales trend.
A share or composition of the whole. Note it gets hard to read with too many items.
When seeing the relationship between two numbers. Good for spotting a possible correlation.
💡 Ask for "how to show it" as a set. Consult not just the chart type but also "how to title it," "what to emphasize," and "how to arrange the legend and axis labels" together, and it gets close to a form you can drop straight into a document. Tips for working tables and charts into presentation materials are covered in Chapter 4, "Creating documents and slides."
Cautions — double-checking and confidential data
As we've seen, AI is a powerful spreadsheet partner. But precisely because you're dealing with numbers, there are two principles you must hold. Break these and the convenience flips into a risk.
Don't trust the formulas or aggregated results AI produces as-is. Check that the totals add up and the counts are reasonable, always with a small example. AI can be wrong while sounding plausible. A human bears responsibility for the final numbers.
Don't paste customer names, personal data, or undisclosed sales into an outside AI as-is. It's safe to dummy out the real data and hand over only the structure.
Point ② especially deserves care precisely because it's a spreadsheet. It's tempting to paste your actual customer list or sales ledger straight in, but they're packed with information you must protect. The basics of using it safely come in two stages.
Replace names with "Customer A, B, C" and amounts with fictional values that keep the digit count or ratios. This works fine when you just want to be taught a formula or steps.
Consult only how to build it — "given this column layout, how do I aggregate?" — and apply the real data on your own end. The formula works as long as the column shape matches.
🚫 When in doubt, don't enter real data. If you have even the slightest hesitation about "is it okay to hand this to an outside service?", the right answer is not to. You can check your company's rules and where to draw the line on what not to enter in detail in precautions on information you should never enter into AI. Efficiency only matters when you can use AI safely.
- No need to memorize functions. Say "what's in which column and what you want to produce" in words, and AI builds the formula and fixes the errors.
- For aggregating and cleaning, you can consult the steps for sorting, removing duplicates, unifying notation, and pivots. Messy data starts with prep.
- For trends, paste a table and discuss "what can be said, outliers, possible relationships." But AI's reading is a hypothesis — don't take it at face value.
- For charts, say what you want to convey and it proposes the right type.
- Two principles: ① a human double-checks the numbers ② don't paste confidential data — dummy it out or ask about structure only.
The wall around number work should feel much lower now. Next it's time to gather information. In Chapter 6, "Streamline your research," let's move on to speeding up information gathering, comparison, and fact-checking with AI.