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
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
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
