Build an Excel dashboard with AI

A one-page dashboard, built one prompt at a time

You decide what to show. The AI tells you how: every formula, chart setting and colour rule.

• Plan a dashboard around the questions your manager actually asks
• Create headline number tiles ("KPIs") with formulas the AI writes for you
• Make clear charts that update when the data changes
• Add traffic-light colours with conditional formatting
• Add a dropdown so one dashboard works for every region, team or month
• Make it look professional enough to share

Good for: anyone who sends the same figures to their boss every month, team leaders who want performance at a glance, and anyone who wants something impressive for their next review.

What you need

Do it in seven steps

1. Plan it with the AI

   Good dashboards answer three to five questions, not thirty. Let the AI help you choose:

   Copy this promptCopy

   I want to build a one-page sales dashboard in Excel (Microsoft 365) for my manager. My data is on a sheet called "Sales" with these columns: Order ID, Order Date, Customer, Region (North/South/East/West), Product, Quantity, Unit Price, Sales Rep, Total. About 300 rows covering January to October 2026.
   
   Suggest a simple dashboard layout: 4 headline numbers, 2 or 3 charts, and 1 small table. For each, say what question it answers. Keep it simple enough for a beginner to build. Then give me a plan of the steps in order.

   Pick the parts you like. A good starting set:

   • Headline numbers: total sales, number of orders, average order value, best region
   • Charts: sales by month (line), sales by region (bar)
   • Table: top 5 products with traffic-light colours

   Tip: create a new sheet called Dashboard and keep all the summaries there. Never build on top of your data sheet.

2. Make the data a Table

   This one step makes everything else update automatically when new rows arrive. Click anywhere in your data, press Ctrl+T (Cmd+T on a Mac), tick My table has headers, then on the Table Design tab name it SalesData.

   Copy this promptCopy

   I've turned my data into an Excel Table called SalesData. Please explain in two sentences why that's helpful, and remind me to use table names (like SalesData[Total]) in all the formulas you give me from now on.

3. Build the headline numbers

   Copy this promptCopy

   On my Dashboard sheet I want four headline numbers, using my Excel Table "SalesData" (columns: Order ID, Order Date, Customer, Region, Product, Quantity, Unit Price, Sales Rep, Total).
   
   B3: Total sales
   D3: Number of orders
   F3: Average order value
   H3: The region with the highest total sales (its name)
   
   Labels go in the cells above (B2, D2, F2, H2). Please give me each formula, and tell me how to format the money ones as £ with no decimals.

   Then make them look like tiles:

   Then tryCopy

   How do I make each headline number look like a dashboard tile in Excel: a big bold number, a small grey label above it, a light background and a thin border? Give me click-by-click steps.

4. Add charts

   Charts need a small summary table to draw from. Let the AI build it:

   Copy this promptCopy

   On my Dashboard sheet, starting in cell B8, I want a small table of total sales by month (January to October 2026) from SalesData. Give me the formulas (I have Microsoft 365, so dynamic formulas are fine). Then tell me how to turn it into a clean line chart: no gridlines, a clear title, and £ on the axis.

   Do the same for regions:

   Then tryCopy

   Now give me a table of total sales by Region in cell E8, sorted highest first, and the steps to make a horizontal bar chart from it, with the bars in one colour and the values shown on each bar.

   Tip: a pivot table plus a PivotChart works too, and it's often easier. Ask the AI "Should I use formulas or a PivotChart for this?" and it will explain the trade-off.

5. Add traffic-light colours

   Conditional formatting colours cells automatically based on their values:

   Copy this promptCopy

   In my Dashboard sheet, cells B20:C24 show the top 5 products and their total sales. I want each product's total coloured green if it's above £10,000, amber between £5,000 and £10,000, and red below £5,000. Please give me click-by-click steps for conditional formatting in Excel.

   Want an arrow showing if this month beat last month? Ask: "Add an up or down arrow next to the monthly total showing whether it went up or down compared to the previous month."

6. Add a dropdown filter

   One dashboard for every region:

   Copy this promptCopy

   I want a dropdown in cell K2 on my Dashboard where I can pick All, North, South, East or West, and every headline number, table and chart updates to show only that region. Please explain how to set up the dropdown and give me the updated formulas for B3, D3, F3 and the monthly table. If "All" is chosen, include every region.

   This is the step that makes people say "how did you do that?"

7. Tidy it up

   Copy this promptCopy

   My Excel dashboard works. Give me a short checklist to make it look professional: colours, fonts, alignment, hiding gridlines, freezing the top row, and protecting formulas so colleagues can't break them by accident.

   Finally, add a line at the top: "Data up to [date]. Source: SalesData sheet." People trust a dashboard more when they know how fresh it is.

Quick fixes

Use it on your own work

You'll understand every number on it

Dashboard software costs money and needs IT to set it up. Excel is already on your computer. Building it yourself also means you understand every number on it, which matters the first time your boss asks "where does that figure come from?"

What to build next