How to combine project status trackers into one spreadsheet
By the Analistable team · Updated · 5 min read
Agree a small shared set of columns (project, team, owner, status, RAG, due date, percent complete), map each team's own status words onto one list, then stack the trackers with a Team column using Power Query, VSTACK or IMPORTRANGE. A pivot of status by team becomes the portfolio view, refreshed rather than rebuilt each week.
Part of our guide: How to consolidate data in Excel
Why roll-ups break
Each team's tracker grew to suit that team: one uses "In progress", another "Doing", a third a RAG colour with no words at all. Some put the due date in column E, others in a comment. A portfolio report built by copying rows across every Friday is slow and goes wrong as soon as a team inserts a column.
The fix is not to force everyone into one master spreadsheet. It is to agree the handful of columns the portfolio needs, map each tracker onto them, and stack the results automatically. Teams keep their own sheets; the roll-up reads from them. This is the reporting version of the consolidating data in Excel guide.
Step 1: agree the portfolio columns
| Column | Type | Notes |
|---|---|---|
| Project | Text | Unique within a team |
| Team | Text | Added during the combine, not typed |
| Owner | Text | One named person |
| Status | List | Not started, In progress, Blocked, Done |
| RAG | List | Red, Amber, Green — typed, not just cell colour |
| Due date | Date | A real date, not "end of Q4" |
| % complete | Number | 0 to 100 |
Cell fill colour is not data. Power Query, formulas and pivots can't read it, so a RAG shown only as colour disappears when combined. Ask teams to type the RAG value, and use conditional formatting to colour it.
Step 2: map each team's statuses
Keep a small mapping table and join it to each tracker before stacking, so "Doing", "WIP" and "In progress" all become In progress:
| Team status | Portfolio status |
|---|---|
| Doing | In progress |
| WIP | In progress |
| On hold | Blocked |
| Waiting on vendor | Blocked |
| Complete | Done |
Any status that doesn't match the map shows up as blank after the join; review those each week and add them to the map. Joining a lookup table like this is covered in joining two spreadsheets on a common column.
Step 3: stack the trackers
- Excel files in a shared folder: Power Query From Folder, or one query per file appended together if layouts differ. Rename columns per team in its own query, then Append.
- Tabs in one workbook (Microsoft 365): add a Team column to each tab first, then
=VSTACK(Marketing!A2:H100, Product!A2:H100, Ops!A2:H100)stacks them. - Google Sheets:
={IMPORTRANGE("url1","Tracker!A2:G"); IMPORTRANGE("url2","Tracker!A2:G")}stacks two trackers; see combining multiple IMPORTRANGE formulas for the limits.
Whichever route you take, filter out blank rows that ranges like A2:G100 bring in, and add the Team column during the combine rather than relying on teams to fill it.
Worked example: portfolio view
| Team | Project | Owner | Status | RAG | Due date | % complete |
|---|---|---|---|---|---|---|
| Marketing | Autumn campaign | Jo Reid | In progress | Green | 31/10/2026 | 60 |
| Marketing | New brand guide | Jo Reid | Blocked | Red | 15/10/2026 | 40 |
| Product | Billing page redesign | Omar Ali | In progress | Amber | 20/11/2026 | 35 |
| Product | Mobile login fix | Kate Lin | Done | Green | 30/09/2026 | 100 |
| Ops | Warehouse move | Raj Mehta | In progress | Amber | 30/11/2026 | 50 |
| Ops | Supplier review | Raj Mehta | Not started | Green | 15/12/2026 | 0 |
| Team | Green | Amber | Red | Total |
|---|---|---|---|---|
| Marketing | 1 | 0 | 1 | 2 |
| Product | 1 | 1 | 0 | 2 |
| Ops | 1 | 1 | 0 | 2 |
| Total | 3 | 2 | 1 | 6 |
Useful filters on the combined table: anything Red or Blocked; anything due in the next 14 days with % complete under 50 (the brand guide, due 15 October at 40%); and owners with more than two open projects. With the data in one place these are filters, not meetings.
Asking the portfolio questions directly
If the trackers are Google Sheets or Excel files, Analistable can load them together and answer "which projects are due this month and not green" or "open projects per owner across all teams" without you building the roll-up first. It reads the trackers in your browser and doesn't write back, so teams' sheets stay as they are. For a standing weekly report that others open, a refreshed Power Query or IMPORTRANGE roll-up is still the simpler thing to share.
Keeping the roll-up honest
A portfolio view is only useful if people trust it, and trust goes the moment a project shows Green in the roll-up while its team knows it is in trouble. A few habits help:
- Show the last-updated date per team. Add a Last updated column to each tracker, or use the file's modified date in Power Query, so stale trackers are visible rather than silently old.
- Don't edit the roll-up. If a status is wrong, fix it in the team's tracker and refresh. Edits in the combined sheet are overwritten on the next refresh and leave two versions of the truth.
- Keep history. Save a values-only copy of the combined table each week with a Snapshot date column. Stacking those snapshots shows how long each project has been Amber, and which due dates keep moving.
- Watch for orphans. A project that disappears from a tracker without being marked Done was usually deleted rather than finished; comparing this week's snapshot with last week's on Team and Project finds them.
None of this needs more than the stacked table plus a dated copy, which is another reason to combine into rows rather than copy summary numbers into a slide.
Frequently asked questions
- Can I combine trackers that have different columns?
- Yes. Rename or remove columns per tracker so each matches the shared set, then append. Power Query's Append matches columns by name and fills missing ones with nulls.
- How do I keep RAG colours when combining trackers?
- Store RAG as text (Red, Amber, Green) and colour it with conditional formatting. Fill colour alone doesn't survive Power Query, IMPORTRANGE or formulas.
- Should teams just use one shared tracker instead?
- Sometimes that works, but teams often need extra columns and views of their own. A roll-up of agreed columns lets them keep their tracker while you get a consistent portfolio view.
- How do I see which projects changed status since last week?
- Keep a weekly snapshot of the combined table with a date column, then compare this week to last week on Team and Project. Rows where Status or RAG differ are the changes.