How to Create a Scoring Matrix in Excel (with Easy Steps)

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.

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

In mathematics, a matrix is a collection of data that 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.

How to Create a Scoring Matrix in Excel: with Easy Steps

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.

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.

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.

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.

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

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.

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

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.

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.

Download Practice Workbook

Conclusion

We can create a scoring matrix very easily if we study this article properly. Please share any further queries or recommendations with us in the comments section below.

<< Go Back to Scoring | Formula List | Learn Excel