Use Excel's AI to Build a Subcontractor Leveling Table

What This Does

Instead of building your subcontractor leveling table from scratch every bid, Excel Copilot can create a formatted comparison table from your raw data, with conditional formatting to highlight gaps and differences, in minutes instead of an hour.

Before You Start

• You have Microsoft 365 with Copilot enabled
• Your subcontractor quotes are entered (or ready to enter) in an Excel sheet (even a rough table works)
• You have at least 3 quote columns to compare

Steps

1. Set up your raw quote data in Excel

Create a simple table with columns: Sub Name, Base Bid, and a few rows for each scope item. It doesn't need to be pretty. Just get the numbers in.

What you should see: A basic table with sub names across the top and scope items down the rows, even if some cells are blank.

2. Open Copilot in the Home ribbon

Click Copilot in the Home tab. The Copilot chat panel opens on the right.

3. Ask Copilot to format and analyze the leveling table

Type a request to format and highlight the comparison.

What you should see: Copilot suggests formulas, formatting, or a restructured table layout based on your data.

4. Ask for conditional formatting to highlight gaps

Follow up with: "Add conditional formatting to highlight any cell that's blank or significantly different from the average."

What you should see: Copilot applies color coding, making it visually obvious which subs didn't price certain items.

5. Ask for a summary row

Type: "Add a summary row at the bottom that shows the low, average, and high for each scope item across all subs."

Real Example

Scenario: You have four mechanical sub quotes and need to level them before the 2pm bid deadline.

What you type in Copilot: "I have a leveling table with 4 mechanical subs in columns B-E and scope items in rows 2-20. Add conditional formatting to highlight cells that are blank (meaning that sub didn't include that item). Then add a row showing low/average/high for each scope item."

What you get: A color-coded table where empty cells (uncovered scope) stand out in red, and a summary row at the bottom showing the price range for each line item. You can immediately see which subs are light on scope.

Tips

• Ask Copilot to "identify which sub has the most scope exclusions" for a plain-language summary of your leveling table
• If you want to add an "adjusted" column that includes uncovered scope items, describe what to add and Copilot will suggest formulas
• Save your leveling table format as a template. Copilot can help you create a reusable version with locked headers and pre-built formulas

Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area.