Eshrak Kader
Are you an HR professional, looking to make a salary comparison chart in Excel? Then, you’ve come to the right place. With this in mind, the following article demonstrates how to make a salary comparison chart in Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
4 Steps to Make a Salary Comparison Chart in Excel
Firstly, let’s dwell a little upon what is a salary comparison chart?
A salary comparison chart compares employee salaries across the various departments in an organization. Generally speaking, salaries are compared against survey data or the average wage for that role.
Throughout this article, we’ll be using the dataset shown below in the B4:D14 cells. Here, the dataset describes the employee Names, their Salaries, and the Department respectively.
We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
So, without further delay, let’s see the process in detail.
Step-01: Preparing Dataset for Salary Comparison in Excel
At the very beginning, we need to format our dataset which will simplify our task further downstream.
- Firstly, select the row as shown in the image below.
- Now, right-click on the mouse and select the Insert option.
- Next, in the dialog box, check the Shift cells down option.
- Similarly, perform the same process and the results should look like the picture given below.
- Following, remove the duplicate names of the Departments using the DELETE key on your keyboard.
- In turn, merge the cells with the same Department by selecting the cells and pressing the Merge & Center option in the Home tab.
Read More: How to Compare Two Sets of Data in Excel Chart (5 Examples)
Step-02: Calculating Average Salary for Salary Comparison in Excel
- Secondly, select the E5 cell and enter the formula given below.
=AVERAGE($D$5:$D$8)
In this formula, we utilize the AVERAGE function to calculate the average wage of the employees in each Department. Here, $D$5:$D$8 is the range of the salaries of the employees of the Administration department.
📄 Note: Please make sure to provide an Absolute Cell Reference for the D5:D8 cells using the F4 key.
- In a similar fashion, enter the formula in the E10 cell as shown below.
=AVERAGE($D$10:$D$12)
In this expression, the $D$10:$D$12 cells refer to the salaries of the employees of the Finance department.
- Likewise, repeat the same procedure for the E14 cell.
=AVERAGE($D$14:$D$16)
Here, the $D$14:$D$16 cells indicate the range of salaries of the employees of the Marketing department.
- Eventually, the results should look like the following screenshot.
Read More: How to Make a Price Comparison Chart in Excel (3 Suitable Examples)
 Step-03: Inserting Column Chart to Make a Salary Comparison Chart in Excel
- Thirdly, select the Department, Names, and Salary columns.
- Next, go to the Insert tab >> click the Insert Column or Bar Chart drop-down in the Charts section >> select the Clustered Column chart option.
This results in the chart shown below.
- Now, double-click on any of the bars to open the Format Data Series window.
- Then, adjust the Gap Width according to your preference. In this case, we chose, a Gap Width of 0%.
In turn, we format the Border of the bars by choosing the Solid Line option with black Color.
Read More: Side-by-Side Comparison Chart in Excel (6 Suitable Examples)
Step-04: Inserting Line Chart to Show the Average Salary
- Fourthly, select the chart and right-click on the mouse to go to the Select Data option.
After completing the above step, the Select Data Source dialog box appears.
- Then, select the Add button to add a new series to the chart.
- Following, in the Edit Series wizard, choose the Average Salary column as shown in the image below.
Now, this adds the Average Salaries as a Column Chart. However, we want it to be displayed as a Line Chart along with the Column Chart.
- In the next step, select the chart and right-click on the mouse to choose the Change Series Chart Type option.
After that, the Change Chart Type dialog box will open up.
- Choose the Average Salary series >> click on the Dropdown symbol in the Chart Type portion >> select the 2-D Line option >> press OK.
Moreover, you can format the chart using Chart Elements options.
- In addition to the default selection, you can enable the Axes Title to provide axes names,
- Now, add the Data Labels to show the values in the chart,
- Further, insert the Legend option to show the two series.
- Lastly, you can disable the Gridlines option to give your chart a clean look.
- Finally, we get the completed chart as shown in the screenshot below where we have compared the salaries of the employees of different departments.
Read More: How to Create Month to Month Comparison Chart in Excel
Conclusion
I hope this article helped you understand how to make a salary comparison chart in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
