Submitted by: Craig Brody, Microsoft Office Training/Consulting/Programming Instructor, C. Brody Associates

The last article covered how to create a single Pivot Table to quickly summarize your company’s data.  In this article, you’ll see how to create an Excel dashboard by displaying several Pivot Tables and Charts in one convenient location.

Think of a dashboard in your car; what do you see?  Perhaps you see a temperature gauge, fuel gauge and GPS locator?  By scanning them, you can check the status of your car’s operations in many ways.  Well, an Excel dashboard is similar.  Within a single spreadsheet, you can scan several reports to quickly check on your company’s operations.

Suppose your business sells three different products:  Smartphones, Laptops, and Tablets.  Over the course of the year, you have recorded each product order, entering customer name and relevant order data.

It’s now the end of the year and time to summarize company sales with a pivot table.  But suppose you don’t want to change the table each time to get a different type of sales summary.  The solution?  Create several pivot tables and charts all based off the same sales list and place them together in your spreadsheet.  Voila!…you have a dashboard.  Now, you can quickly scan several sales reports, each summarizing sales in a different way.  And you can insert color and lines to make it more attractive to view.  To top it off, your dashboard can include tools such as the slicer and timeline affording you the ability to “slice and dice” company sales by specific time intervals and other measurements.

Here’s a quick overview of steps to create an Excel dashboard based on Excel 2013:

  1. Insert the first Pivot Table. First click – Click in the list you want to summarize. Second Click – click the Insert Tab. Third Click – click Create PivotTable Icon and specify the report on a new sheet. Fourth Click Pick your categories to sum such as Region and Sales.
  2. Add an optional chart by choosing the Analyze tab, choose PivotChart and select your chart type.
  3. Copy and paste the PivotTable to another area of the sheet. Change categories within that second report to summarize sales in a different way than the first report.
  4. Create a second chart based on the second PivotTable if needed. Optionally add additional tables and charts by following the previous steps.
  5. Move, size and align your Pivot Tables and Charts. Add colors, lines, and other styles to create the dashboard’s design.
  6. Add Slicer and Timeline tools through the Analyze tab. You can choose an option to link both tools to every pivot table so when you “slice and dice” by time or some other category all items of the dashboard update together.

Building an Excel Dashboard is easy and fun and most important, can be an important tool to provide insight into your business’ operations.  Please contact the author if you would like to receive a sample dashboard.

===================================

Brody Associates
Microsoft Office Training/Consulting/Programming
cell: 267-872-7410
email: cbrody@outlook.com
web: www.cbrodyassociates.com
linkedin: https://www.linkedin.com/in/craigbrody