Hello and welcome to A1 Excel! This post in particular is about formatting formulas in such a way that will ensure accuracy in your results. This is regarding cell references: relative, absolute, and mixed. I will go over the various references and when it is best to utilize them in formulas. It is important to be mindful of references because when you copy a formula that contains a relative/not fixed reference then the reference (the cells the formula is reading) will change. If you're not mindful of how the formula is structured from this perspective the formula can pull in an inaccurate result or error.
This chart covers a basic overview of each type of reference and their purpose:
*As a side note you can modify the reference of a cell by either manually entering in $ symbols in formulas or by selecting the F4 button on your keyboard until you reach the desired reference.
Absolute Reference: This reference basically "locks" the referred cells in place within a formula. I've used this personally for when I need a formula to lock in on a specific cell for a parameter in an Excel model or other data cleaning.
Relative Reference: This reference leaves the cell "unlocked" so when the formula is copied to a new cell/location the referred to cell changes. This can be utilized in a table of various summary points so fewer adjustments need to be made.
This example (a summary of hypothetical vet expenses for pets) is about a SUMIFS formula that is tied to a specific parameter. The purpose of using a combination of the fixed and relatives cell references is to ensure accuracy and ease of use when the formula is copied to another cell. Please see the formula bar below and the resulting summary table:
Formula utilized: SUMIFS with a mix of Absolute/Relative References
*Due to the Absolute/Relative References this formula can be copy/pasted to quickly gather accurate data on the types of pets.
Here is the formula itself as utilized if you would like to copy/paste it into an Excel file for practice: =SUMIFS($C$2:$C$9,$D$2:$D$9,B$11,$A$2:$A$9,$A12)
To summarize, these references can be used in any formula to "lock" cells and are especially useful in basic data summary table development (or for any purpose you can think of!).
I hope you enjoyed learning about references!
Comments