Absolute and Relative References - Google Sheets

After the last video, our budget spreadsheet not only looks great, but it also adds up all our expenses categories. In this video we reference that calculation and use to to finish our budget.

Click on cell C6. It’s the one right under Total Expenses. We want this cell to show the same result as the calculation at the bottom. We could enter another SUM function and recalculate it, but there is a better way. References! With C6 still selected, type the equals sign, and click on the the calculation from cell J38. What =J38 means, is that C6 has referenced J38 and will use the exact same value as whatever J38 is.
Hit the return key on your keyboard, and see for yourself!

Now, we want to know how much of our income is left after savings. For that we need an income in that white box. I’m going to type something inside for my income… say… $1200. To find out how much money is left over for savings, we need to take our income and subtract our savings. Doing is is very similar to adding a sum function. Click on the cell C8, type the EQUALS sign, then click on our Income box. When you click, you’ll see C4 automatically typed in the box for you. Now we need to subtract expenses, so we type a negative sign and then click on the expenses cell. And just like that you have how much money you’ve saved!

Wait a second, the number is negative! That means that we are spending more than we are earning. Let’s get a better job…. , one that makes $2900. Much better. Now we have some money for savings.

The last set of calculations we will add is for the amount of each savings category. Click on the first cell there. We need going to calculate what amount 40% of our total savings is. Type the equal symbol and then click on the 40% cell. To multiply we use the asterisk key. It’s the shift 8 character. Now click on the total savings cell, and just like that it’s calculated the correct amount!

Now doing this for each cell could take a very long time. Especially if we had more cells! Luckily there is a shortcut for this situation. Since each cell will be the percentage multiple by savings, we can repeat the pattern by clicking the corner box and dragging down.

UH OH!! It didn't’ work for those other cells. Click on the cell in H5 to see what happened. By clicking on the formula bar, it highlights the cells the formula is using. It got the 25% cell right, but not the savings cell. And it makes sense why it didn’t, because the relative pattern is to take each cell below the next.

That is because the function was using only relative references.

So how to you absolutely lock-in the total savings cell so when we drag down only that cell remains static. And answer is “absolute referencing” . Let’s edit that formula we just made. C8 is our total savings cell we want to make absolute. To do that, we place a dollar symbol before the C and one before the 8. The first dollar sign locks the column, and the second dollar sign locks the row. With both dollar signs, we are guaranteed that when we drag down, the spreadsheet will correctly reference that cell…. Perfect!


Try experimenting with different uses of absolute and relative references. Play around with using just one dollar sign to see what happens. Once you’ve done that, I’ll see you in the next video.

свернуть