Thanks for reading this. This is our second Facebook live session and if you have missed the first one we created three weeks ago, you can simply go to our website and visit us there to see the first Facebook live session that we have. So all you have to do is go to 365 careers.
go to live sessions and click on previous live sessions where you’ll be able to see the live sessions that we have had so far right.
So here is the first post how to be fast in Excel it is really interesting to us. Right so this is the video, it is also in our YouTube channel youtube.com/ 365 careers. If you haven’t still subscribed feel free to check it out it’s really amazing. This is the first Facebook live session that we held a few weeks ago.
Right about today’s session let’s go in Excel and start all over again today’s session.
Fixing cell references in Excel.
Why is it important and how can it help you when you have to work with formulas in Excel?
Well, Excel is a spreadsheet tool, and every sheet in Excel is divided in cells. They have a column reference and row reference.
The way you work with formulas is you have these references as you can see here and this is how Excel shows you what would you’ve, which cells are included in your formula.
Please, be aware that whenever you work with text in Excel the input when you paste different cells is always the same regardless of where you’re pasting it. This is the case for both numbers and text. ABC is ABC whenever I paste it downwards and to the right.
365 is always 365.
This isn’t the case when we work with formulas. They have references, and these references change with respect to the direction we are going in.
So, as you can see this formula here sums H11 and H12, and we when we move to the right it actually sums I11 and I12.
So, whenever we move in different directions in Excel. we are actually changing the cells we are we are taking into consideration. This is the case if we don’t fix cell references.
How do we do that? How we fix the cell references in Excel?
Well, there are two ways to do it. The first one is to add a dollar sign manually in front of a cell reference. and the second one is to use the F4 shortcut. Let me show you what I mean.
Here we have a table which shows us the unit sold of a product in January, February, March, April, May, June. And we also have the price of product an in all these months. Let’s assume that this was the price of the product an in all six months. To calculate the revenue that product A accounted for, all we have to do is multiply the unit sold by the price of product A, right? So, I’m going to multiply the price of product A by 340. In terms of cell references we have C21, which is the price of product a multiplied by C16, the number of units sold in January.
If I copy this function and I move to the right, we’re going to see that we obtain 0. And we obtain 0 simply because cell references that are not fixed change their location. And in this case we wanted our price of Product A cell reference to stay where it was. We wanted C21 to remain fixed, and not move to D21. How do we do that?
Well I’m going to have to insert a dollar sign ($) right in front of C, the letter C which indicates the column reference. And if I do that and then multiply and then copy and paste the formula it works.
Ok. Another way to do that is, I’m going to delete the dollar sign ($), asking you to press F4, which is the shortcut for fixing cell references. The first time when you press it both the column and the row reference will be fixed. The second time, you press it only the row reference will be fixed. If you press it for the third time, the column references only will be fixed.
By the way I am opening the functions that I’ve pasted with the F2 key.
This is an amazing feature, which is very useful in all financial modelling, every time you paste the function you want to use, press F2 to check whether you’ve worked correctly, whether the right inputs have been included.
If when you press F2, you don’t see the inputs, which have been taken into consideration for the calculation of a formula, you will have to do the following.
Go to ‘File’, ‘Options’, and then go to ‘Advanced Options’ and make sure that allow editing directly in cells is unchecked.
If it is unchecked you will be able to see what’s included in a specific calculation. That’s excellent!
Okay so why do we fix cell references?
Isn’t it easier to create functions manually and to change cell references manually?
Well, not all the time and actually most of the time this is quite difficult to do.
In the table we have here, we have the cost per unit for a given item and we have its respective volume of sales. So, if we multiply volume by cost per unit, we will of course get the overall cost right. But I would need to calculate this for all cells that are part of this table, and it contains 15,000 rows.
So, whenever I copy this function downwards and I start obtaining errors because the cost per unit cell moves downwards. Therefore. to prevent this from happening, we have to fix its references. Let’s go to the next sheet and see how this is done.
Of course, I’m going to have the formula that we already discussed but this time I’m going to fix its row reference, the row reference of C5 – cost per unit. We are going to paste downwards. Therefore, I would like for the row reference of C5 to stay fixed. I’m going to add a dollar sign right in front of it, and we’ll paste all the way down.
And as you can see it works, it stays fixed because we added the dollar sign.
What about fixing column references? When do we need to do that?
Well, whenever you are multiplying to the right.
So, to do that I’m going to add a dollar sign in front of C this time because we are moving to the right.
When do we need to fix both references?
Well, let’s take a look at this table right here.
We have some products which are product A, product B, product C, and so on and so forth. We also have the amount of revenue that they registered in US dollars, and the cost that was sustained to produce them. So, what if I want to obtain the amount of revenue and costs in Euro? And I have the conversion rate right here in C5.
Well, in this case all I have to do is multiply revenues by the conversion rate, right?
And then I’m going to copy and paste downwards.
To do that, I’m going to fix the row reference.
But I would like to copy this function and paste it to the right. This time my goal is to obtain the cost. The problem is that it will change its column reference if we move to the right and we will make an error. So, yes we are going to need to fix the column reference as well. I’m going to add a dollar sign right in front of the letter C and now I can easily paste for the entire table.
C5 remained fixed in the entire table. Excellent. So this is how fixing of cell references functions, you can do it manually inserting a dollar sign in front of the reference you would like to fix, or instead you can simply use the F4 key to do it with the keyboard.
Let’s explore a function, shall we?
Here we have a source table in which we have data for 2016, of four products and product groups. We have the volume, showing us how much of these products has been sold. So, our task in this case is to create a SUMIF function, which would allow us to see how much of each product group was sold in terms of volumes.
So, I’m going to create a SUMIF function, and I know that isn’t the topic of today’s lesson, but nevertheless, let’s create a function to see how fixing of cell references works in this case.
Ok. So, the first thing I need to add to the semi function is its range.
I can do this in two ways. I can either select the actual range in the table, which in this case is shown as cell references D4:D 3481.
Or, I can simply do something easier and it is to select the entire column D.
So, the criteria is in the table, we are trying to populate.
I’m going to select it without fixing its cell reference for the moment. And finally, I’m going to select volume, which is the measure we would like to sum.
Whenever you choose to select entire columns, you have to make sure of two things, please. So, you have to be certain that there is no other data below the table you’re creating a sum of – sum if function for, and also, you have to make sure that you’re using the entire column for both the range and for the criteria so that they have the same dimension.
Here it is, our SUMIF function. That’s perfect.
Let’s say that I would like to sum also the cost of sales field. Although I’m not sure it really make sense in the in this situation. But let’s say I would like to sum the cost of sales field, ok?
I’m going to copy the function we just created to the right.
To do this properly we’re going to have to fix her references. I’m going to fix the source criteria range. F4 and the entire column is fixed.
Then I’m going to fix the cell reference of B5 which is my criteria. I’m going to add the dollar sign in front of the column B only, because we want to keep the function flexible when we move to the right.
In a second, I’m going to paste it to the right. I would like my criteria to remain being ‘meat’ when I move to the right, but I would like to be able to paste downwards and that’s why I’m not going to fix the row reference.
Here’s how the function works once we have fixed cell references properly.
This is how we fix cell references in Excel. Thanks so much guys, thank you for joining today’s session! I was happy to have you here and looking forward to next time.