Home > Data > Dashboards > Templates

Excel Dashboard Templates & Tips

Introduction

When you build an Excel dashboard, be sure to show key information clearly, and highlight important data.

In the sections below, you'll find Excel dashboard templates that I've built. You can download them, see how they work, and use these techniques in your own Excel workbooks.

Excel Dashboards - 5 Quick Tips

This short video shows 5 Quick Tips for Excel Dashboard. Keep these tips in mind when you work on your next project, to make the data easier to understand. The steps are also shown below.

Find more tips on the

Dashboard - Interactive Totals

In this interactive dashboard, enter a data range on the dashboard sheet, select criteria from the Range and Category lists, then click the Quantity and Price check boxes, to control which data is shown in the chart. There is no programming required, just formulas.

This technique works best for small to mid-sized tables. Formulas are added to the data table, so the workbook will slow down if you have thousands of rows of data.

There are written steps and screen shots on the Excel Interactive Totals page.

Totals - Top 3 Plus Other

While working on a client project, I had to make charts that showed the top 3 amounts in a category, and the remaining amounts lumped together as “Other”.

First, I built a pivot table to summarize all the sales data, and filter for the Top 3 only. Next, to calculate the amount in the "Other" category, I used formulas beside the pivot table.

You can adapt this technique for other groupings, like sales for the top 3 dates, shown below. You can filter for any number of top or bottom numbers, when setting up the pivot table filter.

Written details are on my Contextures Blog, and you can this workbook in the downloads section, below.

Show Chart or Data on Dashboard

This will save some space on your dashboards. Select "Chart" or "Chart Data" from a drop down list, to show either a Microsoft Excel chart or the chart's data on your dashboard.

There are no macros in the workbook -- this tip uses named ranges and a linked picture.

This video shows you the simple setup steps, and there are written details on the Contextures Blog.

Change All Pivot Charts - One Filter

This video shows how you can use a single Report Filter, connected to a slicer, to update multiple pivot charts. The slicers are stored on a different worksheet, so they don't take up room on your Excel dashboard sheet.

Here is a screen shot of the dashboard, with two single cell filters at the top of the sheet.  One filter is for the date, and the other filter is for the Market areas (East, West, North and South).

Watch this video to see how to set up the pivot tables and the pivot charts, and connect them to the slicer. There are written steps on my Pivot Table blog, and the sample file is in the download section, at the bottom of this page.

Report Diagnostic Dashboard

Use an Excel Report Diagnostic dashboard, to check key pivot tables, and make sure that all the filters are set correctly, before you print the workbook.

In the screen shot below, there's a problem with Report B, highlighted with a FALSE in the Match column.

There are written steps on the Report Diagnostic Dashboard page, and the sample file is in the download section, at the bottom of this page.

Excel Dashboard Training

Note: I am an affiliate for the products mentioned below, and earn a commission on the sales.

Free Excel Dashboard Webinar by Mynda Treacy

A few times each year, Mynda Treacy, from My Online Training Hub, presents a free webinar on creating Excel Dashboards. Click the image below, to get the registration details, and attend the next free session.

Get the Dashboard Templates

Interactive Totals: To see how the completed chart works, you can download the Interactive Totals Chart workbook. The file is zipped, and is in xlsx format, with no macros.

Top 3 Totals: To see Top 3 dashboard chart, with the underlying pivot table and formulas, you can download the DashboardTopDates.zip workbook. The zipped Excel file is in xlsx format

Show Chart or Data: Using named ranges and a linked picture, show or hide a chart based on the selection from a drop down list. Download the Show Chart or Data sample file to see how it works.

Change All Charts: Change the filter at the top of the dashboard sheet, and all the connected pivot charts change. This technique uses Excel slicers, and no macros, and you can download the Change All Charts sample file to see how it works.

Report Diagnostics: To see the completed workbook for report diagnostics, download the Report Diagnostics sample file. The zipped Excel file is in xlsx format, and does not contain any macros.

More Dashboard Info

Dashboard Problems

Dashboard Course Review

Dashboard Tips

Last updated: January 15, 2024 11:28 AM