search Buscar

    How to use absolute references in Excel

    Who I am
    Judit Llordés

    Item Feedback:

    content warning

    Most people are familiar with using relative references in Excel. This is because cell references in Excel spreadsheets use the relative reference method by default.

    However, there are times when relative references become annoying. Cell references change every time you copy cells or fill columns and rows. If you don't want the references to change, you'll need to use absolute references or mixed references (including relative and absolute references).

    In an absolute reference, both column and row references are "locked", so neither of them change when you copy or fill from that cell.

    This article will look at how to use absolute references to make Excel behave the way you want with your data.

    The best free Excel alternatives

    What are relative references and how do they work in Excel

    When you enter values ​​in an Excel spreadsheet, each cell is assigned a specific letter and number. Represents the column and row of that cell.

    For example, the value of "1" in the underlying spreadsheet is in column A and row 2. So the "reference" to this cell is A2.

    If you want to perform a calculation in the next cell based on this cell, adding 1 to it, you will write the following formula:


    This formula will insert the value from A2 into the formula, calculate it, and then return the result in the cell where this formula is located.

    With relative reference, you don't need to type this formula into every other cell. All you need to do is drag the corner of the cell with the original formula down as far as you want. 

    In the next cell, the reference to A2 will become A3. In the cell below, A3 will become A4. In other words, Excel knows you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.

    It works the same way if you drag the formula between columns. Instead of updating the number, Excel will update the last part of the reference (the column) to always refer to the cell above it.

    The column to the right contains B2, to the right of that contains C2, and so on. This is a simple example of how relative addressing works for both column and cell references.

    How to add a watermark in Excel

    What are absolute references and how do they work in Excel

    Absolute references in Excel allow you to reference the same cell, instead of letting Excel automatically update the row or column references for you. The "mixed" reference is if you freeze only the row or column and the "absolute reference" is when you both freeze.

    Let's take a look at some examples.

    Let's say your spreadsheet has a “10” in a top row and you want each row below to multiply that number by the number in the cell to the left. 

    To do this, enter a formula similar to this:


    This locks the reference “2” so it won't change the row reference if you drag the cell with this formula into the cells below. Since A3 remains “unlocked”, both the row and column reference will still change automatically and will always refer to the cell on the left.

    You will notice that it only works because you are dragging down into cells in the same column. So there is no need to freeze column (B) by placing a dollar sign ($) in front of it.

    The problem is that if you want to use the same formula to the right of the original formula, the reference "B" will change and the formula will no longer refer to B2 as expected.

    Let's take a look at how to use absolute references instead of mixed references to make the fill work correctly in both directions.

    How to download Excel for free (and the best alternatives)

    How to use absolute references in Excel

    To use the correct reference in this formula, you need to carefully consider what you are trying to do.

    In this case, we want the following behaviors when filling right.

    • Always refer to the value in cell B2
    • Always refer to the value in column A
    • Moves the row reference for column A to the current formula row

    By observing these behaviors, you now know what you have to "block" and what not. Both "B" and "2" must be locked (unchanged). Also, column A must be locked.

    So your formula in B3 must look like this: =$B$2*$A3 Now when you drag this same cell down or up, the formula works as expected.

    Proper use of absolute references can get tricky, so it's essential that you take the time to carefully consider how you want Excel to update your formula as you fill in columns or rows in either direction.

    Create Excel drop-down list

    Go through the reference types in Excel

    You can speed up the process when typing formulas with absolute references by pressing the key F4 , which will make the cell reference absolute.

    The cursor can be on either side of the cell reference (or even in the center of it) when you press F4 and it will convert that single reference to absolute anyway.

    If you don't want the absolute (for example, mixed), keep tapping F4 until the reference looks the way you want.

    If you want to add any kind of reference to other cells in the formula, just place your cursor there and start scrolling F4 again.

    Once the formula is set up, press Enter and start filling the spreadsheet in the direction you want. If you set up your references correctly, everything should work as expected.

    Further Reading:

    • How to track changes in Microsoft Excel
    • How to enable or disable AutoCorrect in Excel
    • How to add a watermark in Excel
    • What is a CSV file and how to open a .csv file?
    • How to install Microsoft Office on Chromebook
    add a comment from How to use absolute references in Excel
    Comment sent successfully! We will review it in the next few hours.