Life is full of decisions. Some are easy and require little thought while others require more analysis. In many scenarios, Excel can help by creating a weighted decision matrix. You define the ranking criteria, importance and Excel will do the math and pick a winner from your options. (Includes Excel decision matrix macro for Excel 2007 and newer)

To help you make decisions easier, we came up with a fun and useful Excel macro to celebrate “March Madness,” the annual college basketball tournament. It doesn’t matter if people follow basketball, you’ll hear complete strangers come up with their own reasons why certain teams will progress to the Final Four. The factors they choose are as diverse as the weightings they give to each factor.

Macro Requirements

The Excel decision macro is easy to use and flexible. All that is required is that you know:

• What to rank (e.g. the different basketballs teams )
• Evaluation criteria (e.g. players, coach, schedule, conference…)
• Importance of each criteria (number you assign that represents its overall weight)
• Scores for each criteria (number you assign for each contender)

And by flexible, I mean you can use it for lots of decisions where you evaluate multiple factors. It’s not limited to sports. As example, you could use it to:

• Evaluate job offers
• Evaluate purchase decisions like a car
• Evaluate hardware (Apple VS. PC)
• Evaluate colleges

How to use the Excel decision ranking macro

For these instructions, I’ll use a fictional example of evaluating different web browsers.

1. Determine what you want to rank and the criteria.

2. Open the Excel_decision_macro.xlsm file.

3. You may get a security warning asking you to enable the content since this is a macro. If so, click Enable.

4. In the Ranking Factors dialog, enter in the number of factors. A “factor” is something that you want to rank across all contenders. As example, if I were doing the browser evaluation, I might have “plug-ins,” “support,” “documentation” & “speed,” as factors. I would then enter “4.”

5. Enter in how many items to evaluate. You might also think of these items as “contenders.” As example, in my analysis, I might have “Internet Explorer 9,” “Google Chrome 10” & “Firefox 4” as evaluation items. In this case, I would enter “3.”

6. Click Enter. This will build out your decision matrix according to your numbers.

7. Replace the default macro labels for your ranking factors (1) and contenders (2) as I’ve done in the example below.

8. In Column B, enter in a number that represents the Factor Weight. The higher the number, the more weight it has in the overall decision. As you enter your values, the Total value will update. I like to have the Total=100, but that’s not a requirement. You can also have the Factor Weight be a negative number.

9. Enter in your values for each contender. You can devise your own scale or a simple one like 0-10. The higher the number, the better it ranks.

10. When you’ve filled in the matrix, press Ctrl+R to run the macro. You’ll be reminded to check for blank cells.

11. Press Y to proceed.

The macro will compute a total score for each contender and rank them from highest to lowest. It will also highlight in green those cells that scored the highest for a given factor.

If you wish to add or delete rows or columns, you can and recalculate. You can also press Ctrl+Q to open the original dialog box.

As you can see, Excel is merely scoring your choices based on your input. The program is good, but doesn’t have any insight. You’ll still have to do some research and make judgment calls. However, it can help you systematize your decision process.

Related Excel Resources

• PDF Screen steps of this article
• How To Use Excel Conditional Formatting