Excel 2010: Absolute & Relative Reference


By default, Excel uses relative referencing, that’s mean if you want to use the same formula somewhere else in the datasheet, it will be automatically changed with its original reference. In some cases, a relative reference is useful, but when you want to apply the same formula with absolute reference, you need to slightly change the formula. In this post we will check how how absolute and relative references work using a simple example.

To get started, we will create a simple datasheet that illustrates relative cell references. Enter values in the data sheet as shown in the screenshot below.


We have written a simple SUM formula, if you copy the formula by selecting the cell that contains the formula, and paste it in another cell, it would not show the same result i.e. (A1+A2=90) but yields 40, as you can notice in the screenshot below, that the formula pane shows cell A2 instead of A1. This happens because of relative cell referencing.


When you copy the formula, Excel doesn’t only copy it, it remembers the position of the cell and on pasting it into another cell, it pastes the formula with its original cell reference, as we pasted it beneath the cell, which contain value 90. In our case, Excel also changes the location to one cell down, referring to A2 and A3.

For copying the exact formula into other cells, we need to write it in a slightly different way, for example if we want to copy the SUM formula, we will write it with absolute reference, that contains $ sign;


As you can see in the screenshot, Excel ignores the reference when formula is written with $ sign. So, if you want to use the same formula without changing the cell location, just use $ with cell original location.


The absolute referencing may help you easily use the same formula or function in different data sets. Unlike relative referencing, it doesn’t change the cell location and perform the action over the same cells where you first used it. It must be noted that you cannot drag the formula cell to other fields/rows to apply the formula on corresponding cells, as doing so will yield the same result. To quickly apply a function or formula over the corresponding cells, relative referencing must be used.

You can check out previously reviewed guides on Usage of Data Bars in Excel & How to split columns

  • Pingback: Office Excel 2010: Working With XML Format()

  • Excel is hands down one of the best tools around. I consider it an extension of my brain, just like google search.

  • Pingback: Convert Excel Worksheet Into Graph Paper()

  • Pingback: Ranking Values In Excel 2010 With RANK function()

  • Marie

    Oh dear. Gone to Windows 7 after Vista after XP, and have always used the F4 button to create an absolute cell reference in Excel, but now when I press F4 it does something completely different. Any ideas? I find keyboard shortcuts very useful and timesaving.

    • Terri Snider


      F4 Still does the toggle of all Relative Referencing.  I use it all the time in all versions.  The only thing is that you must click on the cell you wish to make a reference, in the formula bar, before clicking F4…otherwise it will do something different. 

  • Ian

    I have a spreadsheet that is used for one day to store operational data. In one cell I want to reference a cell from the previous worksheet. But I need it as a relative reference as when I change to a new sheet I want the previous days data and not the day before. So how do I do this