How to Make a Dashboard in Excel: Step by Step
A dashboard in Excel puts your key numbers and charts on one sheet, with filters that update everything at once. To make one, format your data as a table and build a PivotTable for each view. Add a PivotChart to each, then add slicers and a timeline to filter them together. Finish by arranging the layout on one screen.
This guide covers what an Excel dashboard is and how to plan one. It then walks through building one with PivotTables, PivotCharts, slicers, and a timeline. The later sections cover an AI method in three steps, an example layout, refreshing and sharing, and common mistakes.
What an Excel dashboard is
Microsoft's dashboard tutorial gives a clear definition. "A dashboard is a visual representation of key metrics that allows you to quickly view and analyze your data in one place."
It also explains why dashboards beat static reports. They offer "a self-service business intelligence opportunity, where users are able to filter the data to display just what's important to them." In the past, Excel reporting "often required you to generate multiple reports for different people or departments."
A dashboard in Excel usually has four parts, whatever the data:
- KPI tiles. A few headline numbers, such as revenue, orders, or margin.
- Charts. Three to six charts that show trends, mix, and rankings.
- Filters. Slicers and a timeline that change every chart at once.
- A data sheet. The source table, often on a hidden or separate sheet.
The method in this guide follows Microsoft's approach. It uses "multiple PivotTables, PivotCharts, and PivotTable tools to create a dynamic dashboard." The benefit is speed. Microsoft says "you only need to create the dashboard report once," then refresh it when data changes.
Plan your dashboard before you build it
A good dashboard in Excel answers a few questions well. Write those questions down before you open Excel. Most weak dashboards fail here, not in the charts.
- Who reads it? A sales manager, a CFO, and an operations lead need different numbers.
- What decisions does it support? For example, which region needs attention this week.
- Which metrics answer those questions? Pick four to six. More than that turns a dashboard into a report.
- How often does the data change? Daily data needs a different refresh routine than monthly data.
- What filters do readers need? Common choices are region, product, salesperson, and date.
Then sketch the layout on paper. Put the most important number at the top left, where eyes land first. Put trends below it, and breakdowns to the right.
For help choosing which metrics earn a place, see our guide on how to build a KPI dashboard from a spreadsheet. It covers which metrics to cut and how many tiles a dashboard should have.
Prepare your data
Every PivotTable on the dashboard reads from one source, so the source must be clean. Microsoft's tutorial says "Verify your data is structured properly with no missing rows or columns." It adds "Each row should represent an individual record or item."
Microsoft's PivotTable guide gives more data tips:
- "Organize your data in columns, not rows."
- Give every column a header, "with a single row of unique, non-blank labels for each column."
- "Avoid double rows of headers or merged cells."
- Format the data as an Excel table, by selecting Insert, then Table.
Here is a sample layout for sales data. The values are invented.
| Order date | Region | Product | Salesperson | Units | Revenue |
|---|---|---|---|---|---|
| 2026-09-02 | North | Desk chair | A. Patel | 12 | $2,280 |
| 2026-09-02 | South | Standing desk | J. Kim | 4 | $1,960 |
| 2026-09-03 | West | Monitor arm | L. Moreno | 20 | $1,180 |
Keep numbers as numbers. Microsoft warns that if Excel reads a value field as text, it shows a count instead of a sum. That is why "it's so important to make sure you don't mix data types for value fields."
Build the PivotTables
PivotTables do the counting and summing behind each chart.
- Select any cell in your table.
- Select Insert, then PivotTable, then New Worksheet.
- Drag fields into Rows and Values. For example, Region in Rows and Revenue in Values.
- Format the numbers, sort the rows, and pick a report layout.
Microsoft suggests building one careful master PivotTable first. It says "This PivotTable will be the basis for others." So set the style, report layout, and number formats now, before you copy it.
Next, copy and paste the master to make more views, then change the fields in each copy. One might show revenue by month, another revenue by product, and another units by salesperson.
Leave space between them. Microsoft warns that "PivotTables can't overlap one another." Plan for them to grow as filters change.
Finally, name each PivotTable. Select it, then go to PivotTable Analyze, then PivotTable, and type a name. Microsoft says the names matter "when it comes time to connect your PivotTables to Slicers and Timeline controls."
Add PivotCharts
A PivotChart turns each PivotTable into a chart that follows its filters.
- Click inside a PivotTable.
- Go to PivotTable Analyze, then Tools, then PivotChart.
- Choose a chart type, then select OK.
- Size and format the chart from the PivotChart Analyze, Design, and Format tabs.
Match the chart to the question:
| Question | Chart type |
|---|---|
| How is it changing over time? | Line or column chart |
| How do categories compare? | Bar chart, sorted |
| What is the mix? | Stacked bar, or a pie with few slices |
| How do two measures relate? | Combo chart with a secondary axis |
Microsoft's example uses a Combo chart. Sales appear as columns, and the share of total appears as a line on a secondary axis.
Rename each chart too. Go to PivotChart Analyze, then PivotChart, and type a name in the Chart Name box.
Add slicers and a timeline
Slicers and timelines make a dashboard interactive. Microsoft says "Slicers provide buttons that you can click to filter tables, or PivotTables."
Insert slicers. Select any PivotTable, then go to PivotTable Analyze, then Filter, then Insert Slicer. Check the fields you want, such as Region and Product.
Connect them to every view. A new slicer controls only one PivotTable at first. Microsoft says "Slicers will only be connected to the PivotTable you used to create them." Select each slicer, then go to Slicer, then Report Connections, and check every PivotTable it should control. Slicers "can only be connected to PivotTables that share the same data source."
Insert a timeline. Select a PivotTable, then go to PivotTable Analyze, then Filter, then Insert Timeline. Choose your date field. A timeline filters by years, quarters, months, or days with a slider.
Connect the timeline. Microsoft says "you can use a single Timeline to filter multiple PivotTables," if they share a data source. Use Report Connections on the timeline as well.
If you want a slicer and a timeline on the same date field, one setting is needed. Check "Allow multiple filters per field" in the PivotTable Options dialog box.
Arrange the layout and finish
Now bring everything onto one sheet. This is where the PivotTables become a dashboard in Excel that people can read at a glance.
- Move the charts and slicers onto a new sheet named Dashboard. Keep the PivotTables on their own sheets.
- Add KPI tiles at the top, using simple formulas that point to PivotTable totals.
- Add a title that states the scope, such as "Sales dashboard, September 2026."
- Turn off gridlines and headings. Microsoft's example turned them off from the View tab for a cleaner look.
- Align the pieces. Hold Ctrl, select several slicers, and use the Align tools.
- Test every filter. Microsoft says to test each slicer and timeline before you share.
That last check matters. Microsoft notes some selections can make one PivotTable try to grow into another. Excel then shows an error, and you should fix the spacing before others use the file.
How to make a dashboard in Excel with AI
The manual method is the classic way to make a dashboard in Excel, and it works well once the data is clean. Often it is not. The numbers sit in several exports, or the columns need fixing first. An AI tool can do that preparation and draft the charts in one request.
Here is the method in three steps, using Powerdrill Bloom.
Step 1: Upload your Excel or CSV file
Start with the raw export rather than a hand-edited copy. The Free plan lists "Upload Excel, CSV, PDF, and docs."
The Excel AI Assistant page says you can "Upload your Excel files and let Powerdrill Bloom automatically analyze data, uncover insights, and create visual reports." If the file has several sheets, name the one you want in your request.
Step 2: Describe the dashboard in natural language
Describe the metrics, charts, and filters in one or two sentences. For example: "Build a sales dashboard with revenue by month, revenue by region, and top 10 products. Let me filter by region and quarter."
You can also ask follow-up questions, such as "Which region fell furthest below last quarter?" The page says the tool "identifies trends, correlations, and key metrics in your Excel data automatically."
Step 3: Check the numbers and share the dashboard
Before you share, check the totals against the source. Ask for the table behind each chart, and confirm the revenue total matches your file. The Powerdrill Bloom homepage says "Every number comes back with the page, the row and the figure behind it."
For finance data, the AI Finance Dashboard Builder offers a CFO scorecard and a P&L board. Its page lists sharing as an "internal link or scheduled PDF snapshot." For a recurring version, the Scheduled Agent can rerun the analysis daily, weekly, or monthly.
An example dashboard layout
Here is a layout for a monthly sales dashboard built from the sample data above. The structure is an example to adapt.
| Area | What goes there |
|---|---|
| Top row | Four KPI tiles: revenue, units, orders, average order value |
| Left, large | Line chart: revenue by month for the last 12 months |
| Right, top | Bar chart: revenue by region, sorted |
| Right, bottom | Bar chart: top 10 products by revenue |
| Bottom | Table: revenue by salesperson, with this month and last month |
| Filter strip | Slicers for Region and Product; a timeline for Order date |
Read a dashboard in Excel from the top left, the way readers scan a page. The tiles answer "how are we doing?" The line chart answers "which way is it moving?" The bar charts answer "where is it coming from?"
For more chart ideas, see our guide to free Excel dashboard tools. It compares other ways to build dashboards from spreadsheets.
Keep the dashboard up to date and share it
Refresh after new data arrives. Add new rows to the Excel table, then refresh. Microsoft says to select any PivotTable, go to PivotTable Analyze, select the arrow under Refresh, and select Refresh All.
Share one current copy. Microsoft's tutorial shows how to share through a Microsoft 365 group. You store the workbook in the group's files library, then pin it to the top of the SharePoint document library. Readers then open the same current version each time.
Protect the layout. Lock the dashboard sheet if others will use it. That keeps charts and slicers from being moved by accident.
Keep a change note. Add a small text box with the data date, such as "Data through September 30, 2026." Readers then know how fresh the numbers are.
Review it with a reader. Once a quarter, ask one regular reader which charts they actually use. Remove the ones nobody opens, and add the question they keep asking. A dashboard in Excel stays useful only if it keeps up with the decisions it supports.
Dashboard mistakes to avoid
Watch for these before you share a dashboard in Excel.
- Too many charts. More than six views on one screen hides the main message. Cut or move extras to a second tab.
- Unconnected slicers. A slicer that filters only one chart confuses readers. Check Report Connections for every slicer.
- Messy source data. Merged cells, blank headers, and text in number columns break PivotTables. Clean the table first.
- Pie charts with many slices. Use a sorted bar chart once you have more than five categories.
- No refresh routine. Decide who refreshes the file and when. A stale dashboard is worse than none.
- Missing labels. Every chart needs a title, axis labels, and units.
Want to skip the setup? Try Powerdrill Bloom with one sales export and ask for a dashboard with the filters you need.
Frequently asked questions
How do I make a dashboard in Excel?
Format your data as a table, then build a PivotTable for each view you need. Add a PivotChart to each PivotTable, then insert slicers and a timeline. Connect them to every PivotTable with Report Connections, and arrange everything on one sheet.
Do I need PivotTables to make an Excel dashboard?
No, but they make it much easier. PivotTables summarize the data and work with slicers and timelines, so every chart filters together. Without them, you need formulas such as SUMIFS for each chart, which takes longer to update.
How do I make slicers control all charts on a dashboard?
Select a slicer, then go to Slicer and select Report Connections. Check every PivotTable the slicer should control. Slicers can only connect to PivotTables that share the same data source.
How do I update an Excel dashboard with new data?
Add the new rows to the source Excel table. Then select any PivotTable, go to PivotTable Analyze, select the arrow under Refresh, and choose Refresh All. Every PivotTable, chart, and slicer updates.
Can AI make a dashboard from my Excel file?
Yes. AI tools can read an Excel or CSV file and build charts and summaries from a request in natural language. Check the totals against your source data before you share the result.
Sources: Microsoft Support, Create and share a Dashboard with Excel and Microsoft Groups · Microsoft Support, Create a PivotTable to analyze worksheet data · Microsoft Support, Create a PivotChart · Microsoft Support, Use slicers to filter data · Microsoft Support, Create a PivotTable timeline to filter dates.