Creating pay ranges in Excel is like trying your luck with a ticking bomb: one wrong formula and everything collapses. That sinking feeling hits when you realize the market data is three months old, formulas are broken, and somehow the ranges look completely wrong.

Anyone who's dealt with compensation spreadsheets knows this struggle. The endless cycle of copying formulas, manually aging data, and hoping nothing breaks feels all too familiar. 

But that’s not the end of it. Spreadsheet errors impact real people. Consider the employee who discovered through an accidental salary leak that they were earning $5,000 while less experienced colleagues made $8,000-$9,000. 

When companies continue to use spreadsheets for pay ranges, they risk creating inequitable structures, compliance issues, and losing talented individuals who realize they're underpaid.

But with a pay range builder, things are different. A customizable salary range builder automatically updates market data, ensures consistent methodology across all roles, and creates transparent, defensible pay structures that eliminate the guesswork and human error. 

In this article, we will explore how you can create a salary range using Compport’s Pay range builder. 

Let’s get started. 

Here’s How to Create a Pay Range Builder Using Compport…

How to Create a Pay Range Using Compport in Seven Steps 

Before getting started, make sure to cross-check the following: 

• Access to the Compport platform - Your new command center for compensation management
• Access market survey data - The foundation of competitive pay ranges. Stale data creates stale ranges, so ensure yours is up to date.
• Define job architecture (roles, grades, levels) - If you don't know how jobs relate to each other, the system can't help you create consistent progressions.

Now follow the steps below: 

Step 1: Setting the stage

Navigate to Settings → Pay Range Builder and click "Create"

This is where you define the universe you're working in. Don't rush through this—getting it wrong means starting over.

Define your foundation:

• Pay Range Name: Be specific. "2025 Tech Roles - US" beats "New Ranges" when you're managing multiple structures
• Compensation Elements: Base salary, total cash, total compensation—pick what matters for this analysis
• Parameters: Country, Role, Grade—these filters ensure you're comparing apples to apples

Step 2: Feeding your data

Download the template, then populate it with your market data

Here's where most Excel approaches fall apart—data consistency. The template eliminates guesswork by standardizing the flow of information.

Your data should include:

• Survey Provider details: Mercer, Aon, Internal surveys—track sources for transparency
• Country, Role, Grade specifics: Geographic and hierarchical context matters
• Complete percentile data: P10, P25, Median, P66, P75, P90—the more data points, the better your analysis

Upload the completed file

The system validates everything on upload. No more discovering broken formulas three months later.

Step 3: Making your data current

Configure aging parameters to bring historical data forward

Market data isn't like wine—it doesn't improve with age. This step ensures your 6-month-old Mercer data reflects today's market reality.

Set your aging foundation:

• Data effective dates: When was this data actually collected?
• Aging percentages by survey provider: Mercer might age at 3%, while your internal data needs 4%
• Age up to date: Today's date for the current range

Confirm your selection:

• Target roles/grades: Don't age everything if you only need specific populations
• Compensation element: Base salary ages different from total compensation
• Differentials: Geographic or industry premiums that need special handling

Step 4: Filling the gaps with job pricing

For roles without direct market matches

Not every job has perfect market data. That's reality, not failure. This step creates defensible proxies for hard-to-match roles.

Build logical connections:

• Select similar matched jobs: A Senior Data Scientist might proxy for a Senior ML Engineer
• Choose calculation formulas: Regression, ratio analysis, or simple scaling
• Set midpoint statistical basis: How conservative or aggressive should your estimates be?

Step 5: Creating your market midpoints

Blend multiple data sources into a single, defensible midpoint

Here's where art meets science. You have Mercer data, Aon data, maybe some internal benchmarks. How do you create a single number that you can defend?

Configure your methodology:

• Select calculation formulas: Simple average, weighted average, or custom blending
• Choose statistical basis: Median is more conservative than mean when outliers exist
• Let the system blend sources: Automatic calculations eliminate human error and bias

The result? A midpoint that combines the best of your data sources with transparent methodology.

Step 6: Building your range architecture

This is where your ranges come to life

Think of midpoints as the spine of your structure. Now you're building the skeleton that determines how people progress through their careers.

Set range spreads:

• Min to Median spread %: How much room for growth within the lower half?
• Median to Max spread %: What's the ceiling for top performers?

Configure the details:

• Level-on-level progression %: The percentage increase between job levels
• Rounding settings: Round to 1, 100, or 1000—clean numbers are easier to communicate
• Apply differentials: Geographic premiums, shift differentials, or other adjustments

Review your analytics: This is where you catch problems before they become Reddit horror stories:

• Pay gap analysis: How do your ranges compare to the market?
• Population spread: Where do current employees fall within the new ranges?
• Budget projections: What will it cost to bring everyone to a minimum?

Step 7: Bringing it home

Review, finalize, and deploy your new structure

You've done the hard work. Now it's time to package everything for the real world.

Final steps:

• Review calculated ranges: Do the numbers pass the sanity test?
• Submit pay range: Lock in your decisions
• Download results: Export for presentations, HRIS uploads, or further analysis
• Save configuration: Preserve your methodology for next year's update

What you get: More than just numbers

The real magic isn't in the calculations—it's in what comes out the other end:

Complete range structure:

• Minimum, P25, Midpoint, P75, Maximum values for every role
• Clean, defensible numbers that make sense in sequence

Analytical insights:

• Pay gap analysis showing where you stand versus market
• Population distribution reveals how many people need adjustments
• Budget impact projections for planning conversations

Defensible methodology:

• Transparent calculations you can explain to auditors, executives, or employees
• Consistent approach across all roles and levels
• Documented decisions that survive leadership changes

Final Words 

Compensation managers no longer need to stare at their screens and regret life choices for choosing spreadsheets. Neither do you need to deal with the accidental salary leaks that reveal shocking pay inequities or the endless cycle of manual data updates that somehow always go wrong.

Compport’s pay range builder transforms compensation management from reactive damage control into strategic advantage. Your employees get fair, market-competitive ranges built on solid methodology. 

Your team gets defensible structures that survive audits and leadership changes. 

Ready to ditch the spreadsheet and embrace a pay range builder?

Explore Compport

FAQs 

How to set a range for salary? 

To set a salary range, start with market data for the midpoint and then apply consistent spreads. Typically 15-25% from minimum to midpoint, and 15-25% from midpoint to maximum. Consider job complexity, career progression needs, and budget constraints when determining your salary range.

How do I create a salary structure? 

To create a salary structure, define job levels and families, gather market data, establish midpoints for each role, apply consistent range spreads, and ensure logical progression between levels. Use a systematic approach rather than ad-hoc decisions to maintain equity and defensibility.

How to write the expected salary range? 

To write an expected salary range, research market rates for similar roles in your location and industry. Provide a realistic range based on your experience level, typically a 10-20% spread. Example: "Based on my experience, I'm seeking compensation in the $80,000-$95,000 range."

What is the formula for salary making?

Market data (P50 median) serves as your midpoint foundation. Apply range spreads: typically, the minimum is at 80-85% of the midpoint, and the maximum is at 115-125% of the midpoint. Factor in experience level, performance, geographic location, and internal equity. Most effective salaries land between the 25th and 75th percentile of your established range.