Relative References in Excel

Table of Contents

• Relative References in Excel
• What are Relative Cell References in Excel??

Relative References in Excel

In my personal opinion, your understating level of references in excel will decide how quickly you can understand the working style of Excel software. If you are quick enough to understand the reference styles in excel probably you will go long way in excel.

Excel worksheets made up of rows and columns, which contains cells. Each cell represents the particular column and particular row. For example, cell A1 refers first column (A) and first row (1). Similarly, C15 refers third column (C) and fifteenth row (15).

The real advantage of Excel is in the usage of these cell references in other cells when creating formulas.

You can use three types of cell references in Excel 2007 formulas: relative, absolute, and mixed. Using the right type of references could save you tons of work time and make you feel like a hero.

• Relative Reference in Excel
• Absolute Reference in Excel
• Mixed Reference in Excel

What are Relative Cell References in Excel?

Relative references in excel mean it refers to a cell or range of cells in excel. When this reference is copied to another location or cell relative references also automatically changes.

These are standard references in excel. When there is a need of doing the same set of calculations on excel you can use Relative References in excel.

How to use Relative References in Excel?

If you do not get what we are referring now below examples will give you the practical idea about how to use Relative References in Excel.

Example #1

Let me take a simple example to explain the concept of relative cell references in Excel. Assume you want to perform the summation of two numbers that are located in cells A1 & A2 and you want to do the sum in the cell A2.

So, you applied A1+ A2 that should be equal to 100.

Now, you have different values in the cells B1 & B2 and do the similar summation in the cell B3.

We can do the summation in 2 ways. One is once again by applying the excel formula in the cell B3 or you can copy paste the A3 formula to B3.

You must be thinking when you copy the cell A3 and paste in B3 you should get 100. However, the cell A3 contains the formula, not the value. That means A3 cell depends on others cells A1 & A2. When you copy the A3 cell and move one cell to the right A1 becomes B1 and A2 becomes B2, so the cell B3 takes the values of B1 and B2 and apply the sum of those two numbers.

Example #2

Now, look at the one more example of Relative references. Assume you have a data, which includes unit price and units quantity sold for each product and you want to do the calculation of Unit Price * Unit Cost = Sale Price.

To calculate the sales price for each product we need to multiply Unit Sold with unit Price i.e. B2*C2 and similarly for all the products. Now go ahead and apply the formula.

The above formula should give us the sales amount for the product 1. We have totally ten products here and we cannot apply the same formula every time. Instead, we can just copy paste the formula to other cells.

As you copy and paste the formula from cell D2 to cell D3 the formula reference also changes from B2*C2 to B3*C3 and so on. Probably it would have taken 1 minute to apply to all the ten products but copy paste or dragging the formula would take hardly 5 seconds of your time.

Either press Ctrl + D or Copy and Paste the cell D2 to all the selected cells.

Things to Remember

• When copying formulas in Excel, relative addressing is usually what you want. That is why it is the default behaviour. Sometimes you do not want relative addressing but rather absolute addressing. This is making a cell reference fixed to an absolute cell address so that it does not change when the formula is copied.
• No dollar signs at all! If you copy this bad boy from place to place, the formula will move with it. So if you type =A1+A2 into cell A3, then copy and paste that formula into cell B3, it will automatically change the formula to =B1+B2.
• In Relative reference, each referred cell keeps changing along with the cells you move either left, right, downward and upward.
• If you give reference to cell C10 and move to one cell down it changes to C11, if you move one cell upward it changes to C9, if you move one cell to the right it changes to D10, if you move on the cell to the left then it changes to B10.

Recommended Articles

This has been a guide to Relative References in Excel. Here we discuss how to use Relative Cell References in Excel along with the example and downloadable excel templates. You may also look at these useful functions in excel –

• VBA Transpose using paste special method
• 3D Cell Reference | Explanation | How to Use?
• Multiply in Excel
• AutoFormat Excel
• Remove (Delete) Blank Rows in Excel
• ISNUMBER in Excel
• ISBLANK in Excel
• TRANSPOSE Excel Function
• Char Function In Excel