It’s Absolute(ly) Relative

If I had a dollar for every time someone asked me “what the heck are those dollar signs for?,” I’d be a millionaire.  Well, anyone worth their weight in gold as an Excel user should really understand what those pesky dollar signs are used for.

Most of us know how to use relative references

It’s really pretty simple.  The inclusion of dollar signs ($) in a formula changes at least part of the cell reference from relative to absolute.  Let me explain.  You see, Microsoft Excel is a very, very powerful tool. So powerful, in fact, that more times than not, it tries to help you get your work done faster and more effectively, even attempting to save you, the user, from yourself.  Like auto-numbering and spell-check in Microsoft Word, this type of “help” is what we’ve come to expect with Microsoft products.  So when you copy a formula for use somewhere else in your worksheet, which I’m sure you’ve done, Excel assumes that you want to keep the same relationship.

For example, say if you have three columns of data that represent daily transactions for your store.  Column A represents quantity sold, Column B lists the price per item, and Column C is the product of the two (A*B).  Because of this intuitiveness built into Excel, you need only enter “=A2*B2” into column C (assuming the first row is your column headings).  You can then copy and paste or fill-down the formula. And, as I’m sure you know, Excel will continue with the relative positions (A3*B3, A4*B4, etc.). Read more of this post