Excel Formulas and Data Analysis with AI
You generate complex Excel formulas and analyze datasets with AI.
Why this lesson matters
You're in Excel. You need to write a VLOOKUP that works across multiple sheets, or an INDEX/MATCH that also has to be conditional. You google, you paste formulas, you get #REF! errors. Sound familiar?
AI is your new Excel buddy. Because it can generate the right formula syntax for exactly your situation in seconds. And if you upload a dataset, you get analyses that would normally cost you an hour in a pivot table.
Asking AI for Excel formulas
The trick is: give AI exactly the context it needs. Describe your situation instead of asking for "a VLOOKUP".
Bad prompt vs good prompt
Bad: "Make a VLOOKUP formula"
Good: "In Sheet1 I have column A with product numbers and in Sheet2
column A with the same product numbers and column C with prices.
In Sheet1 column B I want to pull the price from Sheet2.
Give me the formula for cell B2."Frequently requested formulas
| What you want | Which formula | When AI helps |
|---|---|---|
| Look up a value | VLOOKUP / XLOOKUP | Complex lookup with multiple criteria |
| Flexible lookup | INDEX/MATCH | When VLOOKUP doesn't work (looking up to the left) |
| Multiple conditions | SUMIFS/COUNTIFS | Combining many criteria |
| Array formulas | FILTER, UNIQUE, SORT | Dynamic arrays that expand automatically |
| Editing text | TEXTJOIN, LEFT/RIGHT/MID | Complex string manipulation |
Always include an example of your data. Just type: "My data looks like this: Column A = customer name, Column B = date, Column C = amount." The more concrete, the better the formula.
Keep reading
Leave your name and email address and you can read the rest
You get the whole lesson right away, and every other lesson stays open after that. No password, no confirmation email.
Uploading a dataset and having it analysed
The real power of AI for Excel work is in data analysis. You can upload a CSV or Excel file to ChatGPT (Plus/Team) or Claude and ask:
"Analyse this file. Give me:
1. An overview of the columns and data types
2. Missing values per column
3. The top 5 rows by revenue
4. Mean, median and standard deviation of the 'amount' column"AI can also spot patterns you miss yourself:
"Are there notable trends in this data?
Are there outliers?
Is there a correlation between column X and column Y?"Don't upload files with personal data or financial data to consumer AI tools (free ChatGPT, free Claude). Use enterprise versions or anonymise first. More on this in the Responsible AI module.
Pivot-style analyses with AI
You know pivot tables. AI can do the same, but in plain language.
"Make a summary per region with:
- Total revenue
- Number of orders
- Average order value
Sort by revenue, highest first."Or more complex:
"Compare Q1 2024 with Q1 2023 per product category.
Show the percentage change in revenue and volume."You get the result as a table you can copy straight into Excel or Sheets.
Prompt templates for Excel tasks
Here are five templates you can reuse:
Test your knowledge
What is the best way to have AI write an Excel formula?
Why should you be careful about uploading datasets to AI?
Want to keep your progress, get the practice files and sign up for the free evening? Create a free account. All you do is click a link in your email.
Create a free account