Using absolute references in Excel allows you to create formulas that, when copied, refer to the same cell. This is very convenient, especially when you have to work with a large number of formulas. In this lesson, we will learn what absolute references are, and also learn how to use them when solving problems in Excel.
In Microsoft Excel, there are often situations when it is necessary to leave the reference unchanged when filling in cells. Unlike relative links, absolute links do not change when copied or filled. You can use an absolute reference to keep a row or column unchanged.
You can read more about relative links in Excel in this lesson.
Absolute link – what is it?
In Excel formulas, an absolute reference is followed by the addition of a dollar sign ($). It may precede a column reference, a row reference, or both.
Typically, when creating formulas that contain absolute references, the following format is used: $ A $ 2. The other two formats are used much less frequently.
When creating a formula, you can press the key F4 on the keyboard to switch between relative and absolute links. This is the easiest and fastest way to insert an absolute link.
Create a formula using absolute references
In the following example, we will enter a tax rate 7.5% in cell E1 to calculate sales tax for all items in column D. Since each formula uses the same tax rate, you need to make sure that the reference remains the same when you copy the formula in column D. To do this, you must make an absolute reference $E$1 into our formula.
- Select the cell that will contain the formula. In our example, we will select cell D3.
- Enter an expression to calculate the desired value. In our case, we will enter =(B3*C3)*$E$1.
- Press Enter on keyboard. The formula will be calculated and the result will be displayed in the cell.
- Find the autofill marker in the lower right corner of the cell in question. In our example, we are looking for the autofill marker in cell D3.
- Click and, holding the left mouse button, drag the autofill marker over the required cells. In our case, this is the range D4:D13.
- Release the mouse button. The formula will be copied into the selected cells with an absolute reference, and the result will be calculated in each.
You can double click on completed cells to check if your formulas are correct. An absolute reference must be the same for each cell, while a relative one will be different depending on the row.
Be sure to use the dollar sign ($) when creating absolute links. In the following example, the dollar sign has been omitted. This caused Excel to interpret the link as relative when copying and calculate an incorrect result.
In Excel, you can also create links between worksheets within a document. Read more about this in the lesson Linking to other sheets in Excel.