If you are looking for **how to create a scoring matrix in Excel**, then you are in the right place. Excel is a platform where we need to deal with diverse types of data and hence do the performance evaluation of employees of a company or students or others. We also do analyses according to our database in Excel. Most of the time we do these things by creating a scoring matrix. In this article, we’ll try to discuss how to create a scoring level in Excel.

**Table of Contents**hide

## Download Practice Workbook

## What Is a Scoring Matrix and Where Do We Use It?

In mathematics, a matrix is a collection of data which is kept in a rectangular format. The horizontal part is known as Rows and the vertical part is known as Columns. It is possible to compress and expand the rows and columns of data that make up a matrix. When the matrix incorporates a hierarchy, one useful feature is the ability to drill up and down. A matrix is therefore particularly useful for displaying data.

We can use a scoring matrix as a tool to give various jobs, projects, or properties a relative worth. Using scoring models, governance teams can rate projects according to factors including cost, risk, and potential financial returns. The evaluation of students in an institution or the performance demonstration of the employees of a company can be done by using a scoring matrix.

## 5 Steps to Create a Scoring Matrix in Excel

We can follow some useful steps to create a scoring matrix in Excel.

### Step 01: Making Criteria First to Create Scoring Matrix

Making Criteria is the basic prime term that we need to create a scoring matrix.

- So, firstly, we need to make criteria. Here, we have a column header as
**Criteria**which has**4**The criteria are**Accuracy**,**Discipline**,**Perseverance**, and**Analytical Capability**which have weights of**1.50**,**1.00**,**0.50**, and**2.00**respectively. These are the individual weights of the criteria within the total weight of**10**. - Secondly, we have put
**three**students’ scores in the dataset. Columns**D**,**E**, and**F**represent the score of**Robert**,**Nicolas**,**and Henry**The range of the score of each student for different criteria is**1-5**. The dataset showing**Criteria**and**Score**is like this.

**Read More: ****How to Create a Scoring System in Excel (With Easy Steps)**

### Step 02: Calculating Total Score to Create Scoring Matrix

After making the criteria the second step is to** calculate the total score** of the individual students. In **Row 11**, we’ll calculate the total score of students. To calculate the total score of **Robert**, first, write the following formula in the **D11** cell.

`=SUM(D6:D9)`

Here,** D6**,** D7**, **D8**, and **D9 **are **Scores of Robert **in **Accuracy, Discipline, Perseverance**, and **Analytical Capability** respectively.

- Secondly, press
**ENTER**to find the output as 14. - Thirdly, use
**Fill Handle**by dragging the cursor**rightwards**while holding the**right-bottom**corner of the reference**D11**

Eventually, we’ll get the scores as output like this.

**Read More: ****How to Calculate Quality Score in Excel (with Easy Steps)**

### Step 03: Finding Weighted Score to Create Scoring Matrix

We need to find a weighted score after calculating the total score of the students. We can find it by using **the SUMPRODUCT function**. To find the weighted score of **Robert**, write the following formula in the **D12 **cell.

`=SUMPRODUCT(D6:D9,$C$6:$C$9)`

**Formula Explanation:**

**SUMPRODUCT(D6:D9,$C$6:$C$9)**→ returns the sum of**D6*C6, D7*C7, D8*C8,****D9*C9**, i.e.**(1.50*3 + 1.00*4 + 0.50*2 + 2.00*5)****Output**→**19.5**

- Secondly, press
**ENTER**and use**Fill Handle**to get all the outputs like this.

**Read More: ****How to Calculate NPS Score in Excel (2 Methods)**

### Step 04: Calculating Percentage of the Best

In this step our aim is to calculate the percentage of the best. It is done by finding the maximum value among the Weighted Scores first. We can find the maximum value by using **the MAX function**.

To show this, firstly, write the formula in the **D13** cell to calculate the percentage of the best of **Robert **like this.

`=D12/MAX($D$12:$F$12)`

**Formula Explanation:**

**MAX($D$12:$F$12)**→ returns the maximum value among cells**D12, E12, F12**E. among**19.5, 15.5, 18**.**Output**→**5****D12/MAX($D$12:$F$12)**→ returns the output of division between**5**and**19.5.****Output**→**1.0000**

- Secondly, press
**ENTER**and use**Fill Handle**to find the other outputs.

**Read More: How to Create Scorecard in Excel (with Detailed Steps)**

### Step 05: Determining Rank

Determining rank for each student is the last step. We’ll try to give the students a rank who is better than the others. Using** the RANK function**, we can do it easily.

Initially, write the formula to find the rank of Robert in the **D14 **cell like this.

`=RANK(D13,$D$13:$F$13)`

Here,** the RANK function** mainly finds out the rank of **D13 **i.e. **1.0000** among the other values of cells **D13:F13** i.e.** 0.7949** and **0.9231** respectively.

- Similarly, press
**ENTER**and use**Fill Handle**to find the other outputs like this.

## Conclusion

We can create a scoring matrix very easily if we study this article properly. Please feel free to visit our official Excel learning platform **ExcelDemy **for further query.