How to Use Relative and Absolute Cell References in Excel Functions

Relative and absolute cell references in Excel functions may or may not be something you are familiar with. Whether you realized it or not, if you have seen a function in Excel, you were exposed to at least one of them. Excel is best used to organize data which includes creating formulas. Most people use Excel for those functions they have figured out over time, learned from others or replicated from existing documents.

This post covers the differences and importance of both relative and absolute cell references in Excel functions, as well as how you can benefit from using both. A video accompanying this post is also available below.

How to Use Relative and Absolute Cell References in Excel Functions

Excel can be a great way to organize information, track information or inventory, and track changes in information. These can be as simple as tracking exercise routines or as complicated as tracking expenditures from multiple accounts and over periods of time.

As a simple example of the possibilities, the following is a small list showing the many ways functions can help analyze data around power consumption. These include, but are not limited to:

• Tracking daily, monthly and annual average energy usage.
• Tracking daily, monthly and annual total energy usage.
• Calculating daily, monthly and annual total energy costs broken out by time of use costs and more.
• Tracking how much of daily, monthly and annual electrical usage comes from solar.
• Creating percentages of usage from solar versus traditional power sources.
• Much more!

This list, which is focused on a single topic, is a peek into how many ways you can use Excel functions to track, compare and manage data. Whatever types of data or information you are working with, there is likely a way in Excel to work with it more efficiently.

Relative cell references

Relative cell references are the more common type of cell reference used within functions. Relative cell references used in functions refer to cells, so as the function is extended to other rows and columns, the function enumerates relative to them. This is easier understood by a demonstration!

For example, if you have data in cells B1-B3, and you add an average function in cell B5, you can copy B5 to C5 and the function in C5 will apply the same average function but to cells C1-C3. This is how the relative cell reference works - it adjusts to be relative to where you copy/extend an existing function.

This example in images:

First, some basic data in cells B1 - D3. The type of data really does not matter at this point.

Then a simple average function for the first column of data.

Once a function has been created, it can be copied to another row or column instead of typing the same function again for each place you need it. To copy a function select the cell, then hover over the small box in the right corner until you get the black plus symbol. Then left-click and drag to copy. Copying a function is especially helpful in preventing mistakes with more complex functions.

NOTE: You cannot expand a function to rows and columns in a single move. If you need to do both, drag to the right to encompass additional columns, then select all the cells in that row and drag down to copy to a new row which will copy the formula for all columns at once.

Looking at the formulas auto-filled when copying, the relative cell references correctly created functions that match the columns they correspond with.

Functions created with relative references can be extended to other columns and rows and they will enumerate accordingly. In addition, you can move a function to a totally unrelated cell away from the original data, copy it, and still the functions automatically created will be relative to the original function.

Absolute cell references

An absolute cell reference is a static reference to a specific row, column or cell. These come in handy when you have information in a single cell that needs to be referenced throughout multiple functions.

To explain absolute cell references we will use another example. This time the example is someone tracking weekly weight loss compared to a starting weight that will not change, which is why the absolute cell reference makes better sense for this function.

Adding a formula to subtract the starting weight from the current week weight in the cell B7, "=B5-B2", gives us the right answer. Unfortunately, when copied, this function will fail unlike the function above. The reason this function fails is because there is no weight to subtract in cell C2, where the copied function will reference.

To fix this function we need to use an absolute cell reference for the starting weight portion of the function. To do this, add a dollar sign in front of the column and row in the function which denotes it as an absolute cell reference. So the original function becomes "=B5-$B$2".

With the function corrected to use relative cell references for the weekly weight and absolute cell references for the starting weight, it can be copied and all column functions will calculate correctly.

NOTE: If you add an absolute cell reference to a function, that cell will be part of that function no matter where you move, copy or otherwise adjust the function. The ONE exception to this rule is if you insert a row above or a column before a function with an absolute cell reference. In this case the function will automatically adjust to remain referencing the original cell.

The difference between relative and absolute cell references is that functions using relative cell references enumerate while absolute cell references stick with the specified cell when copied to additional rows and columns. Keep in mind an absolute cell reference requires the dollar sign before the column and row to work. The only exception to how an absolute cell reference operates is when you add rows above or columns before a function using an absolute cell reference. In this case, the cell reference will adjust the same way a relative function does to prevent the function from breaking.

As always, knowing the nuances of how different features work, and how to implement them, can save a great amount of time and present data in more helpful ways!