A project prioritization template is a spreadsheet that scores every candidate project against agreed weighted criteria and sorts the result into a ranked list. Nine columns is enough. The build takes fifteen minutes; the work that makes it credible is agreeing the criteria and their weights with the people who will later dispute the ranking, and doing that before anyone has seen a single score.
This page is the artifact. For the reasoning behind the criteria and how to choose between scoring approaches, start with project prioritization criteria and project prioritization frameworks, then come back and build the sheet.
Key takeaways
- Five criteria, maximum. Every extra criterion pulls the scores toward the middle until the ranking stops separating anything.
- Weights must be agreed and locked before scoring starts. Weights set after the scores are visible are negotiation, not prioritization.
- Score 1 to 5 with a written anchor for each value, so a 4 means the same thing to two different scorers.
- Add a cost or effort column and a running total, then draw the capacity line. Without it you have a ranked wish list, not a portfolio.
- SUMPRODUCT does the whole weighted calculation in one formula and survives someone reordering the criteria.
- Keep the projects that fell below the line visible. Deleting them is how the same request comes back in six weeks with a new name.
Last updated July 2026.
The columns a project prioritization template needs
Start with these. One row per candidate project, one tab, no merged cells.
| Column | Format | Why it earns a column |
|---|---|---|
| ID | P-01, P-02 | So a project can be referenced in a meeting without ambiguity, and never reused. |
| Project name | Short text | Six words. Long names hide what the work actually is. |
| Sponsor | A person | Never a department. A row without a named sponsor should not be scored at all. |
| Criterion 1 to 5 | 1 to 5 score | One column per agreed criterion, with the weight in the header row. |
| Weighted score | Formula | The single number the ranking sorts on. |
| Cost or effort | Dollars or person-weeks | Turns a ranked list into a portfolio you can afford. |
| Cumulative cost | Formula | Running total down the ranked list. Where it crosses the budget is the line. |
| Rank | Formula | Calculated, never typed, so nobody can nudge it. |
| Decision | Approved / Deferred / Declined | Three values. The audit trail for why a project is not running. |
Resist the urge to add a comments column that becomes a novel. Add a separate notes tab keyed by project ID if the discussion needs recording.
Choosing the criteria and their weights
Five criteria covering value, cost, risk, strategic fit, and urgency will separate almost any portfolio. Weights should reflect what the organization says it cares about, and they should be argued about in a room, once, and then written into the header row where everyone can see them.
| Criterion | Typical weight | What a 5 means | What a 1 means |
|---|---|---|---|
| Strategic fit | 30% | Directly advances a named strategic objective this year. | No stated link to any objective. |
| Financial value | 25% | Quantified benefit, validated by finance, landing inside 12 months. | Benefit is asserted and unquantified. |
| Urgency or cost of delay | 20% | Value falls sharply if this slips a quarter, or a deadline is external. | Nothing changes if it starts next year. |
| Risk if not done | 15% | Regulatory, security, or a system already failing. | Nice to have, no consequence. |
| Delivery confidence | 10% | Scope is clear, the team exists, similar work has been delivered before. | Scope is vague and the skills are not in house. |
Write the anchors for every score value, not just 5 and 1. The middle values are where scoring drifts, and a three-word definition of what a 3 looks like removes most of the argument later. Two people scoring the same project independently and landing within one point is the test that your anchors work.
The exact Excel and Google Sheets formulas
Assume criterion scores sit in columns D through H, and the weights sit in row 2 of those same columns as decimals (0.30, 0.25, 0.20, 0.15, 0.10). Data starts in row 4.
- Weighted score, in I4:
=SUMPRODUCT($D$2:$H$2,D4:H4). Fill down. This returns a number between 1 and 5 and keeps working if you reorder the criteria. - Rank, in L4:
=RANK(I4,$I$4:$I$40). Highest weighted score ranks 1. - Cumulative cost down the ranked list, in K4:
=SUMIFS($J$4:$J$40,$L$4:$L$40,"<="&L4). This sums the cost of every project ranked at or above this one, so it works without physically sorting the sheet. - Above or below the line, in M4:
=IF(K4<=$B$1,"In","Below the line"), where B1 holds the available budget or capacity. - Weight check, somewhere visible:
=SUM($D$2:$H$2). It must equal 1. Weights that quietly sum to 1.15 are the most common defect in a prioritization sheet.
All five work identically in Excel and Google Sheets. Add conditional formatting on the decision column and nothing else. A sheet with color on every cell is unreadable on a projector, which is where this one will be used.
A worked example
Six candidate projects, the weights above, and a budget of 900,000 dollars. Scores and figures are illustrative.
| ID | Project | Fit (30%) | Value (25%) | Urgency (20%) | Risk (15%) | Confidence (10%) | Weighted | Cost | Cumulative |
|---|---|---|---|---|---|---|---|---|---|
| P-03 | Billing platform replacement | 5 | 4 | 4 | 5 | 2 | 4.25 | 420,000 | 420,000 |
| P-01 | Customer portal redesign | 4 | 4 | 3 | 2 | 4 | 3.50 | 260,000 | 680,000 |
| P-06 | Security remediation phase 2 | 2 | 1 | 4 | 5 | 4 | 2.80 | 180,000 | 860,000 |
| P-04 | Warehouse automation pilot | 3 | 4 | 2 | 1 | 3 | 2.75 | 310,000 | 1,170,000 |
| P-02 | Reporting data mart | 3 | 2 | 2 | 2 | 4 | 2.50 | 150,000 | 1,320,000 |
| P-05 | Office intranet refresh | 1 | 1 | 1 | 1 | 5 | 1.40 | 90,000 | 1,410,000 |
The line falls after P-06 at 860,000 dollars. Three projects are in, three are below the line. Notice what the arithmetic surfaced: the security remediation, which scores badly on strategic fit and financial value, still makes the cut because risk and urgency carry it. That is the weights doing their job. Notice also that the billing replacement has the lowest delivery confidence in the portfolio and the highest cost, which is a separate conversation the ranking should trigger rather than settle.
Where the capacity line really goes
Budget is the easy constraint. The one that actually stops portfolios is people. If the top three projects all need the same four integration engineers in the same quarter, the money says yes and the calendar says no. Run the ranked list against a real capacity view before you commit to it, using the columns and formulas in the capacity planning template, and expect the line to move up.
When it does, take the projects that fell out and put them in a visible deferred section with the date they will be reconsidered. Deleting them feels tidy and guarantees the same request reappears in six weeks with a new sponsor and a new name. The full mechanics of turning a scored list into a funded portfolio are in how to prioritize a project portfolio.
Running the prioritization session
Score independently before you meet. Send the sheet to three or four scorers, collect the numbers, and bring the spread to the room. Discuss only the rows where scorers disagreed by two points or more, which is usually six or seven rows out of forty, and resolve them by clarifying the anchor rather than by averaging. Averaging hides the disagreement that was the useful part.
Then show the ranked list with the line drawn, and ask one question: what would you move, and what comes out to pay for it. Making the trade explicit is the entire point of the template. A room that can add a project without naming what it displaces is not prioritizing.
Frequently asked questions
How do you create a project prioritization template in Excel?
Create one row per project and one column per criterion, put the weights as decimals in a locked header row, then calculate the weighted score with SUMPRODUCT and the rank with RANK. Add cost and cumulative cost columns and a budget cell, then flag every project whose cumulative cost exceeds the budget. It takes about fifteen minutes.
What criteria should a project prioritization template use?
Five criteria work for most portfolios: strategic fit, financial value, urgency or cost of delay, risk if the work is not done, and delivery confidence. Weight them to match stated organizational priorities and write a short anchor describing what each score value means, so two scorers reach similar numbers independently.
What is a project prioritization matrix template?
A prioritization matrix template plots projects on two axes, most often value against effort, and sorts them into quadrants rather than a single ranked list. It is faster than weighted scoring and better for a first pass over a large backlog. Weighted scoring is better when you have to defend the order to a finance committee.
How do you calculate a weighted score for project prioritization?
Multiply each criterion score by that criterion's weight and add the results. In a spreadsheet, =SUMPRODUCT($D$2:$H$2,D4:H4) does it in one formula, where row 2 holds the weights as decimals summing to 1 and row 4 holds one project's scores. The output falls on the same 1 to 5 scale as the inputs.
What is a project prioritization scorecard?
A scorecard is the same weighted template presented one project at a time, showing each criterion, its score, the evidence behind that score, and the total. Portfolio committees use the ranked sheet to decide and the scorecard to justify individual decisions to sponsors who ask why their project ranked where it did.
How many criteria should a prioritization template have?
Three to five. Beyond five, scores regress toward the middle and the ranking stops separating projects, because a weak project scoring badly on two criteria gets rescued by mediocre scores on six others. If a sixth criterion feels essential, check whether it is really a component of one you already have.
Should the prioritization template live in Excel or a PPM tool?
Start in a spreadsheet. It is faster to change while the criteria and weights are still being argued about, and most organizations rewrite both twice in the first year. Move into a portfolio tool once the criteria are stable and the volume of requests makes version control the bigger problem.
Where this fits
The template is a means of making a conversation honest, not a machine for producing decisions. It works when the weights were agreed in advance, the anchors are written down, and the capacity line is drawn on the same page as the ranking. It fails, quietly and quickly, when scores get adjusted after the ranking is visible. If you want the underlying model in more depth, see the project scoring model, and for the technique-by-technique comparison see the project prioritization matrix and project selection methods.