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

Visualizing the data is significantly more impactful and conveys 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 in the same worksheet, just a few columns over from my salary grade table (above).   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

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, left. 

Step 4) Building the Stacked Column Chart

Now 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 below.

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. 

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, representing 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.

While this article walks through the process to visualize a grade structure in Microsoft Excel, our salary structure generator, here, will automate this entire process for you!