Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When copying formulas it's important to understand the difference between Relative and Absolute addresses. We'll start with our Utility Costs by Month table. Here we see a formula calculating the percent increase from one year to the next for our January column. When we click that cell, we can see the formula in the Formula Bar is using the values in the January column. Now if we use the Fill Handle in the bottom right corner of this cell and drag across to the right, we can copy the formula to the remaining cells.
But notice the values come out different for each column. If we were to click for example cell F4 and look at the Formula Bar, it's using the values in the May column. Same goes for June, July and the other months of the year. This is known as Relative Addressing. Numbers is smart enough to use the cells in the column where the formula is getting copied to. But on occasion, you do not want to use Relative cell addresses. For example, in our Utility Costs by Type table, here we have a column, which will calculate the Total in U.S. Dollars. Let's click cell F2. In this case, the formula is going to be the Total multiplied by the Exchange Rate. So we'll start with an = sign, click the Total for 2007 and multiply that which is the asterisk by the value that appears in another table in cell B1.
Notice it's called Table 19 in this case and the formula is going to be whatever is in the Total 2007 cell multiplied by that Rate. When we accept this, we see the end result. Now if we use the Fill Handle to copy this formula down, let's see what happens. We don't see an actual answer. Instead we see a little marker representing some kind of error. We can click either one of those markers to view the error itself. Table 19::B2 isn't a valid reference. There is no cell B2. So Relative Addressing is trying to happen here but it's not working. So let's go back to cell F2.
In the Formula Bar, you can see we are going to use the Total for 2007, multiplied by the contents of B1 in Table 19. That's right, but we can make it Absolute. So when we copy this it will always use the cell B1 of Table 19, by clicking the Drop-down arrow to the right and choosing one of the Absolute options. Notice the checkmark next to Relative indicates it's the default. So we can use both the row and the column, in this case the cell that we want to use or we can freeze on a particular row or column. In this case, we want to use both.
So click the second the option, which is Absolute Row and Column. This rewrites our formula to include the dollar signs around the B and the 1, and now when we accept this, the same answer appears but when we go to copy it, click-and-drag, we get actual answers. For example, if we click cell F4 and look at our Formula Bar, it's using cell B1 of Table 19. Let's deselect the table by clicking the canvas. So Relative Addressing is the default when copying formulas but when you need to use the values in a row, column or even cell, remember these Absolute address options.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70013 Viewers
80 Video lessons · 127415 Viewers
52 Video lessons · 62362 Viewers
59 Video lessons · 48064 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.