Prioritizing, justifying, and documenting decisions are easier when you use an Excel-based decision matrix. However, there are a few rules that will make this work better for you.

For clarity, this description and the downloadable Excel sheet show a set of prioritized projects. You can use this method to prioritizing or deciding between many types of alternatives.

Click here to download the Excel-based Decision Matrix

Make Data-Driven Decisions Using an Excel Decision Matrix

Use an Excel Decision Matrix to prioritize or decide between almost any list of projects or choices. The basic concept comes from Six Sigma tools: the Pugh Matrix and Quality Functional Deployment (QFD).

With an Excel-based Decision Matrix and the following process will help you with,

• HIPPO – Highest Paid Person’s Opinion. The matrix helps prevent the HIPPO from pushing forth and trampling fact-based decisions.
• Buy-in – The decision team should contain eight to 12 people composed of Subject Matter Experts (SMEs), stakeholders, and managers. With these key players in the process you will produce a more informed decision and build buy-in.
• Documentation – This method makes it easy to document the decision reasoning right within the Excel worksheet. A later section shows how to document within Excel.

The Decision Process

The steps for bringing a team to a decision are,

1. Identify alternatives. Alternatives are the competing projects or solutions.

2. Identify the decision team. Include SMEs, stakeholders, and managers.

3. Educate the decision team. Distribute briefing notes before the meeting and use a rapid briefing technique at the beginning of the meeting. An effective rapid briefing technique will be in a following article.

4. List decision criteria. Decision criteria are attributes used to compare alternatives. Examples might be Competitive Advantage or Strategic Business Objective Alignment. Try to use no more than five decision criteria, or the weighting and comparisons become muddied and insignificant.

5. Assign a weight of 1 to 5 for each Decision Criteria. Assign a rank of 1 – 5 for how important each Decision Criteria is in the selection process.

6. Assign a rank of 1, 3, or 9 for each Project based on how it compares to other Projects in that decision criteria.

7. Calculate the total scores by multiplying the Criteria weight and the Project rank and totaling for each project.

8. Take time for a “gut” check!

Identify and Weight Decision Criteria

Pick the three to five most critical Decision Criteria. Using too many decision criteria creates confounding variables that distract from critical discussion and muddies results.

Weight Decision Criteria using a scale of 1 to 5 or 1 to 10. In general, use a scale of 1 to 5. Psychological research has shown humans have difficulty discriminating between the small differences in a 1 to 10 scale.

Here is a list of possible Decision Criteria.

• Alignment with Strategic Business Objectives
• Estimated ROI
• Competitive Advantage
• Estimated Cost
• Ease of Execution
• Time to Completion
• Customer Desire
• Stakeholder Impact/Buy-in
• Leverage with Existing Brand, Products, Services, Channels
• Return on Scale
• Resource Availability
  • Internal Capability
  • Outsource Capability
  • Financial
  • Expertise
  • Partners

Use 0, 1, 3, and 9 for Project Ranking

Use weights of 0 (None), 1 (Low), 3 (Mid), or 9 (High) to rank how Projects compare to each other for the same criteria.

Ranking with a 0, 1, 3, and 9 geometric progression produces a clear well-defined winner in the final totals. Using a linear ranking of 1 – 5 or 1 – 10 very often produces a cluster of three or four alternatives with very close total scores. This final cluster of ties makes it difficult to see a clear winner.

Prepare Decision Makers

Send a brief to Subject Matter Experts, stakeholders, and managers before the meeting. Keep briefs to a concise one-page description of critical factors for each project.

At the beginning of the meeting, after presenting the need for a solution, you may want to review alternatives using a “rapid visual presentation” method that is short and quick. (I’ll cover “rapid visual presentation” in a later article.) It can also be good to do a visual review of the SWOT results and the resulting SWOT Action Plan.

Assign 0, 1, 3, and 9 Ranking Judgements

One of the easiest ways to rank projects is to quickly walk through each column in turn and look for obvious low cells, ranked 0 – None or 1 – Low. Follow this by walking through all columns again looking for cells where there is a consensus of 9 – High.

Once you have obvious None, Low, and High filled-in, begin work on the interior.

One of the easiest ways to rank the interior is to go one column at a time and ask which project is topmost, which is bottommost, (you may have done most of these), and then do comparative card sorting to decide on those in the middle. Remember, the only rank values allowed are 0, 1, 3, and 9.

Do You Trust the Matrix?

Do you trust the results of the matrix?

Highly experienced teams with tens of years of experience in a vertical domain sometimes reach intuitive decisions that contradict data. These decisions come from years of experience and outcomes. That is why your decision team should have the most experienced people. Their intuition is based deep in the amygdala of their brain. It produces that gut-level feeling that that something is wrong.

Few business decisions face the immediacy of pilots with engine flame-outs or front-line warriors ambushed in a firefight. So, sit back.

It’s best to sit with the results for a few days. Reconvene a senior group and look at the results.

Did anyone have misgivings? Was anything missed? Was a weighting incorrect? Was undue political pressure used to skew the decision?

Documenting the Decision Matrix

Document your decisions in another Excel worksheet tab with the same row-column structure as your Decision Matrix. You can just make a copy of the blank Decision Matrix. 

Designate one person to be the recorder and take notes on critical decisions or reasoning. It is too hard for the facilitator to lead, facilitate, and transcribe at the same time.

Click here to download the Excel-based Decision Matrix

The dedicated recorder can manually break lines or word wrap as they type in a cell by pressing Alt+Enter.

Widen matrix columns so cells can hold a few words. Apply word wrap to all interior cells so long lines wrap within the cell. To do this, select interior cells, choose the Home tab, Alignment group, and select Word Wrap.

Make rows tall enough for multiple lines by selecting all rows of the matrix and dragging the bottom edge of the row header down.

To keep headings on screen with the larger matrix, select the top left cell in the matrix, E8 in the example, and on the View tab, in the Windows group, choose Freeze Panes, Freeze Panes. You can now scroll the matrix any direction, and the headings remain visible.

Creating the Excel Weighted Decision Matrix

The decision matrix is pretty easy to create. Although any basic Excel user could create the matrix using just multiplication and addition, doing so would require some bruising manual formulas. This downloadable Excel decision matrix uses a few tricks to make the work easier.

Click here to download the Excel-based Decision Matrix

Criteria in the top headings are on a 45-degree slant. To do slant text headings, select the cells, right-click and choose Format Cells, select the Alignment tab, then enter 45 for the Text Orientation.

Totals could be calculated by manually entering a demanding number of multiplications and totals. However, the downloadable example uses the SUMPRODUCT function.

=SUMPRODUCT($E$7:$I$7,E8:I8)

The Criteria weighting array, $E$7:$I$7, uses Absolute reference so that the formula in J8 can be copied down the Totals column.

Use Data Validation lists to make drop-down lists for Criteria and alternative weightings. This ensures only valid weights are entered. In the downloadable worksheet with conditional formatting, shown below, the Data Validation list for Criteria with ranges of 1 – 5 are in O7:O11. The Data Validation list for Project rankings of 0, 1, 3, and 9 are in P7:P10.

The Decision Matrix using conditional formatting also uses conditional formatting to highlight the top three Projects. To do this, the range, M8:M10, uses Excel’s LARGE function to find the 1st, 2nd, and 3rd largest numbers in the total column. Each of the three top results has its own conditional format rule. The conditional format is applied to range D8:J17.

To sort results, select D7:J17, then on the Data tab, in the Sort & Filter group, select Sort to display the Sort dialog box. Select My Data has Headers. Sort by Total with the Order of Largest to Smallest. This puts the highest scoring projects on top.