Excel and Spreadsheet AI

Excel is where most finance work actually happens. AI can help you write formulas, build structures, clean data, and solve problems that used to require hours of Googling or asking a colleague.

This lesson covers the most practical AI applications for Excel and Google Sheets users.

The Formula Writing Use Case

This is where AI delivers the most immediate value. You describe what you need; AI writes the formula.

The Formula Prompt Template

"Write an Excel formula that [describe what it should do]. My data is structured as follows: [describe columns/structure]. The result should [describe output]. Include a brief explanation of how the formula works."

Examples to Try

XLOOKUP with fallback:

"Write an Excel XLOOKUP formula that searches for a product code from cell A2 in column A of a table named 'PriceList', returns the price from column C of that table, and returns 'Not Found' if the product doesn't exist."

Conditional aggregation:

"Write a SUMIFS formula to sum values in column E where: column A contains the word 'Actual' (exact match), column B is a date in the current month, and column C equals the value in cell H2."

Dynamic percentage change:

"Write a formula that calculates percentage change between two values, but shows 'N/A' if the base value is zero or blank, and formats the result as a positive or negative percentage."

Text extraction:

"Write an Excel formula to extract the department code from a string in A1. The format is always 'DEPT-XXXX: Description' where XXXX is a 4-character code. I want just the XXXX part."

Data Cleaning with AI

Paste messy data directly and ask AI to clean it:

"Clean this data. Requirements:

• Column A (names): Title case, remove extra spaces
• Column B (dates): Standardise to DD/MM/YYYY
• Column C (amounts): Remove currency symbols and commas, leave as numbers
• Remove any completely blank rows
• Flag any rows where the amount is negative (these may be errors)

[paste your data]"

Building Better Spreadsheet Structures

"I'm building a budget tracking spreadsheet for a [type of business]. It needs to track monthly actuals vs budget for 8 departments and 12 months. Design the tab structure, describe the key input ranges, and suggest the main summary calculations I should build. What are the most common structural mistakes in this type of spreadsheet?"

Pivot Table and Chart Help

"I have a data table with columns: Date, Product, Region, Sales Rep, Revenue, Units. I want to build a pivot table that shows monthly revenue by product, with the ability to filter by region. Walk me through the exact setup steps."

"Suggest the best chart type to show: monthly revenue trend vs budget over 12 months, with actual shown as a line and budget shown as a shaded area or dotted line. Then describe how to build it in Excel."

Google Sheets-Specific

"Write a Google Sheets QUERY formula that pulls rows from Sheet1 where column B equals 'Confirmed', column C is greater than 1000, and returns only columns A, B, C, and E, sorted by column C descending."

"Write a Google Sheets IMPORTRANGE formula to pull data from [describe source sheet] and explain any permissions setup required."

Debugging Help

When a formula isn't working:

"My Excel formula is returning an error. Here is the formula:

[paste formula]

Here is the error I'm seeing: [describe error]

My data structure: [describe]

What's wrong and how do I fix it?"

Using Copilot in Excel (Microsoft 365)

If you have Microsoft 365, Excel Copilot is built in. You can:

• Ask it to create formulas by describing what you want
• Ask it to explain what an existing formula does
• Ask it to suggest insights from your data
• Ask it to format your table

The advantage is it can see your actual data. The same prompt principles apply.

Your Turn

Write down the 3 Excel formulas you most often need to look up or ask for help with. Use the formula prompt template to get AI to write all three. Test them in Excel. Save the prompts that work.