How to Create a Utilization Report in Google Sheets

A utilization report is one of the most powerful tools for understanding how your team's most valuable resource - time - is being spent. Whether you run an agency, a consultancy, or a software team, tracking billable versus available hours is critical for profitability and team health. This tutorial will walk you through building a functional, and even visually appealing, utilization report right within Google Sheets, from setting up your raw data to building interactive charts.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Is a Utilization Report, Anyway?

In simple terms, a utilization report measures how much of your team's available time is being used for productive, revenue-generating activities versus internal tasks or downtime. The core metric is the utilization rate, usually expressed as a percentage. It helps you answer essential questions like:

• Are our projects staffed correctly?
• Is anyone on the team overworked or facing burnout?
• Are we pricing our services profitably?
• How much capacity do we have for new projects?

While dedicated project management tools offer this, building your own in Google Sheets gives you complete control over the inputs and outputs, allowing you to customize it perfectly for your business needs.

Step 1: Laying the Groundwork for Your Data

The foundation of any good report is clean, well-structured data. We'll start by creating a dedicated sheet for raw data entry. This separation is key - it keeps your formulas and your final dashboard neat and tidy.

Create a new Google Sheet and name the first tab "Raw Data" or "Timesheet Data". Set up the following columns:

• Date: The date the work was performed.
• Team Member: The name of the person who did the work.
• Project Name: The project the task is associated with.
• Task Description: A brief description of the work.
• Hours Logged: The number of hours spent on the task.
• Billable?: A simple "Yes" or "No" to indicate if the time can be billed to a client.

Pro Tip: Use Data Validation to Prevent Errors

Typos are the number one enemy of accurate reporting. "John Smith" and "Jon Smith" will be treated as two different people by your formulas. You can prevent this with data validation.

1. Select the column where you want to restrict input, like the "Team Member" column (Column B).
2. Go to Data > Data validation.
3. From the "Criteria" dropdown, choose "List from a range".
4. Click the grid icon and select a range where you've listed all your team members' official names (you can put this list in another tab called "Lists" to keep things clean).
5. Ensure "Show dropdown list in cell" is checked and click Save.

Now, instead of typing a name, you'll get a dropdown menu. Repeat this process for the "Project Name" and "Billable?" (with a "List of items" and "Yes,No" as the criteria) columns to create a "locked-down" data entry system.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Building the Calculation Engine

With your data structure in place, it's time to build the engine that will crunch the numbers. Create a new tab and name it "Dashboard" or "Report". This is where our calculations and visualizations will live.

Dynamically Listing Team Members

First, we need a unique list of all team members who have logged time. Instead of manually copying them over, we can use a formula to do this automatically. This ensures that when a new person starts logging hours, they'll instantly appear on your report.

In cell A2 of your "Dashboard" tab, enter this formula:

=UNIQUE('Raw Data'!B2:B)

This formula looks at the "Team Member" column in your data tab and returns a clean list with no duplicates.

Calculating Total and Billable Hours with SUMIFS

The SUMIFS function is perfect for this task. It totals numbers in one column based on one or more criteria in other columns. We'll use it to calculate total logged hours and total billable hours for each person.

In cell B1, type the header "Total Hours". In C1, type "Billable Hours".

In cell B2 (next to the first team member's name from your UNIQUE formula), enter this formula to calculate their total hours:

=SUMIFS('Raw Data'!E:E, 'Raw Data'!B:B, A2)

This formula reads as: "Sum all the numbers in column E (Hours Logged) from the 'Raw Data' sheet, but only if the corresponding cell in column B (Team Member) matches the name in cell A2 of this sheet."

Now, in cell C2, enter this formula to calculate their billable hours:

=SUMIFS('Raw Data'!E:E, 'Raw Data'!B:B, A2, 'Raw Data'!F:F, "Yes")

This is almost identical, but we've added a second condition: "...and only if the corresponding cell in column F (Billable?) contains the text "Yes.""

Click on cell B2, then hold Shift and click on cell C2 to select both. Grab the small blue square (the fill handle) in the bottom-right corner of cell C2 and drag it down. The formulas will automatically adjust for each team member in your list.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Defining Capacity and Calculating Utilization

To calculate a utilization rate, you need to know the total capacity. Let's start with a simple calculation based on a 40-hour work week. In cell D1, type "Capacity (Hours)". In D2, enter:

40

Now, in cell E1, add the header "Utilization Rate". In cell E2, enter the following formula:

=C2/D2

This formula divides the Billable Hours (C2) by the Total Capacity (D2). Select cell E2 and click the "%" icon in the Google Sheets toolbar to format it as a percentage. Drag the formula down to apply it to all team members.

Note: This is a simple weekly capacity. You can create a more complex system where you define daily or monthly capacity and adjust calculations accordingly based on your chosen date range.

Step 3: Bringing Your Report to Life with Charts

Raw numbers are great, but visuals tell the story much faster. Let's add a few charts to make the insights pop.

Team Utilization Bar Chart

A bar chart is perfect for comparing utilization rates across the team.

1. Select the "Team Member" and "Utilization Rate" columns (click column A, then hold Ctrl/Cmd and click column E).
2. Go to Insert > Chart. Google Sheets will likely default to a bar chart, which is what we want.
3. Use the Chart editor on the right to customize the title (e.g., "Team Utilization Rate"), change colors, and add a target line. To add a target, go to the "Customize" tab, find "Gridlines and ticks," scroll down, and find the settings for the horizontal axis. You can create an additional series with a single value, your target rate, and overlay it as a line.

Now you can see at a glance who is near, below, or over capacity.

Billable vs. Non-Billable Effort Stacked Column Chart

To see how each person's total time is split, a stacked column chart is ideal.

1. First, we need a "Non-Billable Hours" column. On your "Dashboard" sheet, add that header in F1. In F2, enter the formula:

=B2-C2

and drag it down. 2. Select your range of data including the headers: "Team Member," "Billable Hours," and "Non-Billable Hours" (e.g., A1:A10, C1:C10, F1:F10). 3. Go to Insert > Chart and select a "Stacked column chart" from the chart type dropdown.

This chart gives you a quick visual breakdown of each person's workload, making it easy to see who is spending a lot of time on internal tasks versus client work.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Add Slicers for Interactive Filtering

Slicers turn your static report into an interactive dashboard. This allows you to filter the entire report by project, date range, or team member without editing any formulas.

1. Click anywhere inside your table of calculations.
2. Go to Data > Add a slicer.
3. A filter box will appear. In the Slicer settings sidebar, choose "Project Name" for the column.
4. Repeat the process to add another slicer for "Date".

Now you can use these slicers to drill down. Want to see utilization for "Project X" last month? Just set the filters, and all your formulas and charts will update instantly.

Keeping Your Report Accurate and Up to Date

Your finished report now has a data entry tab, an automated calculation engine, and a dashboard with interactive charts and slicers. However, its accuracy depends entirely on consistent and timely data entry from your team. Make it a habit for everyone to update their hours daily or at the end of each week.

This manual system is powerful and flexible, but it comes with the administrative overhead of making sure everyone logs their time accurately and that the underlying formulas and connections in the sheet remain intact.

Final Thoughts

Building a custom report in Google Sheets is a great way to gain control over your team's performance metrics. By separating your data, using powerful formulas like SUMIFS and UNIQUE, and adding visual elements and slicers, you can create a robust tool for analyzing team utilization without spending any money on new software.

While Google Sheets is fantastic for custom builds, we know the manual data entry and report maintenance can become a major time sink. At Graphed , we built our tool to eliminate this busywork entirely. By connecting directly to your project management apps, CRMs, and ad platforms, we automatically pull in your team's data in real-time. You can then build powerful, always-up-to-date utilization dashboards just by describing what you want to see, letting you focus on making smart staffing and project decisions instead of chasing down timesheets.