Home›Guides›AI Prompts for Excel Lookups, Pivots and Dashboards
Intermediate
AI Prompts for Excel Lookups, Pivots and Dashboards
The next step after the basics: joining two sheets, summarising thousands of rows, and catching the errors that make a lookup silently return the wrong answer.
10 min read · Updated 2026-08-18
Step 1
Join Two Sheets Without Learning VLOOKUP
The most common intermediate task is pulling a value from one sheet into another — a price from a product list, a name from a customer table. Describe the two sheets and what should match. Ask for XLOOKUP and a fallback, because which function you should use depends on your Excel version.
Row 4 shows what a missing code looks like when you handle it properly.
Sheet 2 — Products (where the price lives)
A
B
1
Product code
Price
2
P-204
24.90
3
P-118
11.50
4
P-301
149.00
Prompt to copyI have two sheets. Sheet "Orders" has order number in A and product code in B. Sheet "Products" has product code in A and price in B. I want column C of Orders to show the price for that product code. Give me the XLOOKUP version and the VLOOKUP version in case my Excel is older, and make it show "not found" instead of an error when the code is missing.
Why ask for both versions: XLOOKUP only exists in Microsoft 365 and Excel 2021 onward. If you are on an older build the AI will happily hand you a formula your Excel does not have. Asking for the fallback in the same prompt saves a round trip.
Step 2
Diagnose a Lookup That Returns the Wrong Answer
This is the failure mode that costs people hours: the formula does not error, it just returns the wrong row. Almost always it is one of three causes — a text number matched against a real number, trailing spaces, or an approximate match left switched on. Give the AI those symptoms rather than asking it to guess.
Prompt to copyMy lookup returns a value, but it is the wrong row. The lookup column in one sheet was imported from a CSV and the other was typed by hand. Walk me through how to check whether the mismatch is text-vs-number, trailing whitespace, or an approximate match, and give me one formula per check that tells me which it is.
The three checks, so you can spot them yourself: Type mismatch — =ISNUMBER(A2) on both columns. If one says TRUE and the other FALSE, that is your problem. Hidden spaces — =LEN(A2) against what you expect. "P-204 " is 6 characters, not 5. Approximate match — the last argument of VLOOKUP. It must be FALSE (or 0) for an exact match, and it is TRUE by default if you omit it.
Step 3
Summarise Thousands of Rows with a Pivot Table
Once your data passes a few hundred rows, formula-based summaries get slow and fragile. This is where pivot tables earn their reputation. Ask for the click sequence and name your Excel version — the ribbon moved between versions and generic instructions send you hunting.
What you have — one row per sale
A
B
C
D
1
Date
Region
Product
Revenue
2
2026-03-01
North
Keyboard
298.80
3
2026-03-01
South
Mouse
345.00
4
2026-03-02
North
Monitor
447.00
5
…
…
…
…
Repeat that for 4,000 rows.
What the pivot gives you
A
B
C
D
1
Region
Mar
Apr
Total
2
North
745.80
612.40
1358.20
3
South
345.00
489.90
834.90
4
Total
1090.80
1102.30
2193.10
Same data, one screen.
Prompt to copyI have 4,000 rows of sales data: column A date, column B region, column C product, column D revenue. Give me the exact click-by-click steps to build a pivot table showing revenue by region as rows and month as columns, in Excel for Microsoft 365 on Windows. Number each click. Then tell me how to make the dates group by month rather than showing every single day.
Date grouping is the step everyone gets stuck on. Drop a date field into Columns and Excel shows one column per day — 90 columns for a quarter. The fix is right-clicking any date in the pivot and choosing Group, then Months. Ask for it explicitly or you will get a pivot you cannot read.
Step 4
Flag Problems Automatically with Conditional Formatting
Instead of scanning for the numbers that matter, let the sheet mark them. Conditional formatting rules are easy to describe in words and fiddly to build by hand, which makes them a good fit for an AI prompt.
The rules doing the work
A
B
C
1
Product
Stock
Margin
2
Keyboard
4
32%
3
Mouse
58
11%
4
Monitor
21
44%
Stock under 10 turns red; margin under 15% turns amber.
Prompt to copyIn my sheet, column B is stock quantity and column C is margin as a percentage. I want B highlighted red when stock is below 10, and C highlighted amber when margin is below 15%. Give me the exact steps for the New Rule dialog, including which rule type to pick and the formula to enter, for Excel on Windows.
Ask for "use a formula to determine which cells to format". The simpler preset options apply per-cell and break as soon as you want one column to react to another column's value. Naming the rule type in your prompt gets you the flexible version straight away.
Step 5
Clean Imported Data with Power Query Instead of Formulas
If you import the same messy file every week, cleaning it with formulas means redoing the work every time. Power Query records the steps once and replays them on the next file. Beginners avoid it because the name sounds advanced; the prompt below is all you need to start.
Prompt to copyEvery week I download a CSV with the same columns: date, customer, amount. The dates arrive as text, the amounts have currency symbols, and there are blank rows between sections. Give me the Power Query steps to clean all three problems, in Excel for Windows, so that next week I can just refresh instead of redoing it. Number each step and tell me what to click.
The phrase that changes the answer: "so that next week I can just refresh". Without it you will get a one-off cleanup. With it, the AI structures the steps as a repeatable query — which is the entire reason to use Power Query over formulas.
Step 6
Turn the Summary Into a One-Screen Dashboard
The last step is presentation. You do not need a design tool: a pivot, two charts and a slicer on one sheet is what most people mean by a dashboard. Describe the questions it should answer rather than the widgets you think you want.
The three questions the layout answers
A
B
C
1
Panel
Shows
Built from
2
Top left
Revenue by month
Pivot chart, line
3
Top right
Revenue by region
Pivot chart, bar
4
Bottom
Filter by product
Slicer
Prompt to copyFrom my sales pivot table, I want a one-screen dashboard that answers three questions: how revenue is trending by month, which regions perform best, and how both change when I filter to a single product. Tell me which chart type suits each, how to add a slicer that controls both charts at once, and how to lay them out on a single sheet. Excel for Microsoft 365 on Windows, numbered steps.
"A slicer that controls both charts at once" is the detail that makes it feel like a dashboard rather than two pictures. It requires both charts to come from the same pivot cache — if you build the pivots separately the slicer will only drive one of them, and the AI will not warn you unless you ask for the shared behaviour up front.
💡 Two habits carry this whole level: name your Excel version in every prompt, and describe the symptom rather than your theory about the cause. "It returns the wrong row and one column came from a CSV" gets a diagnosis; "my VLOOKUP is broken" gets a guess.
Some outbound links on this page are affiliate links. If you sign up through one we may earn a commission, at no extra cost to you. This never affects which tools we list or how we rank them.