Compensation benchmarking tells you whether you're paying competitively for each role. Underpay and you lose people. Overpay inconsistently and you create internal equity problems. A benchmarking spreadsheet in Excel lets you compare your current salaries to market data systematically, identify outliers, and build pay bands that scale with the company.
Your Employee Compensation Data
Start with a flat list of all employees. Columns: employee name, role title, level (IC1, IC2, Senior, Staff, etc.), department, location, base salary, bonus target, total cash, equity (current grant value), and total compensation.
Keep this data on a restricted sheet — only HR and leadership should see individual compensation.
Market Data
You need market rate data to benchmark against. Sources include Levels.fyi (for tech), Radford, Mercer, or free benchmarks from LinkedIn Salary or Glassdoor. For each role and level in your company, find the P25, P50, and P75 market rates from your chosen source.
Add a Benchmark sheet with: role, level, P25, P50, and P75. This is your reference table.
Calculating Compa-Ratio
Compa-ratio compares an employee's actual pay to the market midpoint (P50). Use VLOOKUP to pull the P50 for each employee's role and level, then calculate:
=D2/VLOOKUP(B2&C2, BenchmarkTable!A:D, 3, FALSE)
Where D2 is base salary, B2&C2 concatenates role and level for the lookup. A compa-ratio below 0.85 means the employee is significantly below market. Above 1.15 means they're above market.
Building Pay Bands
A pay band defines the min, mid, and max salary for each role and level. Typically:
- Min = P25 market rate
- Mid = P50 market rate
- Max = P75 market rate
Add columns showing whether each employee's salary falls within their band (between min and max) or is out of range. Flag anyone below the minimum in red — these are your most urgent retention risks.
The Easy Way: Using SheetXAI in Excel
Example 1: You have employee salary and benchmark data in the spreadsheet.
"I have employee salaries on Sheet 1 and market benchmark data by role and level on Sheet 2. Calculate compa-ratios for each employee, build pay bands based on the P25/P50/P75 benchmarks, and flag employees who are below the band minimum."
SheetXAI reads both sheets, calculates compa-ratios, builds pay bands, and flags outliers.
Example 2: Your employee data is in your HRIS.
"Pull current employee salaries and roles from our HRIS and compare them to the market benchmark data on Sheet 1. Show me who's below P50 and who's below P25."
SheetXAI connects to your HRIS, pulls salaries, and builds the full compensation benchmark report.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Headcount Tracker in Excel, How to Build a Payroll Summary in Excel, and Google Sheets AI Guide.
