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

Subscribe to B/P Impressions

Impressive Impressions

a blog that is also a resource for businesses

Bennett/Porter's Impressions blog provides sensible insight into the places where technology and business intersect. Yet beyond practical reflections, you'll find actual resources to help keep your business operations optimized. These resources include:

  • critical announcements about the ERP systems we support
  • efficient strategies for network management
  • tips and tricks on using business software
So read on, and get the inside track from the Pacific Northwest's premier experts in ERP software and Managed IT & Cloud Hosting services.

Recent Posts