How to Make Decision Matrix in Excel to Make Better Decisions! [Step by Step Guide]
From simple to difficult we are facing decisions every day. We think about the parameters which could affect the decision and in the end, by looking at the overall situation, we make our decision. And that is exactly what a decision does. It helps you to quantify and rank the importance of your options, depending on the parameters you determine. This artircle will explain how to make decision matrix in Excel.
Here’s quick tutorial on Decision Matrix in Excel:
You may see an example of a Decision Matrix. This example demonstrates the decision of a recruiter who had interviews with four candidates with different properties.
- List and quantify all of your options
- Calculate the weighted average of each option
- Give them numerical values (which is called the weighting factor) according to the level of importance
- Determine which one is superior compared to others
According to the weight of parameters and rates for each option, the recruiter thinks Candidate 3 is the best outcome. That’s how the recruiter will be able to choose the best option among his choices.
However, in the event of a change in weights such as an increase in weight of “Language” from 4 to 5 and a decrease in weight of “Years of Experience” from 4 to 1 the scores would readjust.
That would lead to a conclusion which the recruiter thinks Candidate 4 is the best outcome according to the new weights he decided.
And you can also achieve the best outcome among your options by using an Excel sheet such as this. So, let’s see how to prepare a decision matrix in Excel.
Let’s list down all the procedures you are going to apply to prepare a Decision Matrix.
So let’s start with our first item in the list to construct your Decision Matrix in Excel
You may also download our Free Decision Matrix Template to understand the structure of the template.
1. Setting the Structure
You need to plan the structure in your head.
Make an effective design where you can easily conclude the outcomes.
In this case, we decided to keep the criteria vertically and projects or cases horizontally for an easier evaluation. Keeping the freeze panes right under the cases by selecting the row will help you to see the criteria ratings much easier (if you have many criteria).
Extra Information: You don’t need to Merge & Center the “Ratings” cell to point out the title. You could select the cells where you would like to center, right-click and choose format cells to center the cell among the chosen cells.
Select Center Across selection and hit OK!
You may want to group some of your extra criteria and cases to leave some space for yourself.
To group vertically, select the rows and later hit the group command.
To group horizontally, apply the same procedure except, select columns instead of rows this time and hit the group button.
Once you enter these basic inputs you are ready to formulate the template.
2. Formulate your worksheet
Make sure you put the borders not to get confused during the formulation. Once you entered all borders your document should look like this :
While formulating it is better to keep the grouping signs open. Otherwise, you may skip formulating the grouped cells.
Enter the values you would like to input as a rate for each criterion.
Later let’s formulate our first cell.
You can use “=SUM()” to sum up all the weight values. It is going to be helpful while calculating the weighted average score of each project or case.
Later on, you have to formulate the total score part for each case.
After you calculated the total weight ( Cell D20 in this case ), multiply the weight values with each relevant rating of the candidates and later divide it by the total weight.
That is the necessary formula to find the weighted-average score.
What Is a Weighted Average?
“Weighted average is a calculation that takes into account the varying degrees of importance of the numbers in a data set. In calculating a weighted average, each number in the data set is multiplied by a predetermined weight before the final calculation is made.” Investopedia
After you input the “=sumproduct” formula for the Candidate 1, copy the cell and select the area where you would like to paste the formula and then paste it as formulas.
It is also better to point out the formulated cells not to edit later accidentally. You may color the formulated cells with a light gray so you will know not to edit those cells.
Now your Decision Matrix mainly works however, it cant be said that it is in its best shape. It still needs a bit of formatting for a much better look.
3. Format your worksheet
Now it is time to make your Matrix much cooler. You can play with the colors, add a chart and set conditional formatting for the table.
But before adding a chart and conditional formatting lets make this Matrix a little bit colorful.
With a couple of different colors and formatting now your matrix looks much cooler!!!
Let’s also add some additional features for a nicer functionality.
Adding Conditional Formatting will be a nice touch:
Choose the Total Score cells for all options. Press Conditional Formatting on top and select manage rules. The new rule button on the top will allow you to create a new rule, later you may format all cells based on their values like on the image above.
The conditional Formatting will allow you to color the scores based on their values. As the scores change, the colors will also change accordingly.
By selecting the criteria and criteria values (by holding the ctrl button and selecting the necessary cells) you may create a column chart for a nice visual illustration.
And your template is ready to use…
So, now you have the knowledge about how to make a Decision Matrix in Excel.
You may start and create your own decision matrix. Or check out Decision Matrix Tutorial to learn more about the decision matrix and the template.
Cheers!
Recommended Readings:
Eisenhower Matrix: How to prioritize tasks?
Related Posts
