How to Build a Billable Hours Tracker in Excel

A billable hours tracker in Excel is a time log with one row per task, plus a few formulas that answer the questions a client or manager asks: how many hours can we bill, to whom, and for how much? You need one table, SUMIFS for the totals and a small rate list. This guide is for freelancers, consultants and anyone at a firm that bills by the hour, and every step uses the same small example.

What a billable hours tracker in Excel needs#

A good tracker separates the log (what you did) from the summary (what you bill). The log only grows; the summary is all formulas. Billable hours are the hours you can charge to a client. Non-billable hours are internal work, such as admin or team calls.

Keep these rules for the log:

• One task per row. Don't merge cells or type subtotals between rows. Formulas need a clean list.
• Hours as decimal numbers. 1.5 means one and a half hours. A time like 1:30 is a fraction of a day, so it adds up differently.
• The same spelling every time. "Harbor Dental" and "Harbour Dental" are two different clients to a formula. A drop-down list fixes this; see how to add a drop-down list in Excel.

Step 1: Set up the time log#

Type these headers in row 1 and add one row for each piece of work:

Turn the range into a table with Ctrl+T (Mac: ⌘T). New rows then pick up the formatting, and formulas that point at the table grow with it.

If you prefer to log start and end times, add two time columns and work out the hours with =(End-Start)*24. Excel stores times as parts of a day, so multiplying by 24 turns 1:30 into 1.5. The guide to calculating the difference between two times covers shifts that pass midnight, and the weekly timesheet template shows a full start-and-end layout.

Step 2: Total billable hours by client with SUMIFS#

SUMIFS adds the numbers in one column when the other columns match all your conditions. To total billable hours for Harbor Dental:

=SUMIFS(D2:D8,B2:B8,"Harbor Dental",E2:E8,"Yes")

• D2:D8 is the range to add (hours). In SUMIFS it always comes first.
• B2:B8,"Harbor Dental" keeps only that client's rows.
• E2:E8,"Yes" keeps only billable rows.

The result is 7.75 (3.5 + 2.75 + 1.5). The 1.25-hour internal call is left out because it says No.

For a summary table, put the client names in H2:H4 and point the criteria at them:

=SUMIFS($D$2:$D$8,$B$2:$B$8,H2,$E$2:$E$8,"Yes")

Fill it down (Windows: Ctrl+D; Mac: ⌘D) and you get 7.75 for Harbor Dental, 2 for Lumen Studio and 4 for Patel & Co. The $ signs keep the log ranges fixed while H2 moves. If you only need one condition, SUMIF is shorter: =SUMIF(E2:E8,"Yes",D2:D8) gives all billable hours, 13.75. The SUMIFS function guide and the SUMIF function guide list every argument, and how to sum by category shows more summary layouts.

Step 3: Add rates and work out the amount to bill#

Keep the hourly rates in their own small table, not typed inside formulas. Then a rate change is one edit.

Add an Amount column F to the log:

=IF(E2="Yes",D2*XLOOKUP(B2,$H$2:$H$4,$I$2:$I$4),0)

XLOOKUP finds the client's rate and the IF returns 0 for non-billable rows. XLOOKUP needs Excel 2021 or Microsoft 365; in older versions use VLOOKUP(B2,$H$2:$I$4,2,FALSE) instead. Then total each client's amount with SUMIF: Harbor Dental comes to 697.50 (7.75 × 90), Lumen Studio to 150 and Patel & Co to 440. To turn those totals into a document you can send, follow how to build an invoice with formulas.

Step 4: Track billable hours by week or month#

Add date conditions to SUMIFS to total a period. Put the start date in K1 and the end date in K2, then:

=SUMIFS(D2:D8,E2:E8,"Yes",A2:A8,">="&K1,A2:A8,"<="&K2)

With 2 Nov 2026 in K1 and 3 Nov 2026 in K2, the result is 9.5 billable hours (3.5 + 2 + 4). The & joins the operator to the date in the cell. Writing ">=K1" inside the quotes would compare against the text "K1" and return 0.

For a whole month, type any date in that month in K1 and use EOMONTH(K1,-1)+1 for the first day and EOMONTH(K1,0) for the last. This works because Excel stores every date as a number, so "greater than or equal to" compares dates correctly. Real dates matter here: a date typed as text won't match any condition.

Step 5: Round time to billing increments#

Many firms bill in fixed units, such as 6 minutes (0.1 hour) or 15 minutes (0.25 hour). CEILING rounds a number up to the next multiple:

Keep the raw hours in one column and the billed hours in another. That way you can always show what was actually worked. If your agreement says to round to the nearest unit instead of up, use ROUND: =ROUND(D2/0.25,0)*0.25. The guide to rounding time in Excel covers clock times as well.

Check your utilization rate#

Utilization is the share of your working hours that you could bill. Divide billable hours by total hours:

=SUMIF(E2:E8,"Yes",D2:D8)/SUM(D2:D8)

Format the cell as a percentage with the % button on the Home tab. In the example, 13.75 of 15.5 hours were billable, so the rate is 88.7%. Run the same formula per person or per month to see trends. If total hours per week climb past contracted hours, calculating hours worked and overtime helps you split normal and extra time.

Practise it#

The formulas behind this tracker are the ones job tests ask for most. Practise SUMIF in a live spreadsheet: you type the formula and it checks it instantly. Then practise COUNTIFS and SUMIFS with two conditions and practise how Excel stores dates so date-range totals work first time. Or try the Harbor Dental exercise on this page now.