Prioritizing Projects in Excel? Here's What Can Go Wrong

Some organisations prioritize projects in Excel using little more than lists, costs and colour-coded scores. Others maintain complex workbooks with detailed criteria, formulas and layers of adjustments. Neither approach tells you whether the method is suited to the funding decision it supports.

Excel is familiar, flexible and readily available. That familiarity creates a hammer-and-nail problem: people reach for the tool they know, even when the method they've built in it isn't suited to the decision.

At TransparentChoice, we often see lightweight prioritization methods borrowed from agile product backlogs and applied to major investment decisions with little adaptation. These methods work well for backlog planning because teams can revisit their choices in the next sprint, and a poor decision is often relatively inexpensive to correct. But when a decision commits millions in funding, ties up scarce people and delays other investments for years, changing course is much harder. Methods designed for choices that are easy to revisit need rethinking when the consequences are difficult to reverse.

That's why “We already prioritize projects in spreadsheets” doesn't settle the question. What matters is whether the method behind the spreadsheet is suited to the funding decision it supports. Once a spreadsheet shapes which projects receive money and people, it is a decision model, and it deserves to be tested like one.

We'll test that against research on spreadsheet errors, two worked investment examples and the problems TransparentChoice customers have encountered.

Jump to key takeaways

When a spreadsheet takes on a bigger job

A workbook might start as a practical way to compare a handful of projects. It works, so more teams adopt it, new criteria and rules are added, and eventually it becomes part of annual budgeting and decisions across departments. What started as a useful planning tool has become an important decision system, often without anyone deliberately making that transition.

A study of 18 mission-critical spreadsheets described this pattern as “accidental legacy systems”: workbooks that had become essential as the business processes around them grew. ¹ The question is whether the checks, documentation and oversight have kept pace with the workbook's growing responsibilities.

Excel can be enough when the decision is manageable and easy to revisit. A small group can compare a few projects against agreed criteria, check the funding and capacity implications by hand, and change course without major disruption.

As the commitments become harder to reverse, the standard changes. When several people score projects or update forecasts, who reconciles competing versions? Can leadership recover exactly which weights, scores and portfolio it approved? Can the team compare alternative workable combinations without manually rebuilding scenarios, and add projects or resource groups without fragile formula workarounds? The test is whether the decision remains explainable, reproducible and open to challenge, not whether Excel has the right features.

Can the decision process survive its creator?

Project prioritization is not a one-off exercise. Annual planning returns, budgets change and earlier decisions need revisiting. But what happens when the person who built and maintained the spreadsheet leaves? Can anyone reproduce the previous analysis, adjust its assumptions and explain why the result has changed?

At TransparentChoice, we've repeatedly seen planning and prioritization processes depend heavily on one person. When that person leaves, their successor often introduces a completely different way of working. Rather than building on an established process, the organisation effectively starts from scratch.

What looks like unnecessary complexity to a new owner may reflect years of lessons learned. A formula, exception or additional step might exist because an earlier decision exposed a problem. When a successor simplifies the process without understanding why those details were introduced, the organisation risks losing knowledge it has already gained.

That knowledge isn't always easy to recover. In a small study of 15 people trying to understand spreadsheets built by others, 12 ultimately decided to return to the original author for clarification. ²

For organisations trying to standardise planning and make decisions consistently over time, that's a serious weakness. A repeatable decision process should survive the person who built the spreadsheet.

Who checks that the workbook is still reliable?

New projects, changing priorities and revised assumptions lead to changes in formulas and calculations. Over time, formulas may become increasingly complex, with calculations repeated across cells and dependencies that are difficult to follow. Research has found that these patterns can reveal real errors and weaknesses in spreadsheets. ³

In a study of 360 groups of related Enron spreadsheets, 16.9% showed newly appearing built-in Excel error values during revision, including #DIV/0! and #N/A. ⁴ A workbook may have been checked before, but who checks it after the rules and calculations change?

Other studies have examined mistakes made before formulas are written, the size of errors in the results, and how many mistakes reviewers miss:

• The wrong assumptions can matter more than wrong formulas. In a spreadsheet-development experiment, 70 of 85 errors were mistakes in planning what to calculate, rather than how to calculate it. ⁵
• Errors can seriously distort results. A study of 25 operational spreadsheets found seven errors that each changed a calculated output by more than $10 million. ⁶
• Even reviewers miss mistakes. In an experiment, 113 MBA students found only about half of eight planted spreadsheet errors. Access to formulas did not significantly improve detection. ⁷

An error or unapproved change may only come to light after funding has been committed, suppliers engaged, teams reassigned and other projects delayed or rejected.

The Bank of England's Prudential Regulation Authority expects certain UK banks and financial firms to independently validate models used to inform business decisions, regardless of the technology. ⁸ If a spreadsheet guides major funding decisions, its assumptions and logic should withstand independent challenge too.

A signed-off workbook is not the same as a tested decision. Before relying on a ranking, ask what the model leaves out, who supplied its assumptions and whether changing those assumptions would change what gets funded.

Who decides what matters most?

Before projects are scored, someone has already decided which criteria matter, how much weight they carry, which projects are mandatory and what alternatives leadership gets to see. Others may simply be asked to enter ratings. Those decisions can shape what gets funded before anyone looks at the ranking.

Changing who controls those rules can be difficult. A study of spreadsheet-based budgeting at a UK manufacturing and R&D subsidiary found that key process owners resisted proposed changes despite support from other finance staff. The researcher concluded that the changes threatened the existing distribution of power within finance. ⁹

So when a proposal to make decision rules more visible meets the response that “the spreadsheet works fine,” the disagreement may be about control rather than software. Who sets those rules, and who gets to challenge them?

Same projects. Different weights. Different decision.

Consider two proposals competing for funding: a new customer platform and a security upgrade. Assume there is only enough money to approve one for now.

Leaders rate each project from 0 to 10 against two goals: growing the business and making it more resilient. They also decide how important each goal is. That relative importance is called a weight. If growth counts for 60% and resilience for 40%, the platform looks stronger. If the priorities reverse, so does the result.

Both sets of calculations are correct. Nothing about the projects has changed. What changed was the leadership judgement about what matters most. If nobody discussed or approved those weights, the spreadsheet can make one person's assumptions look like an objective investment decision.

Even when leaders agree on the weights, the ratings themselves need scrutiny. For example:

• Clear definitions. Does "risk" mean the risk involved in doing a project or the risk to the business if it is not done? Stuart Easton explores this distinction in Value-Based Project Selection and Prioritization.
• Comparable benefits. Are forecasts based on the same time horizon? Are any benefits counted twice across different criteria?
• Independent assessments. Have people who understand the projects contributed their own judgements, or simply accepted the most senior person's view? In From Driving Your Backlog to Driving Your Strategy, we examine how relying on one person's spreadsheet ratings can conceal bias or mistakes.
• Meaningful rating scales. Turning High, Medium and Low into 3, 2 and 1 assumes equal differences between levels. That may not reflect reality, and adding decimal places doesn't make the judgement more reliable.

At Energinet , 60 of 80 IT projects were initially described as “top priority”. If almost everything is at the top, that label does little to help leaders make difficult choices. Energinet moved towards clearer strategic criteria, more consistent assessments and regular review. This was a problem of making meaningful distinctions, not one that more precise arithmetic could solve.

What happens when leaders disagree with the ranking?

Look at your last five major project approvals. Can you explain why those projects were funded and others were not? If leaders overrode the Excel ranking, was the reason recorded?

Some overrides are justified. A legal requirement may have been missed, new evidence may have emerged, or leadership priorities may have changed. The problem begins when the spreadsheet presents one set of priorities while the actual funding decisions follow another.

In a 2015 Economist Intelligence Unit survey for PMI of 514 senior executives, 61% said decisions about strategy implementation involved substantial negotiation between powerful people rather than being process driven. In the same survey, 56% said senior executives' interests and favoured projects undermined formal portfolio management. ¹⁰

A spreadsheet cannot resolve disagreements about priorities on its own. But it can give an apparently rigorous explanation for decisions whose real reasons are never examined.

When the numbers become negotiable

Imagine an Excel workbook with a bright yellow cell labelled “Adjustment Factor.” Change it, and a project's score rises.

The adjustment might correct something the original assessment missed. Or it might help a favoured project secure funding. Without a recorded explanation, the final score looks precise, but an important judgement remains hidden.

Now imagine a project scoring 2.1 out of 5 is denied funding while one scoring 2.3 is approved, even though nobody has established whether that small difference matters. Yet when an influential sponsor's proposal comes up, the numbers suddenly become open to adjustment.

The spreadsheet is treated as a strict rule for some projects and a flexible guide for others.

If adjustments and exceptions are not recorded, the workbook may show which projects scored highest without explaining why leadership chose differently, or what other worthwhile work gave way.

Which projects actually fit, and what must give way?

Even a trustworthy project ranking does not tell you which projects can be funded and delivered together. Projects compete for the same money and specialist capacity. The best individual projects may not form the best combination.

That combination is a portfolio. It must fit the available funding and people. A project may look affordable but still be impossible to deliver because the specialists it needs are committed elsewhere.

Same £7m. Two very different choices.

Imagine three proposed projects. You have £7 million and five available units of specialist capacity. For this example, one capacity unit means an equal block of time from the same scarce specialist team during the planning period.

For this illustration, each project has value points representing its expected benefit. All projects use the same scale, and we assume their benefits do not overlap, so the points can be added. They are separate from the earlier 0-to-10 ratings.

If you fund the customer platform, you use £6m and four of the five capacity units. You cannot fit either of the other projects into what remains. You receive 70 value points.

But suppose you fund security and data migration together. They cost £7m, use all five capacity units, and provide 104 points (54 + 50). On these figures, that is a better combination. Funding the platform means giving up both alternatives, not simply choosing a project with a good score.

Now change one fact: the specialist team has only four capacity units available. Security and migration can no longer happen together, because they require five. On the same assumptions, the platform on its own is now the strongest affordable and deliverable choice, with 70 points.

Even a seemingly sensible shortcut can mislead. Choosing the project with the most value per pound spent would start with data migration: about 17 value points per £1m, compared with 13.5 for security and 11.7 for the platform. But under the four-unit limit, that would leave too little specialist time for either other project. The result would be 50 points, not 70. The best use of the budget depends on the people available to do the work.

800 projects ranked. All 800 still running.

In our three-project example, the alternatives are easy to work through. But as the list grows, a ranking still leaves the hardest question unanswered: which projects should actually go ahead, and what must give way?

In a TransparentChoice webinar, practitioner Stijn Janssens described an organisation that had put 800 projects in priority order but continued running all 800. The projects had been ranked, but the difficult decisions about what to stop, delay or release resources from had not followed.

Three projects offer eight possible combinations, including funding none. Forty projects offer more than a trillion.

And not every combination is workable. Budgets, scarce specialists, mandatory requirements, dependencies between projects and existing commitments all limit what can realistically be delivered. The challenge is not simply to rank projects, but to identify which combinations fit those constraints and what each choice means for the organisation.

Excel can be equipped with optimisation tools. But comparing feasible portfolios is a different job from scoring proposals. It requires finding workable combinations, showing what each would fund and what would give way, and recalculating when leadership changes its priorities or assumptions. The question is whether your spreadsheet process can do that quickly and reliably enough for the decision.

Before approving a plan, ask which alternative combinations were tested. What would each fund, delay or stop? How would they use the available money and people, and what benefits would each choice give up? If only one combination is feasible, which constraints rule out the others?

When something changes after approval

Now suppose leadership approves security and data migration, but the specialist team then loses one capacity unit. On the original figures, a fresh comparison with four units would favour the customer platform. But the organisation is no longer starting from scratch. Money may already have been spent, and specialists assigned. Leadership must now consider which commitments to continue, delay, change or stop. Recalculating the options might take minutes. Changing the commitments already made can be much harder.

Priorities can change too. At RNLI , COVID prompted the organisation to change the importance of its assessment criteria and rebalance its projects. Whether circumstances change or a flaw emerges in the original analysis, leaders need to recover the previous assumptions, weights and rejected alternatives, alongside what is already committed. Can your spreadsheet process reconstruct the earlier decision and compare realistic alternatives from where you stand today?

How these problems show up in practice

Some of the hardest spreadsheet problems have little to do with formulas. They arise when persuasive voices sway decisions, teams struggle to reconcile competing versions, or divisions score their projects on different terms. Three TransparentChoice customers encountered these problems in different ways.

• A shared file does not guarantee a shared decision. At Simon Fraser University, project selection involved Google Sheets and ad hoc workshops. Its PMO leader described how persuasive personalities could influence which proposals moved ahead. The university introduced common value-and-complexity criteria and broader stakeholder input; the case study describes participants becoming more willing to accept decisions even when their preferred project was not chosen. Source: SFU case study.
• Manual reconciliation can become the real planning bottleneck. A global life sciences company had an established Excel-based annual planning process, but version conflicts, sequential input and manual consolidation slowed decisions across divisions. Its replacement process brought financial measures and strategic assessments together, allowed hundreds of contributors to participate in parallel and assembled divisional views into enterprise scenarios. The case reports planning cycles shortening from weeks to days. Source: Global Life Sciences case study.
• Different divisions need comparable criteria before they can make shared choices. After a merger, Suzano needed to compare more than 130 innovation projects across business areas that used different criteria and scoring methods. The company introduced shared strategic, risk and market-attractiveness criteria across six portfolios; its case study reports prioritization 10 times faster than spreadsheets. Source: Suzano case study.

Key takeaways

• A method borrowed from agile backlog planning may end up guiding million-pound investments. A scoring approach that works when choices can be revisited next sprint may be poorly suited to decisions that commit funding and scarce specialists for years.
• A signed-off workbook can become unreliable as it changes. New formulas, assumptions and exceptions introduce risks that an earlier review cannot settle. Research shows errors appearing during spreadsheet revisions, and even reviewers can miss mistakes.
• The same Excel workbook can rank different projects first without a formula error. In our example, changing the importance of growth versus resilience reverses the result. Nothing about the projects or calculations changes. What changes is leadership's judgement.
• Resistance to changing Excel can be about control. Whoever determines criteria, weights and adjustments can shape what leadership sees. Making those decisions open to challenge may disrupt established influence, not just familiar working habits.
• The spreadsheet's ranking may not explain what actually gets funded. When leadership overrides scores without recording why, the workbook can appear to justify decisions that were made for reasons it does not reveal.
• You can rank 800 projects and still leave all 800 running. The practitioner's example shows that putting projects in order doesn't ensure difficult decisions about what stops or waits actually follow. Our £7m exercise shows why those decisions require comparing what fits together within the available funding and specialist capacity.
• The decision process must survive the person who built the spreadsheet. A successor should be able to explain why rules and exceptions exist, reconstruct earlier choices and reconsider them when priorities, capacity or commitments change.

A completed scorecard may show which projects rank highest without showing which commitments can fit together. TransparentChoice software helps leaders make their priorities and differing judgements visible, then compare workable groups of projects within funding and staffing limits. They can change assumptions or constraints and compare the options again. Leadership still makes the decision.

At your next investment meeting, bring the spreadsheet behind your last major funding decision. Ask “Why did we choose these projects over the alternatives? If priorities, funding or specialist capacity changed, what would still fit, and what would have to give way?”

💚 If you enjoyed this article, please consider following us. It’s a small gesture that means a lot to us and encourages us to keep sharing what we learn. See the ways to follow us below ↓

References

1. Grossman, Mehrotra and Özlük (2007), “Lessons from Mission-Critical Spreadsheets”, Communications of the Association for Information Systems. ↩
2. Ragavan and colleagues (2021), “Spreadsheet Comprehension: Guesswork, Giving Up and Going Back to the Author”. ↩
3. Hermans, Pinzger and van Deursen (2015), “Detecting and Refactoring Code Smells in Spreadsheet Formulas”, Empirical Software Engineering. ↩
4. Dou and colleagues (2016), “VEnron: A Versioned Spreadsheet Corpus and Related Evolution Analysis”. ↩
5. Panko and Aurigemma (2010), “Revising the Panko–Halverson Taxonomy of Spreadsheet Errors”, Decision Support Systems. ↩
6. Powell, Baker and Lawson (2009), “Impact of Errors in Operational Spreadsheets”, Decision Support Systems. ↩
7. Galletta and colleagues (1996), “Spreadsheet Presentation and Error Detection: An Experimental Study”, Journal of Management Information Systems. ↩
8. Bank of England, PRA Supervisory Statement SS1/23, “Model Risk Management Principles for Banks” (2023, revised 2026). ↩
9. Douglas Howcroft (2006), “Spreadsheets and the Financial Planning Process: A Case Study of Resistance to Change”, Journal of Accounting & Organizational Change, 2(3), 248–280. ↩
10. Economist Intelligence Unit for PMI (2015), “Implementing the Project Portfolio: A Vital C-Suite Focus”, PMI Thought Leadership Series. ↩