How to Create a Project Portfolio Dashboard in Google Sheets

Trying to manage multiple projects at once can feel like juggling chainsaws. Keeping track of budgets, deadlines, and progress for each one in separate files or tools is a recipe for chaos. A project portfolio dashboard in Google Sheets solves this by bringing all your critical project data into a single, easy-to-understand view. This guide will walk you through, step-by-step, how to build your own dashboard from scratch.

What Exactly is a Project Portfolio Dashboard?

Think of it as the mission control for all your projects. Instead of clicking through a dozen different tabs or documents, a project portfolio dashboard gives you a high-level, visual summary of your entire project landscape on one screen. It’s designed to help you quickly answer important questions like:

• What's the status of every project we're working on?

• Are we on track with our overall budget?

• Which projects are at risk of falling behind schedule?

• How are our resources allocated across different projects?

By organizing this information visually, you can spot trends, identify potential blockers, and make faster, more informed decisions without getting lost in the details.

Step 1: Structure Your Project Data Source

Every great dashboard starts with clean, well-organized data. Before you can build any charts or summary metrics, you need to create a central “Source of Truth” for all your project information.

Create a “Project Data” Tab

In a new Google Sheet, create two tabs. Name the first one Dashboard and the second one Project Data. The Project Data tab will be where you input and track the details for every project. It acts as the engine for your dashboard.

Set up your Project Data sheet like a simple database table. Each row will represent a single project, and each column will represent a piece of data about that project. Here are the essential columns you should include:

• Project Name: The unique name for each project.

• Project Owner: The person responsible for the project's success.

• Start Date: The official start date of the project.

• End Date: The projected end date.

• Status: The current status of the project. Use a consistent set of statuses like Not Started, In Progress, Completed, On Hold, or At Risk.

• Budget ($): The total allocated budget for the project.

• Actual Spend ($): The amount spent to date.

• Progress (%): How far along the project is, represented as a percentage (e.g., 75 for 75%).

• Priority: The project's priority level (e.g., High, Medium, Low).

Pro Tip: Use Data Validation for Cleanliness

To avoid typos and keep your data consistent (e.g., preventing "In Progress" and "In-Progress"), use Data Validation for the Status and Priority columns.

1. Select the entire Status column (Column E).

2. Go to Data > Data validation.

3. For the criteria, choose "List of items" and enter your statuses separated by a comma: Not Started,In Progress,Completed,On Hold,At Risk.

4. Check the box for "Show dropdown list in cell."

5. Click "Save."

Now, you’ll have a neat dropdown menu in each cell of that column, ensuring your status reporting stays consistent.

Step 2: Build Your Dashboard and Key KPIs

Now for the fun part. Head back to your Dashboard tab. This is your canvas. We’ll start by creating a top-level summary section with some Key Performance Indicators (KPIs) to give you an at-a-glance overview.

Let’s add some key metrics at the top of the sheet:

KPI 1: Project Counts by Status

It's useful to see a quick tally of how many projects are in each stage. We can use the COUNTIF formula for this.

In your Dashboard tab, set up a few cells:

• Total Projects

• In Progress

• At Risk

• Completed

Next to each label, enter these formulas:

• Total Projects: This counts all projects that have a name. =COUNTA('Project Data'!B:B)

• In Progress: =COUNTIF('Project Data'!E:E, "In Progress")

• At Risk: =COUNTIF('Project Data'!E:E, "At Risk")

• Completed: =COUNTIF('Project Data'!E:E, "Completed")

KPI 2: Overall Budget Health

Here we'll track portfolio-wide spending against the budget. Set up labels for "Total Budget," "Total Actual Spend," and "Remaining Budget."

• Total Budget: =SUM('Project Data'!F:F)

• Total Actual Spend: =SUM('Project Data'!G:G)

• Remaining Budget: =[Total Budget cell] - [Total Actual Spend cell]

Ensure you format these cells as currency for easier reading.

Step 3: Add Visualizations and Charts

Numbers are great, but charts are better for quick comprehension. We’ll add three powerful charts to bring your project portfolio to life.

Chart 1: Project Status Breakdown (Pie Chart)

A pie chart is perfect for showing the proportion of projects in each status category. First, we need to create a small summary table on the Dashboard tab to power the chart.

1. On your Dashboard tab (off to the side), create a small table with two headers: Status and Count.

2. Under "Status," list out your unique statuses: In Progress, On Hold, At Risk, Completed.

3. Beside each status, use a COUNTIF formula to get the count from your Project Data tab. For "In Progress" it would be: =COUNTIF('Project Data'!E:E, "In Progress")

4. Once your summary table is complete, select the data in your summary table, then go to Insert > Chart. Google Sheets will likely suggest a Pie Chart. If not, you can select it from the Chart editor on the right.

Chart 2: Budget vs. Actual Spend (Bar Chart)

This chart helps you instantly spot which projects are over or under budget.

1. Select the Project Name, Budget, and Actual Spend columns in your Project Data tab (hold Ctrl or Cmd to select non-adjacent columns).

2. Go to Insert > Chart.

3. In the chart editor, choose a Column chart.

Each project will have two bars - one for its budget and one for its actual spending - making comparisons easy.

Chart 3: A Simple Gantt-Style Timeline (Stacked Bar Chart)

While Google Sheets doesn't have a native Gantt chart, you can create a simple but effective timeline using a stacked bar chart trick.

1. Create Helper Columns: In your Project Data tab, add two new columns: Start Day and Duration.

   • For Start Day, we need to calculate how many days have passed since the very first project started. The formula is: =C3-MIN(C:C) Drag this formula down for all your projects.

   • For Duration, simply subtract the start date from the end date: =D3-C3 Drag this formula down as well.

2. Create the Chart:

   • Go to your Dashboard tab.

   • From the menu, navigate to Insert > Chart.

   • In the Chart Editor, select "Stacked bar chart" as the chart type.

   • For the "X-axis," select your list of Project Names.

   • For the "Series," add two:

     1. Select your Start Day column.

     2. Select your Duration column.

3. The Trick: Now, in the Chart Editor under "Series," click on the Start Day series. Change its color to "None" to make it transparent.

This leaves only the "Duration" bars visible, floating in the correct timeline position to create a visual Gantt chart that shows when each project is active.

Step 4: Make Your Dashboard Interactive with Slicers

Want a truly dynamic dashboard? Add a slicer. A slicer is a filter button that allows you to easily filter all your KPIs and charts at once.

1. Click anywhere inside one of your charts on the Dashboard tab.

2. Go to Data > Add a Slicer.

3. A filter box will appear. In the sidebar options for the Slicer, choose the column you want to filter by, for instance, Project Owner.

4. Drag the slicer to a convenient spot at the top of your dashboard.

Now, you can use the slicer's dropdown menu to select a specific project owner and watch your charts and KPIs update instantly to show only their projects.

Final Thoughts

By following these steps, you can create a centralized, powerful Project Portfolio Dashboard in Google Sheets. This setup moves you from reactive project management scattered across endless files to proactive oversight, all from one clear, consolidated view. It empowers you to better manage resources, communicate progress, and steer your projects toward success with confidence.

Manually wrangling data and keeping spreadsheet dashboards updated, however, can quickly become a full-time job, especially when your project information lives in multiple apps like Asana, Jira, or a CRM. Here at Graphed , we automate all of that tedious work. Instead of spending hours building formulas, we let you connect your tools in just a few clicks and build real-time dashboards simply by describing what you want to see - like, “Show me all projects at risk, grouped by project owner.” We turn that request into a live, interactive dashboard instantly, so you can spend your time acting on insights, not just finding them.