How to Build an Excel Dashboard in 15 Minutes (Step by Step)

An Excel dashboard puts your key numbers and charts on one screen, so you can see how the business is doing without digging through rows of data. In this tutorial you will build one from scratch using PivotTables and pivot charts. It shows total sales, profit and quantity, plus profit by segment, category, region, shipping mode and city. The video below walks through every step in about 15 minutes.

What the dashboard shows

The finished dashboard has three headline numbers at the top and five charts below them:

  • Total sales, total profit and total quantity as headline numbers
  • Profit by segment (donut chart)
  • Profit by category (donut chart)
  • Profit by region (line chart)
  • Profit and sales for the top eight cities by sales (column chart)
  • Profit by shipping mode (bar chart)

The data you need

The tutorial uses a sales table with these columns: order ID, order date, shipping date, shipping mode, customer information, city, state, zip code, product details, quantity, discount and profit. The charts also use sales, segment, category and region, so your own data needs fields like these. To practice, you can generate a sales dataset with our Excel Sample Data Generator.

How to build the dashboard, step by step

Step 1: Create the PivotTables

Select all of your data, go to Insert, choose PivotTable and click OK. Build one PivotTable per chart:

  1. Profit by segment: put Segment in Rows and Profit in Values.
  2. Profit by category: copy the first table and replace Segment with Category. Sort largest to smallest.
  3. Profit by region: copy it again and swap in Region. Sort largest to smallest.
  4. Top 8 cities: swap in City and add Sales to Values. Sort largest to smallest, then use the filter button, choose Value Filters, then Top 10, and set it to the top 8 by sum of sales.
  5. Profit by shipping mode: copy the table and replace City with Mode. Sort largest to smallest.

Rename the headings and fix the number formatting as you go.

Step 2: Add the headline numbers

For total sales, total profit and total quantity, use the SUM formula on the matching columns, then correct the number formatting.

Step 3: Plan the dashboard layout

Before you create the charts, lay out the dashboard sheet so you know where each chart will go. It is then easier to place them once they are ready.

Step 4: Create the pivot charts

Click inside a PivotTable, go to PivotTable Analyze, choose PivotChart and pick the chart type:

  • Segment and category: donut charts
  • Region: line chart (add data labels and remove the gridlines)
  • City: 2D column chart
  • Shipping mode: bar chart, with the legend moved to the top

For each chart, right-click and choose Hide All Field Buttons on Chart, remove anything you do not need, and set a clear chart title. Then cut the chart (Ctrl+X) and paste it into its place on the dashboard sheet.

Step 5: Format the dashboard

  • Select each chart, go to Format, then Shape Fill, and choose No Fill to remove the background.
  • Change the chart colors so they match.
  • Add a heading at the top of the dashboard and increase the font size.
  • Insert shapes for the headline numbers. Set the fill and outline as you like, then link each shape to its cell by typing the cell reference in the formula bar. Press Ctrl+D to duplicate a shape and change the reference.
  • Add a second shape with no fill and no outline for each label, linked to the heading cell. Use Format Painter to copy the formatting across.

Practice with the free toolkit

Want a ready-made example to explore? The free Excel toolkit includes a Sales Dashboard sheet, along with a formula cheat sheet, shortcuts, sample data, a budget tracker and a task planner.

Frequently asked questions

Which Excel features does this dashboard use?

It uses PivotTables, pivot charts (donut, line, column and bar), the SUM formula for the headline numbers, and shapes linked to cells.

Do I need to write formulas?

Only a simple SUM formula for the three headline numbers. The rest is built with PivotTables and charts.

Can I use my own data?

Yes. Your table needs fields for sales, profit, quantity, segment, category, region, city and shipping mode, because the charts are built on them.