From my experience teaching excel, one of the main things people forget about when writing formulas and functions are their absolute and relative cell references. The funny thing is lots of people are familiar with the concept and how to properly reference cells, but fail to implement the proper protocols when using excel! This drives me CRAZY!!
If you ever used a function that worked properly at first and then broke when you dragged it down or across, then this is the section for you! If you have no idea what I’ve been talking about, then this is DEFINTELY the section for you!!
…ok, enough ranting…
How it works
The types of cell references you use in excel are important because they offer an added level of flexibility when you want to automate reports (more on that later). When cell references are used incorrectly (or not at all) you’ll soon find yourself swimming in an ocean of formula errors.
|
Relative Cell Reference |
Absolute Cell Reference |
|
=D4 |
=$D$4 |
When you use a relative reference, the cell reference shifts along with location of your formula. In others words, if you drag your formula to another cell, the cell reference will shift relative to the formula you’re dragging.
The opposite happens when you use an absolute reference; no matter where you drag or copy your formula to your cell reference will remain in place.
All of this may seem simple, and it is. Just because its simple though doesn’t mean you should treat it as an afterthought. Many people, myself included, start writing formulas only to realize later that their formulas are pointed to the wrong cells because they dragged (or copied) the formulas over to a new location and used the wrong cell reference.
There is a third reference type; mixed reference:
To switch from absolute to relative reference, you can press F4 on your keyboard as you’re entering your formula or function. If you’ve already written your formula no worries; just click on the cell reference you want to change (in the formula bar) and press F4.
Lets get Practical
In the example below, we are analyzing the sales tax applied to breakfast items in different counties. Here, we entered the correct formula in cell E3, but we forgot to change the cell reference types to absolute. Here is what happens when we drag the formula down:
Not fun (unless you are planning on selling ham for over $86 million, in which case you have nothing to worry about).
Lets see what happens when we turn reference to cell E2 into an absolute reference.
Since we made reference to E2 absolute, the formula continues to reference this cell even when we drag the formula down. Now, lets drag it across and finish the analysis
Something is definitely wrong! Not only are we still using 6% tax (instead of 7%) but we’re also multiplying it by the wrong amount ($1.50, E11).
This happened because we needed a mixed reference instead of an absolute one. For the tax part of the calculation, we need the reference to shift horizontally along the row but not vertically along the column. We accomplish this by writing the dollar symbol before the row reference (ie. A$1)
For the price we need the opposite, the formula should shift along shift along the column vertically but not horizontally. We need to enter the dollar symbol next to the column reference so we can lock it in place (ie. $A1). Here’s a summary:
Lets apply this to our report and see what happens.
It works! Now we can drag the formula across to the next column.
Using the right cell reference is key when building out reports of any kind. It took me a while to get used to the different reference types. When I finally felt like I knew it like the back of my hand it made me more efficient and quick at building reports and dashboards. Staring at this book won’t help though; go out and start practicing in excel!