Microsoft Excel is one of the most powerful computing tools available to a business person, regardless of the context in which they work, be it a home office, small business or trans-national corporation. Among the many tools available in Excel, absolute and relative cell references are one of the more often over-looked and misunderstood.
Your computer tip of the day is what they are and how to use them both:
A relative cell reference is generated automatically by excel whenever you attempt to insert a cell reference in a formula. They are distinctive by the combination of letters and numbers that appear in the formula and the highlight produced on the cell being referenced. Relative references have great power: as you copy and paste the formula left, right, up or down in your workbook, the formula updates to match its’ new position in the document and recalculate based on how far it moved in both directions. This in incredibly powerful for summing columns, or concatenating text strings in rows, for example.
Absolute cell references are different. You have to create them manually, by inserting a dollar sign in front of each portion of a relative cell reference. Yes, you can have partially absolute and partially relative cell references in the same place. An absolute cell reference will always find the exact cell specified, no matter where or how you copy the formula within the spreadsheet. This is an incredibly powerful tool if you need to apply things like a fixed percentage across a range of values, but that percentage needs to be the same for every value. You could simply specify that percentage at the top of the column, and then fill down the absolute reference formula in the same column.
Here are some examples:
- Relative cell reference:Â “B32”, “D17”, “G4”
- Absolute cell reference:Â “$B32”, “D$17”, “$G$4
Absolute and relative cell references are another powerful tool you can use to make Excel work easier for you. Contact us to learn more.
