If you receive 5 to 15 subcontractor bids on a structures or earthwork package, a spreadsheet is the obvious first tool. It is free, every estimator can read one, and a well-built sheet beats a stack of PDFs on a desk. This article gives you a column-by-column layout, the formulas that do the real work, and an honest accounting of where a spreadsheet stops being enough — the point at which most precon teams move to dedicated bid leveling software instead of maintaining the sheet by hand.
Nothing here assumes you bought anything. If you finish this and keep using Excel, you will at least have a cleaner sheet. If you finish it and decide the sheet is costing you more than it saves, you will know exactly which four checks pushed you there.
How to structure the sheet
The mistake most people make is putting bidders in rows. Put line items in rows and bidders in columns. Your scope of work (SOW) line items are the fixed spine of the analysis; bidders come and go. A package with 60 pay items and 8 subs becomes a 60-row, 8-column-block matrix that reads left to right.
Use a stacked-column-group layout. For each bidder, repeat the same three columns: unit price, extended total, and a scope flag. That way a missing line is obvious — it shows as a blank in that bidder's block while the neighbors are filled.
| Col | Field | Purpose |
|---|---|---|
| A | Item No. | SOW / pay-item reference (e.g. NYSDOT 203.0201) |
| B | Description | Your scope wording, not the sub's |
| C | Unit | CY, SY, LF, EA, LS |
| D | Qty | Your estimated quantity, held constant for all bidders |
| E, H, K… | Bidder unit price | As keyed from each bid PDF |
| F, I, L… | Bidder extended | =D2*E2 — never trust the sub's math |
| G, J, M… | Scope flag | 1 = included, 0 = not bid, X = qualified/excluded |
The single most important rule: hold quantity (Col D) constant across every bidder. You are comparing unit prices against your own takeoff, not against each sub's interpretation of the quantity. This is what "normalized to a common basis" actually means in practice.
The formulas that do the work
Five formulas turn the matrix from a typing exercise into an analysis. Assume row 2 is the first line item and your bidder unit price sits in E2.
1. Recompute every extension
=D2*E2 in the extended column. Subs transpose digits and fat-finger extensions constantly; re-extending from your own quantity catches it.
2. Column totals
=SUM(F2:F61) at the foot of each bidder's extended column gives the leveled base bid on your quantities — already different from the number on the sub's cover sheet.
3. Low-bid-per-line
Add a helper column: =MIN(E2,H2,K2,N2). The lowest unit price per line is rarely all in one bidder. Highlighting it shows where each sub is genuinely sharp versus where they bought the job on one big item.
4. Variance to the peer set
This is the closest a sheet gets to outlier detection: =E2/MEDIAN($E2,$H2,$K2,$N2). A result of 2.4 means that bidder is 140% above the median on that line. You still have to read every cell and decide what is suspicious.
5. Coverage count
=COUNTIF(scope_flag_range,1) per bidder against your total line count tells you who actually bid the whole package. A low base bid with a coverage of 52 of 60 lines is not a low bid; it is an incomplete one.
What the sheet can never do
A spreadsheet is a calculator with a grid. It will compute anything you tell it to compute, on the rows you typed in. The problem on a real bid package is the four things it cannot do — and these are exactly the things that get an award challenged.
It cannot run peer-median outlier detection on its own
You can write a MEDIAN formula, but you have to read every cell, decide the threshold, and remember which lines you flagged. There is no rule sitting behind the sheet that says "any unit price above 2x or below 0.5x the peer median is an outlier" and surfaces them for you. On a 60-line, 8-bidder package that is 480 cells to eyeball — every time a sub sends a revised number.
It cannot flag unbalanced unit prices automatically
Front-loaded mobilization and penny-priced line items are the classic way a sub games a unit-price bid: bury cost in the items that get paid early, zero out the items the GC might delete. A spreadsheet will happily sum a $0.01 unit price and a mobilization line worth 14% of the bid without a word of warning. You only catch it if you already knew to look for it on that specific line.
It cannot hold an audit trail
When a PM overrides the low bid and picks the second bidder, the spreadsheet does not record who decided, when, or why. The justification lives in an email, or in someone's memory. Six months later when the losing sub asks why, or an owner audits the award, "we just went with them" is not a defensible answer.
Re-keying eats the hours
Every unit price in that matrix was typed by a human off a PDF. Eight bids at 60 lines each is 480 unit prices to key, plus descriptions, plus units. Realistic rate with double-checking is 2 to 4 hours per package before a single number is analyzed — and every revised bid means re-keying that column. That manual time is the real cost, and it is easy to underprice. The free leveling ROI calculator lets you plug in your package count and loaded estimator rate to see what those hours add up to over a year.
When to graduate from the sheet
The sheet is fine for one or two packages a month with a handful of bidders and no audit pressure. The breakpoints where teams move on are concrete:
- Volume: you are leveling enough packages that re-keying is a named line in someone's week.
- Bidder count: 8+ subs per package, where eyeballing 480 cells for outliers stops being reliable.
- Audit exposure: public or owner-funded work where every award needs cited, defensible reasoning — not a recalled conversation.
- Override frequency: you regularly pick someone other than the low bid and have to justify it in writing.
This is where dedicated tooling earns its place. Bid Reasoner reads each sub bid PDF directly, extracts and normalizes the line items, and maps them to your SOW — removing the re-keying step entirely. It runs four deterministic risk rules on every bid automatically: unbalanced unit prices at or below $1.00, peer outliers above 2x or below 0.5x the peer median, total-bid outliers beyond 20% deviation, and front-loaded mobilization above 10% of the total. It scores each bidder on price, scope, schedule, compliance, performance, and risk, runs scope-coverage gap analysis to surface missing and extra scope, and recommends a winner with page-cited evidence and a forced-override audit trail. The same analysis your sheet does by hand, plus the four checks it cannot.
If you want to keep the spreadsheet, keep it — the layout and formulas above will make it better today. If the four limits above are biting, the move from a hand-maintained matrix to automated bid leveling software is usually the cheapest hour you will buy back all year.