Data, Spreadsheets and Code: Analysis, Formulas and Debugging

You do not have to be a programmer or a data analyst to benefit from AI with numbers and code. An assistant can explain a table, suggest a spreadsheet formula, translate a cryptic error message or walk you through a small script. But numbers and code are also areas where a small mistake can have big consequences, so verification becomes even more important.

The previous lessons focused on text. Text tasks tolerate some imprecision; spreadsheets and code usually do not. A formula either gives the right result or it does not, and a script either runs or fails. That makes errors easier to detect if you test, but also easier to miss if you simply trust.

What AI does well, and badly, with data

Strengths:

• Explaining what a table or chart seems to show.
• Suggesting formulas, functions and approaches ("how do I count the rows where...?").
• Cleaning and reformatting data (standardizing dates, splitting columns, fixing inconsistent labels).
• Proposing possible analyses and charts for a given question.
• Writing a plain-language summary of results you have already checked.

Weaknesses:

• Arithmetic. A model that only predicts text can make errors on calculations, especially long or multi-step ones. Some tools can run code to compute results, which is more reliable, but you should still check.
• Large tables. Pasting thousands of rows may exceed the context, or the model may overlook some of them.
• Misreading structure. Merged cells, odd headers or hidden columns can confuse it.
• Invented patterns. It can "explain" a trend that is just noise, or propose causes the data cannot support.

Working with spreadsheets

Describe the structure, not the whole file

For formulas, you rarely need to share the data. Describe the layout and what you want:

I use a spreadsheet. Column A has dates (one row per sale), column B
has product names, column C has quantities and column D has prices
per unit. The data starts in row 2.

I want a formula that gives the total revenue for the product named
in cell F1. Explain how the formula works and give me an example of
what it should return for invented sample data.
Tell me if the formula differs between common spreadsheet programs.

A typical answer would use a function that sums products of columns under a condition. The explanation matters as much as the formula: it lets you understand, adapt and debug what you paste.

Test the formula yourself

1. Try it on a few rows where you can compute the result by hand.
2. Test edge cases: an empty cell, a zero, a text where a number is expected, a product that does not exist.
3. Check that the references are correct and that the formula still works when copied down.
4. Compare a result with an independent calculation (a different method or a quick manual sum).

Analyzing a small table

If you do share a small, non-sensitive table, you can ask for analysis. Specify the question, the rules and the checks:

Below is a small table of monthly website visits (invented data).
1) Describe the main trend in two sentences.
2) Point out anything unusual and say what extra information would
be needed to explain it.
3) Suggest a simple chart that would show the trend clearly.
Rules: only use the numbers in the table; show any calculation you
make; do not claim a cause unless the data supports it.

Month, Visits
Jan, 1200
Feb, 1350
Mar, 900
Apr, 1500

Check any calculation the model shows, and notice if it moves from "what the data shows" to "why it happened," which goes beyond the data. Statistics also have traps (small samples, averages hiding differences, correlation mistaken for causation): if a decision depends on the analysis, ask a person with the right expertise to review it.

Working with code

Even if you only write a little code (or none), you may meet snippets: a script a colleague wrote, a small automation, a web page, a formula language. An assistant can act as a patient explainer and helper.

Explain code

Paste a short snippet and ask for a line-by-line explanation at your level.

Explain this code to someone who has never programmed. Go line by
line, in plain English, then summarize what the whole thing does
in one sentence.

total = 0
for price in [12, 8, 20]:
    total = total + price
print(total)

The answer should explain that the code starts at zero, adds each price in the list to the total and finally displays the sum, which is 40. Checking that kind of result against the code is also a way to learn.

Debug an error

Error messages are intimidating, but they are exactly what an assistant can translate. Provide the code, the exact error message, what you expected and what happened.

This Python script should print the average of a list but I get an
error. Explain the error in plain language, show the fix and say
why it works.

Code:
numbers = [4, 8, 15]
print(sum(numbers) / len(numbers)

Error message:
SyntaxError: '(' was never closed

Here the fix is to close the parenthesis of the print call. The key habits: share the exact error text, minimal code that reproduces the problem, and the context (the language, the version, what you tried).

Write small pieces of code

• Describe the input, the desired output and the rules, with an example.
• Ask for simple, readable code with comments.
• Ask for the assumptions and for a few test cases.
• Build in small steps rather than asking for a whole program.

Limits and verification for code

• Run it. Never trust code you have not executed in a safe test environment.
• Test it. Try normal inputs, edge cases and wrong inputs. Ask the assistant to propose test cases, then check that the expected results are correct.
• Invented functions or options. Models sometimes call libraries, commands or parameters that do not exist, or that exist in other versions. Check the official documentation.
• Security. Generated code can contain weak practices (for example handling passwords or user input unsafely). Do not run code you do not understand on important systems or with real data, and never paste secrets such as keys or passwords into a prompt or into code you share.
• Licenses and ownership. Check the terms of the tool and your organization's rules about using generated code in products.
• Understand before you deploy. If you cannot explain what the code does, ask the assistant to explain it, or ask a knowledgeable person, before relying on it.

Your turn: formulas and code

Exercise 1. Write a prompt asking for a spreadsheet formula to calculate the percentage of tasks completed, using a column of statuses ("Done," "In progress," "To do"). Describe the layout without sharing real data, and request an explanation and an edge-case test.

Exercise 2. Take the snippet print(10 / 0) in a Python context. Ask an assistant to explain the error that it produces and propose two ways to prevent it. Check the explanation with the code.

Exercise 3 (project). Add a "spreadsheet helper" prompt to your library with variable parts for the layout and goal, and a "Checks" line (test on three rows and one empty row).

My spreadsheet has task statuses in column C, from row 2 to row 50.
The possible values are "Done", "In progress" and "To do". Some
cells may be empty.
Give me a formula that returns the percentage of tasks that are
"Done" among the non-empty rows. Explain each part of the formula,
tell me how to format the result as a percentage and give three test
cases (including one with empty cells and one with all tasks done).