If your team bills by the hour, a billable hours dashboard tells you where the time actually goes: which clients use the most hours, who is overloaded, and how much of the week is spent on work you can invoice. In this guide you will build one from a simple timesheet, first in Excel with formulas and PivotTables, then as a Power BI version with a few DAX measures. By the end you will be able to see utilization %, billable versus non-billable hours, and totals by client and staff member for any date range.
I build timesheet and billing reports for agencies, law firms and service businesses quite often, and the structure below is the one that holds up best when the data grows from a few hundred rows to tens of thousands.
Set up the timesheet table correctly
The dashboard is only as good as the timesheet behind it. The single most important rule: one row per time entry, one column per field, no merged cells and no subtotals in the data. Put the data on its own sheet and format it as an Excel Table (Ctrl+T). Name the table tblTime on the Table Design tab.
| Column | Example | Notes |
|---|---|---|
| Date | 03-Nov-2026 | A real date, not text |
| Staff | Priya | Pick from a list to avoid spelling variants |
| Client | Northwind LLC | Use “Internal” for admin, training and sales |
| Task | Monthly bookkeeping | Free text or a list |
| Hours | 2.5 | Decimal hours, not 2:30 |
| Billable | Yes | Yes or No from a dropdown |
| Rate | 85 | Hourly rate for that entry |
Add data validation dropdowns for Staff, Client and Billable (Data, Data Validation, List). Then add one calculated column to the table for the billable value:
Amount: =IF([@Billable]="Yes", [@Hours] * [@Rate], 0)On a separate sheet, create a small tblStaff table with each person’s name and their weekly capacity in hours, for example 40 for full time and 20 for part time. Capacity is what turns raw hours into a utilization percentage.
Build the billable hours dashboard in Excel with formulas
Create a Dashboard sheet. Put a start date in C2 and an end date in C3 so the whole report can be switched to any week, month or quarter. Then list your staff names in column B starting at B6 and use these formulas across the row.
- Total hours in C6:
=SUMIFS(tblTime[Hours], tblTime[Staff], $B6, tblTime[Date], ">="&$C$2, tblTime[Date], "<="&$C$3) - Billable hours in D6 (same formula with one extra condition):
=SUMIFS(tblTime[Hours], tblTime[Staff], $B6, tblTime[Billable], "Yes", tblTime[Date], ">="&$C$2, tblTime[Date], "<="&$C$3) - Non-billable hours in E6:
=C6-D6 - Capacity hours in F6. This takes weekly capacity, converts it to a daily figure and multiplies by working days in the period:
=XLOOKUP($B6, tblStaff[Staff], tblStaff[WeeklyCapacity]) / 5 * NETWORKDAYS($C$2, $C$3) - Utilization % in G6, formatted as a percentage:
=IF(F6=0, 0, D6/F6) - Billable value in H6: the same SUMIFS pattern pointed at
tblTime[Amount].
XLOOKUP needs Excel 365 or Excel 2021 and later. On older versions use INDEX(tblStaff[WeeklyCapacity], MATCH($B6, tblStaff[Staff], 0)) instead. If your team has public holidays, add a holiday list as the third argument of NETWORKDAYS so capacity is not overstated.
Add conditional formatting to the utilization column: for example green above 75%, amber between 60% and 75%, red below 60%. The right target depends on the role. A senior partner who also sells work will never be at 90%, and that is fine.
Add PivotTables for client and staff breakdowns
Formulas are great for the fixed summary. For exploring the data, PivotTables are faster to build and easier to change.
- Click inside
tblTimeand choose Insert, PivotTable, New Worksheet. - Drag Client to Rows, Billable to Columns and Hours to Values. You now have billable and non-billable hours per client.
- Add a second PivotTable with Staff in Rows, Billable in Columns and Hours in Values. Right click a value, choose Show Values As, % of Row Total to see each person’s billable ratio.
- Select either PivotTable and use PivotTable Analyze, Insert Timeline on the Date field, plus a slicer on Client.
- Right click the timeline, choose Report Connections and tick both PivotTables so one click filters everything.
- Insert a clustered bar chart from the client PivotTable to show the top clients by hours.
Note the difference between the two percentages. Billable ratio is billable hours divided by hours logged. Utilization is billable hours divided by available capacity. Someone who logs only 25 hours in a 40 hour week can have a 100% billable ratio and still be at 62.5% utilization. Show both, and label them clearly.
Remember that PivotTables do not update on their own. After new entries, use Data, Refresh All.
The Power BI option for larger teams
Once you have several people logging time every day, or timesheets coming from a tool like Harvest, Clockify or QuickBooks Time, Power BI is a better home for the dashboard. It handles more data, refreshes automatically and can be shared with managers who should not edit the source.
Load the timesheet and the staff table, add a Date table, and relate Timesheet[Date] to Date[Date] and Timesheet[Staff] to Staff[Staff]. In the Date table, add a column IsWorkday = WEEKDAY('Date'[Date], 2) <= 5. Then create these measures:
Total Hours = SUM ( Timesheet[Hours] )
Billable Hours = CALCULATE ( [Total Hours], Timesheet[Billable] = "Yes" )
Non-Billable Hours = [Total Hours] - [Billable Hours]
Billable Ratio = DIVIDE ( [Billable Hours], [Total Hours] )
-- Working days in the selected period times each person's daily capacity
Capacity Hours =
VAR WorkDays =
CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[IsWorkday] = TRUE () )
RETURN
SUMX ( Staff, Staff[WeeklyCapacity] / 5 * WorkDays )
Utilization % = DIVIDE ( [Billable Hours], [Capacity Hours] )
Billable Value =
SUMX (
FILTER ( Timesheet, Timesheet[Billable] = "Yes" ),
Timesheet[Hours] * Timesheet[Rate]
)A practical page layout: cards for Billable Hours, Utilization % and Billable Value across the top, a stacked bar of billable and non-billable hours by staff, a matrix of clients by month, and slicers for date and team. I used a similar layout in the legal timesheet billing dashboard in my portfolio.
Common mistakes with billable hours reports
- Hours typed as time. An entry of 2:30 is stored as a fraction of a day (0.104), so totals look tiny. Use decimal hours or convert with
=[@Time]*24. - Inconsistent client names. “Northwind”, “Northwind LLC” and “northwind ” become three clients. Dropdown lists solve this.
- Using a fixed range instead of a Table. Formulas that point at A2:G500 silently miss row 501. Structured references grow with the data.
- Measuring everyone against the same target. Set targets per role, or utilization will look like a performance problem when it is not.
- No capacity data. Without capacity you can only show the billable ratio, which hides people who simply are not logging their time.
Frequently asked questions
What is a good utilization rate?
It depends on the role and the business. Many professional services firms aim for somewhere around 70% to 85% for delivery staff and much lower for managers, but the useful number is your own trend over time.
Can this work with Google Sheets?
Yes. SUMIFS, NETWORKDAYS, XLOOKUP and pivot tables all exist in Google Sheets. Structured references like tblTime[Hours] only work inside Sheets tables, so with a plain range use normal ranges or named ranges instead.
Should I use Excel or Power BI?
Excel is perfect for a small team with one person maintaining the timesheet. Move to Power BI when data comes from several sources, when the file gets slow, or when managers need a shared, read-only view.
How do I include time from a time-tracking app?
Most apps export a CSV with the same columns described above. Load that export with Power Query and append it to your table, then refresh the dashboard instead of copying and pasting.
Want this done for you?
If your timesheet data is spread across files or apps and you would like a finished dashboard, I set this up as part of my Power BI dashboard service and also build Excel versions with VBA-automated billing. Get an instant quote and see what it would take for your team.
