AI for Excel and Google Sheets: formulas, clean data and simple reports

Most small businesses run on spreadsheets: sales, stock, payments, attendance. Many owners know what they want from the data but not the formula to get it. This is one of the most practical uses of AI, because you can check the result yourself.

Two ways to use AI with spreadsheets

1. An assistant inside the spreadsheet. Some plans of Excel and Google Sheets include an AI helper. Check whether yours does.
2. A general AI assistant in another window. You describe your sheet, it gives you the formula, and you paste it in. This works with any spreadsheet and is enough for most needs.

Step 1: Describe the sheet, not the data

You do not need to paste your data. Describe the layout:

I have a Google Sheet. Column A is the date, B is the customer name, C is the product, D is the quantity, E is the price per piece, F is the amount, G is "Paid" or "Unpaid". The data starts in row 2. Write a formula for the total unpaid amount, and explain each part in simple words.

Say which spreadsheet you use. Formulas differ slightly between Excel and Google Sheets.

Step 2: Ask for common tasks in plain words

• "Total sales for each month."
• "Count how many orders each customer placed."
• "Show the top five products by amount."
• "Mark in red every invoice unpaid for more than 30 days."
• "Find duplicate phone numbers in column C."
• "Split the full name in column A into first name and surname."

If a formula gives an error, paste the error message back and say what you expected. AI is good at finding its own mistakes when you show them.

Step 3: Clean the data first

Reports go wrong because the data is messy. Ask AI how to fix the usual problems:

• the same customer written three ways ("Sharma Traders", "sharma traders", "Sharma Trd")
• dates typed as text
• extra spaces
• amounts with "Rs" typed into the cell
• merged cells and blank rows

Three rules prevent most of this: one row per record, one kind of information per column, and no merged cells in the data area.

Example: a weekly sales summary

A made-up hardware shop, used only to show the method.

The owner asks:

Using the sheet I described, tell me step by step how to build a summary on a second tab showing, for each week: number of orders, total amount, amount unpaid, and the best-selling product. I use Google Sheets. I am a beginner, so explain where to click.

The AI gives steps to create a pivot table and two formulas. The owner follows them, then checks one week by adding it up on a calculator. The numbers match, so the report is trusted from then on.

Step 4: Always test

AI formulas are usually right, but "usually" is not enough for money. For every new formula:

1. Pick three rows where you can work out the answer by hand.
2. Compare.
3. Try an unusual case: an empty cell, a zero, a cancelled order.

Step 5: Ask for explanations, and learn as you go

Explain this formula as if I were new to spreadsheets: [paste formula].

After a few weeks you will recognise the common ones (SUMIF, COUNTIF, VLOOKUP or XLOOKUP, pivot tables) and need AI less.

Reports in words

Once you have the numbers, AI can help you read them:

Here are my monthly totals for the last six months: [paste totals only]. Describe the main changes in five simple points. Do not guess the reasons; list questions I should ask my team instead.

Paste totals, not customer-level data.

What to keep out of AI tools

• customer names with phone numbers or addresses
• bank account details
• salary sheets
• anything your accountant would call confidential

Describe the columns, or copy the sheet and replace real names with "Customer 1, Customer 2" before sharing. See Simple AI rules for your team.

Where AI should not be trusted

• Tax and compliance calculations. Use your accountant and your accounting software.
• Large calculations done "in its head". Ask for the formula and let the spreadsheet calculate.
• Final accounts. A spreadsheet summary is for decisions, not for filing.

Mistakes to avoid

• Pasting a formula without testing it.
• Building reports on dirty data.
• One giant sheet for everything. Keep raw data on one tab and summaries on another.

Next step

Use the unpaid column you just built: Payment reminders and collections with AI.