Unbelievable Dashboards (in 3 easy steps)

Creating an interactive Excel dashboard doesn’t have to be complicated. With just a few steps, we can transform raw data into a dynamic, easy-to-read report that updates automatically as new data comes in.

In this guide, we’ll walk through the process of building an Excel dashboard using pivot tables, pivot charts, and a timeline filter. Whether you’re tracking survey results, sales data, or any other performance metrics, this approach will help you create an engaging and functional dashboard.

Let’s get started!

Video

Step-by-step Tutorial

Step 1: Create Pivot Charts from Your Data

We’ll begin with a simple data table containing survey results for our Net Promoter Score (NPS) surveys.

Note: If your dashboard requires multiple data tables, the same steps that follow will still apply.

Insert the First Pivot Chart

Select any cell in your data table.

Go to the Insert tab → Click Pivot Chart.

Excel will automatically select your data range. Choose New or Existing Worksheet and select a cell as a location for your pivot chart.

Click OK, and you’ll see placeholders for a Pivot Table and a Pivot Chart appear, as shown below.

Summarize Data by Region

In the PivotTable Fields pane, check the box for Region (to categorize data) and Score (to display values).

By default, Excel will sum the scores, but we want to see the average score instead.

Click on Sum of Score → Value Field Settings → Select Average → Click OK.

Now, we have a bar chart displaying the average NPS by region:

Add a Line Chart to Show Trends Over Time

To see how scores change over time, we need another pivot chart:

Go back to your data source, select any cell, and insert another Pivot Chart (same process as before).

In the PivotTable Fields pane, check the boxes for Date and Score.

Excel will create a column chart, but we need a line chart instead.

Go to Design → Change Chart Type → Select Line Chart → Click OK. And bam…

Now, we have two visual elements:

• A bar chart showing NPS by region.
• A line chart showing NPS trends over time.

Step 2: Formatting & Refining Your Dashboard

Good design makes dashboards easier to read. Let’s clean up our charts and make them more visually appealing.

Resize & Align Charts

To make the charts uniform in size:

1. Click the first chart, then hold Shift and select the second chart.
2. Drag from a corner while holding Shift to resize them proportionally.
3. Use Shape Format → Align → Align Top to ensure they are neatly aligned.

Remove Unnecessary Elements

We can reduce chart clutter (also called “chart junk”) by removing extra elements:

• Click each chart and hide all field buttons (PivotChart Analyze → Hide All).
• Remove the legend (if unnecessary).
• Remove gridlines for a cleaner look.

Add Descriptive Titles

1. Click on each chart title and rename them:
   • Net Promoter Score by Region
   • Net Promoter Score by Month

This small step improves readability and makes the dashboard more intuitive.

Step 3: Add Interactivity with Filters

To make the dashboard interactive, we’ll add filters using slicers and a timeline.

Insert a Timeline to Filter by Date

Select either pivot chart and go to PivotChart Analyze → Insert Timeline.

Select the Date field and click OK.

A timeline control appears, allowing users to filter by month, quarter, or year.

By clicking and dragging on the timeline, we can instantly adjust the date range for both charts!

Connect the Timeline to Both Charts

By default, the timeline controls only one pivot table. To sync it with both:

1. Click the Timeline → Report Connections.
2. Check both pivot tables and click OK.

Now, filtering the timeline will update both charts at the same time!

Keeping Your Dashboard Updated

One of the best features of this setup is that it updates automatically when new data is added.

How to Refresh the Dashboard

When new survey data is added to the table:

1. Click Data → Refresh All.
2. The pivot tables update automatically.
3. If needed, adjust the timeline to include new data.

This ensures your dashboard always reflects the latest information!

Final Thoughts

With these three simple steps, we’ve created a fully interactive Excel dashboard using pivot charts, slicers, and a timeline filter. The key takeaways are:
✔ Use pivot charts to summarize and visualize data.
✔ Apply formatting to improve clarity and readability.
✔ Add filters (slicers and a timeline) to enhance interactivity.
✔ Use Refresh All to keep data up to date.

This method is flexible and works for many types of reports. Try it out and customize it for your own needs!

Sample File

Feel free to download the sample file used during the video.

FAQ: Interactive Excel Dashboards

1. Can I add more than two charts to my dashboard?

Yes! You can create multiple pivot charts and arrange them as needed. Just make sure they remain clear and not overcrowded.

2. How do I remove old data from my dashboard?

If data is no longer needed, remove it from the source table and refresh the pivot tables. If necessary, update filters to exclude older periods.

3. Can I use this method with multiple data tables?

Absolutely! If your data comes from multiple tables, you may need to create a data model and use Power Pivot to connect them.

4. Will this work in Excel for Mac?

Yes, but some menu names and features (like timelines) may be slightly different or unavailable in older Mac versions.

5. How can I improve chart design?

For better visualization, follow best practices from experts like Stephen Few’s book “Show Me the Numbers”—it’s a great resource on data visualization principles.

6. What’s the best way to share my dashboard?

Save it as an Excel file (.xlsx) for internal sharing, or export charts to PowerPoint or PDF if you need a static version.