I’m finally making my first blog post to my WordPress site! Took way too long to get to this point, but I’m hoping this is the start of something at the very least enjoyable.

OK if you’ve used Microsoft Excel to make charts, especially using PivotTables, have you ever wondered how to make the chart interactive? In other words what if I want the chart to change based on specific segments of the data? I’ll be honest even in all of my years using Excel I never took the time to understand how to create a dashboard. I did some poking around online and found some helpful resources. A special thanks to instructor Andreas on Udemy.com for his Dashboard examples that inspired this post. Microsoft makes changes with each new version of Excel, however the steps to make a dashboard in Excel 2016 are extremely easy. There are two key words: Slicer, and if your data involves a time element, Timeline. Below I walk through how I transformed PivotTables and PivotCharts into a dashboard.

Here’s the link to my file. I started with a set of made-up scrap-metal data inspired by a company I’ve done Excel consulting for in the past. Then I made a “Report” sheet where I could summarize the data by year and track amount of product sold and total revenue. I added a drop-down list so that I could change the data by year (see cell AC1). Next I made the “Charts” sheet based on the summarized data in Report. The drop-down menu in cell A2 controls the data shown in the charts. Additionally I moved the cell reference for the drop-down in the Reports sheet to cell A2 of the Charts sheet. So now each time I change the year in the drop-down of the Charts sheet the Reports sheet summary data will also change. OK that’s cool but it doesn’t give me the option of slicing the data into further pieces. What if I only want to see how certain metals sold over the timeframe? Or how much I sold to certain companies? Those are the metrics that are going to help me understand my data and make strategic decisions.

Enter Dashboards. In the “Dashboard” sheet I first created two PivotTables that connect to my summary data from Reports. One tracks product sold and revenue in total, the other shows me the top 5 metals sold. Next I created two Pivot Tables “Pivot-Amt in Tons_Total Rev” and “Pivot-Total Revenue” from which I created the two charts in the Dashboard. I simply created these PivotCharts in the PivotTable files FIRST and then moved them over to the Dashboard.

OK finally the fun part. The key to the Dashboard as I mentioned earlier are Slicer and Timeline. First I right-click on one of my PivotTables in the Dashboard and under PivotTable Tools-Analyze I click Insert Slicer found in the Filter section. A window pops open, and I click the check box next to Metal Type, Item Type, Company, and Years. A slicer then appears for each of these data variables. Then I click on each Slicer and go to Slicer Tools-Options-Report Connections. I click on Report Connections and connect each Slicer to the four PivotTables I created for the Dashboard. This is very important! If I don’t create connections the Dashboard will not work!

I repeat the same process above for Timeline, adding Date, and then changing the drop-down to show months (rather than days, which is slicing the data too finely). My Dashboard is finished. OK, so how about testing it? Well, say I click on Aluminum within the Metal Type Slicer. My PivotTables and PivotCharts automatically change to only show data for Aluminum. Also – and here’s a really cool feature – the other Slicers will shade in dark blue the Item Types, Companies, and Years which involve Aluminum data. On the timeline I can, for example, remove the 2012 data since it is thin and just focus on 2013 and 2014.

In addition to all of this I made several formatting changes that help make the Dashboard more visually pleasing, such as removing gridlines and hiding the field buttons on the PivotCharts. Some of the functions that I used to create the Report might be a bit confusing – this will be the topic of a future post.

Now that you know how to make a dashboard in Excel why not give it a try? They are perfect if you are an analyst producing reports for management, or for personal use, such as tracking your daily exercise routine. Have any thoughts, suggestions, or questions? Let me know – I appreciate the feedback!