How to Make a Dashboard in Excel

Charts & pivot tables · Updated November 18, 2026

To make a dashboard in Excel, organize your data in a clean Excel Table, summarize it with PivotTables, and turn the summaries into charts and key performance indicators (KPIs) on a separate sheet. Add slicers for interactive filtering, then arrange and format the elements so someone can understand the main results at a glance.

What an Excel dashboard does

A dashboard is a compact view of important measures and trends, usually assembled from charts, summary figures, and filters. It is not simply a colorful worksheet: it should help a reader answer a defined set of questions, such as whether sales are increasing, which region contributes the most, or which products need attention. A useful dashboard emphasizes decisions and patterns rather than displaying every available number.

For example, a sales dashboard might show total sales, units sold, sales by month, and sales by region. A manager could use it to compare performance across regions and investigate a particular product or period. The same approach works for project tracking, inventory, customer service, and budgets, provided the source data contains fields that can answer the questions.

Excel dashboards are often built with PivotTables and PivotCharts, which summarize and visualize data without requiring a formula for every grouping. Formulas can also calculate headline KPIs. The two methods can coexist, but filters such as slicers do not automatically change ordinary formula results; that distinction matters when designing an interactive page.

Define the questions before building

Start by writing down the audience and the decisions the dashboard should support. A dashboard for a sales representative may need daily activity and product detail; an executive overview may need only total sales, a trend, and a comparison with a target. A clear purpose helps keep the page focused and prevents a common problem: adding charts simply because the data is available.

Choose a short list of measures and the dimensions people need to compare. Measures are numeric values such as revenue, units, or response time. Dimensions are descriptive fields such as date, region, product, or salesperson. Decide in advance which filters would be useful, such as year, region, or product category. If a measure needs a definition, record it: “sales” could mean gross orders, shipped orders, or revenue after returns.

Sketch the layout before formatting. Put the most important KPIs near the top, a time trend where it is easy to see, and comparisons or breakdowns below. A chart should have a reason to exist: if it does not make a comparison or pattern easier to understand, a small table or a single number may be clearer.

Prepare the source data

Good dashboards depend on consistent source data. Use one row per record and one column per field, with a single header row. Avoid merged cells, blank rows within the data, subtotal rows, and several unrelated tables in one range. Dates should be real Excel dates, numbers should be numeric values, and category labels should be consistent. For example, “North,” “north,” and “N.” may be treated as different categories in a summary.

For a simple example, imagine a sales list with columns named Date, Region, Product, Sales, and Units. It contains six records: North sales of 1,200 and 1,500; South sales of 900 and 1,100; and East sales of 800 and 700. The total is 6,200. Use your own real data when building a dashboard; these figures are illustrative and make it possible to check whether the summaries are working.

Select a cell in the data and choose Insert > Table. Confirm that the table has headers, then give it a meaningful name from Table Design > Table Name, such as SalesData. An Excel Table expands when new rows are added and makes PivotTable sources and structured references easier to manage. Check that every header is unique and descriptive before creating summaries.

Build summaries with PivotTables

Click inside the table and choose Insert > PivotTable. In the dialog, confirm that the table is the source and place the PivotTable on a new worksheet. In the PivotTable Fields pane, drag fields into Rows, Columns, Values, and Filters. For example, drag Region to Rows and Sales to Values to compare sales by region. Excel generally sums numeric fields, but verify the calculation: open the value field settings if it shows Count instead of Sum.

Create separate PivotTables for distinct questions. One can show sales by month, another sales by region, and another sales by product. For a time trend, put Date in Rows and Sales in Values; right-click a date in the PivotTable and choose Group to group dates by months or years when the option is available. If Excel does not recognize the dates as dates, correct the source column first. A text-formatted date cannot be grouped reliably as a calendar date.

For the example data, the region summary should show North at 2,700, South at 2,000, and East at 1,500. These values add to the 6,200 overall total. Checking a few totals against the source is a simple but important quality-control step. A dashboard can look polished while still being wrong because of duplicate records, missing rows, or an incorrect aggregation.

Add KPI figures and formulas

Use a prominent number for a small set of headline measures, such as total sales or units. You can link a dashboard cell to a PivotTable result, or use a formula when the KPI should summarize the entire source table. Keep labels next to the values so that a number is not ambiguous, and apply a consistent number format, such as currency for revenue or a whole number for units.

With the data formatted as a table named SalesData and the sales field named Sales, this formula returns the total of the six example records:

=SUM(SalesData[Sales])

The result is 6,200. The structured reference includes the table’s Sales column and adjusts as table rows are added. If the source columns have different names, use those exact names in the reference.

To calculate sales for a region listed in cell A2, where A2 contains North, use:

=SUMIFS(SalesData[Sales],SalesData[Region],A2)

The result is 2,700 for the example. This formula uses SalesData[Region] as the condition range and A2 as the region to match. These formulas summarize the table itself; a slicer connected to PivotCharts will not filter them. If the KPI must respond to slicers, base it on a PivotTable that shares those slicer connections, or design the KPI using a PivotTable result.

Choose charts that clarify the result

Select a cell in a PivotTable and choose PivotTable Analyze > PivotChart to create a chart connected to that summary. A line chart is usually suitable for change over time. A bar or column chart works well for comparing a manageable number of categories. A stacked chart can show how components contribute to a total, but it is harder to compare individual values when there are many segments.

Use chart titles that state what is being measured and, where useful, the period or unit. Label axes when the meaning is not obvious. Avoid 3-D effects, excessive gridlines, and too many colors; they add visual noise rather than information. Pie charts are best reserved for a small number of parts that form a meaningful whole. If categories are numerous or close in value, a sorted bar chart is often easier to read.

Format numbers consistently across charts and KPI cards. For example, show all sales values in the same currency and use the same level of rounding. Do not hide inconvenient categories or truncate an axis in a way that exaggerates differences. A brief note about the data period or definition can prevent a reader from drawing the wrong conclusion.

Add slicers and make the dashboard interactive

To add a slicer, select a PivotTable and choose PivotTable Analyze > Insert Slicer, then select fields such as Region or Product. A slicer displays selectable buttons, making it more discoverable than a small filter dropdown. For dates, a Timeline can be inserted from PivotTable Analyze > Insert Timeline when the source field contains valid dates.

A slicer initially filters the PivotTable or PivotChart from which it was created. To connect it to other PivotTables, select the slicer and use Slicer > Report Connections (the command may also be labeled PivotTable Connections in some Excel builds). Select the relevant PivotTables. This works when the PivotTables share a compatible source or PivotTable cache. If a PivotTable is missing from the list, check how it was created and whether it uses the same source.

Test every filter before sharing the workbook. Confirm that the chart titles, totals, and other summaries respond as expected, and provide a clear way to remove filters, such as the slicer’s clear-filter button. Remember that a formula-based KPI is not automatically part of a PivotTable’s slicer connections. Either connect a PivotTable-based KPI or make the limitation clear so the headline number does not contradict filtered charts.

Arrange, format, and share the dashboard

Create a dedicated worksheet called Dashboard and move or copy the relevant charts and KPI elements there. Keep related items aligned and leave enough white space for labels. A simple grid helps: KPI cards across the top, one main trend chart beneath them, and smaller category comparisons lower down. Use consistent fonts and a restrained color palette, reserving a highlight color for important changes or selected categories.

Hide worksheet gridlines on the dashboard if they distract, using View > Gridlines. Consider hiding source and PivotTable sheets from casual viewers, but do not mistake hiding for security: workbook users may still be able to unhide sheets or inspect data. If the file contains sensitive information, use appropriate access controls rather than relying on worksheet visibility. Test the dashboard at the size and zoom level people will use, and check that slicers and labels remain readable.

Before distributing the workbook, include a short note stating the reporting period, the last refresh date, and any important metric definitions. Protecting a worksheet can reduce accidental edits, but it may restrict interaction if applied carelessly. Test the workbook as a recipient would, including filtering and refreshing, and make sure the data source is available to intended users.

Refresh data and troubleshoot common problems

When the source table changes, use Data > Refresh All to update PivotTables and PivotCharts. If new records were appended directly below an Excel Table, the table usually expands automatically, and a PivotTable based on that table can include them after refresh. If the source range was a fixed cell range instead, new rows may be outside it; change the PivotTable data source or convert the source to a Table to make future additions easier to manage.

If totals look too high, check for duplicate transactions, hidden filters, and whether the field is being summed or counted. If a new category is absent, confirm it exists in the source and refresh the PivotTable. If dates appear as separate entries instead of grouped months, check for blanks, text dates, or inconsistent date values. If a slicer affects one chart but not another, check Report Connections and verify that the PivotTables can share the same slicer.

For larger or more complex workbooks, Power Query can help import and clean data before it reaches the dashboard. In Microsoft 365 for Windows, use Data > Get Data to access data import options, then load the cleaned result to a worksheet or the Data Model as appropriate. Power Query is useful for repeatable cleanup, such as removing blank rows or standardizing columns; it does not replace the need to define correct metrics or verify results.

Frequently Asked Questions

Can Excel create a dashboard automatically?
Excel can create PivotTables, charts, and suggested visualizations, but it cannot know which measures matter to your audience. You still need to choose the questions, check the summaries, and arrange the dashboard so the results are understandable.

Should I use PivotCharts or regular charts?
Use PivotCharts when you want charts tied directly to PivotTables and slicers. Regular charts can be useful when you need a custom layout or a chart based on a formula range, but their data ranges and filter behavior may need more manual maintenance.

Do Excel slicers filter every number on a dashboard?
No. Slicers filter connected PivotTables and PivotCharts, not ordinary formulas by default. Connect all relevant PivotTables through Report Connections, and build any filter-sensitive KPI from a connected PivotTable.

How do I update a dashboard when I add new data?
Add records to the source Excel Table, then choose Data > Refresh All. Confirm that the table expanded and that the PivotTables use it as their source. A PivotTable based on a fixed range may need its source range updated first.

Can I make an Excel dashboard without PivotTables?
Yes. You can use formulas and regular charts, or use Power Query to prepare data and formulas to summarize it. PivotTables are often the more convenient choice for interactive category summaries, while formula-based designs offer more direct control over calculations and layout.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.