How to Make a Salary Scale Spreadsheet

Using a spreadsheet to establish a salary scale allows you to apply general rules about how your salary structure works to the specific instances for each employee's grade and seniority, as well as compare various salaries to each other. Using referential formulas creates a cascading effect, so you only need to adjust one or two cells to affect the entire spreadsheet.

Begin by selecting the mid-level salary for an entry-level position. You can use a salary survey website to determine what the market average is for your industry so that you can ensure a competitive wage. Using a salary midpoint for each grade allows you to create a range of salaries for each grade that are all based off of one number: the Grade 1 mid-point salary.

Across the top of your spreadsheet, enter the column headers: Grade, Low Range, Midpoint, High Range and Percent. Type the number 1 into the first cell underneath Grade, then click and drag the handle in the lower-right corner of the cell down until you've filled cells for all of your grade levels. For example, if your company has 10 pay grades, enter the mid-point salary for the first pay grade in the empty cell under the "Midpoint" heading. If you want to extrapolate an hourly wage into a yearly salary you can use the equation "=(hourly wage)_40_52" (without quotation marks), where 40 is the number of hours per week and 52 is the number of weeks per year.

Determine the percentage range you want each pay grade to encompass -- for example, you may want a 10 percent range within a pay grade -- then divide your range by two and subtract the decimal value of that percentage from one. So, the decimal value of five percent is 0.05; subtracted from one, that gives you 0.95. In the first cell under the heading Low, enter the following formula: "=0.95_C2" without quotation marks. In the example, C2 is the location of the mid-point salary you defined for the first pay grade. In the first cell under the High heading, enter the following formula: "=1.05_C2" without quotation marks. This gives the high range for your first pay grade.

In the second empty cell under Percent type in the percentage increase you want from the first pay grade to the second pay grade, for example ten percent, using the decimal value. In the cell below that enter the following formula: "=E3+0.01" without quotation marks. In the example, E3 is the percentage difference between the first and second pay grades and you want to increase the difference between each subsequent pay grade by one additional percentage point. Click and drag the handle in the lower-right corner of cell E4 down until you are at the end of your list of pay grades. Select cell B2 and do the same to fill in the low-range formulas. Select cell D2 and do the same to fill in the high-range formulas. Click on cell C3 -- the midpoint salary for the second pay grade -- and type in the following formula: "=C2*(1+E3)" without quotation marks. In this example, the midpoint salary for the first pay grade is multiplied by one plus the percentage difference between the first and second pay grades. Click and drag the handle on cell C3 to fill in the midpoint salaries for all the pay grades. You can now adjust all of the salaries by simply changing the midpoint salary for the first pay grade.