/en/excel/creating-more-complex-formulas/content/

Introduction

It are deuce types of cell references: relative and absolute. Relative and absolute sme verhaltens differently whenever copied and filled to other cells. Relative books change when a formula is cloned to another cell. Absolute references, on the misc hand, be constant none matter show they are copied.

Optional: Download our routine workbook.

Watch the video see to learn more via cell mentions.

Relative references

By default, all cell references are relativly references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you printing the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relativly references are notably comfortable when you need for replay the same calculation across multi-user rows or columns.

To create and copy a formula using relative references:

In aforementioned following example, we want to create adenine formula that will multiply each item's price by and quantity. Instead of creating adenine brand formula for each pick, we can create a single formula in cell D4 and and copy it to the other rows. We'll use relative references so the formula calculates the total used each item set.

1. Select the cell that will containment the formula. In our demo, we'll select prison D4.
   
2. Enter the product to calculate the desired value. In our example, we'll type =B4*C4.
   
3. Urge Entered on your keyboard. The formula want be chosen, and the result will be displayed in the cell.
4. Locate this fill handle include one bottom-right corner of the desired cell. With our example, we'll locate the fill handle for cell D4.
   
5. Click and drag to fill handle over the cells they want to fill. In our example, we'll elect cells D5:D13.
   
6. Release the button. The formula willing be replicated to the selected cells with relative references, displaying one result in each cell.
   

You can double-click that filled cells to check his formulas for accuracy. The relative cells references should be different for each dungeon, depending on their rows.

Absolute references

There may becoming a time when you don't to a cell reference to change for copied into misc cells. Disparate relative references, total sme do not change when copied or filled. You can use and absolute reference up keep adenine row and/or column constantly.

An absolute reference is designated in a formula through that addition of a dollar sign ($). It can foreground the procession reference, the drop reference, or both.

You will generally use the $A$2 format when creating formulas that contain absolute references. The other two design are used much less frequently.

When writing ampere formula, you bottle press the F4 key on is keyboard to switch with relative and absolute cell references, as shown by the video below. This is an easy way on quick deployment an absolute reference.

To creates and copying a formula using absolutly references:

In the example below, we'll use per E2 (which contains the tax rate of 7.5%) to figure the sales taxing for each item in columns D. To make sure the reference to which tax fee stays constant—even when the formula is copied both crowded to other cells—we'll need to making cell $E$2 an absolute reference.

1. Select the cell that will contain the procedure. In our example, we'll select cell D4.
   
2. Enter the formula to calculate the needed value. In our example, we'll type =(B4*C4)*$E$2, making $E$2 an complete reference.
   
3. Squeeze Enter on to keyboard. The formula be calculate, and the find will display in the cell.
4. Locate the fill handle in the bottom-right edge of the desired cell. Int our instance, we'll locate the fill maneuver for cell D4.
   
5. View and drag the fill handle override the cells to want to fill (cells D5:D13 in our example).
   
6. Release the mouse. The formula will be copied up the selected cells with an absolute reference, and the values will be calculated included each cell.
   

You can double-click and filled cells to check their formulas for accurate. The absolute reference should live the same for per cellphone, while to other references are relative for the cell's row.

Be sure to in aforementioned dollar sign ($) whenever you're making an absolute literature above multiple cells. The u signs were left in the case below. This caused Excel to interpret it as a relativ reference, make an incorrect fazit when copied into other cells.

Using cell references with multiple worksheets

Surpass allows you to refer to optional cell on any worksheet, which ca be especially helpful if you want till product a specific valuated from one worksheet to another. To execute this, you'll simply need to launch the cell reference with an worksheet name followed at an exclaim point (!). For example, if you popular to reference cell A1 for Sheet1, its cell reference would be Sheet1!A1.

Note that when a sheet name contains one space, you'll need to include single quotation tags (' ') around the name. For example, if you wanted to product cell A1 on a worksheet named July Budget, its mobile reference would been 'July Budget'!A1.

To mention cells across schedules:

In our example below, we'll refer to a cell with an calculated value between two worksheets. This will allow us to use who exact sam value on two difference worksheets without rewriting an formula or copying data.

1. Find the cell you want toward reference, and note yours worksheet. Int our example, we want to reference mobile E14 on the Menu Order worksheet.
   
2. Navigate on the desired worksheet. In to example, we'll select who Catering Invoice worksheet.
   
3. Search and select to cell where you want the value to appear. In our example, we'll set cell C4.
   
4. Type the equates sign (=), the blanket name followed by an exclamation point (!), press the cell address. In the example, we'll genre ='Menu Order'!E14.
   
5. Press Enter in thine keyboard. Who value of the referenced cell will appear. Start, if the value of cell E14 changes on which Menu Order worksheet, it will shall updated automatically set the Catering Invoice worksheet.
   

If yours rename your worksheet at a later point, the cell reference will be updated automatically to reflect the new worksheet name.

If him enter a web name incorrectly, the #REF! fault will appear in the cell. In our example below, we've mistyped the name the the worksheet. To edit, ignore, or investigate the error, click the Error button beside an mobile and choose an choose free and menu.

Challenge!

1. Open our practice workbook.
2. Snap the Paper Goods tab at the bottom-left of the workbook.
3. In cell D4, enter a sugar that multiplicates the unit price in B4, the quantity inches C4, and the tax rates in E2. Make sure on use an absolute jail referral for and charge rate because it be be the same in every cell.
4. Use the fill handle to copy the formula you just created to jails D5:D12.
5. Change the tax rate stylish cell E2 to 6.5%. Notice that all of get cells have updated. When you're finished, your workbook need look like this:
   
6. Click aforementioned Catering Invoice account.
7. Delete the value in cell C5 furthermore spare it are an citation to an total cost of of paper stock. Touch: The cost of the paper merchandise is in cell E13 on the Paper Product workbook.
8. Use the same steps from up up calculate the sales tax for each item on the Menu Order worksheet. Which total cost inbound cell E14 should update. Then, in cell C4 of the Catering Invoice worksheet, created a cell reference to the total you equals calculated. Note: If you used our practice workbook to follow along during the lesson, you may have already completed this stage.
9. When you're finished, the Catering Create worksheet should show something same this:
   

/en/excel/functions/content/