Start Learning Contact Us
Excel for Data Analysis

Project: Build a Sales Dashboard in Excel

Lesson 12 of 12

A dashboard puts the answers to a few important questions on one screen. This project uses every skill from the course.

1. Decide the questions

Write down what the reader needs to know. For the sales data:

  • How much have we sold, and how many orders?
  • Which regions and reps are selling most?
  • How is revenue changing month by month?
  • Which products bring in the most revenue?

Four to six questions is enough. A dashboard that tries to show everything shows nothing clearly.

2. Prepare the data

  • Load the raw data with Power Query, set data types, trim text and remove duplicates.
  • Add Revenue and a Month column.
  • Load the result as a Table named Sales.

3. Organise the workbook

Use three sheets:

Sheet Contents
Data The Sales table, and lookup tables such as Reps
Calc PivotTables and formulas that feed the dashboard
Dashboard Only what the reader sees

4. Build the calculations

On the Calc sheet create one PivotTable for each question:

  • Revenue by Region
  • Revenue by Rep
  • Revenue by Month
  • Revenue by Product, filtered to the top five

Add the headline numbers with formulas:

=SUM(Sales[Revenue])
=COUNTA(Sales[OrderID])
=SUM(Sales[Revenue])/COUNTA(Sales[OrderID])

These are total revenue, number of orders and average order value.

5. Lay out the dashboard

  • Put the three headline numbers across the top in large type. Link each to its cell on the Calc sheet, for example =Calc!B2.
  • Below them, place a line chart of revenue by month across the full width.
  • Below that, place sorted bar charts for region, rep and product.
  • Create each chart as a PivotChart from its PivotTable, then cut and paste it onto the Dashboard sheet.

6. Add slicers

Insert slicers for Region and Category. Right-click each slicer, choose Report Connections and tick every PivotTable, so one click filters the whole page.

The headline formulas above do not respond to slicers. To make them respond, read the totals from a PivotTable with GETPIVOTDATA, which Excel writes for you when you type = and click a PivotTable cell.

7. Finish

  • Give every chart a title that states its message.
  • Use one colour family, and align the edges of the charts.
  • Turn off gridlines on the Dashboard sheet under View > Gridlines.
  • Add a line of text showing when the data was last refreshed.

8. Test it

Add three new orders to the source, choose Data > Refresh All and confirm that every number and chart changes. Click each slicer button and check that the totals still make sense.

What comes next

Excel is excellent for analysis up to a few hundred thousand rows on one person’s computer. The next course, Power BI Mastery, uses the same ideas (Power Query, a data model, measures and visuals) to build dashboards that handle more data and can be shared across an organisation.