Complete guide to Excel 2016 (Part 15): Relative and absolute reference cells

Relative references change when a formula is copied to another cell. However, absolute references remain the same no matter where they are copied. Join TipsMake.com to refer to the article about the relative and absolute reference boxes in Excel 2016 !

Relative and absolute reference cells in Excel:

1. I. Relative references (Relative references)
   1. Create and copy a formula using relative references
2. II. Absolute reference (Absolute references)
   1. Create and copy a formula with absolute reference
3. III. Use cell references with multiple sheets
   1. Reference cells on sheets:

Watch the video below to learn more about cell references:

I. Relative references (Relative references)

By default, all cell references are relative references. When copied over multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula = A1 + B1 from row 1 to row 2, the formula becomes = A2 + B2 . Reference documents are relatively convenient when you need to repeat the same calculation on multiple rows or columns.

Create and copy a formula using relative references

In the example below, we want to create a formula that will multiply the price of each item by quantity. Instead of creating a new formula for each item, we can create a single formula in cell D2 and then copy it to other rows. We will use relative references to calculate the sum of each item correctly.

1. Select the cell containing the formula. In the example, we will select cell D4 .

2. Enter the formula to calculate the desired value. In the example, we will type = B4 * C4 .

3. Press Enter on the keyboard. The formula will be calculated and the result will show in the box.

4. Locate the fill handle at the bottom right corner of the desired cell. In the example, we will locate the fill handle for cell D4.

5. Click and drag the fill handle into the cells you want to fill. In the example, we will select cells D5: D13 .

6. Release the mouse. The formula will be copied into the selected cells with relative references, displaying the results in each cell.

1. You can double-click the filled box to check their exact formula. Relative references for each cell should vary, depending on the row.

II. Absolute reference (Absolute references)

Sometimes you may not want to change a cell reference when copying to other cells. Unlike relative references, absolute references do not change when copying or filling. You can use an absolute reference to keep rows and / or columns in a row.

An absolute reference is specified in a formula by adding a dollar sign ($) . It can precede column references, line references or both.

1. You will usually use the $ A $ 2 format when creating formulas containing absolute references. Two other formats are used less frequently.

When writing a formula, you can press the F4 key on the keyboard to switch between relative cell references and absolute cell references, as shown in the video below. This is an easy way to quickly insert an absolute reference.

 

Create and copy a formula with absolute reference

In the example below, we will use cell E2 (with a 7.5% tax rate) to calculate the sales tax for each item in column D. To ensure a reference to a constant tax rate - even when public How to copy and fill in other fields - we need to make the $ E $ 2 cell an absolute reference.

1. Select the cell containing the formula. In the example, we will select cell D4 .

2. Enter the formula to calculate the desired value. In the example, we will enter = (B4 * C4) * $ E $ 2 , making $ E $ 2 an absolute reference.

3. Press Enter on the keyboard. The formula will calculate and the result will show in the box.

4. Locate the fill handle at the bottom right corner of the desired cell. In the example, we will locate the fill handle for cell D4.

5. Click and drag the fill handle into the cells you want to fill in (cell D5: D13 in the example).

6. Release the mouse. The formula will be copied into the selected cells with the absolute reference and the values ​​will be calculated in each cell.

1. You can double-click the filled cell to correctly check their formula. The absolute reference in each cell should be the same, while other references are related to the row of the cell.

1. Be sure to include the dollar sign ($) whenever you make an absolute reference on multiple cells. The dollar signs have been omitted in the example below. This causes Excel to interpret it as a relative reference, resulting in incorrect results when copied to other cells.

III. Use cell references with multiple sheets

Excel allows you to reference any cell on the spreadsheet, which can be particularly useful if you want to reference specific values ​​from another spreadsheet. To do this, simply start the cell reference with the name of the spreadsheet followed by an exclamation point (!) . For example, if you want to reference cell A1 on Sheet1 , its cell reference will be Sheet1! A1 .

1. Note that if a spreadsheet name contains spaces , you'll need to include parentheses ('') around the name. For example, if you want to refer to cell A1 on a worksheet called July Budget , its cell reference will be 'July Budget'! A1 .

Reference cells on sheets:

In the example below, we will refer to a cell with a value calculated between two tables. This will allow us to use the exact value on two different spreadsheets without rewriting the formula or copying the data.

1. Locate the cell you want to reference and note its spreadsheet. In the example, we want to reference cell E14 on the Menu Order worksheet.

2. Navigate to the desired spreadsheet. In the example, we will choose the Catering billing worksheet.

3. Locate and select the cell that you want the value to appear. In the example, we will select cell C4 .

4. Type the equal sign (=) , the table name after the exclamation point (!) And the cell address. In the example, we will type = 'MenuOrder'! E14 .

5. Press Enter on the keyboard. The value of the referenced cell will appear. Now, if the value of cell E14 changes on the Order Menu worksheet, it will be updated automatically on the Catering billing worksheet.

1. If you rename your worksheet at the following point, the cell reference will be updated automatically to reflect the new worksheet name.

If you enter the table name incorrectly, the #REF error ! will appear in the box. In the example, we confused the name of the spreadsheet. To edit, skip or investigate errors, click the Error button next to the box and select an option from the menu.

Having fun!