AI for Spreadsheets and Numbers

AI is very good at explaining spreadsheets and writing formulas. It is less reliable at doing arithmetic in its head. The trick is to let AI design the calculation and let the spreadsheet do the calculating.

Writing formulas

Describe your columns and what you want:

“In Excel, column A has dates, column B has the branch name, and column C has daily sales in shillings. Write a formula for total sales for the branch named in cell F1 during the month in cell F2. Explain each part.”

Ask for an explanation every time. You will learn the formula, and you will spot mistakes in the logic.

Common requests that save real time:

• Totals and counts with conditions (SUMIFS, COUNTIFS)
• Looking up values from another sheet (XLOOKUP, or VLOOKUP in older versions)
• Splitting full names, or combining first and last names
• Calculating days between dates, or flagging overdue items
• Highlighting rows with conditional formatting

Explaining a sheet you inherited

Paste a formula you do not understand:

“Explain in plain English what this formula does, step by step, and tell me what could make it give a wrong answer.”

Cleaning messy data

Phone numbers in five formats, names in mixed capitals, extra spaces: AI can give you the formulas or steps to clean them. For example, ask for a formula that turns 07XX XXX XXX and +254 7XX XXX XXX into one consistent format. Always keep a copy of the original column until you have checked the result.

Analysing figures

Ask AI how to analyse, then do the analysis in the sheet:

“I have monthly sales for three branches over 12 months. What three charts or calculations would show me which branch is growing fastest? Give me the exact steps in Google Sheets.”

Microsoft Copilot in Excel and Gemini in Google Sheets can work directly inside your file on the business plans that include them. Even then, check totals against a figure you already know.

The checking rule

For any number that goes into a report, an invoice, a tax return or a decision:

1. Test the formula on rows where you know the answer.
2. Check one total by a second method, such as a quick manual sum or a pivot table.
3. Never copy a figure from an AI chat answer without recalculating it.

Keep personal data out

Customer lists, payroll and patient or student records contain personal data. Before you paste any of it into a free AI tool, replace names and phone numbers with codes, or work with column headings and a few made-up rows instead. Lesson 7 explains why. When spreadsheets start to run your whole operation, our article on when spreadsheets start costing you money covers what comes next.

Key takeaways

• Let AI design the spreadsheet formula and let the spreadsheet do the arithmetic.
• Test every AI-written formula on rows where you already know the right answer.
• Keep a copy of the original column before you clean data with an AI-suggested formula.
• Replace customer names and phone numbers with codes before pasting a sheet into a free AI tool.