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

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

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

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

### 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)

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

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

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

**Download Practice Workbook**

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

l**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.

**<< Go Back to Salary | Formula List | Learn Excel**