guide · 4 Sep 2026
Consultant booked over 100%: how to find it in Excel
Overallocation is almost never visible in the file you are looking at. It is visible in the sum of 3 files, once the same person stops appearing under 3 different names. Below is the check, and the 5 places it breaks.
Why it hides
A consultant does not appear once. She appears in the staffing file, again in a project tracker kept by a delivery lead, and again in a private copy someone made before a client meeting. Each file was written by a different person, so each file spells her differently.
Sarah Laurent
50%
Meridian phase 2 · staffing_2026_v3_FINAL_(2).xlsx
S. Laurent
20%
Pre-sales, Delta · staffing_2026_v3_FINAL_(2).xlsx
laurent.s
11 days
Halden audit · project tracker mars.xlsx
Sarah Laurent
0,1
Internal, recruiting · staffing_copie_JD.xlsx
No single file shows her above 50%, so no single file triggers a conversation. Normalise the 4 rows onto one person, convert the 11 days against an October with 22 working days, and the 4 rows read 50, 20, 50 and 10: she is at 130%. The number was always in the files. It was never in one of them.
This is why utilization reports built per project agree with each other and are all wrong at once. Overallocation is a property of a person across the portfolio, and the portfolio is the one view nobody owns.
The check, by hand
6 steps. Steps 02 and 04 are the ones that decide whether the answer is right, and they are the 2 that get skipped because they look like cleanup rather than analysis.
- 01Freeze the files. Copy every staffing file, tracker and private copy you were given into one folder named for the cycle, and list them. If you are not sure a 4th file exists, that is the first finding.
- 02Add a normalised name column. Build one key per row: trim, collapse double spaces, strip accents, lowercase, then last name plus first initial. Do it in a new column and never overwrite the original spelling.
- 03Build the people list by hand. Every distinct key, its real name, its capacity in percent, and its status. 2 keys that are one person get merged here, and each merge gets written down.
- 04Convert every allocation to one unit before summing. Pick percent of capacity per person per month, and convert days using that month's working days, not a flat 20.
- 05Sum per person per month. SUMIFS over the normalised key and the month, or a pivot with the key as rows and the month as columns. One number per person per month, from all files at once.
- 06Compare each total against that person's capacity, not against 100%. Flag anything over, list it with the file names and projects that produced it, and state the total you computed.
one key per row
=LOWER(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))
total per person per month
=SUMIFS(Load,Key,$A2,Month,B$1)
the threshold check
=IF(B2>Capacity,"over","")
The first formula is a starting point, not the whole normalisation. Accents survive it, so strip them with a lookup column, and initials only work once you have decided that laurent.s and S. Laurent resolve to the same key. That decision is yours to make and to write down.
The unit trap
Allocation is stored 3 ways, often inside the same folder, and all 3 look like numbers. Summing them without converting is the fastest way to a total that is confidently wrong.
0.5
Means: a fraction of full time, so 50% of capacity.
Trap: Excel shows it as 0.5 or as 50% depending on the cell format, and a SUM of fractions next to a SUM of percentages silently reads as 0.5 versus 50.
11 days
Means: 11 working days in the month.
Trap: 11 days is 55% of a 20-day month and 50% of a 22-day month. Without a days-per-month figure for that specific month, this row cannot be converted, and guessing 20 every month moves the person's total by 5 points.
"50%"
Means: 50% of capacity, stored as text.
Trap: Text rows are skipped by SUM and by SUMIFS, so the person's total comes back lower than the file says. This is the single most common reason an overbooked consultant reads as fine.
One guard before you trust the pivot: count the rows your SUMIFS actually used. If the allocation column has 412 rows and the sum used for 398, the 14 missing rows are text, blanks or merged cells, and they are the rows most likely to hold the overallocation.
The 5 places it breaks
The arithmetic is not the risk. These 5 failures are, and each one produces a total that is under 100% and wrong.
1A file version nobody told you about
The check ran on v3 while the delivery lead is staffing from v4, or from a private copy on a laptop. Every number below is correct and the answer is still wrong, because a row you never read carries 30%.
2A leaver still allocated
Someone who left in July still holds 40% on 2 projects in September. The total is above 100% and the finding is not overallocation, it is that the work is unowned. Both need saying, and they need saying differently.
3An accent or a trailing space
Sarah Laurent and Sarah Laurent with a trailing space are 2 keys to every lookup in Excel. So are Zoé and Zoe, and so are a non-breaking space pasted from a deck and a normal one. The pivot shows 2 rows at 70% and 60%, both under 100%.
4Part-time capacity treated as 100%
A consultant at 4 days a week is at capacity at 80%, not 100%. A flat threshold of 100% clears her at 90% while she is 12.5% over. Capacity per person has to sit in the people list, and it usually sits in HR instead.
5One project in days, another in percent
The staffing file stores percentages and the tracker stores days. Summed as-is, 60 plus 10 is 70 and reads as safe. Converted first, 60% plus 50% is 110% and is a staffing decision.
What to do once you find it
130% is not a spreadsheet error to be corrected. It is 4 commitments made by 4 people who could each see only their own project. The fix is a staffing conversation, and the spreadsheet is only the evidence you bring to it.
- ✓Name the person, the total and the projects: Sarah Laurent is at 130% in October, across Meridian phase 2, the Halden audit, pre-sales and 10% of internal recruiting.
- ✓Say which file each row came from, so the delivery leads argue about the staffing and not about your arithmetic.
- ✓Bring the 3 options a partner can actually choose between: move scope out of one project, move a person in, or move the date.
- ✓Put the decision in the pack with a date on it. An allocation above 100% that nobody decided is still above 100% next cycle.
Do not quietly reduce a percentage to make the row balance. Whoever wrote 60% expects 60% of that person, and editing the cell moves the overallocation from the file into the delivery.
Or drop the files
This is the check Operations Cell runs on every consolidation. You drop the staffing file, the tracker and the private copy exactly as they are, a model reads every row, and the pack comes back with the allocation per person per month, every merge it made, and every row it could not convert.
Read a real pack before you trust it with client numbers.
The example is a real run of the engine on an invented firm whose files are as broken as yours: one consultant spelled 2 ways, one project tracked in days, and an allocation at 130% nobody had seen.