Last 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.
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.
Now copy the formula to columns C, D, and E by dragging the fill handle to the right.
Then double-click the fill handle to quickly copy down the formulas in columns B, C, D, and E.
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.
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.