Are You Overstaffed? Track Billable vs Non-Billable Headcount % in Excel

How to Calculate Billable vs Non-Billable Headcount % in Excel

1. Create a role classification table

• Start with the Role field in your dataset.
• Use the UNIQUE function to generate a distinct list of roles.
• Next to each role, assign a simple classification:
  • 1 for billable roles
  • 0 for non-billable roles
• This gives you a clean lookup table that separates revenue-producing roles from support roles.

2. Add the billable classification back into the dataset

• In the main dataset, create a new column for the billable classification.
• Use VLOOKUP to match each employee’s role to the classification table.
• This returns either a 1 or a 0 for every row.
• Once this is in place, the rest of the analysis becomes much easier because you can count or sum billable versus non-billable employees by month.

3. Create a unique list of months

• Build a monthly summary section for the KPI.
• Use the UNIQUE function on the Month End field to create one row for each month.
• Format the results as short dates for a cleaner view.
• Center the summary area if you want the table to look more polished before charting.

4. Calculate billable headcount by month

• For the billable count, use SUMIFS.
• Since billable roles are marked as 1, summing those values by month gives you the billable headcount.
• Fill the formula down so each month returns its own billable total.
• This creates the first half of the monthly headcount split.

5. Calculate non-billable headcount by month

• For non-billable roles, use COUNTIFS.
• Instead of summing zeros, count the rows where the classification equals 0 for each month.
• Fill the formula down for all months.
• This gives you the non-billable headcount side of the KPI.

6. Calculate billable and non-billable percentages

• Once you have both monthly counts, calculate the percentage split for each month.
• Format the results as percentages using Excel percentage formatting.
• Fill the calculations down so every month shows both:
  • billable headcount %
  • non-billable headcount %

7. Add a target line

• Create a target percentage in a separate cell, such as 60% or 65%.
• Then repeat that same target down the full month range so it can be plotted on the chart.
• This creates a dynamic target row that updates the visual comparison when you change the target value.

8. Insert the stacked column chart

• Highlight the month column along with the billable % and non-billable % columns.
• Insert a Stacked Column Chart or 100% Stacked Column Chart.
• Either option works, but the 100% stacked version makes the percentage split easier to read because the top axis aligns to 100%.

9. Add the target series to the chart

• Copy the target cells and paste them directly into the chart.
• Then go to Change Chart Type and switch the chart to a Combo Chart.
• Keep the billable and non-billable series as stacked columns.
• Change the target series into a line so it appears across the chart as a benchmark.

10. Format the final chart

• Adjust the target line so it stands out clearly.
• Reduce the line size if needed and change the style so it is easier to compare against the columns.
• Update the chart title to match the KPI name.
• Once formatted, the chart will show month-by-month billable versus non-billable headcount, along with a target line that can be adjusted at any time.

11. Review the monthly pattern

• With the chart complete, compare the billable portion of each column against the target line.
• Any month where the billable section sits below the line indicates the business is under target.
• Because the target is dynamic, you can test different thresholds and instantly see how performance changes across the year.