How’s it looking for you so far, You got your calculations, I have got my total row, Got the total for each month and I have got my total column in there my total for each bill, as well is a grand total for all the bills for all the months.
If using your own numbers, Just be aware of that, Do not try to compared against mine. If you are using my exact numbers make sure everything’s looking good in here.
If I got something wrong right let me know, jump into the review section let me know I got one of them wrong.
If I got something wrong right let me know, jump into the review section let me know I got one of them wrong.
So I want to take this idea of cell references and formulas a step further, We are going to do a couple of things here, but the topic that we are going to get into is a difference between our two reference types, because we actually have two different types of ways or methods that we can use to reference cells inside of an Excel worksheet, We have what is called relative and absolute.
So, the first one a relative reference versus the second one is an absolute reference or I have also heard it called a fixed reference. So, what is the difference?
Let us go ahead and delete out the formulas that you just create, I am going to delete them all except the first one The January total. So, let us delete out all that hard work you just did.
However, it is going to be I am going to show you even simpler ways to do this, but again building these key concepts.
So, our first formula if I get them on a double click at cell B8 we have four-cell references (B4, B5, B6, B7). We are just adding them up, Adding them up giving us a total sum.
These reference types these are called a relative reference. What is a relative reference?
A relative reference means from this location you are going to get cells B1 B2 B3 B4. You are going to get these four cells right here right B7 cell. Now let us take this step further.
I want to get February’s total but this time instead of the B cells it should sum up the C cells.
To copy: press Ctrl + C at cell B8, then I go over to the C8 and paste it in Ctrl+V to paste.
Now I have got my total for February.
How did it know which cells to sum up? It knew because it was a relative reference the formulas is same, if I give it a double click we still got the equal sign we still get the plus side but the column changed it is now C4,C5,C6, C7.
So now we are just saying copy that from B8 to C8.
I am going to copy this one Ctrl+C over to the empty cell for March.
It performs the same formula but relative to its location. I am just in a new location new cell right but it is always five cells up that is a relative reference.
Or whatever direction.
You do that again copy, paste relative references for cell E4,E5,E6,E7,E8.
You have got consistency in the layout of your document.
Saves so much time and this is relative references.
Now, remember I mentioned that there are two different types of references that was relative and that is what we have got by default, but you also have what is called an absolute reference.
I am going to hop over to the F column F three and I am going to tape in Percent and hit Enter key, So now what I want to do here is I want to perform a calculation. We are going to do some division here that will tell me what percentage each of my bills is of my grand total.
Still a formula you start all your formulas with the same character and say equal “=” then I am going to say E for this time I am actually just going to click on E for at that puts it in F4 for me so equals E for divide and I mean to use the backslash as the division character and then I am going to go grab E9. So, I am taking E four and I am going to divide that with E9, backslash forward slash.
Looking at your number pad, that is actually your forward slash.
You want to use the one off your number pad if you do not have a phone number pad then you are going to look at your keyboard and it is with the question mark key. So, you want that slash right there that is your forehead slash right backslash for it slash. We want the forward slash, so E4 Divide that by E9 then hit Enter key.
So do not give me exactly what I am looking for but it gave me a percentage. It decimal percentage so roughly seventy two percent. seventy percent of my grand total goes to rent.
Now what type of reference is this, You get that double click, you see E4 E8 these are relative references. Now I want to do that exact same thing but for a phone and then I want to do it for food and then I want to do it for candy and so on. I jump up I grab that formula, I copy Ctrl+ C, I dropped down a cell I pasted in Ctrl+V.
What is that? Slash zero, exclamation mark, what went wrong?
What is going on, well remember there is two different types of references, here We are using a relative reference,
From F4 we are going to go one cell to the left that is E4 and then we are going to go four cells down and we are going to divide with E9.
So now I copy that down and I got an error. Let us give F5 a double click (or hit F2), what is going on here.
From F5 we went one cell to the left, and then we went four cells down because the first one went four cells, so when we copied it down it still went four cells.
This time we need it to go one cell to the left but then always go to E8, that should not change.
So this is where the absolute reference comes into play, I am going to go back to my first formula at cell F4 and I am going to make a change to the E9 reference, I am going to make it an absolute, So I am going to put a dollar sign before the column reference E and we put a dollar sign before the reference 9 ($E$9) (or hit F4 on keyboard), That makes E9 that cell absolute. then hit Enter key.
Note: You can hit F4 to quick change the absolute reference address of the cell.
I am just going to copy that Ctrl+C and I will paste (Ctrl+V) it in F5,F6,F7,F8 cells.
Grand total of 100 percent.
Now each of these formulas is now referencing E8.
You copy that formula anywhere does it matter where they take that? You got a zero, because it is not doing much math for you, but it is still E9.
So, you got relative references really just based on its position it is going to move in a specific direction. Many cells and you got absolute references that absolutely reference this cell and remember the dollar signs ($), You have got your absolute reference.
So, again just like every time practice try it out jump into your own version create a percent column. Make sure you got all the other formulas in there because you are going to need those and then create your percent and find out what percentage each bill is of your grand total. And remember to streamline it make the absolute reference copy paste.
