A free Excel salary planning spreadsheet for running your annual merit cycle. Model raises, track spend against each department’s budget, and see where every employee sits in their pay range before you finalize a single number.
Format: Excel (.xlsx) | Tabs: 5 | Setup time: about 30 minutes | Cost: Free, no signup
The file comes pre-loaded with 15 sample employees across five departments, so you can watch every formula work before you swap in your own data. Here’s what each tab does and how to use it.
What’s in the Template
The workbook has five tabs. Four do the planning work, and one is there for reference.
Roster
This is the tab everything else depends on. The other tabs pull employee data from here using the Employee ID, so it has to be right.
For each person you enter current salary, pay grade, hire date, most recent performance rating, and the pay range minimum, midpoint, and maximum for their grade. Compa-ratio fills in on its own: current salary divided by range midpoint.
Someone earning $96,000 against a $110,000 midpoint comes out at an 87.3% compa-ratio, which is another way of saying they’re paid about $14,000 under the market target for their grade.
Merit Planning
This is where the actual decisions get made. Type in an Employee ID and the name, department, current salary, performance rating, and compa-ratio all populate from the Roster.
You fill in one thing: the proposed merit percentage. The tab works out the increase amount, the new salary, and the new compa-ratio. There are columns for effective date, notes, and approval status too, plus a totals row that tracks the blended average increase and the total dollars across everyone in the plan.
Dept Budget
Think of this as the budget control panel. You enter the allocated merit pool percentage for each department, and the tab calculates how many dollars that gives you against current payroll, pulls planned spend from Merit Planning as you go, and shows what’s left. A status column marks each department on track, under budget, or over budget, so you catch a problem before it reaches an approver.
Compa-Ratio
This is the analysis layer, and it’s the part most free templates skip. It takes each employee’s position in their pay range, lines it up against their performance rating, and sorts everyone into four groups with a recommendation for each. That’s the difference between a spreadsheet that does arithmetic and one that helps you make a call. There’s more on how to read it below.
Instructions
A reference tab. It has the color key (blue cells take your input, white cells are formulas), a short walkthrough of each tab, and plain definitions of the terms. Start here if you’re handing the file off to someone who hasn’t seen it.
How to Use It
Once you’ve swapped in your own data, the workflow runs in six steps, roughly the order you’d follow during a real planning cycle.
1. Fill in the Roster
Swap the sample employees for your own. Before you enter pay ranges, check when the midpoints were last benchmarked. Ranges that are more than a couple of years old in competitive roles tend to sit below current market, and that error flows into every compa-ratio after it. Compa-ratio itself calculates once salary and midpoint are in.
2. Set the department budgets
On Dept Budget, put each department’s pool percentage in column D. Get these numbers agreed with finance before you open the cycle, not after a manager asks why their figure looks off.
3. Model the Increases
On Merit Planning, enter proposed merit percentages in column H. Keep an eye on Dept Budget while you work, because it updates live. If a department burns through its pool before you’ve reached the bottom of its roster, flag it with the department head before anything goes to approvers.
4. Check the Budget in Both Directions
An over-budget department announces itself. The one to watch is the department sitting well under budget, which usually means flat increases went out without anyone opening the compa-ratio view, so the people who actually needed a bigger raise didn’t get one.
5. Review Positioning
Open the Compa-Ratio tab before you finalize anything. Look for high performers below midpoint getting small increases, below-expectations employees getting any increase at all, and anyone whose new compa-ratio would land above their range maximum.
6. Track Approvals
Use the Approval Status and Notes columns in Merit Planning. The notes field is where the reasoning goes. When someone asks six months from now why an employee got 3% and not 5%, that column is your answer.
How to Read the Compa-Ratio Tab
Most salary cycles hand out the merit pool on performance rating alone. The catch is that two people with the same rating can sit in completely different spots in their pay range, and they shouldn’t be treated the same. The Compa-Ratio tab splits everyone into four groups.
High performer, below midpoint. Strong rating, compa-ratio under 100%. This is your top priority and your biggest flight risk at the same time. These people are valuable and underpaid for their grade. Spend above-average increases here.
High performer, above midpoint. Strong rating, compa-ratio at or above 100%. Already paid well for good work, so a standard increase is fine. If someone is near the top of their range, a lump sum or some non-monetary recognition makes more sense than a base bump that pushes them through the ceiling.
Average performer, below midpoint. Meets expectations, compa-ratio under 100%. Standard increases, with the aim of nudging them toward midpoint over a few cycles. They’re not going anywhere today, but leave them stuck below midpoint for years and that changes.
Below expectations, above midpoint. Already paid above midpoint and not performing. No base increase. A lump sum at most, or hold off until the performance issue is sorted out.
One thing to settle first is that the whole analysis is only as good as the ratings feeding it. If one manager calls half the team “exceeds” and another gives it to one person in ten, the first team walks away with a bigger slice of the pool because of how a form got filled in, not because anyone performed better. Calibrate ratings across managers before you lean on this tab.
Frequently asked questions
What format is the template in?
Excel (.xlsx). It opens in Microsoft Excel and Google Sheets, though the conditional formatting looks best in Excel. Every cell and formula is unlocked, so you can edit anything.
What does each tab do?
Five tabs. Roster holds employee data and works out compa-ratio. Merit Planning is where you model increases, with employee info auto-filled and new salaries calculated. Dept Budget allocates the pool and tracks spend by department. Compa-Ratio handles the pay-position analysis and recommendations. Instructions is the reference and color key.
How is compa-ratio calculated?
Current salary divided by the pay range midpoint, shown as a percentage. Under 100% is below midpoint, 100% is right at it, above 100% is over. The Roster tab does this for you once salary and midpoint are entered.
Do I have to write any formulas?
No, they’re all built in. You enter employee data on the Roster, pool percentages on Dept Budget, and merit percentages on Merit Planning. The rest calculates itself. Input cells are shaded blue and formula cells are white.
Can I use it for more than one department?
Yes. Dept Budget handles as many departments as you need, each with its own pool percentage, and rolls them up to an organization total. The sample data covers five departments so you can see it working.
How many employees does it handle?
The sample has 15 rows, but you can copy the formula rows down to add as many people as you want. Once headcount gets large, or once you’re running merit, bonus, and equity in the same window, a spreadsheet starts to creak and dedicated compensation software is worth a look.
When You’ve Outgrown the Spreadsheet
A spreadsheet does fine with a steady headcount, one annual cycle, and one person who owns the file. It starts to creak when merit, bonus, and equity all run in overlapping windows across separate files, when approval routing has to be enforced instead of just tracked, or when pulling a single consolidated view eats a week of reconciling spreadsheets against HRIS exports.
CompLogix runs the whole compensation cycle in one place, with approval workflows built in, real HRIS integration, and audit trails that survive any question asked after the cycle closes. If keeping the spreadsheet straight has started to cost you more time than the planning itself, [let’s talk].