Turn Raw Sales Data into an Interactive Excel Dashboard Using PivotTables

TWX Margerate
Jul 24
4 min read

Updated: Aug 19

In this tutorial, I’ll show you how to build an interactive Excel sales dashboard using PivotTables, PivotCharts, slicers, and timelines.

We’ll start from raw sales data, review the dataset, plan what the dashboard should show, create the supporting PivotTables, and then turn everything into a clean dashboard.

You can download the practice file below and follow along with the video.

Download the practice file:

Watch the full tutorial:

https://youtu.be/fhVXfSjLb6I

Dashboard scenario

In this example, we are working with a sales dataset that contains order-level sales records.

The manager wants a simple dashboard to review sales performance without scrolling through thousands of rows of raw data or checking multiple PivotTables one by one.

The dashboard should help summarize:

Total revenue
Total orders
Quantity sold
Average order value
Monthly revenue trend
Regional performance
Product category performance
Sales channel contribution
Top products by revenue

What’s inside the workbook

The practice file includes several worksheets to help you follow the full dashboard-building process.

Worksheet	Purpose
Start_Here	Quick guide to the workbook
Dashboard_Plan	Dashboard requirements, elements, and fields used
Sales_Data	Raw sales dataset
Supporting_Pivots	PivotTables used behind the dashboard
Sales_Dashboard	Final interactive dashboard

You can use the file to follow along with the tutorial or practise rebuilding the dashboard on your own.

Main steps covered in the tutorial

1. Review the raw data

Before building the dashboard, we first review the raw sales data to understand what each row represents and what fields are available.

For this dataset, each row represents one sales order. The dataset includes fields such as Order ID, Date, Region, Sales Channel, Customer Type, Product Category, Product, Salesperson, Quantity, Unit Price, Discount, and Revenue.

We also do a quick check to make sure the data is suitable for PivotTables, such as checking for proper headers, date format, numeric fields, and consistent labels.

2. Convert the data into an Excel Table

Next, we convert the dataset into an Excel Table.

This helps keep the data source structured and easier to work with when creating PivotTables. It also makes the workbook easier to maintain if more rows are added later.

3. Plan the dashboard

Before creating PivotTables, we decide what the dashboard should show.

This step helps connect the dashboard requirements to the actual dashboard elements.

For example:

Dashboard Requirement	Dashboard Element	Fields Used
Total Revenue	Total Revenue KPI	Revenue
Total Orders	Total Orders KPI	Order ID
Quantity Sold	Total Quantity Sold KPI	Quantity
Average Order Value	Average Order Value KPI	Revenue and Order ID
Monthly Revenue Trend	Revenue by Month chart	Date and Revenue
Regional Performance	Revenue by Region chart	Region and Revenue
Product Category Performance	Revenue by Product Category chart	Product Category and Revenue
Sales Channel Contribution	Revenue by Sales Channel chart	Sales Channel and Revenue
Top Products by Revenue	Top 5 Products chart	Product and Revenue

This gives us a clear plan before building the dashboard.

4. Create supporting PivotTables

Each KPI card and chart in the dashboard is supported by a PivotTable.

Instead of creating charts randomly, we create PivotTables based on the dashboard plan. For example, we create one PivotTable for total revenue, one for revenue by month, one for revenue by region, and so on.

These PivotTables act as the foundation behind the dashboard.

5. Create PivotCharts

After creating the supporting PivotTables, we create the PivotCharts for the dashboard.

The final dashboard includes charts such as:

Revenue by month
Revenue by region
Revenue by product category
Revenue by sales channel
Top 5 products by revenue

These charts help turn the PivotTable summaries into visuals that are easier to read.

6. Add slicers and timelines

To make the dashboard interactive, we add slicers and timelines.

Slicers allow users to filter by fields like Region, Product Category, Sales Channel, and Customer Type.

Timelines allow users to filter the dashboard by date. In the tutorial, I also show how the timeline filters can be arranged for year, quarter, month, and date filtering.

7. Build and format the dashboard layout

Once the main components are ready, we arrange the dashboard layout.

The dashboard includes:

A title/header section
Data updated date
Slicers and timelines
KPI cards
PivotCharts
Clean formatting and spacing

The goal is to make the dashboard easy to read and easy to use.

8. Protect the dashboard sheet

At the end, we protect the dashboard sheet so users can interact with the slicers and timelines without accidentally moving or editing the dashboard layout.

This is useful if you want to share the dashboard with others.

Key notes from the tutorial

Here are a few important reminders from the video:

Review your raw data before creating the dashboard.
Make sure dates and numbers are recognized correctly in Excel.
Plan what the dashboard should show before creating charts.
Use PivotTables as the supporting layer behind KPI cards and charts.
Arrange the dashboard layout before spending too much time on detailed formatting.
Test the slicers and timelines after protecting the worksheet.

Download the practice file

You can download the Excel practice file here:

More practice coming soon

I’m also working on upgrading my PivotTables Practice Pack.

The updated version will include more PivotTable exercises and dashboard practice, so you can practise using PivotTables to answer business questions and build clearer reports.

Stay tuned for more updates.