Decision Matrix Template in Excel: Weighted Criteria, Scores and a Sensitivity Check

A decision matrix compares several options against the same weighted criteria and gives each option a total score, so the choice rests on stated priorities rather than on whoever argues loudest. A decision matrix template in Excel also ranks the options automatically and lets you test whether the winner changes when the weights move, which tells you how solid the decision really is. This guide covers the steps, the formulas and an example.

When to use a decision matrix

• Three or more options that differ on several criteria.
• A decision that is expensive to reverse: a location, a software system, a key hire, a supplier.
• Several people involved who need to agree on what matters.

For sorting a to-do list, the Eisenhower matrix is the better tool; for choosing among suppliers specifically, see the vendor comparison template.

Step 1: must-haves

List the requirements an option must meet to be considered at all, such as budget ceiling, zoning, or a required integration. Options that fail are removed before scoring.

Step 2: criteria and weights

Choose four to seven criteria and give each a weight; the weights add up to 100%. Agree on the weights before scoring the options, otherwise people adjust them to favor their preferred choice.

Step 3: scores and formulas

Score each option from 1 to 5 per criterion, with a short note explaining the score. With weights in B2:F2 and an option’s scores in row 5:

• Weighted total: =SUMPRODUCT($B$2:$F$2,B5:F5)
• Rank: =RANK.EQ(G5,$G$5:$G$7)
• Winner: =INDEX($A$5:$A$7,MATCH(MAX($G$5:$G$7),$G$5:$G$7,0))

Example: choosing a location for a second store

The suburban center wins, but only by 0.05 over the strip mall. That is too close to call without a second look.

Step 4: the sensitivity check

Change the weights to reflect a reasonable alternative view and see whether the winner changes. If foot traffic is raised to 35% and rent lowered to 20%, the scores become 3.35 (downtown), 3.65 (strip mall) and 3.50 (suburban center): the strip mall wins. So the decision depends on one question, how much the business relies on walk-in customers. Answer that with data, such as the share of sales from new walk-ins at the current store, and the matrix will give a clear result.

A quick way to test: keep two weight rows, “base” and “alternative”, and calculate both totals side by side. If the winner is the same in both, the decision is robust.

After the decision

• Check the numbers behind the winner, for example rent against expected sales with the break-even analysis.
• Put the reasoning in the business plan if lenders or partners need it.
• Note the risks of the chosen option and how to reduce them; a SWOT analysis of the winner is a good start.

More guides are in Project Management.

Frequently asked questions

What is the difference between a decision matrix and a pros and cons list?

A pros and cons list treats every point as equal; a decision matrix weights criteria by importance and scores every option on the same scale.

How many criteria should I use?

Four to seven. With more, each criterion’s weight becomes too small to matter.

Can several people score together?

Yes. Let each person score separately, then average the scores and discuss large disagreements before finalizing.

Does it work in Google Sheets?

Yes. SUMPRODUCT, RANK, INDEX and MATCH work the same way.