Skip to main content

Excel absolute reference (how to make and use)

When referencing a cell in a formula in Excel, the default reference type is a relative reference. These references will change when the formula is copied to other cells based on their relative column and row. If you want to keep a reference constant, regardless of where the formula is copied, you need to use the absolute reference.

Free download the sample file doc sample


Video: Absolute reference

 


What is an absolute reference

 

An absolute reference is a type of cell reference in Excel.

Compared to a relative reference that will change based on its relative position when a formula is copied to other cells, an absolute reference will remain constant no matter where the formula is copied or moved.

An absolute reference is created by adding a dollar sign ($) before the column and row references in the formula. For example, to create an absolute reference for cell A1, you should represent it as $A$1.

Absolute references are useful when you want to refer to a fixed cell or range in a formula that will be copied to multiple cells, but you do not want the reference to change.

For example, the range A4:C7 contains products' prices, and you want to get the payable tax of each product based on the tax rate in cell B2.

If you use relative reference in the formula like "=B5*B2", some wrong results are returned when you drag auto fill handle down to apply this formula. Since the reference to cell B2 will change relative to the cells in the formula. Now, the formula in cell C6 is "=B6*B3", and the formula in cell C7 is "=B7*B4"

But if you use an absolute reference to cell B2 with the formula "=B5*$B$2" will ensure that the tax rate stays the same for all cells when the formula is dragged down using the auto fill handle, the results are correct.

Using relative reference   Using absolute reference
doc absolute reference 3 1   doc absolute reference 4 1

How to make absolute references

 

To make an absolute reference in Excel, you need to add dollar signs ($) before the column and row references in formula. There are two ways to create absolute reference:

Manually add dollar signs to the cell reference

You could manually add dollar signs ($) before the column and row references that you want to make absolute while typing the formula in a cell.

For example, if you want to add the numbers in cell A1 and B1 and make both of them absolute, just type the formula as "=$A$1+$B$1". This will ensure that the cell references remain constant when the formula is copied or moved to other cells.

Or if you want to change references in an existed formula in a cell to absolute, you can select the cell, then go to the formula bar to add the dollar signs ($).

Using shortcut F4 to convert relative reference to absolute
  1. Double click the cell with the formula to enter the edit mode;
  2. Place the cursor at the cell reference you want to make absolute;
  3. Press F4 key on the keyboard to switch the reference types until the dollar signs are added before both column and row references;
  4. Press Enter key to exit edit mode and apply the changes.

F4 key can toggle reference between relative reference, absolute reference and mixed reference.

A1 → $A$1 → A$1 → $A1 → A1

absolute reference f4 toggle 1

If you want to make all references absolute in a formula, select the whole formula in the formula bar, press F4 key to toggle reference types until the dollar signs are added before both of column and row references.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

absolute reference f4 toggle 2


Use absolute reference with examples

 

This part provides 2 examples to show when and how to use absolute references in an Excel formula.

Example 1 Calculate percentage of total

Suppose you have a data range (A3:B7) containing the sales of each fruit, and the cell B8 contains the total sales amount of these fruits, now you want to calculate the percentage of each fruit sale of the total.

The generic formula to calculate the percentage of total:

Percentage = Sale/Amount

Use the relative reference in the formula to get the percentage of the first fruit like this:

=B4/B8

When dragging the auto fill handle down to calculate the percentage of other fruits, #DIV/0! errors will be returned.

Since when you drag auto fill handle to copy the formula to cells below, the relative reference B8 is automatically adjusted to other cell references (B9, B10, B11) based on their relative positions. And the cell B9, B10 and B11 are empty (zeros), when the divisor is zero, the formula returns to an error.

To fix the errors, in this case, you need to make cell reference B8 absolute ($B$8) in the formula to keep it from changing when you move or copy the formula to anywhere. Now the formula is updated to:

=B4/$B$8

Then drag the auto fill handle down to calculate percentage of other fruits.

Example 2 Look for a value and return to corresponding match value

Assuming you want to look for the names list in D4:D5 and return their corresponding salaries based on the staff names and corresponding annual salary provided in range (A4:B8).

The generic formula to lookup is:

=VLOOKUP(lookup_value, table_range, column_index, logical)

If you use the relative reference in the formula to lookup a value and return corresponding match value like this:

=VLOOKUP(D4,A4:B8,2,FALSE)

Then drag the auto fill handle down to lookup the value below, an error will be returned.

When you drag the fill handle down to copy the formula to the cell below, the references in the formula automatically adjust down by one row. As a result, the reference to the table range, A4:B8, becomes A5:B9. Since "Lisa: cannot be found in the range A5:B9, the formula returns an error.

To avoid the errors, use absolute reference $A$4:$B$8 instead of the relative reference A4:B8 in the formula:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Then drag the auto fill handle down to get the salary of Lisa.


 

2 clicks to batch make cell references absolute with Kutools

 

Whether you choose to type manually or use F4 shortcut, you can only change one formula at a time in Excel. If you want to make cell references in hundreds of formulas absolute in Excel, the Convert Refers tool of Kutools for Excel can help you handle the job with 2 clicks.

Select the formula cells that you want to make cell references absolute, click Kutools > More (fx) > Convert Refers. Then choose the To absolute option and click Ok or Apply. Now all cell references of the selected formulas have been converted to absolute.

Notes:

Relative reference and mixed reference

 

Apart from absolute reference, there are other two reference types: relative reference and mixed reference.

Relative reference is the default reference type in Excel, which is without dollar signs ($) before row and column references. And when a formula with relative references is copied or moved to other cells, the references will automatically change based on their relative position.

For example, when you type a formula in a cell such as "=A1+1", then drag autofill handle down to fill this formula to the next cell, the formula will auto change to "=A2+1".

Mixed reference is made up of both an absolute reference and relative reference. In other words, mixed reference uses the dollar sign ($) to fix either the row or column when a formula is copied or filled.

Take a multiplication table as an example, the rows and columns list the numbers from 1 to 9, which you will multiply each other.

doc absolute reference 15 1

To start, you can use the formula "=B3*C2" in cell C3 to multiply 1 in cell B3 by the number (1) in the first column. However, when you drag the autofill handle to the right to fill the other cells, you'll notice that all of the results are incorrect except for the first one.

doc absolute reference 16 1

This is because when you copy the formula to the right, the row position won’t change, but the column position changes from B3 to C3, D3, etc.. As a result, the formulas in the right cells (D3, E3, etc.) change to "=C3*D2", "=D3*E2", and so on, when you actually want them to be "=B3*D2", "=B3*E2", and so on.

In this case, you need to a add dollar sign ($) to lock the column reference of “B3”. Use the formula as below:

=$B3*C2

Now when you drag the formula to the right, the results are correct.

doc absolute reference 17 1

Then you need to multiply the number 1 in cell C2 by the numbers in the rows below.

When you copy the formula down, the column position of cell C2 won't change, but the row position changes from C2 to C3, C4, etc. As a result, the formulas in the cells below change to "=$B4C3", "=$B5C4", etc. which will produce incorrect results.

doc absolute reference 18 1

To solve this problem, change “C2” to “C$2” to keep row reference from changing when dragging auto fill handle down to fill the formulas.

=$B3*C$2

doc absolute reference 19 1

Now you can drag the auto fill handle right or down to get all results.

doc absolute reference 20 1


Things to remember

 
  • Summary of cell references

    Type Example Summary
    Absolute Reference $A$1 Never change when formula is copied to other cells
    Relative Reference A1 Both of row and column reference change based on relative position when formula is copied to other cells
    Mixed Reference

    $A1/A$1

    Row reference changes when formula is copied to other cells but column reference is fixed/Column reference changes when formula is copied to other cells but row reference is fixed;
  • Generally, absolute references never change when a formula is moved. However, absolute references will automatically adjust when a row or column is added or removed from the top or left in the worksheet. For example, in a formula "=$A$1+1", when you insert a row at the top of the sheet, the formula will auto change to "=$A$2+1".

  • The F4 key can switch between relative reference, absolute reference and mixed reference.

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!