Visualizing a Grade Structure

The source files can be downloaded here:  Salary Grade Visualization 
 
range table

Visualizing salary ranges is one of my favorite things to do as a Compensation Manager.  For whatever reason, the graph that this article will show you how to develop tends to have one of the biggest “wow factors” compared to my other visuals.

Visualizing the data is significantly more impactful and can convey the information in a much more tangible way than a chart, similar to the one, below.  For this exercise we are going to use a stacked column chart, and “trick” excel into presenting the salary ranges in a simple and aesthetically pleasing fashion.

The concept is pretty simple:  create a stacked column chart where the difference between the top of midpoint and top of the minimum sit atop the difference between the midpoint and the maximum rest on top of the minimum.  The following steps will walk you through the process, step-by-step, and I’ve also included a link to the source files.

Step 1) Setting up the Data Table to Create the Salary Range Chart

To begin creating our salary range visualization, we need to first create our regular salary range table, such as the one on the left.  This table will include your salary grade name, the minimum and the maximum.

Step 2) Setting up the Data Table

The second part of this process is to set up the data table so that the salary range will be visualized properly.  The table should look the same, and include the salary grade name and the minimum, but will not include neither the midpoint nor the maximum.  For simplicity, I tend to create this table just a few columns over from my salary grade table (above).

range table 2

The midpoint and maximum will be created using a simple formula based upon the salary range table, in the next step.

Step 3) Calculating the Midpoint and the Maximum for the Chart

range table 3At this point, we already have our minimum but we need to calculate our midpoint and maximum.  This step is where we “trick” excel into creating a chart that looks like what we want.

For the midpoint and maximum columns, we are going to create a formula to calculate the difference between the midpoint and the minimum, and the difference between the midpoint and the maximum of the salary ranges.  The resulting table should look similar to the table on the left.

 

 

 

Step 4) Building the Stacked Column Chart

stacked columnNow that our table shows (a) the minimum of our salary range (b) the difference between the minimum and the midpoint of our salary range and (c) the difference between the midpoint and the maximum of our salary range, we can create our stacked column chart, which is show by the selecting the highlighted icon, in picture on the left.

To create the stacked column chart, select our table, and then choose the stacked column chart in the insert tab of the menu ribbon.  The resulting chart will appear like the one below.  All that is left now is to change the formatting so that the chart is easy to understand.

 

 

unformatted table 1

 

Step 5) Cleaning up the Chart

The first step, is to select the minimum and remove all color.  This step will create the illusion that the salary ranges are “floating”.  My next step is to choose colors for the graph.  I tend to pick colors that match the Company’s branding guidelines.  Finally, I add outlines to the midpoint and maximum columns which creates a line in the middle, which represents the midpoint.

Once we have removed the color from the minimum, and added outlines to the midpoint and the maximum, our chart now resembles what our salary ranges look like.  Advanced users can overlay average employee salaries by grade, as well as average market rate, by grade, for an even more impressive visual.

formatted table