- [Instructor] And the first thing that you'll notice
is that it calls it Untitled Spreadsheet.
So we don't like that,
we want to give it a more specific name.
And when you're naming a file,
just use as many specific words as you can,
because you can search for it later.
It'll make it easier to find if you name it well.
So let's name this Kitchen Inventory,
and maybe it's our Kitchen Inventory Tracker,
so let's add that word in there too.
Percenter in Google sheets is always automatically saving,
so I don't have to go to file and save.
Once I hit enter, that name is saved
and everything else that I do as we go along,
it'll be automatically saved as well.
I know I named the file,
but let's also name the little table in here.
So let's say that what we're going to do,
this is our Pantry Inventory
and hit enter when you're done.
And we're going to end up just adding a table of inventory
that takes up about this range here that I'm highlighting
with my mouse and we'll just start
in the upper left-hand corner,
and we designate the names of the cells
by saying the column letter first, and then the row number.
So I'm going to skip a row just to make it,
give it a little bit of space.
So we're going to start in A3.
So just click in A3,
the blue rectangle goes around just that cell.
So it means that it's ready for you to start typing.
And our first column is just going to be item number.
It's gonna give us a little count of the number of items.
Let's type that in again
and it just puts this value in the cell, hit enter,
and then you're done there.
And this is just going to be numbers in a sequence, right?
So item number one, item number two,
but you can stop here because you've already given
this spreadsheet enough information
where you can auto fill the rest of the numbers.
So it will recognize a pattern.
If you left click and highlight cell A4
and go down to A5 while still holding your mouse,
and then you let go,
you'll notice there's a little blue square in the bottom
right-hand corner of the range.
If you hover over it, left click and hold your mouse
and drag down.
Go down as far as you wanna have your inventory items,
we'll just say, we're going to do four of them and let go,
and it guessed the rest of the sequence.
So one, two, spreadsheet guessed three and four.
So you're already saving time
as opposed to just writing this out.
Now let's do another column.
We'll just say this is the items
and you're looking in your pantry.
So you have things that are non-perishable, right?
You have barley, you have let's say crackers,
beans, maybe some rice.
All right, so let's get into some numbers here.
So let's go to C3 and we're going to type quantity.
You can shorten it.
As long as you know what it means, that's fine.
And for barley, we'll say two for crackers,
three beans, two rice, five.
You count that pretty quickly.
That's what's in your pantry
and now we'll say two to what, right?
So what's the unit here. So we'll say unit of measure.
And we'll say for the barley it's bags,
for the crackers, it's boxes,
for the beans, it's probably a bag,
and for the rice, we'll say it's the instant rice.
So that's a box too.
All right, so we're getting there, moving right along.
Let's say you want to figure out the value
of your inventory, so we need a price.
Let's say it's a price per unit.
So for the barley, be a price for bag,
we'll say a dollar 99, crackers 2.99.
Obviously it doesn't matter what you put in here,
put any number in.
We'll say the beans are 2.40
and you'll notice something happened here.
So when I put in 2.40,
it decided that I didn't need the last zero,
because it didn't add anything to the number,
2.4 is the same as 2.40.
We're going to come back in and style the numbers
in this column
so that they all have the same amount of decimals.
So let's just put in the last dollar value,
let's say 7.82, expensive rice for some reason.
Let's highlight all of these cells
and then we'll go to the menu bar
and you'll see there should be a currency sign for you.
So if, let's just go to file real quick
and your spreadsheet settings.
Mine's set that I'm in the United States, okay?
So mine's going to default to dollars.
We'll cancel out of there.
If you are somewhere else,
you might see another currency sign here,
but I'm going to left click on the dollar.
And that the two things, one,
it put a dollar sign before each value,
but it also forced two decimal places.
So all my numbers were lined up now,
they're a little bit easier to see.
All right, now we're going to do our first formula.
We'll come into F3 and we'll say total price.
And I'm thinking that total price should be the quantity
times the price per unit.
So whenever you start a formula in a spreadsheet,
you always start it with the equal sign
that tells Google sheets that you're going to write in
something that's then going to produce a value.
So what you type isn't going to be what shows,
the result of what you type is going to be what shows.
And then when you look right to the right
of that equal sign, there's a little gray underline,
that's saying it's waiting for you to type.
It knows you're not done yet,
because all you have is the equal sign.
So we want to multiply the quantity
times the price per unit.
The quantity in this case is in cell,
so let's go over here with our mouse.
I'm not clicking on anything.
C4, right? So you just type out C4.
The case doesn't matter.
I like to make a capitalize
just because it's usually that way in documentation,
but you can not capitalize it if you don't care
and then multiplied by, so you use a little asterisk
above the eight, so I do Shift eight for the multiply.
You don't type an X, just needs a little asterisk.
And then E4.
So if you look, both of these cells have been highlighted
and they've been highlighted in the corresponding color.
So C4 in the formula is a burnt orange
and that's the color of the border
that goes around the value and C4.
In E4 is a purple and this has been highlighted too.
Let's go back to C4.
You can also see the cell that you've actively clicked in
with your mouse in the formula.
Also has the background highlighted
in a corresponding color.
This formulas already been calculated.
See the blue font here is saying 3 98.
And you've also lost the little gray bracket here.
So it's telling you, Hey, it's fine if you press enter now.
Know that the formula is done and you'll get a value.
So let's press enter, 3.98.
So it doesn't share the formula anymore. Shows a result.
If you click back into F4 and you look in the formula bar,
the formula's still there, or you can double click
and see that the formula's still there.
You can change it if you want to,
but it's not going to show you that formula
unless you're in that cell actively.
All right, so now we have a total price
for the barley.
Next thing that we wanna do, we want to calculate
that total price for all of these items
and this is a very powerful thing that Spreadsheets can do
that saves tones of time.
So when you learn to use them well,
you use all of these tricks.
So the one thing that we want to do here,
let's go back into the cell, is that we want to drag this
down, similar to how we did the item numbers.
So let's left click, drag it down
and let's see what happened here.
So let's go down to the bottom.
39.10 is the result of multiplying five times 7.82
so Google Sheets is smart enough that it increments
the cell references down every time,
so once you go down, it would say, C5 times C5.
You put it down again, C6 times E6.
It adjusted those formulas for you automatically
so whether or not there were two or 200,
if you drag them down, all the cell references adapt.
Let's do some more exploration on this data
and for that you could use, ironically,
probably not ironically,
going down in the lower right hand corner,
there's a button that says explore.
If you left click on that, then you get all these options
where the AI and Sheets in trying to be smart enough
to guess some of the analysis that you want to do,
and then as you hover over each one,
it's highlighting the data that it's working with.
So let's go down to this pi chart.
You see this is analysis of the quantity
of the items in your pantry.
Maybe a little bit overkill to do a pi chart
about your pantry, but let's left click
and drag this onto here.
There we go, we have a lot of rice.
If you hover over it, it's showing the five units
and the 41.7%.
Go in the upper right hand corner
and you can edit this chart, make the values
whatever you want.
So this could be pretty handy,
but right now, I just wanted to show that to you
so I'm going to click and just do delete chart.
And then next thing that we wanna do,
you can do it just from this explore bar,
but I'm going to close this
so you learn the other way to do it.
We're going to just format the data
so it's a little bit easier to see what's going on.
So we'll go to format and this table of data
will look better if we just do alternating colors.
Left click on alternating colors,
let's pick whatever color that we want.
Let's say we wanted to be this orange color
and pay attention here to the style options.
So it does have a header and what it does
is it makes the header a color that stands out.
So you can see the difference between the labels
of the columns and the actual data themselves,
and if you had a footer, you could left click on that.
We don't in this case, but you see it made the bottom
a different color.
Actually, we should do something here.
So we're going to add some totals of the columns.
So we will have a footer, so let's just expand the range.
Let's make it for F7 to F8.
Click outta there and then we do have a footer,
so we'll keep that option there.
So let's just left click on done.
Click outta there. Our tables looking better.
Next thing you can do is you can customize
the width of the columns,
and the easiest way to do that, let's select all of them
and if you go to one of the separators between the columns,
you'll see that the mouse changes into a two sided arrow,
here, let's get that again, there it is.
If you double click, it automatically sizes each column
to the width of the widest piece of data in there.
It made it probably fit better,
but you will also notice if you have something in cell A1
that doesn't really belong to the table,
Sheets doesn't know that and it made this wide enough
to accommodate the title of the table,
so let's just left click and hold onto this and drag it
over to the left a little bit more,
and that fits better.
You can also change the height of the rows.
So if I go in between three and four,
left click my mouse, it's a similar process.
And I'll drag this down to the next row,
so I'm essentially doubling the height of this row.
I'll let go.
It's not apparent why it did that right away,
but why I did do that, let's highlight all of the header.
It will tell Sheets, when it gets to the end of the line,
do text wrapping.
And then if I make these columns a little bit thinner again,
it's going to wrap the text.
So we'll do that in price per unit also.
It's where you can just make it a little bit tighter
if you want.
I'd also like my headers to be more apparent
that they're headers, so I'm gonna make them bold
and I'm going to align them in the center
and then we just have to fix one thing here,
so quantity got too big and it wrapped,
so let's just double click on that again.
All right, one more thing to style the table.
Promise, just one more thing, here we go.
So, I wanna have a row at the bottom
that just makes it more evident we're at the end
of the table.
So if you go up to borders, left click on that,
we'll put a bottom border on this part.
It's not an underline, so it's not going under
the actual text, it's going at the bottom
of the entire cell on each one.
All right, so let's say that we want to total up
the number of items in our inventory.
So we're going to, there's a few different ways to do this,
well one that you could do, and you might do this
if you're a real beginner in Spreadsheets,
you just look at this, you say,
oh that's two plus three plus two plus five,
and this is great because it's like a built in calculator
in my Spreadsheet.
If I hit enter, I get the right value.
Well that is true, you get the right value,
but if you change something, let's say we changed to two
to three, it doesn't update the value.
It updated the multiplication over here, the total price
because this is just doing the cell reference,
but it didn't update this formula.
So that just shows you should always dupe cell references
instead of values if you can,
even if you can see the means lease.
So we would change this to,
C4 and I'm not typing out C4 this time,
I'm just left clicking on that cell reference
with my mouse, plus C5 plus C6 plus C7.
So this is a much easier way and it's dynamic
and we're going to come back in a minute
and make this even a higher level function
that it just, if you add an entire row later,
we're not there yet, so let's just use this function
as it is, and then we'll act like we came in
and we found something else in our inventory,
so we're going to add a line.
So I'm going to right click on number seven here
and I get this right click menu of options
and one of them is insert one above.
So I'm going to insert an entire row
and let's just add something in here.
We'll say, I found potatoes.
Two and potatoes are cheap these days.
We're going to copy one of the formulas
in the total price column, come down and paste it.
So this has been added in, but if we come back
to our formula where we're adding everything together
and if I double click in the cell.
You can see that it didn't automatically add in the C7,
but almost every time, if you're summing an entire column,
you want all the values,
even if you add or delete something.
So that's, I think, a good way to show that,
yeah, you can build a formula this way,
but the best way to do something like a sum,
and this would work for other things too
like a count or an average,
is to actually use a built in function
that's made for doing this in a spreadsheet.
So let's delete this.
Let's go to our entire list of functions,
so if you go up to the tool bar,
you left click on this, it says functions,
you'll see all of these hundreds of functions
that are available for you,
but by far the most common ones are just listed at the top
and we want sum, so let's left click on sum.
Come down here, you get this helper text that pops up
once you type in the right function name.
In this case, we did it for the menus
but you could also just type in SUM
and then the parenthesis,
and these parenthesis are waiting for data.
So you'll see that same gray bracket underneath
that's saying, go ahead and tell me what the formula is.
So it starts at C4 and we'll introduce the next concept here
that makes Spreadsheets really powerful
is that we're going to do a cell range,
not just a list of cells.
So we're not gonna say C4, C5, C6,
we're going to say, C4 and then a colon
and then extend the rage to C8.
And if you hit enter, you get the right sum, you get 15,
but also if you added something else in here,
so let' just insert one above
and we'll just put a big number here to see.
It's going to pick that value up.
So this range extended.
We go back into the range, it goes down to C9 now
and it includes everything in between.
So using cell ranges instead of a series of cell references,
if they're all in a row, it's definitely the best practice.
Try to use the cell range.
And the last thing that we're going to do,
well let's get rid of that row first.
The last thing that we want to do is,
let's say that we want to sum up the price.
All you need to do is copy the sum function in C9,
so I just did Control C or you could just go to edit, copy,
and go into F9 and we'll do paste.
Edit, paste, and now that sum function
automatically knows to sum what's above it.
So double click in it, it's picking up these values
and you did not have to retype that.
So you'll notice a lot of this centers around
getting the cell references right.
So this next video, we're going to talk
about different types of cell references.
So we'll have the regular cell references
that we just talked about, but then a few other types
that you'll use pretty quickly
once you start using Spreadsheets
and I'll see you in that next video.