.png){kind=icon}
In this Excel tutorial, I'm continuing with Excel basics. Now, notice here I have a spreadsheet that says, I get 38 and a half miles per gallon and I have variables, various amount of miles that I drive per year and the cost of gas is varying from $2.25 to $3. If I want to calculate, how much does my gas cost for the whole year based on, you know, this combination of miles and cost of gas plus, you know, take into account my miles per gallon, how could I calculate that? Now, you could simply go to the cell and as we’ve talked about before, when you're putting in a formula, you just put an equal sign and to calculate this this would equal my miles per year which is A5 divided by A1 times B4.
And you can see it cost me $292 per year, if I have that combination of miles and cost of gas. Now, in Excel, the reason you're using this is to make calculations easier. So, one of the tricks that you can do in Excel is when you get a formula in like this, notice how my cursor changes in the corner here. When you get the cross here like that, you can pull down that same formula down a column, as far as you want, or you can grab it and go across. But notice what happens when I just grab it and go to the next corner. Notice it…my original formula was A5 divided by A1 times B4, but notice when you drag it down a column, the column stays the same but notice that my numbers incremented to 6, 2, and 5. I don't want that to do that.
Somethings need to be locked. So let me go back to this formula and make some changes and show you how you can get this to work, so you can do the pull down and the pull across. Okay, now, the 292, I want the A column to stay in A but I wanted to increment by A5, A6 or A7, so what I can do is I can lock the column, I could simply highlight this and hit the F4. Now, I want to do this until I get just a dollar sign in front of the A. What that means is I'm locking on column A, but I'm going to allow the cell 5 to increment to 6 or 7.
A1, I don't want to increment at all, I wanted to stay A1 permanently. So if you highlight it and hit F4 notice that I get a dollar sign in from the A and a dollar sign in front of the 1, that means no matter what I do, it will always lock to A1. For B4, if I want to drag it across, I wanted to keep it on 4, but I wanted to increment the column, so in this one, I can hit F4 and get it to where it locks, row 4 but let’s increment to 2.25, 2.50, 2.75 or 3. Now, when I do this, I get 292. Now watch what happens when I grab the corner and go down. Notice that it appropriately took care of keeping the A allowing it to increment to 6, it kept the A1 locked and it kept the B4 locked.
Now, if I go across watch what happens. When I do it this way, notice when I came here, it kept the 5000 miles which is A5, it kept the A1 but allowed it to increment keep row 4 but increment over to C4 which gave it the change in gas. Now, you can just play here and pull these across. You can have it down like this and grab the whole corner and…and oop! Hang on. Did something wrong there.
Got my corner here, notice I drag it across, did you see how fast I just did these calculations? So this is a great tool to try out, it's just called, t's basically locking, you can lock the cell completely down to just one cell or you can lock a column and let the row numbers increment or you can lock a row and let the columns increment. Try it out it's a great way to speed up calculations.