Absolute vs. Relative References in Excel

36 HBMA BILLING • MARCH.APRIL.2015

he ability to create one formula and copy it hundreds

or thousands of times is a great feature of spread-

sheets like Microsoft Excel. You can make copying those

formulas even more powerful if you understand the

difference between absolute and relative cell references

in Excel.

Relative References

Formulas like =A1+B1 are relative references in Excel. If the

formula =A1+B1 is stored in cell C1, copying the formula from cell

C1 to cell C2 (changing the row of the copied formula) will change

the formula in C2 to be =A2+B2. Copying the formula from cell

C1 to cell D1 (changing the column of the copied formula) will

result in the formula =B1+C1 being stored in cell D1. The row

and column references in the copied cells change as the desti-

nation cell changes.

Cell references like A1 and B1 are called relative cell references

because when formulas with relative references are copied, the

cell references change proportionately, in relation to the original

cell. If our =A1+B1 cell is copied from cell C1 100 rows down to

cell C101, the new formula in cell C101 will change by the same

100 rows to be =A101+B101.

Most of the time, relative references work just fine when copying

formulas, but consider the example in Figure 1. A manager is

calculating 2015 salaries based on 2014 salaries, plus a 3 percent

increase. The formula in cell C4 is =B4*(1+B1), which works just

fine. The formula in cell C4 is also shown in cell D4 for reference.

Watch what happens when the manager copies the formula from

C4 to cells C5 through C9, as shown in Figure 2. Disaster strikes!

(Unless you’re Larita, Pamela, or Jayson!)

Notice what Excel did to the copied formulas as shown in column

D of Figure 2. Since the formula was copied down several rows

and the formula was comprised of relative references, the cell

references also changed relative to the original formula. Instead

of basing each salary increase on cell B1, Margery’s salary increase

was based on $73,500 times the text “2014 Salary,” which resulted

in an error. Pamela’s salary increase was based on $120,000

times 1+$68,000 (cell B5), which is clearly wrong.

Absolute References

The solution is to let the part of the formula that references each

employee’s 2014 salary remain a relative reference, but to hold

the cell reference constant (cell B1) that refers to the percentage

increase. Holding a cell reference constant when a formula is

copied is known as an absolute reference. A relative reference

to cell B1 in Excel is entered as B1. An absolute reference to cell

B1 in Excel is stored as $B$1. The “$” symbol in front of the

column reference (B) and the row reference (1) locks the formula

to always reference cell B1, no matter where the cell is copied.

Figure 3 shows the absolute reference at work. Notice that the

formula in cell C8 is now =B8*(1+$B$1). The B8 part of the

formula is relative. In the next row down, that part of the formula

changes to B9. The $B$1 part of the formula is absolute. No

matter where the formula is copied in the spreadsheet, that part

of the formula will always reference cell $B$1.

Mixed References

Excel allows cell references to be mixed references, where the

row or column part of the reference is absolute and the other

part of the cell reference is relative. For example, $C5 locks in

column C as absolute, while row 5 is relative. If a formula refer-

encing $C5 is copied from cell D5 to cell D7, the copied formula

will reference cell C7 because column C is absolute, while row 5

is relative. Figures 4 and 5 are examples of when mixed references

are helpful. In Figure 4, the mixed reference keeps the column

absolute while leaving the row reference relative. The percentage

increase or decrease varies by specialty and is stored in column

B, so the formula in the highlighted cell, D21, is =C21*(1+$B21).

As the formula is copied throughout the table, the formula refer-

ences the increase or decrease, which is an absolute column

reference to column B.

In Figure 5, the mixed reference keeps the row absolute while

Absolute vs. Relative

References in Excel

By Nate Moore, CPA, MBA, CMPE

FIGURE 1

FIGURE 2

SOFTWARE

FIGURE 3

FIGURE 4

FIGURE 5

THE JOURNAL OF THE HEALTHCARE BILLING AND MANAGEMENT ASSOCIATION 37

leaving the column reference relative. The percentage

increase or decrease varies by year for each location and

is stored in row 30, so the formula in the highlighted cell,

D32, is =C32*(1+D$30). As this formula is copied

throughout the table, the formula references the increase

or decrease, which is an absolute reference to row 30.

The ability to fluently use relative, absolute, and mixed

references in formulas adds a tremendous amount of

flexibility to spreadsheets. Relative, absolute, and mixed

references can also be used in functions, making the

functions more powerful, as well. The ability to use one

formula correctly throughout a spreadsheet, rather than

having different versions of a formula to deal with absolute

and mixed cell references, makes a spreadsheet more

accurate and easier to maintain. The next time you can

use an absolute or mixed cell reference in a formula, try

it. You’ll be glad you did.

■

Nate Moore, CPA, MBA, FACMPE, writes custom SQL

server code to mine practice management data for

analysis in Excel, web pages, and via email. Nate’s first

book, Better Data, Better Decisions: Using Business

Intelligence in the Medical Practice, written with Mona

Reimers, was recently published by MGMA. His free Excel

videos have been viewed over one million times and are

available at mooresolutionsinc.com. Like PivotTableGuy

on Facebook or follow @PivotTableGuy on Twitter to be

notified each time an Excel video is released.