Foundations: Build AI-Powered Excel Dashboards with Copilot & ChatGPT

A dashboard is one Excel sheet that shows the health of a business at a glance using numbers, charts, and filters. Microsoft Copilot and ChatGPT speed this up by writing formulas, cleaning data, and suggesting layouts for you. Master this shared method first — every domain dashboard in this module builds on it.

Table of Contents

• 1. Curriculum Note: How These Tutorials Are Organized
• 2. What You Need Before You Start
• 3. The AI Dashboard Workflow
• 4. Prepare Data with Copilot
• 5. Prompt Patterns That Work
• 6. Build KPI Cards
• 7. Choose the Right Chart
• 8. Add Interactivity with Slicers
• 9. Layout & Design Rules

1. Curriculum Note: How These Tutorials Are Organized

This module covers nine dashboards: Sales, HR, Finance, Marketing, Inventory, Student, Hospital, Project, and CEO. They all use the same AI tools but answer different questions, so they are grouped into four closely-related thematic tutorials after this one:

• Tutorial 2 — Sales & Marketing Dashboard: Sales + Marketing (revenue functions, both funnel-based).
• Tutorial 3 — People Dashboards: HR + Student (track individuals and performance).
• Tutorial 4 — Financial & Executive Dashboards: Finance + CEO (plan-vs-actual, and the executive summary).
• Tutorial 5 — Operations Dashboards: Inventory + Project + Hospital (resource and capacity tracking).

This first tutorial teaches the shared method; the next four apply it to each theme and add only the domain-specific KPIs, data model, and prompts. Read this one first.

2. What You Need Before You Start

You need a Microsoft 365 subscription with Copilot for Excel, plus a free or paid ChatGPT account. Your data should live in an Excel table (not a plain range) so Copilot can read column names.

Why it matters: Copilot reasons over structured tables. A messy range with blank rows and merged headers will confuse it.

Turn a range into a table

Select your data → Insert → Table (or press Ctrl + T)

Give the table a clear name like SalesData or HRData so your prompts stay readable.

3. The AI Dashboard Workflow

Follow the same five steps for any dashboard:

1. Load & clean the data (Copilot).
2. Plan KPIs with ChatGPT (ask what metrics matter for your domain).
3. Build calculations — totals, ratios, trends (Copilot writes the formulas).
4. Visualize — charts and KPI cards (Copilot inserts them).
5. Polish — layout, colors, slicers (ChatGPT suggests, you apply).

This order avoids rework. Always plan the KPIs before you draw a single chart.

4. Prepare Data with Copilot

Copilot sits in the Excel ribbon. Click a cell inside your table, then type a task in plain English.

Example prompt in Copilot

Highlight duplicate rows in this table and add a column that flags them.

Copilot returns a formula such as:

=IF(COUNTIFS([OrderID],[@OrderID])>1,"Duplicate","")

Simple explanation: COUNTIFS counts how many times that OrderID appears; if it shows up more than once, the row is labeled "Duplicate."

5. Prompt Patterns That Work

Use these four patterns so Copilot and ChatGPT give consistent, reusable results.

ChatGPT planning prompt

I run an HR team. Suggest 6 KPIs for an Excel dashboard, with the
source columns I need and the Excel formula for each.

ChatGPT returns a ready spec you can paste straight into Excel.

6. Build KPI Cards

A KPI card is one big number with a label, e.g. "Total Revenue: $1.2M". Place 4–6 across the top of the dashboard.

Total revenue

=SUM(SalesData[Revenue])

Month-over-month growth

=(SUMIFS(SalesData[Revenue],SalesData[Month],"2026-07")-
  SUMIFS(SalesData[Revenue],SalesData[Month],"2026-06"))/
 SUMIFS(SalesData[Revenue],SalesData[Month],"2026-06")

Format that result as a percentage to show growth at a glance.

7. Choose the Right Chart

Pick the chart by the question you are answering, not by how it looks.

• Column / Bar — compare categories (sales by region).
• Line — show a trend over time (revenue by month).
• Pie / Doughnut — show a share of a whole (use sparingly).
• Combo — bars plus a line for target vs actual.

Copilot prompt:

Add a line chart showing the revenue trend by month from this table.

8. Add Interactivity with Slicers

Slicers let viewers filter the whole dashboard by clicking buttons. They connect to PivotTables and tables.

Steps

Insert → Slicer → choose Region and Month

In Slicer settings, open Report Connections and link it to every PivotTable on the sheet. One click now filters all charts.

Note: Plain SUM formulas do not react to slicers. For slicer-driven visuals, build the chart from a PivotTable, or use SUMIFS fed by cells the slicer controls.

9. Layout & Design Rules

Keep it scannable. Top row = KPI cards. Middle = main charts. Bottom = a detail table. Use one accent color on a neutral background.

Best practices:

• Aim for one screen, no scrolling, on executive dashboards.
• Left-align titles; right-align numbers.
• Skip 3D charts — they distort values.