**Excel** is an excellent tool to calculate and track records of employee performance, salaries, and other related data. In this article, we will learn how we can create a salary increase matrix in excel step by step. We will also provide a formula and a related example to demonstrate how to **calculate salary increases** using a salary increase matrix.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**What Is a Salary Increase Matrix?**

A salary increase matrix sometimes referred to as a merit increase matrix or merit matrix, is a mathematical grid that compensation specialists offer to assist business managers in precisely and successfully giving salary raises to an organizationâ€™s personnel. There are two types of salary increase matrices.

**Standard Salary Increase Matrix**. (Only employee performance is considered)**Two-Variable Salary Increase Matrix.**(Employee performance and compa-ratio are considered)

Currently, the **Two Variable Salary Increase Matrix** system is more popular than the standard one. Hence, we will learn how to create a salary increase matrix based on two variables. They are **Employee Performance** and **compa-ratio**.

## Two Variable Salary Increase Matrix

In this type of salary increase matrix, there are usually two criteria based on which salary is increased.

**Employee Performance: **Usually employee performance is categorized into **5** levels from **1** to **5** where **1** being the lowest performer and **5** being the best performer.

**Compa-Ratio: **It is the short form of a **comparative ratio**. it is the measurement of current salary and market average expressed in percentage. The formula of the compa ratio is below

**Compa ratio= (Current Salary/ Market Average) * 100**

In the matrix, there will be 2 axes. One is vertical and the other is horizontal. See the figure below.

Now, we need to put the values into those cells in such a way that every cell is dependent on the anchor cell.

**Step-by-Step Procedures to Create a Salary Increase Matrix in Excel**

In this section, we will demonstrate how we can establish relationships between the anchor cell (the cell on which values of all the other cells will be calculated) and create a salary increase matrix.

### Step 01: Selection of Anchor Cell

- Here, we will first choose an anchor point. It is the point based on which all the other cell values will be calculated.
- For convenience, we should choose the cell in the middle of the matrix. Here we choose cell It has a
**90 to 100%****compa ratio**and**Employee Performance**of**3.**

- Then, we need to input the percentage increase in salary for this cell. For demonstration purposes, we take the value of
**3%**.

**Read More:** **How to Calculate Average Salary in Excel (6 Effective Methods)**

### Step 02: Setting Weightage Factors for Horizontal & Vertical Scale

- After that, we need to set values to the other cells with respect to the value in cell
**F8**. To do that, we first need to establish the relationship based on some weightage factors. - For instance, we usually would like to give more raise to a level
**5**performer than a level**1**As a result, level**5**will have more weightage factor and it will gradually decrease from level**4**to**1**. - On the other hand, the employee who is underpaid compared to the current market (
**compa-ratio of 90% or less**), deserves more raises than those who are already paid better(**compa-ratio of 100%**or more). - By taking those considerations, we take some dummy weightage percentages for each axis. (see the figure below)

**Read More:** **How to Calculate Net Salary in Excel (With Easy Steps)**

**Similar Readings**

**How to Make Salary Sheet in Excel with Formula (with Detailed Steps)****Attendance Sheet with Salary in Excel Format (with Easy Steps)****How to Calculate Income Tax on Salary with Example in Excel****Leave Salary Calculation in Excel (With Easy Steps)****How to Calculate Bonus on Salary in Excel (7 Suitable Methods)**

### Step 03: Application of Formula in the Cells

- Now that we have taken the weightage factor, we will use them to calculate the value for each cell.
- First, go to cell
**D3**and write down the following formula.

`=$F$8*$B6*D$3`

Here, we multiplied the anchor value **F8 **with vertical weightage factor **B6 **and horizontal weightage factor **D3**.Â Please notice that we have locked the cell reference properly so that we can copy the formula to other cells as well.

- After that, you should have the following results.

- Moreover, use the
**Fill Handle**to auto-fill the formula in other cells.

- Now that all the cell values are connected with the anchored value if we change the value in
**F9 (the anchor)**then all other cell values will also subsequently get changed.

- Now, our Salary Increase Matrix is ready to use.

**Read More:** **How to Calculate Gross Salary in Excel (3 Useful Methods)**

### Step 04: Use of Salary Increase Matrix to Calculate Salary.

- Suppose, we have a list of employees, their
**IDs, Employee Performance, Compa Ratio,**and**basic salary**. (See the figure below)

- Then, we want to determine the
**%Raise**for each employee. To do that, we have to find out the corresponding value according to**Employee Performance**and**Compa-Ratio**in the Salary Matrix. - To automatically determine the value, apply the following formula in cell. It uses
**the INDEX**and**the MATCH functions**.

`=INDEX($D$6:$H$10,MATCH($L6,$C$6:$C$10,0),MATCH($M6,$D$5:$H$5,0))`

ðŸ”ŽHow Does the Formula Work?

ðŸ”ŽHow Does the Formula Work?

**MATCH($M6,$D$5:$H$5,0)**

It searches the Compa Range (M6) in the matrix Horizontal axis of the Compa Ratio(D5:H5) and returns the column number.

**MATCH($L6,$C$6:$C$10,0)**

It searches the Employee Performance (L6) in the matrix vertical axis of the Employee Performance (C6:C10) and returns the row number.

**INDEX($D$6:$H$10,MATCH($L6,$C$6:$C$10,0),MATCH($M6,$D$5:$H$5,0))**

This returns the value of the cell of the array** D6:H10** corresponding to the column number and row number input by Two MATCH functions.

- You should get the following result.

- After that, to get the
**Raised Amount**, multiply the**%Raise**by the**Basic Salary**. Use the following formula below.

`=$N6*$O6`

- Now, use the
**Fill Handle**to copy the formula to other cells. Consequently, you will get the following result.

- So, our final result should look like this.

- To verify, we can check our calculation. For instance,
**Joe Trump**, having Employee ID 1105, has**Employee Performance**Rating of 4 and a**Compa Ratio**in the range of**Above 120%.**If we see at the matrix, we will discover that it has**%Raise**of 3.2%.

This is how we can construct a **Salary Increase Matrix** and use it to calculate the salary increase for all the employees.

**Read More:** **How to Calculate Monthly Salary in Excel (with Easy Steps)**

**Things to Remember**

- We can always change the anchor point to adjust all the
**%Raise**all by once. - We can also adjust the weightage factor according to our needs.

**Conclusion**

That is the end of this article. Hopefully, now you have a fair idea of how to construct a salary increase matrix in excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit **Exeldemy** for more exciting articles on **Excel**.

**Related Articles**

**How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)****How to Calculate Basic Salary in Excel (3 Common Cases)****Daily Wages Sheet Format in Excel (with Quick Steps)****How to Calculate DA on Basic Salary in Excel (3 Easy Ways)****How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)****Per Day Salary Calculation Formula in Excel (2 Suitable Examples)**