Excel Guides

Understanding Relative and Absolute Addressing in Excel

When working with large amounts of data in Microsoft Excel, it is often necessary to use absolute and relative cell references to ensure that formulas and functions are calculating correctly. This can be a confusing concept for those who are new to Excel, but with a little practice it is easy to understand how relative and absolute cell references work.

An absolute cell reference is a cell address that does not change when the formula or function is copied or moved to another cell. Absolute cell references always begin with a dollar sign ($). For example, if you have a formula in cell A1 that uses the value in cell B1, you would use an absolute reference for B1 so that the formula always uses the value in B1, even if it is copied to another cell.

A relative cell reference is a cell address that changes when the formula or function is copied or moved to another cell. Relative references do not use the dollar sign ($) like absolute references do. For example, if you have a formula in cell A1 that uses the value in cell B1, you would use a relative reference for B1 so that the formula would use the value in the cell next to it if it were copied to another cell.

You can also use mixed references, which are a combination of absolute and relative references. Mixed references use both the dollar sign ($) and letters (A-Z) to indicate which parts of the reference should stay the same and which should change. For example, if you want to lock column A but allow row 1 to change, you would use $A1. If you want to lock row 1 but allow column A to change, you would use A$1.

Now that you know the basics of how absolute and relativecell referencing works in Excel, let's look at some examples of when you might need to use each type.

Example 1:

You have a list of data in columns A through E and you want to find the average of each row. You could use the AVERAGE function and enter =AVERAGE(A1:E1) into cell F1. Then, you could copy this formula down column F until all rows have been averaged.

However, if you simply copy this formula down column F, all cells will still reference row 1 (e.g., F2 will show #DIV/0 because there are no values in A2:E2). This is where using relative references comes in handy.

If we modify our original formula slightly by changing it to =AVERAGE(A1:E$1), we can tell Excel that we want column E to remain static (absolute reference), while allowing row 1to change (relative reference). Now when we copy this modified formula down column F, each row will be averaged correctly since column E will adjust accordingly.

Example 2:

You have a list of data in columns A through D and want find out how many times each unique value occurs in column C. To do this, we can use the COUNTIF function.

=COUNTIF(C:C,"red")   //will count how many cells contain "red"  
=COUNTIF(C:C,"blue")  //will count how many cells contain "blue"  
=COUNTIF(C:C,"green") //will count how many cells contain "green"  

Note:, since we used relative references (no $ signs), as we copy these formulas down columns E through G, Excel will automatically update Column C each time.

Example 3:


Suppose we have sales data for 3 products ("Product A", "Product B", "Product C") across 5 different regions ("North", "South", "East", "West", "Central"). We want calculate total sales for each product by region.

| | North | South | East | West | Central | Total | |------|-------|-------|------|------|---------|-------| | Prod | | | | | | | | A | 100 | 200 | 300 | 400 | 500 | | | B | 110 | 220 || 310 || 420 || 510 || || || C || 130 || 230 || 330 || 430 || 530 || ||

In order calculate totals by region for each product, we can use SUMIF.
=SUMIF(B$2:B$6,"North",C$2:C$6)    //will sum values in range C2:C6 where corresponding cells in range B2:B6 equal "North"    =SUMIF(B$2:B$6,"South",C$2:C$6)    //will sum values in range C2:C6 where corresponding cells in range B2:B6 equal "South"    =SUMIF(B$2:B$6,"East",C$2:C$6)     //will sum values in range C2:C6 where corresponding cells in range B2:B6 equal "East"     =SUMIF(B$2::B$6,"West",C$2::C$6)     //will sum values in range C2::C6 where corresponding cells in range B2::B6 equal "West"     =SUMIF(B$2::B$6,"Central",C$2::C$6) //will sum valuesin range C2::C6 where corresponding cellsin range B2::B6 equal "Central" 

We can then copy these formulas down columns H through L.
However, there is one problem with this approach.
Notice how our ranges are defined using an absolute reference for the first row ($2) but a relative reference forthe last row (no $ sign before 6). This means that as wecopy these formulas downcolumns H through L, onlythe last row number willchange.

So while our formulas currentlysum valuesfor regionsin rows 2 through 6,...after copying themdown columns H through L,...they will insteadsum valuesfor regionsin rows 2 through 12! Thisis probably not whatwe want.

To fix this problem,...we needto make surethat boththe firstandlastrow numbersuseabsolutereferences.

We can do thisby simplyaddinga $ signbeforethe 6in eachformula,...like so:

=SUMIF(B$2::B$6,"North",C$2::C$6)    //now sums valuesin rows 2through 6for region"North"    =SUMIF(B

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.