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. |
Swipe to see more →
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) |
Swipe to see more →
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. |
Swipe to see more →
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.
The roll-up rule that turns project status into portfolio status
A roll-up rule is the written arithmetic that turns a set of project statuses into one portfolio status. Without it, someone judges the color by feel every month, and the judgment drifts toward green. The most common defect in a portfolio tracker is a green portfolio built out of amber projects, and it survives because nobody ever wrote down how the aggregation is supposed to work.
Pick one of these rules, write it in the tracker, and stop negotiating it in the meeting. The color criteria for an individual project belong in your RAG status definitions; what follows is only the aggregation on top of them.
| Roll-up rule | How it works | What it is good for | How it fails |
|---|---|---|---|
| Worst child | The portfolio takes the worst status of any project in it | Small portfolios where every project is material | One perpetual red project makes the portfolio permanently red and the signal dies |
| Weighted by spend | Status weighted by each project's budget share | Portfolios where money is the main exposure | Hides a small project carrying large strategic or regulatory risk |
| Count threshold | Amber if more than X percent are amber, red if any red lasts two cycles | Large portfolios of similar-sized projects | Needs the thresholds agreed before the first bad month, not during it |
| Exception only | No portfolio color at all, just a named list of projects off track | Executive reporting where a single color adds nothing | Boards that want one number will invent one anyway |
Swipe to see more →
Whichever you choose, add one rule that costs nothing and catches the most damage: a project cannot move from green to red in a single cycle. If it does, the tracker records that it skipped amber. A green-to-red jump is almost never a sudden event; it is a reporting failure that had been building for weeks, and counting those jumps tells you more about how safe people feel reporting bad news than any status column will.
The refresh contract sets who updates which column and when
A refresh contract assigns every column group an owner, a source system, and a cadence. Trackers rot because everything is nominally everyone's job, which means it is the PMO analyst's job, which means it happens once and then only when someone complains. Name the owner per column group, not per project.
| Column group | Owner | Source | Cadence | If it is late |
|---|---|---|---|---|
| Identity, sponsor, category | PMO | Approved business case | On approval, then on change only | Project is not eligible for the portfolio review |
| Actual spend to date | Finance | Ledger export | Monthly, after the close | Last known figure carried with its date shown |
| Forecast at completion | Project manager | Judgment against the plan | Monthly | Escalated by name, since this is the column decisions run on |
| Milestone dates and status | Project manager | Delivery plan | Monthly, or on any baseline change | Status defaults to amber rather than staying green |
| Resource demand | Resource manager | Capacity plan | Quarterly | Flagged, not blocked, since it moves slowly |
Swipe to see more →
The row that earns the contract is the default-to-amber rule on missing milestone data. A blank cell reads as green to every human eye, so silence gets rewarded. Make no update the mildly uncomfortable option and most of the chasing disappears within two cycles.
Five data validation rules that keep the tracker comparable
Comparability is the only reason a portfolio tracker exists. The moment two project managers can write the status column differently, the sheet stops being a portfolio and becomes a folder of opinions. Five validation rules prevent nearly all of it, and every one is a ten minute change in any spreadsheet tool.
| Rule | What to do | What it prevents |
|---|---|---|
| Closed lists everywhere | Dropdowns for status, stage, category, sponsor and priority | Seven spellings of "In Progress" that break every filter and pivot |
| One row per project, forever | A stable project ID that never gets reused or renumbered | History that cannot be traced when a project is renamed mid-year |
| Locked formula columns | Protect variance, forecast at completion and any calculated field | A pasted value silently replacing the formula in one row |
| Real dates, never text | Date-typed cells with one format across the sheet | Sorting that puts October before February and slips nobody spots |
| A visible as-at date | One cell at the top saying what the sheet is current to | Decisions made on last quarter's numbers because nothing said otherwise |
Swipe to see more →
Keep free text to two columns: the commentary field and the next-action field. Everything else should be selectable. If a project manager needs a value that is not in the dropdown, that is a conversation with the PMO about the taxonomy, which is exactly the conversation you want to be having.
What breaks when the tracker grows
Spreadsheet portfolio trackers do not fail at a fixed project count. They fail when the manual effort of one refresh cycle exceeds the value anyone gets from reading the result, and that crossover point depends far more on how many people touch the file than on how many rows it holds.
| What changed | What breaks first | The cheap fix |
|---|---|---|
| More people editing the same file | Two versions in circulation and no agreement on which is current | One shared copy, edit rights limited, everyone else reads a link |
| More columns added over time | Refresh takes longer than the meeting it feeds | Delete any column nobody has filtered or discussed in two quarters |
| Longer history retained | The file slows and formulas break on merged rows | Archive closed projects to a separate tab each year |
| More executive audiences | A bespoke view built by hand for each audience | One data tab, several read-only views built from it |
| Finance and delivery data diverging | Two numbers for the same project and a meeting about which is right | Name one system of record per number and cite it in the header |
Swipe to see more →
Notice that four of those five fixes shrink the sheet rather than replacing it. That order matters, because the instinct when a tracker gets painful is to go shopping, and a tool inherits every taxonomy problem the spreadsheet had, only now with a contract attached.
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.