Free Compensation Analysis Template (Excel)

What's in the compensation analysis template

The template is a single Compensation Analysis sheet, one row per employee or role. Each row captures the employee name, department, level, current pay, the band minimum, midpoint, and maximum for that role, and a market rate if you have one.

From those inputs, the sheet calculates four things per row: compa-ratio, range penetration, a Band flag (Below band, Within band, or Above band), and the dollar gap between current pay and market rate. A bar chart plots compa-ratio by employee so outliers are visible at a glance without scrolling the table.

It's built for a straight pay-band review, not a full comp-planning suite: there's no scenario modeling or budget simulation, just the row-level math you need to see where each person sits.

What calculates automatically

Compa-ratio divides current pay by the band midpoint, so you immediately see whether someone sits above or below the center of their range - the conventional healthy zone is 0.80 to 1.20 around 1.00. Range penetration shows where current pay falls between the band minimum and maximum as a percentage, which is a different lens than compa-ratio and useful when bands are wide.

The Band flag reads the same inputs and labels each row Below band, Within band, or Above band, so you can sort or filter the table to pull up exactly who's outside their range. Gap vs market subtracts the market rate you entered from current pay, giving you a dollar figure for how far off benchmark each person is.

Every formula recalculates the moment you fill in a row, and rows with missing inputs just stay blank instead of throwing an error - so a half-filled table never shows broken math.

Why this beats a blank spreadsheet

Compa-ratio, range penetration, and band-flag logic are easy to describe but fiddly to build correctly - one wrong cell reference and a Below band employee can silently read as Within band. That's a bad place to have a formula error when the output feeds pay decisions.

This template already has those formulas wired row by row, plus conditional formatting that highlights Below band and Above band rows and a summary block counting how many people fall in each bucket. You fill in pay and band numbers; the flags and the chart are already correct.

Who this template is for

HR generalists, People Ops teams, and small-company founders doing a pay-band or pay-equity spot check without buying a comp-planning platform are the core fit. If you're preparing for a compensation review cycle and need to see who's out of range before the meeting, this table gets you there in an afternoon.

It also works as a lightweight starting point for a broader comp study: fill in the roles and bands you already have, sort by Band flag, and use the output to decide where to focus a deeper market-rate benchmarking exercise.

This version doesn't flag pay gaps by demographic group - it's a band/market comparison only, not a pay-equity statistical analysis. If you need that level of review, pair this output with your legal or compensation consulting team.