Why use $ in Excel formula: relative & absolute cell reference

The dollar sign ($) in cell references in Excel formulas often confuses users, but its principle is simple. The dollar sign has only one function in Excel cell references: it tells Excel whether to change the reference when copying a formula to another cell. This tutorial will explain this feature in detail.

The importance of Excel cell reference cannot be overemphasized. Understand the difference between absolute, relative, and mixed citations, and you've mastered half of the power of Excel formulas and functions.

You may have seen the dollar sign ($) in Excel formula and want to know what it is. In fact, you can refer to the same cell in four different ways, such as A1, $A$1, $A1, and A$1.

The dollar sign in an Excel cell reference affects only one thing - it indicates how Excel handles references when moving or copying a formula to another cell. In short, using the $ symbol before row and column coordinates creates an absolute cell reference that does not change. Without using the $ symbol, the reference is relative and it will change.

If you are writing a formula for a single cell, you can use any reference type and the formula is correct anyway. However, if you plan to copy the formula to another cell, it is crucial to choose the right cell reference type. If you feel lucky, you can try to tossing a coin :) If you want to take it seriously, take a few minutes to learn the ins and outs of absolute and relative cell references in Excel and when to use which one.

- What is Excel cell reference

• Relative cell references
• Absolute cell reference
• Use relative and absolute cell references in one formula
• Mixed cell references in Excel
• Whole column and whole row references
• Switch between different reference types (F4 key)

What is an Excel cell reference?

Simply put, the cell reference in Excel is the cell address. It tells Microsoft Excel where to find the value to use in the formula.

For example, if you enter a simple formula =A1 in cell C1, Excel extracts the value from cell A1 to C1:

As mentioned earlier, as long as you write a formula for a single cell , you are free to use any reference type, regardless of whether you use the dollar sign ($) and the result is the same:

However, if you want to move or copy formulas in a worksheet, choosing the correct reference type is essential for the formula to be copied to other cells correctly. The following sections explain each cell reference type in detail and provide examples of formulas.

Notice. In addition to the A1 reference style (where the column is defined by letters and the row is defined by numbers), there is also an R1C1 reference style , where both rows and columns are identified by numbers (R1C1 represents row 1, column 1).

Since A1 is the default reference style in Excel and is used most of the time, this tutorial only discusses A1 type references. If someone currently uses R1C1 style, you can turn it off by clicking File > Options > Formulas and unchecking the R1C1 Reference Style check box.

Excel relative cell reference (no $ symbol)

A relative reference in Excel is a cell address without the $ symbol in row and column coordinates, such as A1 .

When a formula containing a reference to a relative cell is copied to another cell, the reference changes according to the relative position of the row and column. By default, all references in Excel are relative. The following example shows how relative references work.

Suppose you have the following formula in cell B1:

=A1*10

If you copy this formula to another row in the same column, such as cell B2, the formula will be adjusted to row 2 (A2*10), because Excel assumes that you want to multiply the value of each row in column A by 10.

If you copy a formula containing a relative cell reference to another column in the same row, Excel changes the column reference accordingly:

If you copy or move an Excel formula containing a relative cell reference to another row and another column , both the column and row references are changed:

As you can see, using relative cell references in Excel formulas is a very convenient way to perform the same calculations throughout the worksheet. To illustrate this better, let's discuss a real example.

Using Relative References in Excel - Formula Example

Suppose you have a column of USD prices (column B) in your worksheet that you want to convert to Euro. The dollar-euro exchange rate is known (0.93 at the time of writing), and the formula in line 2 is simple, i.e. =B2*0.93 . Note that we are using Excel relative cell references without dollar signs.

Press Enter to calculate the formula and the results will be displayed in the cell immediately.

hint. By default, all cell references in Excel are relative references. So, when writing a formula, you can add relative references by clicking on the corresponding cell on the worksheet instead of manually typing the cell reference.

To copy the formula down , hover over the fill handle (the small square in the lower right corner of the selected cell). When you do this, the cursor changes to a thin black cross that you can hold and drag onto the cell you want to autofill.

That's it! The formula has been copied to other cells and its relative reference has been adjusted correctly for each cell. To ensure that the values ​​in each cell are calculated correctly, select any cell and view the formula in the formula bar. In this example, I select cell C4 and see that the cell reference in the formula is relative to row 4, which is exactly what it should look like:

Excel absolute cell reference (with $ sign)

An absolute reference in Excel is a cell address with a dollar sign ($) in row or column coordinates, such as $A$1 .

The dollar sign pins the reference to the given cell so that it remains the same no matter where the formula moves. In other words, using $ in cell references allows you to copy Excel formulas without changing the reference.

For example, if there are 10 in cell A1 and you use an absolute cell reference ( $A$1 ), the formula =$A$1 5 will always return 15 regardless of which other cells the formula is copied to. On the other hand, if you write the same formula using relative cell reference ( A1 ) and then copy it down to other cells in the column, a different value will be calculated for each row. The following figure demonstrates the difference:

Notice. Although we've been saying that absolute references in Excel never change, in reality, when you add or delete rows and/or columns in your worksheet, it does change, which changes the position of the referenced cell. In the above example, if we insert a new row at the top of the worksheet, Excel is smart enough to adjust the formula to reflect the change:

In actual worksheets, it is very rare to use only absolute references in Excel formulas. However, there are many tasks that require both absolute and relative references, as shown in the following example.

Notice. Absolute cell references should not be confused with absolute values, which are not considered in the size of the number of its symbol.

Use relative and absolute cell references in one formula

You may often need a formula where some cell references are adjusted to the columns and rows the formula is copied to, while others remain fixed on a specific cell. In other words, you have to use relative and absolute cell references in one formula.

Example 1. Relative and absolute cell references used to calculate numbers

In our previous U.S. dollar and euro price examples, you probably don't want to hardcode the exchange rate in the formula. Instead, you can enter that number into a cell, such as C1, and use the dollar sign ($) to fix that cell reference in the formula, as shown in the screenshot below:

In this formula (B4*$C$1), there are two cell reference types:

• B4 - Relative cell references, which will be adjusted for each row, and
• $C$1 - Absolute cell reference, which will not change wherever the formula is copied.

The advantage of this approach is that your users can calculate the euro price based on variable exchange rates without changing the formula. Once the conversion rate changes, you just need to update the value in cell C1.

Example 2. Relative and absolute cell references used to calculate dates

Another common use of using absolute and relative cell references in a formula is to calculate dates in Excel based on today's date.

Suppose you have a column of delivery dates in column B and you enter the current date in C1 using the TODAY() function. If you want to know the shipping time for each item, you can calculate it using the following formula: =B4-$C$1

Similarly, we use two reference types in the formula:

• Relative references are used for cells containing the first delivery date (B4), because you want this cell reference to vary depending on the row in which the formula is located.
• Absolute reference is used for cells containing today's date ($C$1), because you want this cell reference to stay the same.

In summary, whenever you want to create an Excel static cell reference that always references the same cell, make sure to include the dollar sign ($) in the formula to create an absolute reference in Excel.

Excel Mixed Cell References

A mixed cell reference in Excel refers to a reference with fixed column letters or row numbers. For example, $A1 and A$1 are mixed references. But what does each mean? This is very simple.

You remember that Excel absolute references contain 2 dollar signs ($) that lock columns and rows. In a mixed cell reference, only one coordinate is fixed (absolute) and the other (relative) will vary according to the relative position of the row or column:

• Absolute columns and relative rows , such as $A1. When a formula containing this reference type is copied to another cell, the $ symbol before the column letter locks the reference to the specified column so that it will never change. Relative row references (no dollar sign) will vary according to the rows copied to the formula.
• Relative columns and absolute rows , such as A$1. In this reference type, the row reference does not change and the column reference does not change.

Below you will find examples using these two mixed cell reference types, hoping this will make things easier to understand.

Using mixed references in Excel - Formula Example

In this example, we will use our currency conversion table again. But this time, we won't be limited to the USD-Euro conversion. What we are going to do is convert the U.S. dollar price to multiple other currencies, all using a single formula !

First, let's enter the conversion rate in a certain line, for example, line 2, as shown in the screenshot below. You can then write a formula for the upper left cell (C5 in this example) to calculate the euro price:

=$B5*C$2

where $B5 is the US dollar price in the same row and C$2 is the US dollar-Euro conversion rate.

Now copy the formula down to other cells in column C and automatically fill the other columns with the same formula by dragging the fill handle. As a result, you will have 3 different price columns that are correctly calculated based on the corresponding exchange rate in row 2 in the same column. To verify this, select any cell in the table and view the formula in the formula bar.

For example, let's select cell D7 (in the pound column). What we see here is the formula =$B7*D$2 , which takes the U.S. dollar price in B7 and multiplies it by the value in D2 , i.e. dollar-GBP conversion rate, which is exactly what we want :)

Now let's see how Excel knows exactly which price to take and which exchange rate to multiply it by. As you may have guessed, this is where the mixed cell reference works ($B5*C$2).

• $B5 - Absolute columns and relative rows . Here you only add the dollar sign ($) before the column letters to pin the reference to column A, so Excel always uses the original dollar price for all converted. The row reference (no $ sign) is not locked because you want to calculate the price for each row separately.
• C$2 - Relative columns and absolute rows . Because all exchange rates are on line 2, you lock the line reference by adding a dollar sign ($) before the line number. Now, no matter which line you copy the formula to, Excel always looks for the exchange rate in line 2. And since the column reference is relative (no $ sign), it will adjust for the column to which the formula is copied.

How to reference an entire column or a whole row in Excel

When you use an Excel worksheet with variable number of rows, you may want to reference all cells in a specific column. To reference an entire column, just type the column letter twice and add a colon between the two, such as A:A .

Whole column reference

Like cell references, whole column references can be absolute and relative, for example:

• Absolute column references , such as $A:$A
• Relative column references , such as A:A

Again, you lock it to a specific column using the dollar sign ($) in your absolute column reference so that the entire column reference does not change when you copy the formula to another cell.

When you copy or move a formula to another column, the relative column reference changes, and when you copy the formula to another cell in the same column, it remains the same.

Whole line reference

To reference the entire row, you can use the same method, except that you type the row number instead of the column letters:

• Absolute line reference , such as $1:$1
• Relative line references , for example 1:1

In theory, you can also create mixed whole column references or mixed whole row references , such as $A:A or $1:1. I say "theory" because I can't think of any practical application of such citation, although Example 4 proves that formulas with such citation work exactly as expected.

Example 1. Excel whole column reference (absolute and relative)

Suppose you have some numbers in column B and you want to find out their sum and average. The problem is that new rows are added to the table every week, so writing the usual SUM() or AVERAGE() formula for fixed-range cells is not a viable way. Instead, you can reference the entire column B:

=SUM($B:$B) - Use the dollar sign ($) to create an absolute whole column reference , locking the formula to column B.

=SUM(B:B) - Write a formula without the $ symbol to create a relative whole column reference , which will change when you copy the formula to another column.

hint. When writing a formula, click the column letter to add the entire column reference to the formula. Like cell references, Excel inserts relative references by default (no $ symbol):

Similarly, we write a formula to calculate the average price of the entire column B:

=AVERAGE(B:B)

In this example, we are using a relative whole column reference, so when we copy it to other columns, our formula gets the correct adjustment:

Notice. When using whole column references in Excel formulas, never enter formulas anywhere in the same column. For example, it seems that entering formula =SUM(B:B) in an empty cell Enter formula =SUM(B:B) in the bottommost cell of column B to display the sum at the end of the same column. Don't do this! This will create a so-called circular reference and the formula will return 0.

Example 2. Excel whole line reference (absolute and relative)

If the data in an Excel table is organized by rows rather than columns, you can refer to the entire row in the formula. For example, this is how we calculate the average price for line 2:

=AVERAGE($2:$2) - Absolute whole line reference is locked to a specific line by using the dollar sign ($).

=AVERAGE(2:2) - When copying the formula to another row, the reference relative to the entire row will change.

In this example, we need a reference relative to the entire row, because we have 3 rows of data, and we want to calculate the average value of each row by copying the same formula:

Example 3. How to reference the entire column (excluding the first few rows)

This is a very hot question because the first few lines in a worksheet usually contain some introductory terms or explanatory information that you don't want to include in your calculations. Unfortunately, Excel does not allow references like B5:B, it will contain all rows in column B starting from row 5. If you try to add such a reference, your formula will most likely return a #NAME error.

Instead, you can specify the maximum row so that your reference contains all possible rows in the given column. In Excel 2016, 2013, 2010, and 2007, the maximum values ​​are 1,048,576 rows and 16,384 columns. Earlier versions of Excel had a row maximum of 65,536 and a column maximum of 256.

Therefore, to find the average value of each price column in the following table (columns B to D), enter the following formula in cell F2 and copy it to cells G2 and H2:

=AVERAGE(B5:B1048576)

If you use the SUM function, you can also subtract the rows you want to exclude:

=SUM(B:B)-SUM(B1:B4)

Example 4. Using mixed whole column references in Excel

As I mentioned in the previous paragraphs, you can also create mixed whole columns or whole row references in Excel:

• Mixed column references, such as $A:A
• Mixed line references, such as $1:1

Now let's see what happens when you copy the formula containing such references to other cells. Suppose you enter the formula =SUM($B:B) in a certain cell, F2 in this example. When you copy the formula to the adjacent right-hand cell (G2), it changes to =SUM($B:C) because the first B is fixed with the $ symbol, while the second does not. As a result, the formula sums all numbers in columns B and C. Not sure if this has any real value, but you might be wondering how it works:

warn! Don't use too many whole column/full row references in a worksheet, as they may slow down Excel.

How to switch absolute, relative and mixed references (F4 key)

When writing Excel formulas, of course, you can manually type the $ symbol to change the relative cell reference to an absolute or mixed reference. Alternatively, you can press F4 to speed up. To use the F4 shortcut keys, you must be in formula editing mode:

1. Select the cell that contains the formula.
2. Press F2 or double-click the cell to enter editing mode.
3. Select the cell reference to change.
4. Press F4 to toggle between the four cell reference types.

If you select a relative cell reference without the $ sign, such as A1, repeatedly pressing the F4 key to switch between an absolute reference containing two dollar signs (such as $A$1), absolute row A$1, absolute column $A1, and then return to the relative reference A1.

Notice. If you press F4 without selecting any cell references, the reference to the left of the cursor will be automatically selected and changed to another reference type.

I hope you now fully understand what relative and absolute cell references are, and that Excel formulas with the $ sign are no longer a mystery. In the next few articles, we will continue to learn about various aspects of Excel cell references, such as referencing another worksheet, 3D references, structured references, circular references, and more. Thank you for reading during this period and hope to see you on our blog next week!

The above is the detailed content of Why use $ in Excel formula: relative & absolute cell reference. For more information, please follow other related articles on the PHP Chinese website!