How to Make a Salary Comparison Chart in Excel (Create with Easy Steps)

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.

Dataset 1

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.

Preparing Dataset

  • Next, in the dialog box, check the Shift cells down option.

Preparing Dataset

  • Similarly, perform the same process and the results should look like the picture given below.

Preparing Dataset

  • Following, remove the duplicate names of the Departments using the DELETE key on your keyboard.

Preparing Dataset

  • In turn, merge the cells with the same Department by selecting the cells and pressing the Merge & Center option in the Home tab.

Preparing Dataset

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.

How to Make a Salary Comparison Chart in Excel Using AVERAGE Function

  • 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.

How to Make a Salary Comparison Chart in Excel Using AVERAGE Function

  • 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.

How to Make a Salary Comparison Chart in Excel Using AVERAGE Function

  • Eventually, the results should look like the following screenshot.

How to Make a Salary Comparison Chart in Excel Using AVERAGE Function

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.

How to Make a Salary Comparison Chart in Excel Inserting Chart

This results in the chart shown below.

Bar Chart

  • 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%.

How to Make a Salary Comparison Chart in Excel Inserting Bar Chart

In turn, we format the Border of the bars by choosing the Solid Line option with black Color.

How to Make a Salary Comparison Chart in Excel Inserting Bar Chart

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.

How to Make a Salary Comparison Chart in Excel Inserting Bar Chart

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.

How to Make a Salary Comparison Chart in Excel Inserting Line Chart

  • Following, in the Edit Series wizard, choose the Average Salary column as shown in the image below.

How to Make a Salary Comparison Chart in Excel Inserting Line Chart

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.

How to Make a Salary Comparison Chart in Excel Inserting Line Chart

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.

How to Make a Salary Comparison Chart in Excel Inserting Line Chart

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.

How to Make a Salary Comparison Chart in Excel Inserting Line Chart

  • Finally, we get the completed chart as shown in the screenshot below where we have compared the salaries of the employees of different departments.

How to Make a Salary Comparison Chart in Excel

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.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo