When you're using Google Sheets, a very basic concept that you use all the time
when you're working with data is cell references. We will go through four
different types of cell references. We will start with the most basic and work our way
through to referencing cells and other files. The most basic type of
cell reference is called a relative cell reference. It's the type
that you just type right in. In this case, it's going to be C5. You use
uppercase. That's the standard syntax that you are supposed to use, but
lowercase works fine too. In this case, we're going to do =C5-D5. S few things
to keep in mind here. You always type the column letter first and then the row
number. So, we wanted column C row 5. Then we're just going to subtract D5
which is the expenses. We're going to try to calculate operating income.
You would say these are two relative cell references. I'm going to press
ENTER. You will see why they're relative in this next step. That did straight
subtraction. But, if you copy it down to these next two rows...and to do that I'm
going to go in the lower right hand corner where you see a little blue
square and my cursor turns into a plus sign. I'm going to hold my left mouse key
down and drag. That's going to copy it down. Now you could also double
click on that square and it will copy it down as far as the data on the left
goes. It's kind of smart in that way. I'm going to let go and here's what I mean
by these being relative references. When you look back and cell E5, it was C5-D5.
When you copied it down it became C6-D6. Without those change,
spreadsheets would work way differently. Those being smart enough to increment
makes spreadsheets far easier to work with. That's relative cell references
in their most basic form. Now, this next type of cell reference is called a fixed
cell reference and I will show you where you might want something like that
in this next example. In this case, to calculate the tax, you want cell E5 and
then you want to multiply it by D2 which is where the tax rate is and it works
fine. There, that's the right amount. I know it's a negative tax, but you had a
loss. If I copy that formula down to F6 and F7, it stops working and even gets
worse. First it gives zero and then it gives an error. What is happening here is
that it thinks these are both relative cell references because that's the way
you typed them in. If I look at F6, it incremented D2 down 1. Because you move
the formula down 1, you have to tell Google Sheets that D2
shouldn't move and how you specify that as you put a little
$ before the 2. That's a fixed cell reference. If the column were
moving. If you were copying these formulas from left to right, you could
fix the column as well. But it doesn't matter
because the columns not changing. So, let's take that out for now to leave it
clean. Let's copy that formula down. Just so we're
consistent, let's go back into F5 and make that fixed as well even though
that's not causing a problem until you go down. Now we have the same formula in
all those cells. Let's just go ahead and fill in the net income because we need
it for the next step. I'll do that. I'll copy that down with the plus sign.
I'm going to double click, It copies it down but it stops here because there's no
numbers to the left. We have a total income of $29,000. In this example I
have other income. It's on this other sheet and I want to pull in the income
from there. I can do a cell reference. What you do is start it with
an = like you always do and then the easiest way is just take your mouse,
go over to the other income, and select the cell. I'm going to tap C6. Over
here, on the right, it shows you what's in the cell on the other sheet. It's a
little preview window. That's what you want.
Hit enter and it brings over that 29,000. If you wanted to automate
this, or type it in, you have these little accent marks. They're
like a single quote, and then the name of the sheet, and then you close it off with
another accent. Then, you have to put an !, and then the cell
reference. We did all that in there and you're good. The last type of
reference that we're going to do is a reference to another file. Since
we're using Google Sheets, it's all online. To find the file we're actually
going to use the address or URL to it. I'll show you how to get that. First,
you're going to start it with = and the function that you use is
IMPORTRANGE even though we're just picking up one cell, it's a called a
range. If you look at this syntax, Google Sheets is telling you
an important thing here. Don't forget the quotes. If you don't have the quotes, it
won't work. We're gonna put in a quote and then I'm going to go out a full screen
on my browser. I have this other file open. To get the URL, go to the file that
you're linking to, click the share button in the upper right hand corner, and use
this URL that it gives you. I just set this to anyone in my organization can
edit. It should probably work with whatever setting you have there as long
as it you own both files. I copied that URL. Let's look real quickly at the
file. This cell that we want is in D10 here. Remember that. Go back to the link.
We're gonna to go into the formula bar right now to paste this. Close it off
with a quote too. Do a comma. Now, the next input that it wants is the
cell reference. As long as it's on the first sheet in that file, you don't need
the name of that sheet. If you do need a name of the sheet, just do it the way
that we did a lot cell reference. You see here, where I'm moving my mouse. The
other quirk here is that it needs quotes. You usually don't need a quote on cell
reference so it's easy to forget it when you're doing this. Let's
surround it in quotes. Remember, we want it at D10. Let's do D10, close it off
with quotes, and a parenthesis. Now, if this is the first time you're doing this. When you
hit enter, it's going to ask for your permission. But, I've done this before
when I was trying to get this little video together so it's not going to ask me.
It's going to take a second think about it. Then it pulled it in. We can change
that format if you want. A lot of times you can't tell what format it's
going to come over in until you see it. Just get that dollar sign off. Let's take the
decimal points away. Now everything looks real nice. That's it. You have
relative, you have fixed, you have to another sheet, and then you have to
another file. I hope that was helpful to you. If you like this tutorial and you
want to watch others just like it, please subscribe to my channel in the lower
right hand corner. Thanks, bye!