A2 Basic 19 Folder Collection
After playing the video, you can click or select the word to look it up in the dictionary.
Loading...
Report Subtitle Errors
There's a couple different ways to create a Google spreadsheet.
You can either do it from scratch, which is what we'll do first, or you can create it
from a template.
Or, you may have a spreadsheet already that you want to get into Google Drive and then
open it in Sheets.
We'll go over that as well.
The way to just create a blank Google Sheet is to start out by going to drive.google.com.
This is Google Drive.
If you don't have an account, a Google account already then you'll have to create one but
let's assume that you have on and go to a blank area that's in the where that your files
and folders are I'm going to right click and Google sheets is one of the options if you
go over to the right you'll see that you can do it from a blank spreadsheet or a template
so if you wanted to do something common like maybe a budget or create an invoice for a
customer there'll be a template for that that'll get you started but right now we're just gonna
do a blank spreadsheet so you'll see it opened a new window with your Google sheets spreadsheet
in it Google Drive is still open in the background and it'll look exactly like this if you're
using Google Chrome as your browser but if you're using another one it'll still do the
multiple tabs it'll be similar the first thing that you probably want to do is name it something
that's meaningful to what it's going to be so we'll let's just assume that you're doing
inventory name an inventory you're ready to go there's a few basics about how to do this
so you're only in one cell at a time if you're entering data and it starts out in a 1 you
reference these by saying the column first and then the row and type what you want in
here maybe this will be the amount and to go down you either hit enter or you hit the
down arrow typically you would just hit enter which always bring you down but sometimes
you may want to go left or right up down and just use arrow keys so right now I'm going
to hit the down arrow and that's done you've entered your first data in a Google spreadsheet
so let's enter a few numbers in here just to show you what you might want to do next
we'll do three four and three and then and then if you want to sum them go to insert
function and it's going to have the most common functions here this is going to be what you're
using a lot of the time and just choose some left click on it and it wants the range of
the values so we're working in a two all the way through a four and you've also could have
just used your mouse left click than a two and dragged it down to a four and now hit
enter and you've done your first spreadsheet function satting up three four and three so
that's how you create a blank spreadsheet it's pretty easy the key is just remember
to go to your Google Drive great it from there give it a good name then you can find it later
I'm going to show you the steps on how to upload an existing spreadsheet so one of the
little tricks that you want to do is you want to go to your settings and make sure convert
uploads is checked if that's not checked it's going to upload it say in this case in a excel
format that's going to sit on your Google Drive in a non-native format so if you put
on Google Drive to use it in sheets just have this checked in it'll convert it for you automatically
now that you want to upload your file go to new and file upload and this is going to look
at the hard drive on your computer I happen to have put mine here so this is the sample
data this is an Excel spreadsheet select it click open and you see in the lower right
hand corner it's uploading one item it has this X here which is saying that it's in Excel
but if you see by the time it gets into my drive it's in Google sheets if you didn't
click that check mark that I showed you a minute ago this would still be an X and you
could view it but it wouldn't be in the native format if I open up sample data it'll open
up in Google Sheets and it's ready to go see but it doesn't always upload perfectly in
this example the header just looks black if I click in say example C one I can see there's
a value in there but what it did was it turned my fonts black and the background black so
let's just go and turn the text color to white and we can see what's happening here so that's
your three ways to create a new sheets make a blank sheet create one from a template or
upload existing data next we're going to go through how to format it and so hang on for
that.
If you're using Google sheets and you have some data that looks like this on the left
and you want to make it apparent that it's a table, use some nice colors, make it obvious
what the headers and the title are, that's what we're going to do in this tutorial.
We're going to start with unformatted data and work our way over to something that looks
like this.
Here we go.
If we do one cell, let's say we wanted to do A1, you hover over it with the mouse and
you left-click one time.
That selects it.
Don't double click.
If you double click, it brings you inside the cell and it thinks that you want to change
the contents of it.
The second way that we're going to select data is we're going to either select the entire
column, and I did that by left clicking on the A, or you can do the entire row.
Let's left-click on the 2 and, while that's highlighted, if you hold down the shift key,
you can go down and it selects more than one at a time.
You can also select the entire sheet by clicking here in the upper left hand corner.
That's everything.
So, when you're looking at this data, the first thing that you want to do is make it
apparent that these are dollars.
We're going to highlight all of those.
I just left clicked in F3.
I held down the shift key and I used the right and down arrows to select this entire range.
I'm going to go up and click the dollar sign in the toolbar.
That formats it as currency but then I want to go to column F because I don't really find
it to be material what the cents are on a particular item.
I kind of want it on the totals because I want to see everything down to the penny.
Let's go ahead and decrease the decimal places.
I want you to see, for example, this is $1.99, but decrease it by 2, oops wrong way.
If I decrease it by 2, it rounds for me.
Google Sheets uses a standard rounding convention and this turns $19.99 into $20.00.
The value in here is still $19.99.
If I double click, it's in there.
We've just changed how it's displayed.
Let's take this sample data.
This is the title of the entire table.
It's not a header for a particular column so we should probably merge these rows.
I left-clicked in A1, I held down the shift key, and I pressed the right arrow.
Or, you can just take your mouse left click, select all these, left click and drag.
Let's merge cells.
It's a little icon here that's merging them so you merge them together.
But, I want it centered horizontally as well so this icon gives you three options.
Center it left, center in the middle center to the right.
Let's put it in the middle and then let's make it bold.
That would be right here and we're gonna make a 24-point so you really see, "hey this is
table with sample data."
Actually, I'm gonna make it a little smaller.
It's kind of annoying.
There we go.
Now what I want to do is a really handy shortcut that was added just a few months ago to Google
Sheets.
I'm going to select what I'm considering to be the table here.
But, if you see I also selected the header which is row 2 and the footer which is row
7.
You'll see why here.
I go to format.
I'm going to say alternating colors.
Why it doesn't just say format it as a table I don't know.
But, alternating colors, and then on the right-hand side it brings up these options.
Now I have a header and it doesn't know that I have a footer you got to tell it that.
I have a footer as well.
You see the row 7 there became darker.
I'm gonna click, and click it again, and then you just choose the color and hit done.
Now when you look at this, it looks more like a table.
It's apparent that these are the types of units that we're looking at.
This is the content, the actual data.
Let's make it more apparent though that these down here are totals.
I selected A6.
I'm going to hold down my control key and select G6.
That enables you to select two individual cells at once, two or more.
What I'm going to do here is put a bottom border on.
I'm not doing an underline.
I'm doing a bottom border and I'm going to explain the difference.
First, I'll let you see it.
There's the two bottom borders.
I didn't do an underline because, if you sort this data for some reason, the underline is
going to move.
But, if you do a bottom border, it should stay with row 6.
I also don't like this font.
Let's use the trick where you select the entire worksheet by clicking in the upper left hand
corner and then we'll drop down the font and we'll change it to Calibri.
I think that it would be better if the header, the content of the cells in the header, was
Center aligned.
Let's click on the 2 to select that entire row and then go to the vertical alignment
which is here.
Line up vertically.
Let's say I want to zoom in 200% on this, but when I do, things are too wide.
So, I'm going make the unit cost without a space in here.
You'll see why in a little bit.
I'm gonna make the unit cost a lot skinnier like this and I going to make the units skinnier.
Then, maybe I could see everything.
Let's make it a little bit more skinny...a little bit more.
What happened here was I made it so skinny that this isn't fitting well.
What I have to do is, or one of the options that I can do is, to rotate it.
You can do this with all your headers if you want.
You could select row 2 and rotate.
The other option-this unit cost is also running over a little bit.
I'm going to shrink it some more so you can tell.
What I'm doing to shrink this and just changing the column width by hovering over the line
that separates the two.
Holding down the left mouse button and moving left and right.
My other option to make this fit, I could just shrink the font but a fancier option
is to wrap the text, which is this middle option you see.
That put one over the other.
If you don't like how the others are all down at the bottom of the row because now the row
is taller, let's change the horizontal alignment.
That would be, where would that be?
Vertical alignment, excuse me.
Let's change the vertical alignment.
Let's put them in the middle.
Now they're all lined up.
That should pretty much cover the basic things that you can do with your data to format it
and make it look more like a table.
This draws the users eye down to the important parts or up to the important parts like the
header or down to the footer.
Let's go ahead and make these bold.
That way it's more apparent that they're the sum.
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.
We're gonna use this simple table of data here to show you the basic parts of formulas
in spreadsheets, what the different types are called, and how to combine them together
to do most anything you want.
The first formula that we're going to do is we're just going to calculate the order total
and that's going to be the quantity times the price.
If you want to use your spreadsheet just like an old calculator, all you do is just type
the values directly in.
Write 177 times $1.72 and that's gonna get you to 304.44.
But, the problem with doing it this way is that you can't copy this formula down and,
if these values change, this formula doesn't automatically update because it's just hard
coded.
This is probably the most basic type of formula that you can do but it's not very useful.
You don't want any hard coded so let's delete that and let's start over.
Let's type an equal sign.
That starts every formula.
In any formula or function or whatever you do in Google Sheets that's not a value, you
need to start with an equals sign.
Then, it gives you this gray bracket underneath and what that's saying is your formula has
just started.
I need some data.
It's not finished yet so it's waiting for input.
The first cell that we want to give it is B6.
Left-click your mouse in B6 and it's already calculating saying I've got a value, it's
177.
But you're not done yet, right?
You want to multiply that by C6.
Let's grab that value.
You're getting 304.44 which is what you got when you hard keyed but this one's more flexible.
If I hit enter and I go back and I change 1.72 to 1.89, this formula updates.This formula
updates bu it wouldn't if you hard keyed it.
Another big advantage of using formulas is you can copy them down.
So I selected this cell and in the lower right hand corner you get a little square.
If you hover over that with your mouse it turns into a plus sign.
Left-click and drag it down or you can double click too and stop where you want your formulas
to stop and it copies them all down.
It saves you a ton of time.
That's your most basic type of formulas.
Cell references, which is the first line here, combined with operators.
Just multiplying two cell references together.
The second thing that you can do after you hit the equal sign is use a function.
I'm going to show you the COUNT function works.
And again, I hit equal.
It's waiting for something.
That's what the gray bracket is.
I'm going to type in COUNT and I'm gonna do with my caps lock on because you're supposed
to do these things in uppercase if you really want to be proper.
But, you can do it in lowercase to if you want.
So I typed in COUNT.
This is listing all of the functions that are available that start with COUNT.
You can see it was filtering as I type so, if you just have a C in here, it's gonna show
you everything that starts with C. Another way to input this function is to go to Insert
function and this will show you the most common functions.
Actually, COUNT is on here because you will use COUNT all the time.
Let's left-click on that and this put in the two parentheses for me.
Every function has to have an opening in a closing parenthesis and those tell the spreadsheet
when you're starting to give it input and when you're done.
As you start to look at the helper text down here which always pops up after you type in
the name of the formula and you hit the first parenthesis.
It can take a value but it can take other values too if you want.
So this is in brackets.
What that's saying is it's optional.
We're on value one right now and that's highlighted because Sheets is telling you that's where
you are.
If you go down, it also has a little gray triangle which is giving you an explanation
for what value one is and it's just saying give me the range of data that you want me
to count.
I'm gonna do it with my mouse.
You can also just type it in or use the arrow keys.
In this case, I want D6 through D9.
Left-click in D6, hold down the left mouse key, and drag down to D9.
Let off the mouse key and you see it put the range into the function.
This function is done if you want it to be.
Now, there's no little gray bracket here waiting for more data and it's showing you a value
that it's calculated.
Let's go ahead and press Enter.
I've counted four.
We're also going to do a total.
This is going to be another very common function that's going to work the same way and I just
did that one with my arrow keys.
You see if you do these a lot, you get fast at it.
The next concept that I want to show you is stringing functions together for a longer
formula.
You can string them together with other operators.
In this case, we're going to calculate the average.
You would calculate the average by dividing the total by the count, right?
Although I just did these in these two cells, you really could do D12 divided by D13.
That's the average.
You could also kind of homebrew a function here.
You could put the SUM in the numerator and then you could divide it by the COUNT as the
denominator.
Close off the parentheses and type Enter.
It's the same thing.
So, you strung two functions together.
You could also just use the AVERAGE function if you knew that existed an average these
cells.
Close off the parentheses.
Hit the escape key.
Close off the parentheses.
Hit enter.
Same value.
Let's give this a date, right?
If you want to give it today's date just use a popular function called TODAY.
It doesn't need any inputs but it still needs parentheses because it's a function.
Type enter.
There you go.
That's the basics of how to use formulas in Google Sheets.
I have an example spreadsheet here that has some pretty typical data in it.
When you have data like this, a lot of times you just want to put it in a different order
which would be SORT.
Or you only want to show part of it based on some sort of criteria and that would be
FILTER.
I'll show you sort first and then we'll get into filtering a few items.
Keep in mind though, you can do this two main ways.
We're going to sort and filter from the menus first but then I'll show you a new concept
that didn't really exists in previous spreadsheets is that there's functions that can do sort
and filter.
To do those, we'll go down below because they produce a new list that's based on the data
in this list.
All right, what you typically want to do is select the table.
That's gonna make this easier.
Then you go to the Data menu and then do Sort range.
There's shortcuts up here if you just want to sort the entire sheet.
That's not going to know where your header is though so that's gonna sort the header
too.
Maybe that's okay.
You can also just sort a range but we already selected the range so let's just go down to
the Sort range option.
It's going to give you a pop up.
I think it's easier to use.
There's a couple options on here that we can talk through that you're gonna want to change
from time to time and they're not in those shortcuts if you do the other options on the
menu.
It's telling you, look we're looking at sorting A10 through G10 which is what you highlighted
back here behind this box.
But, we did have a header row and I don't want that sorted so put the checkmark there.
It's smart enough to take the names from the header row and put them in this drop-down
list now that you told it it has a header.
In this case we're just gonna want to sort this by region because we want to see what
happened in every region region.
Keep it from A to Z and within those regions were gonna want to sort it by Rep name.
You click sort.
It did not delete any data.
It's showing you the exact same data and you don't have to unsort it to do anything.
You can leave it like this.
If you notice it does it by region alphabetically and then within that region it did it alphabetically
by the name of Rep. That maybe is all you wanted to do in which case all you need is
sort and you're done.
But, if you want to take this a step further by doing some more analysis, let's select
the table again, go to data, and let's try filter.
I turned the filter on and you can see at the top of every column of data, you got this
upside down triangle.
That's letting you know that you can filter that column.
But, there is no filter selected yet.
If you want to see just the pencils that were sold, you want to clear all of these.
Just clicked unclear and just highlight the pencils.
Click OK.
That filtered out everything else in the list.
The data is still there.
As you can see, it goes from 3 to 9 so those rows still exist but they're hidden from view.
One concept here that's important to know is that if I sum these, if I just do E2 to
E3, then you'll get 21 which is these two being added together.
But, if you do the whole range, and again you can see this goes down to row 10, it's
just hidden, it still picks up those values.
327.
They're underneath and your formulas can pick them up if you're doing simple formulas like
SUM and COUNT.
But, I digress.
That's one type of filter.
Reset your filter.
Just click select all so all of them are in play and then do text contains PEN.
That'll pick up pencils, pen set, and pin.
Then, once you have a filter on, you can see that changes from an image of the upside-down
triangle to filter so you know that there's an active filter on here so that tells you
it's probably not showing everything right now and that's true.
It still goes 6, 7, 9.
8 is hidden and 11 is hidden.
While you have a filter on you could still do another sort or another filter but let's
just sort this by Rep. The filter stays.
It's still showing only the things that have the letters PEN in them but this is sorted
so it's not filtered so it didn't turn into a funnel but you could also do another filter
if you wanted.
Let's clear all of them and just so to show Jones and Morgan.
And there you go.
There's two filters applied.
You can apply as many filters as you want.
That's the basics of how to use sort and filter through the menu.
We're going to turn that off.
There's a couple different ways to turn it off.
I'm gonna to turn off filter.
Imagine that this is the data that we started with.
I want to get rid of this row 11 that's highlighted like it's part of the table.
I don't like that.
I'm gonna right-click, delete row.
Now I want to show you the second main way to sort and filter which is using functions.
There's three different functions that we're going to go over.
The first one is SORT.
Hit =SORT.
Open it up with an opening parenthesis which goes on every function and then it knows.
You do the parentheses and it's ready to rock.
Let's give it a range.
In this case, let's do A1 to G10.
Matter of fact, let's do A2 so we don't pick up the header, We want to leave the header
there, to G10.
Let's look at the helper text again.
The second thing it wants is the sort column.
It's, "what do you want me to sort it by?"
Let's do it by the Rep. That's sort column three.
It wants a number, not the name of the header.
We want it to be ascending so leave that as true.
Now you see the next thing in the helper text is brackets which is saying it's not necessary,
it's optional.
So let's end this here.
When I hit enter it's going to write this out to the right and down below it.
So start it where it has room to work.
And they're sorted lists.
You see it got rid of the formatting.
That's alright, you didn't need it.
This is all dynamic so, if you change what's in the original list, it will change down
here.
If you change what's up here it'll change what was output down here.
If you click in one of these columns and hit delete, delete, delete, it doesn't go anywhere
because it's all being driven by the SORT function in A12.
If you were to go in A12 and press delete, it gets rid of everything.
The second function that you do is a FILTER.
You can guess how this is gonna work.
we don't have to go through it in much depth because it's the same concept.
It's going to output a new list that's filtered based on how you want it to be and it's dynamic.
If you change what's above, it'll change what's output and filter it.
It may take some time to study this one.
It's a little bit trickier to use FILTER through a function, I think.
But the last one that's added is a new type of function.
It's called SORTN.
This one kind of deserves its own little tutorial I think.
What it can do is, let's give it the range 10 is, in plain English, it's saying "Hey,
I want the top 10 salespeople from here.
I want the last five dates.
It can sort and return N number of values from your data set.
We'll try one here.
I won't get into it too much.
Let's try A3.
The top three.
That's all you have to do.
The other parameters are optional.
There we go.
That's the first three sales.
If you want to learn more about these functions or about the SORTN function in particular,
you can subscribe to my channel.
I do lots of tutorials on little things, mostly in Google Sheets with some other things as
well.
I hope you enjoyed this video and that's all.
Thanks!
    You must  Log in  to get the function.
Tip: Click on the article or the word in the subtitle to get translation quickly!

Loading…

Google Sheets - Full Tutorial

19 Folder Collection
林宜悉 published on February 26, 2020
More Recommended Videos
  1. 1. Search word

    Select word on the caption to look it up in the dictionary!

  2. 2. Repeat single sentence

    Repeat the same sentence to enhance listening ability

  3. 3. Shortcut

    Shortcut!

  4. 4. Close caption

    Close the English caption

  5. 5. Embed

    Embed the video to your blog

  6. 6. Unfold

    Hide right panel

  1. Listening Quiz

    Listening Quiz!

  1. Click to open your notebook

  1. UrbanDictionary 俚語字典整合查詢。一般字典查詢不到你滿意的解譯,不妨使用「俚語字典」,或許會讓你有滿意的答案喔