Happy almost Thanksgiving!!
Do you spend a lot of time rewriting or editing formulas because the cell references change (e.g. you copy a formula, but you want B2 to remain B2 and not change to C2 or D2 to E2 to on and on, but all your new formulas are broken and displaying error messages)? Using Absolute References correctly can help you avoid this common problem and save quite a bit of time. They can help you so much that our next three posts are going to be dedicated to working with Absolute References. Don't worry...it's mainly pictures.
By default, when you build formulas, the cell references are relative. For example, the following screenshot shows the formula B2*C2:
When the formula is copied down the column, Excel automatically modifies the formula to match each row:
This is normally a really helpful - and courteous - thing for Excel to do. But what if you want to take another step and calculate a 20% discount on all the resulting values in column D from the sample above? You can use an Absolute Reference for cell F1. If you place dollar signs ($) correctly, you'll tell Excel that F1 will always be referenced when copied down the column (in other words, "Please don't start this F2, F3, F4 business." Remember to use the fill handle to copy the formula down quickly.
Notice the formula in column E does exactly what we wanted and multiplies the Extension price by the discount amount specified in cell F1.
Come back next week, when we'll do some more with absolute references.
Leave us a comment and let readers know what Excel tips you use on a daily basis.