Skills level: Intermediate We often need to use the same formula to calculate the same thing for a whole list of items. You may be familiar with the click & drag function that allows you to quickly fill (or copy) the same formula downwards, while Excel assumes that for each subsequent cell into which the formula is copied, the subsequent set of data needs to be used for the calculation. Let’s use an example to illustrate. Below is a simple shopping list, with columns for items, quantity and price per unit. Relative References To calculate the total cost of your shopping list, you can easily use the product function, or simply enter a multiplication formula into Cell D2 (I am going to use the second formula throughout this example, but the principle will apply to both… or any other formula for that matter): =PRODUCT(B2,C2) OR =B2*C2 To save time, it is not necessary to manually enter the same formula for cells D3 through to D8. You can simply select Cell D2, then click on the bottom right corner of Cell D2 (your cursor will change to a plus sign when you hover your cursor over it), and hold while you drag your formula down all the way to Cell D8. As soon as you let go of your cursor, Excel will complete the total cost for each of the rest of the items on your list. When you fill a formula downwards, as illustrated above, Excel assumes that for each cell that you want the formula copied into, the position of the cells used in the calculation is exactly the same, relative to the position of the cell containing the formula. For the first calculation in Cell D2, you used the Quantity of Milk (Cell B2) and multiplied it by the Price of milk (Cell C2). When you copy this formula to Cell D3, Excel assumes that in Cell D3, you want to multiply the Quantity of Buns (Cell B3) by the Price of Buns (Cell C3). And the same applies for every row that follows. All the cells in question remains in a straight line. In the formula in Cell D2, you selected Cell B2 (which is 2 cells to the left of the Cell in which you are currently typing your formula), and multiplied it with Cell C2 (which is immediately to the left to the cell in which the formula is being entered). B2 and C2 are called relative references. Therefore, when copying the formula down, the cells that are used in the subsequent formulas, will have the same relative positions to the current cell. So have a look at the formula in Cell D5. It also starts the formula with the cell which is two cells to the left of the current cell, and multiplies it with the cell immediately left of the current cell, keeping the positions of the cells used in the formula intact. In the above example, we have copied the formula by “filling” (dragging downwards to copy), but if you have copied and pasted the formula from Cell D2 to Cell D5 (skipping the cells in between), the same rules apply and Excel would have applied the same relative references. Absolute References Sometimes, however, you want the same formula to be copied down a list of items, but you do not want Excel to use relative references for all the cells contained in the calculation. Instead, for one of the cells contained in your formula, you may need Excel to use exactly the same cell for every calculation. In other words, for your formula to work, you need excel to keep one of the cells “fixed” in your formula while you drag it down. Say that you are allowed a 10% discount at the shop, and you want to calculate the total discount that you receive on your shopping list. I have entered the percentage discount in Cell B11, which needs to be used in each formula in Column E. Start by typing your formula in Cell E2: =D2*B11 You will notice that if you drag this formula down, the rest of the calculations amount to zero. This is because as you drag the formula down, Excel, once again, uses the relative position of the cells to the current cell in which you are copying your formula. So how do you make Excel use Cell B11 in the calculation for EVERY cell, from E2 to E8? You have to make Cell B11 what we call an absolute reference. To change any cell from a relative to an absolute reference, you make use of dollar signs ($) in front of the Column letter as well as in front of the Row number as follows: Go ahead and drag your formula downwards again. You will now see that for every formula copied into cells E2 to E8, the specific Cell in Column D is multiplied by 10% - the value contained in Cell B11: And for those of you who love shortcuts as much as I do, the shortcut to change a cell reference from relative to absolute, is to press F4 on your keyboard right after selecting the particular cell or entering the particular cell reference. In this case for example, as soon as you have either clicked on Cell B11 to enter it into your formula, or entered it manually using your keyboard, while the cursor is still flashing within your formula bar after B11, press F4 on your keyboard, and see how excel enters the dollar signs for you! Remember that the “fill” function is not only used to copy cells downwards and upwards, but can also be used to copy cells to the left or to the right. And the same rules apply regarding the position of cells relative to the current cell. So, below, I have an example where you would need to drag the formula across (from left to right). To calculate the total cost for each month in Row 4, I can make use of relative cell references in my formula, because I need the position of the cells in my formula to change as I copy the formula to the right for the subsequent months. To calculate mark-up however, we need to make the cell reference for B5 absolute, so that as you copy the formula across, the 30% in cell B5 is used in every calculation: Go ahead and drag your formula across. To calculate the Sales value, you can make use of relative cell references: Mixed reference If you have a whole table of calculations that need to be completed, you sometimes want only 1 formula that you enter in the very first cell, and drag that formula down as well as across so that your input is kept at a minimum, while you still get to the correct answers. In many such cases, you need the cell references to be a combination of relative and absolute. Here is another example to illustrate. Your favourite retailer wants to get rid of all their summer stock to make way for winter coats, jackets & boots. You have your eye on a pair of shoes and a dress or two. The sale starts with 10% off in January, then discount increases to 25% in February, and then to 50% in March. You want to calculate how the prices will change in these months using an excel spreadsheet. I have created a simple table to summarise all of the important information as below. The 6 cells highlighted in light blue is what I want to calculate. In Cell G2, enter the formula to calculate the price of shoes in January. Make all the cells relative to start with, and then I will explain it one step at a time. =B2*(1-B6) If you keep the cell references relative, and you drag the formula down to calculate the same thing for Dresses, Cell B2 will change to B3 in your calculation, which is correct, but cell B6 will change to B7, which will give you the wrong answer since there is no data in Cell B7. Therefore, Cell B6 cannot be relative. Let’s consider making it absolute ($B$6) in Cell G2. If you drag the formula down, the price for dresses will calculate correctly, since B2 changes to B3 and B6 remains fixed. BUT we are trying to use ONE formula that can be dragged down as well as across. So what happens when you drag the formula across to Feb after you have made Cell B6 absolute? Cell B6 will not change to C6 which is what you need to calculate the price of Shoes in Feb. And at the same time, when you consider Cell B2 again, you will notice that while it is relative (which was fine when we wanted to drag it downwards) it will now not be suitable anymore, because when we drag it across, Cell B2 changes to C2… Which has nothing in it. So this is not working. Neither the relative or absolute references is suitable in this situation. What we need is a mixed reference. When we made a cell reference absolute the first time, we changed the cell reference from B11 to $B$11. The $ in front of “B” keeps Column B fixed, and the $ in front of the 11 keeps the Row 11 fixed. What I didn’t mention before, is that it is not necessary to fix both the column letter and the row number for a specific cell at the same time. You can decide to only keep the column fixed ($B11) – when you drag the formula down, the rows will still change. Or you can decide to keep the row fixed (B$11) which will keep the row number in place but change the columns as you drag the formula across. And it is exactly this combination of a relative and absolute reference that we need for the above example. Let’s start again. If you want to drag the formula in G2 down AND across, we need to make cell B2 a mixed reference. It has to change when you drag it down, but not when you drag it across. Therefore, the row needs to be relative, and the column fixed (or absolute). A suitable reference is therefore $B2 (the $ in front of the B fixes the column, but the row is free to change when the formula is dragged down). Cell B6 also needs to be a mixed reference. This time, if you drag the formula in Cell G2 down, the row should NOT change (since the discount data is all shown in the same row), while the column needs to change when the formula is dragged across (so that the appropriate discount percentage is applied for the respective months). A suitable reference would therefore be B$6. There is no $ sign in front of the column letter (B), so the column reference is free to change relative to the position of the current cell, while the $ sign in front of number 6 fixes the row number, and will not change if the formula is dragged down. Go ahead and enter the complete mixed reference formula in Cell G2: This formula can now be dragged down from G2 to G3. Then across from G2 to I2. And then again across from G3 to I3. Another time saving tip: As soon as you have dragged the formula down form Cell G 2 to G3, both these cells will be highlighted. While both are still highlighted, click on the bottom right corner of Cell G3 to fill across. This way you fill 2 cells with 2 formulas across and the same time. And because you make use of mixed references, your calculations will stay intact. The F4 shortcut can be pressed multiple times to change a cell reference, from relative to absolute, then from absolute to mixed (keeping the row fixed) and then to mixed (keeping the column fixed) and if pressed again, it changes back to a relative reference again. I hope this lesson helped you gain a better understanding of relative and absolute references, and that you can apply it to your daily tasks to make your life easier.
If so, feel free to share in the comment section how this function helped you work more efficiently. Remember to share with others who may also find this tutorial useful.
0 Comments
Leave a Reply. |
Carine HoughHi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog. Categories
All
|