Excel Tip Tuesday: More Options with Absolute References

Posted by Lisa on November 27, 2018 at 10:00 AM

Excel IconLast week in Part 1 of this series - before you were crushed under all those leftovers - I showed you a simple Absolute Reference to just one cell. But what if you have multiple references and need to copy the formula into many cells of various rows and columns? You could - if you really, really, really, needed something to do - edit the formula in every result cell to show the correct cell reference. However, a faster option is to use those Absolute References (remember...the dollar sign $). 

In the following example, I want to calculate different discount amounts for each bike. I can create a formula in cell B3 that calculates 8% of $795, 14% of $795, 20% of 795, and so on.

Excel Tip Tuesday: More Options with Absolute References

But then I want to copy the formula to C3, D3, and on over, and I don’t want to write or edit the formula multiple times. Using Absolute References for the column and row, you can create the formula once and then copy the formula across the row and down the column.

Here in cell B3 I created the formula = $A3*B$2. This will keep column A anchored and change the row and keep row 2 anchored and change the column when you copy the formula across the row and down the columns.

Excel Tip Tuesday: More Options with Absolute References

Now copy the formula to columns C, D, and E by dragging the fill handle to the right.

Excel Tip Tuesday: More Options with Absolute References

Then double-click the fill handle to quickly copy down the formulas in columns B, C, D, and E.

Excel Tip Tuesday: More Options with Absolute References

By pressing Ctrl ~, you can see all the formulas. Notice how the Absolute Reference, or cell anchoring, changed in column B, where column A stayed static and the row changed when copied down. Notice also in row 3 where the Absolute Reference in row 2 stayed static and the column changed when copied across.

Excel Tip Tuesday: More Options with Absolute References

Next week, we'll speed this whole thing up and learn how to do Extreme Absolute References! Come back then.

Leave us a comment and let readers know what Excel tips you use on a daily basis.

Topics: Excel