Every PMO that has ever existed started with a spreadsheet, and a surprising number of good ones never left. A project portfolio management template is just a list of projects with the handful of columns that let you answer the four questions an executive actually asks: what are we doing, what is it costing, what is in trouble, and what is it for. Get those four right and a spreadsheet will run a fifty project portfolio. Get them wrong and no PPM tool on the market will save you.
Key takeaways
- A project portfolio template is one row per project, not one tab per project. The moment you start creating tabs, the portfolio view is gone.
- Fourteen columns cover it: ID, project, owner, sponsor, strategic objective, stage, RAG, dates, budget, actual, forecast at completion, variance, and priority score.
- Forecast at completion is the column most trackers omit and the only one an executive cares about. Budget versus actual tells you the past. Budget versus forecast tells you whether you have a problem.
- The strategic objective column is what turns a project list into a portfolio. Without it you cannot answer the one question that justifies a PMO: is our money going where our strategy says it should.
- A spreadsheet handles roughly 30 to 50 projects. The failure mode is not size, it is staleness: when updating the sheet costs more than reading it, people stop updating it.
Download the project portfolio template
project-portfolio-tracker.xlsx is the tracker described below, with live formulas and five worked example projects. Open it in Excel, or upload it to Google Sheets. No signup, no macros.
Two tabs. Portfolio is the register: one row per project, with a variance formula and conditional formatting that colors the RAG status and flags any project forecasting over budget. Dashboard is entirely formulas over that register: project count, total budget, forecast, portfolio variance, budget consumed, a RAG count, value at risk, and spend split by strategic objective.
What is a project portfolio management template?
A project portfolio management template is a structured spreadsheet that holds every project in one register, with a consistent set of columns per project, and a summary view that aggregates them. It is the minimum viable PPM tool. Its job is to make the portfolio visible in a single screen, so that decisions about funding, sequencing, and stopping work can be made against the whole picture rather than one project at a time.
The word portfolio is doing the work. A project list tells you what is running. A portfolio tells you what the collection adds up to: how much is committed, how much is at risk, and which strategic objectives are actually being funded. The difference between the two is about three columns, and it is the difference between a status report and a management tool.
What columns does a portfolio tracker need?
Fourteen columns cover a portfolio properly, and they fall into four groups: identity, governance, money, and priority. Here is each one, and the reason it survives the cut.
| Column | Group | Why it is there |
|---|---|---|
| ID | Identity | A stable key. Project names change, and every cross reference you ever build depends on this not moving. |
| Project | Identity | The name people use in the room. |
| Owner | Governance | The project manager. One name, never a team. |
| Sponsor | Governance | The executive who owns the outcome and can stop the project. If this is blank, the project is unowned. |
| Strategic objective | Priority | The single objective this project serves. One, not three. This column is what makes it a portfolio. |
| Stage | Governance | Idea, planning, delivery, closing. Drives which projects are commitments and which are still optional. |
| RAG | Governance | Red, amber, green, grey. The one column executives read first. |
| Start / End | Governance | Enough for a portfolio view. Detailed schedules live in the project plan, not here. |
| Budget | Money | What was approved. |
| Actual to date | Money | What has been spent. |
| Forecast at completion | Money | What it will cost by the end. The most important number on the sheet. |
| Variance | Money | Budget minus forecast, calculated. Negative means you are heading over. |
| Priority score | Priority | The output of your scoring model, carried across so the register can be sorted by value. |
Resist the urge to add more. Every extra column is a cell somebody has to update every month, and the tracker dies of maintenance long before it dies of missing data. If a column would not change a decision, leave it out.
Why forecast at completion matters more than actual spend
Budget versus actual is a rear view mirror. A project that has spent 40% of its budget looks fine until you learn it is 20% complete, at which point it is a $2M problem that reads as green. Forecast at completion is the project manager's honest answer to "what will this have cost when it is done", and comparing it to budget is the only variance that predicts anything.
In the sample file, the data center exit has a $2.1M budget and $1.74M spent, which looks like a project with room left. Its forecast at completion is $2.48M. The variance formula (=J-L) turns that into a red $380k and the conditional formatting catches it immediately. That is a project the portfolio needs to decide about now, not at year end, and no amount of staring at actual spend would have surfaced it. If you want to make this rigorous rather than judgment based, earned value management gives you a formula for the forecast instead of an opinion.
How to build the dashboard tab
The dashboard should contain no typed numbers at all. Every figure is a formula reading the Portfolio tab, which means the summary can never disagree with the register. This is not a style preference. A dashboard that is maintained separately from the data will drift, and the first time it drifts in a board meeting, the PMO loses the room.
These are the formulas in the downloadable file. They are deliberately boring.
| Measure | Formula |
|---|---|
| Projects in the portfolio | =COUNTA(Portfolio!B5:B50) |
| Total budget / forecast | =SUM(Portfolio!J5:J50) and =SUM(Portfolio!L5:L50) |
| Budget consumed | =SUM(Portfolio!K5:K50)/SUM(Portfolio!J5:J50) |
| Red projects | =COUNTIF(Portfolio!G5:G50,"Red") |
| Value at risk | =SUMIF(Portfolio!G5:G50,"Red",Portfolio!L5:L50)+SUMIF(...,"Amber",...) |
| Spend by strategic objective | =SUMIF(Portfolio!$E$5:$E$50,$A7,Portfolio!$L$5:$L$50) |
The last one is the row that earns the whole spreadsheet. It answers the question a CFO will eventually ask, and it is the question most PMOs cannot answer on the spot: how much of our project money is going to each of our strategic objectives? If an objective the board calls critical is carrying 5% of portfolio spend, the portfolio does not believe the strategy, and that gap is a better agenda item than any status update. See strategic alignment for what to do once the number is on the screen.
A worked example
The sample register has five projects, $5.46M of approved budget, and a forecast of $5.94M. Three numbers tell the story.
| Measure | Value | What it says |
|---|---|---|
| Portfolio variance | -$480k | The portfolio is forecasting 9% over its approved budget, and one project is carrying most of it. |
| Value at risk (red and amber) | $4.04M | Two of five projects, but 68% of the money. Project count is a terrible measure of portfolio health. |
| Spend on "grow retention" | 22% | If retention is the board's number one objective this year, this figure is the problem. |
Notice what happened. The register did not tell anyone what to do, but it produced three sentences that force a decision, which is the entire job. A tracker that generates no uncomfortable questions is not being maintained honestly.
Frequently asked questions
Can you manage a project portfolio in Excel?
Yes, up to roughly 30 to 50 projects with a single PMO analyst maintaining it. Excel handles the register, the roll up, and the dashboard without complaint. What it does not handle is many people editing at once, an audit trail of who changed a forecast, and cross project resource conflicts, which is where most spreadsheet portfolios eventually break.
How do I create a project portfolio in Excel?
Start with one sheet, one row per project, and the fourteen columns above. Add a second sheet whose every cell is a formula over the first. Add conditional formatting to the RAG and variance columns so problems are visible without reading. That is the whole build, and it takes under an hour. Download the template above if you would rather start from a working file than a blank grid.
What is the difference between a project portfolio template and a PPM tool?
A template stores the data you type in. A PPM tool collects the data from the systems where work actually happens, enforces workflow, and keeps a history. The functional gap is smaller than vendors suggest. The real gap is maintenance: the spreadsheet is only as current as the last person who updated it, and a tool is as current as the systems it reads. Our guide to project portfolio management software covers when the switch is worth making.
What is a portfolio tracker?
A portfolio tracker is the register itself: the single list of every project with its status, money, and owner. Tracker usually refers to the raw data, and dashboard to the summarized view on top of it. In the downloadable template they are the two tabs, and the distinction is worth keeping because they have different audiences. The PMO lives in the tracker, and the board only ever sees the dashboard.
How often should the portfolio tracker be updated?
Monthly for money and RAG, and immediately for anything that changes a commitment. A tracker refreshed the day before a steering meeting and ignored the rest of the month is theater, and everyone in the room knows it. Tie the update to the portfolio review meeting cadence so there is a real deadline attached to it.
Should the template include resources and capacity?
Keep them separate. Resource capacity is a different grain (person by month, not project by project), and cramming it into the portfolio register produces a sheet that does neither job. Use a dedicated capacity planning template and link the two by project name if you need to.
When the spreadsheet stops being enough
The tell is never the number of projects. It is the day the tracker is out of date and nobody notices, because the effort of keeping it current has quietly exceeded the value anyone gets from reading it. That happens when a single analyst is chasing twelve project managers for the same three numbers every month, retyping them from status reports into cells.
At that point you have two options, and it is worth being clear that buying a tool is only one of them. The cheaper option is to shrink the sheet: cut columns nobody uses, cut the update cadence to what decisions actually require, and stop tracking projects the portfolio has no intention of stopping. The other is to stop retyping and let the numbers arrive on their own, which means connecting the systems that already hold them, the finance ledger, the timesheet system, and the delivery tooling, so the register updates itself. Most PMOs should try the first before paying for the second. A portfolio tracker fails from neglect far more often than it fails from scale, and a lean sheet somebody actually maintains beats an enterprise tool nobody logs into.
If reporting rather than tracking is where your pain is, the companion piece is the project portfolio dashboard, which covers what to put in front of executives and what to leave out.