Whether you’re tracking subcontractor bids, project costs, or safety scores, Excel’s RANK and RANK.EQ functions can help you identify top performers at a glance.
In this blog, we’ll break down how the RANK functions work, where they shine on a jobsite, and how to put them to work in your construction spreadsheets with just a few clicks.
What Is the RANK Function in Excel?
The RANK and RANK.EQ functions return the rank of a number in a list of numbers. The highest number will get a rank of 1 by default (unless you sort ascending).
- RANK — is the original version (still works in most Excel versions)
- RANK.EQ — is the newer and preferred function in Excel 2010+
Format: RANK.EQ(number, ref, [order])
- number — is the value to rank
- ref — is the range of numbers to compare it to
- order — is optiona, user 0 for decending (default), 1 for ascending
Step-by-Step: Use RANK.EQ
- In a new column, enter the formula: =RANK.EQ(B2, B2:B6, 0)
- To group totals by vendor, date, or category
Excel will assign rank 1 to the highest labor cost (ABC Medical Center), 2 to Park Expansion, and 3 to AZ High School.
Pro Tip: If there are tied values, RANK.EQ will assign the same rank to each and skip the next number (e.g., two #1s, then #3).
Use RANK for Competitive Scoring
Imagine your field safety audit includes:
- PPE compliance
- Cleanliness
- Equipment checks
You total the scores and use RANK.EQ to determine which crews or subs are outperforming. You can even combine it with conditional formatting to highlight the top 5 automatically.
Common Use Cases for Builders
| Use Case | Example Formula |
|---|---|
| Rank projects by total labor cost: | =RANK.EQ(B2, B2:B10, 0) |
| Rank vendors by number of late deliveries: | =RANK.EQ(C2, C2:C20, 1) |
| Rank jobs by number of open tasks: | =RANK.EQ(D2, D2:D50, 0) |
RANK vs. Soring
You have a spreadsheet showing labor costs across active construction projects. You want to know which project is the most labor-intensive.
- Use Sort to organize data visually
- RANK when you want to show relative performance side-by-side with raw numbers
They work great together. Sort by RANK to highlight who’s on top.
Builder Bonus: Combine with COUNTIFS
For more power, combine RANK with COUNTIFS. For example:
=IF(COUNTIFS(StatusRange, "Incomplete", ProjectRange, A2)>5, RANK.EQ(B2, B2:B10),"")
Use this to only rank jobs that exceed a threshold of open items.
Need Help Building Job Scorecards?
We work with builders, subcontractors, and developers across Arizona to streamline field reporting, automate job tracking, and turn messy spreadsheets into reliable dashboards.
Let’s book an automation and efficiency session and we’ll show you how to get more insight with less effort..
Let’s Build Together!
Book Your Free Consultation
