Excel and data analysis prompts: formulas and cleanup

You ask an AI for a formula, it gives you =SUMIFS(D:D,B:B,"Nord"), and your French-language Excel shows an error. No surprise: function names and the separator depend on the language and regional settings. On a French setup, you'll most often need SOMME.SI.ENS and semicolons.

The fix comes down to one sentence to add to every request, and it sums up the spirit of everything that follows: the AI needs to know your environment and the exact shape of your data.

Getting a formula that works the first time

Take the accountant at a small business who wants the revenue of one region over a period. First version:

Make me a formula to add up sales by region.

The AI has to guess everything: the columns, the date format, the Excel version. Corrected version:

I use [EXCEL 365 IN FRENCH / GOOGLE SHEETS / EXCEL 2019], with the semicolon as separator. Give the function names in French.

My table, sheet "Ventes", from row 2 to row 5000:
- A: date (real Excel date, DD/MM/YYYY format)
- B: region (text: Nord, Sud, Est, Ouest)
- C: salesperson (text)
- D: amount excl. VAT (number)

Desired result: the total excl. VAT for the region in G1, between the start date in G2 and the end date in G3, inclusive.

Give the formula to paste, then explain each argument in one line. Tell me the likely causes if it returns 0 or an error.

You should get a formula like this:

=SOMME.SI.ENS(Ventes!D2:D5000;Ventes!B2:B5000;G1;Ventes!A2:A5000;">="&G2;Ventes!A2:A5000;"<="&G3)

If it returns 0, look at your dates first: an export often contains dates stored as text, which comparisons ignore. Another frequent case is a hidden space after "Nord." The end of the prompt, which asks for the causes of errors, saves you that back-and-forth.

When you're working from an English-language solution found on a forum, this little table helps with translation:

Understanding an inherited formula

You inherit a file with a four-line formula nobody dares touch. This prompt makes it readable and looks for its weak points.

Here is a formula from my workbook:
[FORMULA]

It is in [CELL], and the columns used contain: [DESCRIPTION].

1. Explain what it calculates in one sentence, for someone who doesn't know Excel.
2. Break it down from the inside out.
3. Point out the locked ranges, the hard-coded values, and the cases where it would give a wrong result without displaying an error.
4. Suggest a simpler version if one exists, compatible with [EXCEL VERSION].

Point 3 is the one that counts: a VAT rate or a row limit written into the formula is the kind of thing that stays there for years without anyone noticing.

Cleaning an export without spending the afternoon on it

An export from point-of-sale software or a CRM means text mixed in with dates, duplicates, and subtotal rows. For an association merging three member lists, or a restaurateur pulling his register sales, the safest approach is to ask for a plan before any manipulation.

Here is the structure of an export and 15 representative rows, personal data replaced:
[PASTE THE HEADERS AND ROWS]

Goal: [E.G.: A TABLE WITH ONE ROW PER SALE, READY FOR A PIVOT TABLE]
Tool: [EXCEL 365 IN FRENCH, POWER QUERY AVAILABLE]

1. List the visible problems (mixed types, duplicates, empty values, date formats, stray rows).
2. Suggest the cleanup steps in order, specifying for each whether it's done by formula or with Power Query, and how.
3. Indicate the checks to run after cleanup: expected number of rows, total to compare with the original export.

Don't delete any data without telling me which and according to what rule.

Before pasting anything, replace names, addresses, and contact details. Fifteen made-up rows of the same shape are enough to get the method. To know what can leave your machine or not, read what not to paste into a chatbot and, in a company, ask the person who handles compliance.

Analyzing a file: demand the method, not just the answer

Some assistants can read a file and run code to do the calculations, at the time of writing. Check in your tool what it really does. The distinction is important. If it calculates, you can check the calculation. If it answers off the top of its head from a table pasted into the chat, be wary of every total.

Attached is [FILE], which contains [DESCRIPTION OF THE COLUMNS].
Decision question: [E.G.: WHICH DISHES SHOULD COME OFF THE WINTER MENU?]

Before any analysis:
- state the number of rows, the period covered, and the missing values
- restate the assumptions you're going to make and wait for my approval

Then:
- calculate from the file, without estimating, and show the method or the code used
- give the results in a table, then 3 findings maximum, each backed by a figure
- distinguish what the data shows from what you're assuming about the causes
- flag if the sample is too small to draw a conclusion

Asking for approval of the assumptions prevents an analysis that is wrong but well presented. For example, do "sales" include cancellations? Should we reason in terms of margin or volume? For a teacher studying the results of a small class, the reminder about sample size is useful too: with 24 students, a variation of a few points doesn't say much. If the analysis involves several nested calculations, having the AI reason step by step will make errors easier to spot.

Turning figures into a comment for your team

The pivot table is ready; what's left is writing the three lines your management will actually read.

Here is a results table, already checked:
[PASTE THE TABLE]

Write a comment of 100 words maximum for [RECIPIENT], who has to decide on [DECISION].
Start with the main finding. Use only the figures in the table, without recalculating any percentage you can't justify. Put forward no cause. End with the question to be settled.

Whatever the prompt, keep one fixed rule: match at least one total in the result against your source, by hand or with a formula. Our method for verifying an AI answer offers other quick checks.

Take the last formula that gave you trouble, describe your columns as in the second prompt, and add your Excel version. It's often the only thing that was missing.