Making Dashboards in Excel With Map and Gauge Charts

It’s time for an updated dashboard post. My original post is now three years old and probably overdue for an update. This time around, I’m going to start from scratch using a real data set from the Bureau of Labor Statistics, where I’ll walk you through my process from start to finish. To follow along, you can download the data I’m going to use from here (I’m going to use the 2020 state data. This is the XLS link).

Preparing the data

If your data is no good, then it won’t matter how great your charts and visuals look. That’s why it’s important to have a look through the data to see how usable it is. And you may not notice any issues until you start populating your charts. But one of the things that are noticeable right of the bat in this data set is that instead of empty values on this sheet, there are # or * signs.

That’s going to be a problem if you want to do any computations on this data. You can use Find and Replace to replace the data with empty values. Note that for the *, you’ll need to find ~* rather than just *, otherwise Excel will interpret the * as a wildcard and find everything.

One other thing that I am going to do is create another column for the occupation titles. In column J, there are more than a dozen titles for the ‘major groups’ (major is indicated in column K). I am going to create a table to group them even further. I’ve put this on a separate lookup sheet:

Now, what I am going to do is insert a column on my main data sheet, after column J, which will do a lookup on this table. The formula will be as follows:

=IF(L2=”major”,VLOOKUP(J2,Sheet1!A:B,2,FALSE),””)

Now, I have a category field in column K for the ‘major’ group classifications:

Next, I’m going to convert the data into a table. To do this, click on any of the cells in your data set, and on the Insert tab, click on Table:

Once done, you should notice some default table formatting gets applied to your data set:

And to make it easy to reference, I’m going to click on the Table Design tab, and under the Table Name section on the left, I’m going to re-name the table to tblData:

To change the name of a table, all you need to do is click on it and make your changes, then press enter.

Creating the pivot tables

For this dashboard, I’m going to create pivot tables and use charts to show the following:

• Median salary for the specified position.
• Wages by percentile.
• Median salary for the specified state based on job categories.
• A pie chart showing how many jobs there are by category.
• A gauge chart showing how the median salary compares to the national average.
• A map chart showing the median wages by state.

Median salary for the specified position

To create this visual, I’m going to create a pivot table from the tblData and put it on a new ‘PT’ tab. For this, I am just going to take the average of the A_MEDIAN column. I will also filter the O_GROUP field so that it only includes the ‘detailed’ group to avoid including the categories. I will also adjust the formatting so that it uses the accounting format. The pivot table itself contains just one value:

I only want this value to show up in a box but what I’m going to do is create a column chart from this. For just the number to be visible, I’m going to add a data label and then remove everything else, including the legend, gridlines, and make the column a clear color. Lastly, I’ll copy my first visual onto a new ‘Dashboard’ tab and put the words ‘Median Salary’ directly above it:

Wages by percentile

Next, I’m going to create a bar chart that shows the wages for a position by the various percentiles that are in the data set. For this, I’m going to grab all the different percentile fields, including the median:

• A_PCT10
• A_PCT25
• A_MEDIAN
• A_PCT75
• A_PCT90

I’ll need to set these calculations to be averages just like on the earlier calculation. I can re-name these to ’10th percentile’, ’25th percentile’, and so on, to make it easier to read. Then, I’m going to create a 3-D bar chart, change the colors, and add some labels so it looks like this:

Median annual wage for the specified state based on job categories

Now, I’m going to create a pivot table and chart to show what the median annual wage is across the different categories I specified earlier for the selected region. This is a simple pivot table set up, all that’s needed is the A_MEDIAN average in the values section of the pivot table, the CATEGORY in the rows, and the O_GROUP to filter just the ‘major’ jobs. This will result in the creation of the following column chart:

A pie chart showing how many jobs there are by category

One of the interesting metrics in the data set is the number of jobs there are per 1,000 jobs in the given region. This is going to be similar to the previous chart, except this time I am going to use the JOBS_1000 field. I’m going to use a pie chart for this visual just to change it up a little bit.

A gauge chart showing how the median salary compares to the national average

I’m going to use a gauge chart to compare the median salary against the national median and how it compares. For detailed steps on how to create a gauge chart, please check out this post. For this visual, I need to create one pivot table just for the national median wage. To do this, I just need to grab the median value and filter the O_GROUP by ‘total.’

For the actual gauge chart, I need to set up a table for the slices and the ranges. I will go with a setup as follows:

The % of completion will take the median value and divide it by the national average. But to avoid it going over 100, I’ll use the MIN formula. And for the ‘high end’ value, I take 200 (think of 100 as the top half of the circle and the other 100 the bottom half) and subtract the % of completion and add the size of the slice. Here is what it looks like when the median salary is greater than the national median:

This is what the gauge chart looks like once it’s been set up following the steps in the previous post:

A map chart showing the median wages by state

Creating a map chart is pretty easy in this situation because we have all the state names and all I need to do here is create a pivot table with the A_MEDIAN value. Here’s what my pivot table looks like:

However, you can’t create a pivot chart directly from a pivot table. But there is a way around that. I’m going to create another table that copies the values from the pivot table. They simply equal the values to the left:

Now, I can create a map chart based on this table:

I now have all of my charts set up:

What’s next is to set up the slicers.

Adding and linking the slicers

I’m going to add two slicers for the dashboard, one for the state and one for the job title.

To insert a slicer, all that’s necessary is to click on any one of the pivot tables and on the Insert tab, click on the Slicer button:

Then, select the fields you want to add. Generally, I add the fields that have the most selections and longest names going down vertically. In this case, that’s the OCC_TITLE field. For the State and Category slicers, I have those going across:

I’ve also added a title just below the slicers to give the dashboard a name. The last piece of the puzzle here is to link the slicers to the pivot tables. Previously, I linked them to all of the tables. But for some of these charts, I don’t want them to link to everything.

For the State and Category slicers, I want them to update everything except the national median pivot table. And for the OCC_TITLE slicer, it should also not update the jobs per 1000 pivot table or the median wage by category. The reason being is that those charts will lose their value if only one job is selected, as the point is they should give an overview of the different categories. Similarly, you could also unlink the state slicer to the map chart.

To manage these connections, you can slicer and select Report Connections:

From there, you can select with pivot tables you want the slicer to link to:

And to keep your slicers from staying in put despite any changes, you can also right-click and select Size and Properties and then select the option to Don’t move or size with cells:

Now, the dashboard is ready to go!

If you liked this post on Making Dashboards in Excel With Map and Gauge Charts, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.