Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Absolute Reference in Excel

    How to Absolute Reference in Excel

    jabinBy jabinFebruary 27, 2023 Excel 4 Mins Read

    By default, Excel uses relative cell referencing to copy formulas across rows or columns. So, you are knowingly or unknowingly using it most of the time when copying and pasting formulas to other cells.

    However, it may not be appropriate all the time. In some cases, you would want to fix a cell in the formula no matter where you are copying it. That’s where you need to convert the regular relative cell reference to an absolute cell reference.

    What is Absolute Reference?

    A cell reference whose rows or columns are locked in one place and won’t change when copying a formula to other cells is known as an absolute cell reference. This prevents a reference to move as the formula is copied.

    Let’s take a look at an example to better understand the concept.

    Here, we have three customers (John, Jill, Jack) who bought some clothes (pants, shirt, suit) from our store. 

    And, we want to calculate the actual price after applying the discount on the total cost. For this, we are using the following formula:

    Total cost -Discount * Total cost

    Now, please take a look at the above image, specifically under the After Discount column.

    Then, try to guess what would be the result if we copied the formula on the G8 cell for other cells, G9 and G10, below it. 

    Well, as you can see, other cells are not using the correct formula. One cell even resulted in a #VALUE! Error.

    So, what’s going on? How is Excel calculating other cell values?

    As we mentioned earlier, Excel uses relative cell referencing by default. So, as we copied the formula down the column, the cell with the Discount value (D5) also started to shift downwards because of it.

    To visualize which cells are being used in each formula for the G9 and G10 cells, you can use the feature called the Trace Precedents option. You can find it under the Formula tab inside the Formula Auditing section.

    Now, you can notice that the D5 cell isn’t fixed for other cells G9 and G10 as we expected.

    The formula for G9 is currently =F9-(F9*D6). But, since the D6 cell is empty, the formula =F9-(F9*D6) leads to =8000*8000-0 and the final result is 8000. Also, the G10 cell returned an error because it tried using the text “Shirt” with a number in its formula.

    So, how do we fix it?

    In a nutshell, we want to lock the D5 cell which contains our discount value such that whenever we copy the formula, it moves neither across the row nor down the column. In such cases, we need absolute cell referencing.

    How to Use Absolute Reference?

    To use absolute cell referencing in your formula, follow the steps below.

    1. Select the cell which contains the formula and needs absolute cell referencing. In our case, it’s one with the discount value.
      Select-cell-to-make-absolute
    2. Double-click to enter the Edit mode. Or, use the formula bar. One important tip is that always fix the first cell where you copy the formula from.
      Select-cell-to-make-absolute-reference
    3. Place the cursor on the cell reference you want to change.
    4. Insert the dollar sign before both the column and row. Or, simply press the F4 key to cycle through different cell reference types and stop when you see the two dollar signs.
      Convert-to-absolute-cell-reference
    5. Press Enter.
    6. To convert other cells into an absolute cell reference, just repeat Step 4.

    After we used absolute referencing, all other cells only use D5 in their formulas as shown in the image below.

    Note: Some laptops assign the F4 key to use the media controls. In such cases, you have to press Fn + F4 to cycle through the cell reference types.

    Ways to Absolute Reference

    While you can use absolute referencing to lock both the column and row part of a cell reference such as $A$2, you can choose to lock it partially.

    For that, you just need to add the dollar sign before it.

    For instance,

    • If you just want to lock the column and not the row, you would have a cell reference such as $A2.
    • Similarly, to lock only the row, place the dollar sign in front of the row part, such as A$2.
    Excel Basics
    jabin

      As a tech content writer, Jabin covers Excel-related articles at InsideTheWeb. His articles mainly involve helping new users to quickly familiarize themselves with the Excel interface and explaining various essential features. While he got introduced to Excel in his early school days, he developed a keen interest in it after working on a college project. He was impressed at how quickly one could accomplish several tasks with built-in functions like the filter function and user-friendly tools like the power query. Keeping beginner audiences in mind, he loves to explain even the most fundamental Excel concepts in detail and break down complex topics with a step-by-step approach. As an avid Excel user, he believes every task can be done a lot quicker if you know the right tools and techniques. When he’s not behind a keyboard, he loves to listen to interesting audiobooks and podcasts.

      Add A Comment
      Table of Contents
      • What is Absolute Reference?
      • How to Use Absolute Reference?
      • Ways to Absolute Reference
      Recent Posts
      • How to Calculate Discount Percentage in Excel
      • How to Create a Progress Bar in Excel
      • What is VSTACK in Excel
      • How to Separate Dates in Excel
      • How to Lock a Cell in Excel Formula
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.