How to use absolute reference in Excel

Excel spreadsheets are a vital tool for managing projects. Excel makes it easy and convenient for teams to calculate budgets, monitor cash flow, track online marketing performance, and compile and share client information.

When you move past basic knowledge of Excel, your spreadsheets will come together much faster. Budget planning and project reviews will no longer be all-day tasks. Understanding cell references, and the difference between relative and absolute cell references, is the foundation of advanced Excel spreadsheet management.

What are cell references?

In an Excel spreadsheet, a cell reference is a cell or range of cells that can be used in a formula. Cell references refer to data from two or more contiguous cells on the worksheet, data contained in different areas of a worksheet, or data on other worksheets in the same workbook.

When relative cell references are copied to more than one row or column, they change depending on their position. This is useful when you’re performing the same calculation in each row or column.

However, if you need your cell reference to stay the same when it’s moved, copied, or filled, you’ll want to use an absolute cell reference. By doing this, the reference will point back to the same cell, regardless of its appearance in the workbook. A dollar sign in the column or row indicates that a cell reference is absolute.

Rather than having to dig through Excel searching for the menu item, you can instead use a shortcut to apply an absolute cell reference. On a PC, press F4 after typing in the reference to switch from relative to absolute. As you hold F4, Excel will cycle through the different forms of reference so you can pick the right one. On a Mac, the shortcut for absolute reference is to hold the Command and T keys.

When to use absolute cell references

Regardless of your industry or field, there’s a chance you’ll end up relying on absolute cell references in Excel. This LifeWire article recommends using absolute cell references for more efficient calculations and reporting when

• Using a fixed price per unit
• Calculating sales tax on invoices for multiple items
• Applying a single percentage for each year when projecting annual profit targets
• Referring to fixed-availability rates for individual resources in project management
• Using relative column references and absolute row references to match column calculations in your referenced cells to column values in another table

What does that “$” mean in Excel?

The dollar sign in an Excel reference informs the program to change or not change the reference when the formula is copied to other cells, says Absolute Bits. You can see the reference used for the same cell in four different ways, for example: A1, $A$1, $A1 and A$1. The reference without the dollar sign is relative and will change.

Use an absolute cell reference when you’re informing Microsoft Excel where to look for the value you want to remain unchanged, no matter where the formula moves. The dollar sign will fix the reference to a given cell, which allows you to copy the formula in Excel without changing references. For instance, in $A$1, the first dollar sign will lock the column, and the second dollar sign will lock the row.

How to use absolute reference in Excel

1. Click the cell where you want to enter a formula.
2. Type an equal sign (=) to start the formula.
3. Select a cell, and then type an arithmetic operator (=, −, /,*).
4. Select another cell, and then press the F4 key to make the cell reference absolute. If you continue to press F4, Excel will cycle through the different reference types. Note the placement of the dollar sign. (If you’re using a Mac, press the Command and T keys instead of F4.)
5. Click the Enter button on the formula bar, or press Enter on your keyboard.

The more you can tackle in Excel, the better. Adding absolute reference to your repertoire will prepare you to work with invoices, deal with fixed multipliers, and more.