Google Sheets Advanced Tutorial
Google Sheets Advanced Tutorial
Google Sheets Advanced Tutorial
2023 Update
Exercise Files: https://https://learnitanytime.com/p/exercise…
Who it’s for: Anyone looking to gather, format, and interpret data in a spreadsheet.
What it is: Google Sheets is Google’s free, web-based spreadsheet application that mirrors the magic of Microsoft Excel, with some standout features and key differences as well.
What you’ll learn: First, students will be shown how to navigate the app. Then, they’ll learn the basics like creating and sharing spreadsheets. Next, they’ll learn how to calculate data with functions and formulas, as well as how to filter and sort all their data. Finally, users will get some hands-on time with popular features like Pivot Tables, AutoFill, VLOOKUP, Macros, real-time collaboration, and more.
Join Learnit Anytime for ad-free training, exams, certificates, and exclusive content:
https://www.learnitanytime.com
For Private Group Trainings please visit:
https://www.learnit.com/private-group…
Join Offsite for our online community of teachers and peers:
https://www.offsitebylearnit.com
Manuals: https://bit.ly/30xZb59
Username: manuals
Password: password
Start 0:00
Introduction 0:09
List, Format, and Sort 0:46
Multi-Column Sort 3:13
Filters 5:21
Conditional Filters and Filtered Views 8:18
Creating Charts 11:57
Editing Charts 14:49
Removing and Deleting Chart Data 18:04
Sparklines 20:30
Formatting with Alternating Row Colors 23:09
Pivot Tables 24:22
Pivot Charts 28:36
Named Ranges 31:16
IF Functions 36:05
Nested Functions 39:10
VLOOKUP 42:15
SUMIF and AVERAGEIF 45:43
SUMIFS 49:42
AVERAGEIFS 52:19
IFERROR 54:49
LEFT and RIGHT 57:12
CONCATENATE 59:49
Sharing Files 1:01:53
Real-Time Authoring 1:03:38
Google Sheets Add-Ons 1:05:37
Introduction to Google Apps Scripts 1:07:32
Conclusion 1:09:50
#googlesheets #googlesheetstutorial #googleworkspace #googleworkspacetutorial
(C) 2023 Learnit, Inc.
Any illegal reproduction of this content will result in immediate legal action.
Content
0.41 -> Elissa Smith: Welcome
to Learn It Training.
1.98 -> The exercise files for today's course are
located in the video description below.
6.22 -> Don't forget to like and subscribe.
10.14 -> Hi everyone.
10.92 -> Welcome to our Google Sheets
Fundamentals Course Part 2.
14.32 -> This course is for anyone who's looking
to take their knowledge of Google Sheets
17.98 -> to the next level by learning to manage
their sheets with features like sorting,
22.64 -> filtering, inserting charts, and even
building pivot tables and pivot charts.
28.03 -> We're also going to learn advanced
functions, like VLOOKUP, Some if, and
32.47 -> even average, if S we're also going to
learn how to share our Google sheets with
36.87 -> others by using real time collaboration.
40.06 -> We'll also look at permissions within
Google sheets so that you can control how
43.7 -> others can collaborate with you in them.
48.97 -> Hi everybody.
49.72 -> Let's talk about the list format
and sorting in Google sheets.
53.685 -> Now, first of all, I have
a practice file open.
55.775 -> It's called Trip, Sort, and Filter.
58.115 -> It's in with the practice files, so
remember, go to the course description
61.705 -> and look for the link to access any
practice files that go with this course.
65.734 -> Now, first of all, what is a list format?
68.385 -> Excel also uses this same format for
table styles and other analytics.
72.475 -> tools.
73.245 -> In Google Sheets, a list format is
where you first of all provide column
76.965 -> headers at the top of each column.
79.305 -> These help to identify the data below.
81.745 -> The other important detail for
a list format is that there
84.565 -> are no blank rows in your data.
86.835 -> The reason blank rows are bad is
they're like hitting a pothole
89.895 -> when you're driving a car.
91.315 -> The analytics can't work.
93.155 -> So, for example, the sorting and filtering
will get stuck when you hit a blank row.
96.995 -> Now, blank cells are okay.
99.025 -> If you look at the current file that
I'm in, I have no entire blank rows.
103.625 -> And all my columns are the
consistently same kind of information.
107.384 -> So, with this list format in mind,
let's go ahead and look at how to
110.175 -> perform a simple sort in Google Sheets.
112.705 -> I'm going to come in and
actually highlight the range
114.775 -> that I'd like to sort by.
116.165 -> You don't always have to do this,
but I recommend it, especially if
119.545 -> your data isn't quite consistent.
122.265 -> Once you get all the data highlighted,
mine's about 200 rows down, then
126.495 -> what I'm going to do is go back up,
And go to the data drop down menu.
130.345 -> This is where your
sorting tools are located.
133.025 -> You'll notice that you have a sort
sheet option and a sort range.
136.615 -> Now because I have a range selected,
which is on purpose, it helps
139.874 -> to make my sort more specific.
141.835 -> I'm going to go with this second option.
144.155 -> You'll come in and you'll notice
that I have either the ability
146.644 -> to sort the range in column A.
149.005 -> Or I can come in and go down to
advanced range sorting options.
153.545 -> When you really want to control your
sort, this is what I prefer to do.
157.655 -> In this box, I'm going to come in and
tell my range that it has a header row.
161.915 -> Those are the column
headers I mentioned earlier.
164.395 -> And you'll notice that when I do this, it
helps me to identify what my columns are.
169.204 -> In this case, I'm going to pick last name.
171.715 -> I can pick my sorter.
173.095 -> Remember, there are two.
174.495 -> A to Z is ascending, Z to A is descending.
177.575 -> And then I'm going to click on sort.
179.435 -> If I go up and look at my last name
column, you're going to see that
182.565 -> now it is alphabetized by last name.
185.295 -> So remember, we always want
you to try these things out.
187.755 -> Go into Google Sheets, try
selecting a range, and do your
191.185 -> own sorting in Google Sheets.
195.965 -> Hi, I want to learn how to do a two
column sort inside my Google Sheets file.
202.015 -> Right now you'll see that column C, my
last names, are sorted by last name.
206.694 -> But my problem is if I try to
go in and do another sort, this
209.984 -> first level sort will be gone.
212.445 -> So how do I do a multiple
level sort in Google Sheets?
216.185 -> Again, I'm going to come in and highlight
the data that I want to sort by.
219.425 -> This helps Google Sheets to focus
on the area that I'll be sorting.
223.414 -> Also remember, I have the
list format in my data.
227.185 -> I'm going to come back up to the top
just so we can really see how this works.
231.105 -> Once I get back up to the top of my
Google Sheets, I'm going to come back to
235.29 -> the data drop down menu and come to sort
range because right now I have a range of
240.48 -> cells selected not just an entire sheet.
243.619 -> Then I'm going to come down to
advanced range sorting options.
248.549 -> Now from here I can see my first
level sort but I want to go ahead
252.38 -> and update this so that it has both
the sorts I want and it's correct.
256.41 -> I'm going to check off that
my data has a header row.
259.535 -> I'm going to come in and select the
last name column for my first level
263.265 -> sort in ascending order, A to Z.
266.195 -> Then I want to select another sort column,
this is add another sort column, and then
272.275 -> I want to sort by the amount of tickets,
and actually in this case it's the number
277.095 -> of tickets sold, in A to Z order as well.
280.054 -> So there will be a first level sort, but
then when there's a tie in that first
283.754 -> level, it will go to the second level.
285.795 -> Now to actually perform this sort,
I'm going to click on the sort button.
290.095 -> Now what we're going to see is there
are last names that are sorted.
293.295 -> That's my first level sort.
295.275 -> But then if I come in and notice,
after there's a tie in the first
300.465 -> level sort, then in ascending
order it's going to go and it will
303.725 -> do a sort by the tickets column.
305.855 -> So as I come down I can see that there
are two sort orders going on here to
310.034 -> help me further identify an order.
312.275 -> Again, my information in my Google Sheets.
314.53 -> As always, we want you to try this out, so
open up the practice file and try doing a
318.789 -> multilevel sort in your own Google Sheets.
323.925 -> Hi, I want to be able to filter
data in my Google Sheets file.
328.055 -> Remember, filtering is
different than sorting.
330.585 -> When you sort, you reorder the data,
but all the data is still there.
334.474 -> When you filter, think of going
to a public swimming pool.
336.884 -> We want to get rid of all the
stuff we don't want in that water.
340.044 -> Here inside Google Sheets,
it's the same idea.
342.785 -> We will be hiding the data that
doesn't meet the criteria that we set.
346.224 -> set.
346.594 -> So how do we perform a filter?
349.005 -> Well, first of all, make sure you
have a file available to you to use.
352.575 -> I'm in the practice file
called trip sort and filter.
355.804 -> Feel free to use this to
try out filtering as well.
358.715 -> And I'm going to come up to the
toolbar this time, the far right hand
362.574 -> side of the Google sheets toolbar,
you'll see what looks like a funnel.
366.164 -> This is how you can turn on
filtering at each column level.
370.405 -> When I click on this button, you're
going to see that the area where I
373.564 -> have my column headers, Highlights.
375.724 -> This is another reason why the list format
is so useful inside of Google Sheets.
380.425 -> Now next to each of my columns, I see
a small set of horizontal lines that
384.734 -> are supposed to look like a filter.
386.794 -> They kind of do, but this is
where you go to perform filtering
390.205 -> and sorting at the column level.
392.775 -> I love these.
394.034 -> Let's try out by filtering, for
example, by the destination column.
397.95 -> Column E.
399.13 -> I'm going to click on the small
funnel and you'll notice that
401.65 -> I get a couple of options.
403.47 -> At the top I can sort each of my columns
in either ascending or descending order.
408.659 -> And the great value behind this is
that the column header is not included.
412.69 -> So you'll notice I just did
a descending level sort.
416.219 -> Now when it comes to filtering,
I'm going to click back on the
419.39 -> small funnel again and come down.
421.999 -> You'll notice I can
filter based on format.
424.695 -> By conditions, by values.
426.844 -> Now in my case I have text.
429.034 -> I see a list of every possible
text entry in my destinations
432.624 -> column and they're all checked.
435.025 -> If I come down, there's a
select all and a clear option.
438.704 -> If I say clear, it turns them all off.
441.724 -> Then I could just check off
the item I want to have filter.
445.124 -> Or, If there are too many, I can
start typing in the one I want in
449.814 -> the search box, it locates it for me.
453.374 -> I want to make sure there's a checkmark
next to it, and when I click on OK,
457.7 -> It filters by that particular column.
460.26 -> Now, what if I want to
filter by more than one item?
462.82 -> Can I do that?
463.72 -> Absolutely.
464.51 -> I'll click back on the funnel, come
in, and I can just check off the
468.07 -> additional things I want to filter by.
470.4 -> I could also make sure there's a sort,
and once I come in and click on OK, you'll
476.359 -> see that it indeed is going to filter and
allow me to have more than one item here.
481.47 -> Now, how do I turn my filter off?
483.87 -> To turn my filter off, I'm just
going to go up and click on the
486.61 -> funnel, This will remove the filter
and turn all my data back on.
490.955 -> So it's unhiding it.
492.485 -> So as always, we'd love to have you
try this out in your own practice file.
496.034 -> So go for it.
499.284 -> Hey, everybody, let's create a
filtered view and also a conditional
503.604 -> filter inside Google sheets.
505.554 -> Now remember, the list format
is a great way to go for this.
508.515 -> So the first thing I want to do is create
a filter that is for a range of dates.
513.254 -> This is a great place to
use a conditional filter.
516.584 -> I'm clicking inside my Google
Sheets file, inside the data.
520.154 -> I'm going to go up to the toolbar
to the far right hand side and
523.554 -> turn on the create a filter button.
525.785 -> Again, this gives me the ability to filter
at the column level inside my list format.
530.655 -> For this, I'm going to come over
to the date column and click on the
534.315 -> little, again, filtering button.
536.555 -> Now, towards the middle of the drop
down menu, you'll see that this
539.845 -> gives me an option to filter by
condition, which is what I want.
543.965 -> Now, there are three kind of types
of conditions that you can use.
547.634 -> You can filter based on text.
550.035 -> on dates and on values.
552.065 -> Now my criteria is a date
that is after May 1st.
555.755 -> So I'm going to select date is after.
559.065 -> Below that I have to actually
tell it what the date is.
562.345 -> It's going to be an exact date
and I'm going to come down and
565.995 -> enter in a value or formula.
568.554 -> Now just heads up, dates are
actually values in programs
572.095 -> like Excel and Google Sheets.
573.585 -> So I'm going to type in my date And
then I will come down and click on OK.
578.66 -> Now at first it may not seem like
anything's happened, but if I
581.71 -> scroll down through the data, you'll
notice that it's only showing me
585.449 -> dates that happen after May 1st.
588.18 -> Now this is a filter
that I will use a lot.
591.39 -> What I can do is rather than just continue
redoing the filter whenever I need it, is
595.92 -> I'll save the data currently in this view.
599.119 -> To do that, I'm going to go back up to
the toolbar, to the filtering button,
602.92 -> and click on the little arrow next to it.
606.35 -> This gives me an option to save as a
filtered view, which is what I want to do.
611.98 -> Then I'm going to come over to where I
see the name of my new filtered view.
616.62 -> And what I want to do is click
on that and just rename it.
620.23 -> I'm going to call it after May.
622.28 -> first.
622.77 -> You can put spaces in
these filtered view names.
625.68 -> All I need to do to finish
renaming is hit enter.
628.65 -> Now we'll see that my
filtered view is turned on.
631.15 -> I'm going to turn all my filters off and
the filtered view will also be turned off.
635.98 -> To do that, I'm going to go back up
and just click on my funnel again.
639.699 -> Again, you'll notice that when I
do this, it gives me an option to
643.149 -> say none under my filtered views.
645.559 -> Or if I just click away, I can come in and
I'm going to just say Now we'll see that
651.06 -> if I scroll back down, the filter is still
in place, so I'm going to go ahead and
654.709 -> click anywhere in the dataset and click on
the funnel again, and it turns the filters
658.509 -> off, and notice that all the data is here.
661.189 -> Now to return to my filtered view,
I'm going to go back up to the arrow
665.029 -> next to my filter in the toolbar, and
you'll see that any filtered views
669.949 -> you've created will show up in a list.
672.41 -> I'm going to select my filtered view.
674.885 -> And it turns it back on.
676.645 -> And I'll notice that any of the
dates that don't fit that condition
679.905 -> of being after May 1st turn off.
682.975 -> And then again, to return to the full
list without the filtered view, I'm
686.675 -> going to click on the filtering button.
689.785 -> Say none, the filter turns off
and the filtered view turns off.
693.705 -> And at any time you don't want the
filtered view anymore, you can click back
697.855 -> on the arrow and you'll notice there's
an option to delete all filtered views.
702.205 -> But remember, what we're doing is
we're combining a filter with a view
706.505 -> so that whenever we need to filter
by that item again, we don't have
709.625 -> to go back through all the work.
711.065 -> We just turn on the view and
the view will be there for us.
714.495 -> So try this out in your own Google Sheets.
720.06 -> Hi everybody.
720.78 -> I wanna be able to create a chart
inside one of my Google sheets.
724.32 -> Remember, when you create a chart, you're
visually representing values or data
728.31 -> stored inside your Google Sheets file.
731.16 -> It's a great way to help people
understand data quickly and easily.
735.18 -> Now, one acronym I like to use
when I teach people about creating
738.095 -> charts is the KISS acronym.
739.625 -> Don't be offended by
this, but keep it simple.
742.11 -> Stupid is the KISS acronym, and
it basically means keep your
745.53 -> charts as simple as possible.
747.27 -> If someone can't look at your
chart and understand it within.
750.03 -> just a few seconds.
751.21 -> It's probably too complicated.
753.05 -> So with that in mind, let's make our
own chart inside of Google sheets.
757.14 -> There is a practice
file available for this.
759.1 -> So remember if you go to the
description, you can open it up.
761.65 -> There's a link there.
763.01 -> This one is called charts 101.
765.499 -> Now the first step is to select
the data you'd like to chart.
768.449 -> I always like to remind people, we
generally don't include titles and totals
772.649 -> when we select our data for a chart.
774.724 -> So in this spreadsheet, I am going
to highlight cells A3 down to G7.
779.814 -> Notice I'm not including A1
because it's a title, and I'm not
782.844 -> including row 8 because it's totals.
785.214 -> Now to create my chart, I'm going
to come up to the Insert drop
788.974 -> down menu and come down to Chart.
791.824 -> Now, it's automatically going to
already assume the type of chart I want.
796.275 -> Notice the task pane that's
opened up over on the right.
799.045 -> This is called the chart editor.
801.714 -> Now, I can change my chart type
immediately by coming right to the very
805.515 -> first item under the chart setup tab.
809.095 -> These are the different chart types.
810.834 -> You have line charts, area charts,
Par, and bubble, just keep scrolling
816.835 -> down and then specialty charts.
818.345 -> Now generally when you're first starting
out, the first five categories are
822.395 -> going to be the best ones to start with.
824.295 -> And you'll also see at the top, that
based on your data, Google Sheets
828.115 -> will make suggestions of charts
that might work well with your data.
831.884 -> If you want to change your
chart type, come down and pick a
835.035 -> different chart type and you'll
notice that your chart will update.
838.25 -> Now how do you move this
chart around on your sheet?
840.41 -> Because your chart resides
on top of the cells.
843.65 -> Right now my chart is
partially covering up my data.
846.56 -> All you need to do to move your chart is
come to the corner of it, left drag it.
850.72 -> It's like a graphic.
852.36 -> Now I always want to make sure that
my chart doesn't overtake my data.
856.039 -> I can also come to the
corners of my chart.
858.04 -> You'll notice there are small
squares on the corners and the sides.
861.88 -> You can use these to size your chart.
864.53 -> It's a great idea to make sure that
your chart doesn't overtake your data.
868.82 -> Another thing you can do is you
can also come in and right click.
872.01 -> When you right click, you'll notice
you get a menu that will also
875.48 -> help you to adjust your chart.
877.99 -> So now that we've created our first chart,
I want you to try it out in your own file.
881.429 -> They're really easy to make, and
again, they're a great way to
884.68 -> help people understand the data
that's inside your Google Sheets.
891.084 -> Everyone, I'm ready to edit this chart.
893.555 -> So the first thing I need to do is
click on my chart so that it's selected.
897.464 -> When you select your chart, it
makes Google Sheets know that
900.865 -> you're interested in your chart.
902.295 -> Also, if you come in and just double click
on the inside of the chart, you'll notice
906.525 -> that the chart editor task pane opens up.
909.475 -> Now again, the first side of this is
going to allow you to set your chart
913.055 -> up by picking chart type, coming in and
selecting, for example, whether you want
917.545 -> the stack to be at 100% or standard,
but another Important thing that you
922.02 -> can also do here is take the data that's
in the legend and switch it with the
926.12 -> data that's on your category or x axis.
928.439 -> So let's just learn a little bit about
chart terminologies for a minute.
932.54 -> I'm going to go ahead and take my
chart and drag it over so it's a
935.05 -> little bit more front and center and
make it a little bit bigger for this.
938.39 -> Your legend is of course the area where
you understand what your different data
942 -> bars in the chart or the data series are.
944.66 -> Usually the x axis is going to be
the horizontal axis on the bottom.
948.675 -> The y axes or value axes will run
along either the left or right side.
953.185 -> Now you'll notice that at the bottom
of the setup, you can actually
956.245 -> come in and switch between what's
on your legend and your x axes.
960.005 -> By checking this box off, you'll
notice that they switch back and forth.
963.924 -> Also, there is a area where I can
come in, and I can also decide if I
968.764 -> want, for example, different Column
labels to be used in my data set.
972.66 -> When you really want to customize,
though, in the chart editor task
976.5 -> pane, there is a customize area.
978.719 -> This is where you can take the
different areas of your chart
981.349 -> and change the way they look.
983.22 -> Chart style is the overall
look of your chart.
985.71 -> You'll notice you can do things
like make the chart look 3D.
988.91 -> You can also set a different
background area for your chart area.
992.87 -> You can also come in, for example,
and set a different color for
996.18 -> the border of the chart, just so
that it's easier to recognize.
999.52 -> You can also come in and change the fonts
that's being used in titles and legends.
1004.9 -> In addition to that, you're also going
to have an area where you can update the
1008.699 -> chart axes and include a chart title.
1011.12 -> Now I currently have a chart
title, but for example, maybe
1014 -> I'd like to put it in bold.
1015.53 -> And maybe I'd like to change the color
to black to make it stick out more.
1019.319 -> So with each of these areas, you
go in and you customize the portion
1022.67 -> of the chart you'd like to edit.
1024.52 -> Another important one to be
familiar with is the chart legend.
1028.17 -> This again represents
what's in your data bars.
1030.88 -> Notice here I can actually change
where the chart legend goes.
1034.14 -> Right now my legend is
currently on the right.
1036.17 -> I'd like to move it to the bottom.
1037.85 -> When I do this, the great thing,
I guess I moved it to the top.
1040.58 -> We'll move it to the bottom instead.
1042.16 -> The chart adjusts to
make room for the legend.
1045.16 -> So with each of these areas,
you're able to go in and further
1048.319 -> customize the look of your chart.
1050.43 -> You also have an area where you can
come in and customize the series.
1053.55 -> These will be the different items
that are showing inside your chart.
1056.75 -> And notice.
1057.86 -> You can additionally
click on a given area.
1060 -> It will become highlighted.
1061.61 -> I've just selected the wages
data bars or data series.
1064.86 -> I can come in and actually update the
color of those just by selecting them.
1068.99 -> When they're updated in the chart, they'll
also be updated inside my chart legend.
1073.8 -> So we'd love to have you try this out.
1075.559 -> Go in and after you've created your
chart, try going in and customizing.
1080.09 -> And there is a practice file
for this called Charts 201.
1083.6 -> So give it a try.
1086.37 -> Hey, everybody.
1087.11 -> We want to be able to remove
items from our chart and also
1090.2 -> more easily move them around.
1092.05 -> Pretty, again, easy to do.
1093.89 -> You just have to know where to click.
1095.439 -> I'm in a practice file called Charts 301.
1098.309 -> Remember, if you go to the
description, you can find a link and
1101.32 -> go to all the practice files there.
1103.27 -> I'm going to go ahead and
make my chart the focus.
1105.29 -> So I'm going to click on it and
kind of move it around and size it a
1108.09 -> little bit bigger so we can see it.
1110.68 -> Now, first of all, how do I remove
something from the chart I don't want?
1114.03 -> there.
1114.629 -> For example, let's say I don't
want to see the wages in my chart.
1117.92 -> Well, I can't just click on them and hit
delete because if I try to click on them,
1120.98 -> which I can do, they'll get highlighted.
1122.78 -> But if I hit my delete key,
I can't directly delete a
1126.51 -> data series out of my chart.
1127.959 -> But what I can do is remove it.
1130.304 -> So I've clicked on my chart so that
the chart editor task pane opens up.
1134.395 -> And I'm going to go to the setup tab.
1136.805 -> When I come down, I'll see that
each of the sets of data bars
1140.144 -> are represented by a series.
1142.565 -> To remove a specific series, I'm
going to come to that series.
1145.86 -> Just called wages in this case.
1147.47 -> Click on the ellipses at
the side and say remove.
1150.81 -> And you'll see what it does is it
just removes those data bars out.
1154.09 -> So now I see my chart without the wages.
1157.01 -> I'm going to undo that because now
I want to just talk briefly about
1161.04 -> sizing elements on the chart and
removing something you don't want.
1164.789 -> Let's say for example I've
decided I do not want a legend.
1168.139 -> I can click on the legend.
1169.945 -> You'll see it gets selected,
and then if I hit delete on my
1172.425 -> keyboard, it will remove it.
1174.584 -> If I ever want to bring it back, I'd go
back over to the chart editor task pane.
1179.294 -> Find the legend section, which
right now says none, and I just
1182.634 -> move it back by clicking on the
direction or placement of the legend.
1186.645 -> I can also come to the legend and just
left drag it wherever I want it to
1189.925 -> go on the chart, and it will actually
allow me to move different elements.
1194.004 -> As well, if I'd like to size elements, I
can click on a specific part of the chart.
1198.595 -> Come to it.
1199.145 -> And I can also size it as well.
1201.345 -> So the charts are very adaptable.
1203.165 -> It's just a little bit tricky
sometimes when you're trying to
1205.294 -> click on something and move it in
conjunction to the rest of the chart.
1209.105 -> So as always, we'd love to have
you try this out on your own.
1212.685 -> Open up the practice file.
1214.504 -> Try turning some things.
1216.179 -> On and off.
1217.149 -> Also remove some of the
series out of the chart.
1219.77 -> Remember the undo button will bring
them back as long as you haven't
1223.899 -> yet gotten rid of all your undos.
1225.909 -> But try this out so you can see how
flexible these charts are to editing.
1232.26 -> Hi everyone.
1233 -> I want to explore a
different type of chart.
1235.219 -> It's an in cell chart that's great for
showing trends on columns or rows of data.
1239.81 -> It's called a sparkline.
1241.679 -> They're small but they're great when you
want to see something that's right in
1245.189 -> line with the data that it's tracking.
1247.629 -> Now Sparklines are not something you
can get by going to the Insert drop
1251.304 -> down menu, like we do for normal charts.
1253.885 -> They're actually created via a formula.
1256.495 -> So I've opened up a spreadsheet called Try
Sparklines, it's in the practice files.
1260.674 -> I'd love to have you use this
as you follow along with me.
1263.745 -> I'm clicking in the cell where I'd
like my sparkline to go, and then I'm
1267.234 -> actually going to create a function
that will create the sparkline.
1271.014 -> The first step is to start with your
equal sign and then type in SparkLine.
1275.59 -> Now, of course, Google Sheets gets smart
and gives you the spelled out SparkLine.
1279.73 -> I could also be typing this up
in my formula bar if I wanted to.
1283.79 -> The next step is to include the
range of cells that you'll be
1287.05 -> trending with your SparkLine.
1288.849 -> You can either type the range
in or just highlight it.
1291.61 -> And you just want to include
the values in this case.
1294.4 -> When you're done, you can hit enter.
1296.07 -> You don't need the closing parenthesis.
1297.94 -> And you'll see your little
in cell chart or SparkLine.
1301.019 -> And up in the formula bar, we can
see the function that created it.
1304.605 -> Now, if you'd like to get a little
bit fancier, you can include
1307.165 -> things like the type of chart.
1309.055 -> Sparklines can be column
charts and also line charts.
1312.065 -> The default is a line
chart, as you see here.
1314.725 -> So I'm now going to come in and
click in cell F8, and I'd like
1318.265 -> to create a column sparkline.
1321.165 -> For this one, I'm going to start
just like I did before, by typing
1324.035 -> the equal sign, typing in the name
of my function, selecting it, and
1329.255 -> then of course, highlighting my
range, which is A8 through E8.
1333.78 -> After I do that, I want to continue.
1335.98 -> I'm going to hit a comma, because
I need to put in some options.
1340.69 -> Inside of squiggle brackets,
as I call them, I'm going to
1343.73 -> type in, quotes, chart type.
1346.959 -> This lets Google Sheets
know that I want to...
1349.12 -> specify the type of chart
the sparkline will be.
1352.27 -> Then I'm going to do a comma,
and in quotes again, I'm going
1354.82 -> to type in the type of chart I
want, which is a column chart.
1358.909 -> I need to enclose this with a closing
squiggle, and then of course my closing
1363.02 -> bracket, and then I'll hit enter.
1365.28 -> And we'll see that for this
one, I have both positives and
1367.83 -> negatives, but it is a column chart.
1371.25 -> If you ever want to get rid of a
sparkline, just click on it, hit delete.
1375.77 -> And it will take it out.
1376.879 -> These are something that are pretty easy
to add and also pretty easy to take out.
1381.06 -> And there's more that you can
add to them, but start by just
1383.55 -> creating a few on your own.
1385.629 -> And again, it's a function, so a little
bit different way to create a chart.
1389.009 -> Try it out.
1392.915 -> simple way to help you read
down through rows of data.
1396.254 -> And right now I have a practice
file called CustomerInfo open, feel
1399.475 -> free to use this to try this out.
1401.224 -> It's called Alternating Row Color.
1403.615 -> Another thing to notice about my
data is it does include header
1407.514 -> rows at the top of each column.
1409.555 -> So I know what the data is below
and no blank rows, but blank rows
1413.105 -> will still work with this activity.
1415.045 -> All we're going to do is click in
any cell inside the data and then
1418.515 -> go up to the format drop down menu
and come down to alternating colors.
1424.885 -> This will select my data.
1426.899 -> I do have a header row.
1428.689 -> Notice how that's checked off.
1430.04 -> If I had a bottom row that was a
footer row, I could also check it off.
1433.799 -> Then I'm going to select from
one of the prebuilt styles.
1436.929 -> I could also come in and create
my own if I wanted to by clicking
1440.089 -> here, but I can also customize the
colors just by going into each one.
1444.86 -> When I'm finished, I'll click on done.
1447.16 -> And you'll see that it's highlighted
my header row in a darker shade,
1450.68 -> and then I have alternating row
color based on what I've selected.
1454.1 -> It's just an easy, simple way to format
your data and also make it visually
1458.8 -> easier to read down row by row.
1460.96 -> So try it out.
1464.51 -> Howdy, everybody.
1465.52 -> We're ready to look at one of my
very favorite topics in Google
1468.23 -> Sheets, creating pivot tables.
1470.61 -> Now, why pivot tables?
1472.19 -> If I were to try to take this data set
and say that my boss came to me and
1475.94 -> said, I need to know how many tickets
were sold at each of the destinations
1479.609 -> and then at each of the offices.
1481.549 -> How would you go about doing that?
1483.17 -> Well, you could sort.
1484.67 -> You could filter, you could add
some sum functions, but it would
1488.01 -> really be a pain in the neck.
1489.84 -> The great news about a pivot table is
it allows you to take a really thick,
1493.35 -> long data set like this one, and quickly
sort and filter it, and also analyze it
1498.51 -> and calculate it with just a few clicks.
1501.52 -> Now if you set up things, again,
pivot tables work best with that
1504.96 -> list format we mentioned at the
very beginning of the course.
1508.66 -> So I've got column headers at the
top, and no entire blank rows.
1512.9 -> This data is only about 200
rows down, so it's not very big.
1516.615 -> But again, pivot tables can be used
to analyze really large data sets.
1521.315 -> With that in mind, let's
make a pivot table.
1523.275 -> I'm going to click anywhere inside
the data, and then I'm going to go
1526.445 -> up to the insert drop down menu,
and come down to pivot table.
1530.095 -> It's right below chart.
1531.955 -> Now the first step is to pick
the range that I'll be pivoting.
1534.725 -> It's already selected my range because
I don't have any entire blank rows.
1538.885 -> But this button on the right
would allow me to drag over the
1541.475 -> correct range if I needed to.
1543.555 -> Also, I always suggest the
best practice of placing new
1546.685 -> pivot tables on a new sheet.
1548.775 -> That way you don't accidentally
overwrite any of your existing data.
1552.645 -> We're ready to go.
1553.385 -> Let's click on the create button.
1555.254 -> Once we click on create, you'll
see that you get a new blank sheet.
1559.935 -> Again, my original data is still back
on sheet one, and my new pivot table
1564.395 -> is called pivot table one or two,
depending on how many you've built.
1568.385 -> Now we need to take from our column
headers and create our pivot table.
1573.545 -> A pivot table is not about all
your data, it's about focusing
1576.625 -> on specific pieces of data.
1578.824 -> So I'm going to start by taking the office
field and dropping it in the rows area.
1583.385 -> I'm going to bring it under
here and click on app.
1586.05 -> Another way to do this is to actually
click on the part of your pivot table
1590.16 -> by clicking on the add button and then
selecting the field you'd like included.
1594.6 -> Again, with values, I
suggest using value fields.
1597.51 -> So I'm going to pick my totals field
and you'll see it's now doing a sum.
1601.585 -> Based on office.
1603.085 -> Now, I'd also like to see
where this is intersected by
1605.345 -> the destination that was sold.
1606.585 -> So I'm going to take destination
and drop it under columns.
1610.324 -> Now you'll see a lot of
blanks when I do this.
1612.324 -> This is important.
1613.904 -> Sometimes when you first start using pivot
tables, people see these blanks and think,
1617.874 -> Ooh, something's wrong, but it isn't.
1620.205 -> This is telling me an important piece of
information when something didn't happen.
1624.635 -> Now, additionally, I can also filter my
entire pivot table by taking a filter
1629.235 -> down or a field down to this filters area.
1632.225 -> We'll see that the filter will
be available to me right here.
1635.205 -> When I click on it, I can actually
perform a filter and it will filter
1640.674 -> my entire pivot table in place.
1643.734 -> When I'm done, I can turn
it off just by coming back.
1647.85 -> Clearing it, saying select
all, and it turns it off.
1650.73 -> Now to edit my pivot
table is also really easy.
1654.16 -> Let's say instead of the total field, I'd
like to see the number of tickets sold.
1659.12 -> I can actually remove the
total field by clicking on this
1662.02 -> X, and I can bring down a...
1664.405 -> different field under my values area.
1667.395 -> Now I've seen the number of
tickets that were sold at each
1670.105 -> office for each destination.
1672.185 -> I could also actually remove the
destination and we'll see that my
1676.225 -> pivot table will totally adjust.
1678.384 -> Now have any of these changes
impacted the data on sheet 1?
1682.084 -> Let's check it out.
1683.325 -> I'm going back to my original sheet.
1685.445 -> Nothing has happened.
1686.665 -> The beauty of a pivot table is that
you can analyze specific columns
1690.175 -> of data in your pivot table but
your source data never adjusts.
1694.61 -> One important thing to remember
about pivot tables is that your
1699.2 -> source data on sheet one is
refreshed with your pivot table.
1702.65 -> But the great news about Google
Sheets is that they automatically
1705.87 -> refresh with each other.
1707.23 -> So try out a pivot table of your
own inside the practice file.
1710.74 -> It's called Customer Pivot.
1712.379 -> You'll see how amazing and quick
these are to analyze your data.
1718.455 -> All right, so we've learned
how to make a pivot table.
1720.715 -> Now we're ready to make a pivot chart.
1723.375 -> Now, one thing I want to mention
is in this file that I've
1726.445 -> opened in Google Sheets, it's
called Customer Pivot and Chart.
1729.785 -> You can use it as well when
you want to make a pivot chart.
1732.435 -> This data is too thick for me to chart.
1734.615 -> This is one of the reasons that
pivot charts are so important.
1737.325 -> They allow you to basically focus
on specific pieces of data in
1741.655 -> a big data set and chart those.
1743.875 -> I have a pivot table in this
data set on the second sheet.
1747.205 -> So I'm going to click on pivot one and
it's going to take me to my pivot table.
1751.825 -> Let's zoom in on it just a little bit.
1753.825 -> Once I've done that, what I
want you to do is come in and
1756.485 -> I want to create a pivot chart.
1758.23 -> So I've clicked in my pivot table.
1760.72 -> I'm now going to go up to the insert
dropdown menu and come to chart just
1764.7 -> like we would to create a normal
chart, but because it's in a pivot
1768.36 -> table, it creates a pivot chart.
1770.91 -> And for the most part, pivot
charts are going to operate
1773.26 -> a lot like a normal chart.
1775.26 -> You'll notice you can right click
on the chart and come in and get
1778.36 -> lots of options to edit the chart.
1780.39 -> But additionally you also have the
chart editor task pane that opens up.
1784.54 -> At the top of the chart editor
task pane, you can actually change
1787.96 -> to different types of charts.
1790 -> So there are additional types
beyond just a pie chart.
1793.23 -> You have, again, line charts,
area charts, column charts.
1797.5 -> Some of the specialty charts
may not work quite as well.
1800.34 -> So just again, be sure that
you can kiss your chart, right?
1803.04 -> You want to make sure that you're keeping
it simple so it's easy to understand.
1806.945 -> After you've made the change to the chart
type, don't forget that you can go to the
1810.695 -> customize ribbon to come in, for example,
and update things like the chart border.
1816.185 -> Also, you can come in and
change the chart fonts, right?
1819.665 -> And even the background
color of your chart.
1821.765 -> So all the same things that
you use to edit your chart in a
1824.165 -> normal chart can be used here.
1826.125 -> But the beauty of this is that we've
been able to take something like this.
1830.49 -> Focus it with a pivot table
and create a pivot chart.
1834.08 -> Now the final thing I want you to
remember about the pivot chart is
1836.82 -> that it's based on the pivot table.
1839.3 -> So let's say, for example, that
I come in and I have a filter
1842.2 -> on my pivot chart right now.
1843.52 -> It's showing all items.
1845.28 -> But let's say, for example, that I clear
and only show the first five items.
1848.365 -> For example, five days of data.
1850.695 -> When I do that, notice that my
pivot chart also will update
1854.835 -> based on what's in my pivot table.
1857.205 -> So the pivot chart and the pivot
table are always based on each other.
1860.705 -> And when you edit your pivot table, your
pivot chart will follow suit, but try
1865.675 -> making a pivot table and then create
a chart and see how easy it is to take
1870.425 -> a very thick data set and really drill
into it and create a chart based on that.
1877.245 -> Welcome back.
1877.935 -> I want to show you another way to refer
to cells other than their cell name.
1882.515 -> Now we know that whenever you click
in any cell, like I've just clicked
1885.235 -> in cell A2, that directly to the
left of the formula bar in the name
1889.785 -> box, we'll see the cell reference.
1892.165 -> But sometimes I might want to name an
individual cell, like this one, or an
1896.735 -> entire group of cells a different name.
1899.705 -> This can be useful when you're trying
to do formulas, for example, or even
1903.735 -> just select a certain group of cells.
1906.175 -> Now, right now, this group of cells
is known by the sheet that they're
1909.105 -> on, which is the expenses sheet,
and then the range B4 through B7.
1913.945 -> A named range will really simplify
this naming construct with just a name.
1919.195 -> So let's go ahead and
create a named range.
1921.975 -> Remember, there's a practice file for this
called name range in the practice files.
1926.705 -> So if you go to the description
of the course, you can
1928.835 -> access those practice files.
1930.215 -> And I suggest it for this
now to create a named range.
1934.395 -> I've highlighted my cells in this
case, B4 through B7, then I'm
1938.305 -> going to come up to the data.
1939.78 -> drop down and come down to named ranges.
1943.23 -> It's directly above named functions.
1945.82 -> You'll see over on the right hand side
that my named ranges task pane opens up.
1950.5 -> I currently don't have any named
ranges in this spreadsheet,
1953.27 -> but I'm going to create one.
1954.88 -> And right now you'll see that it's
putting in a default named range
1958.09 -> one name for my current cells.
1960.43 -> It also shows me the references, the sheet
name, and then of course, B4 through B7.
1965.67 -> When you create a named range,
you can't put spaces in the name.
1969.195 -> So if you need spaces, use
hyphens or underscores, and you
1972.785 -> can't use a current cell range.
1975.445 -> So I couldn't, for example, take this
range of cells and call it B1, because
1980.365 -> that's already a cell reference.
1981.395 -> I'm just going to give it the simple name
of January, and then I'll click on done.
1985.325 -> Now I'll see my named range
here in the named ranges list.
1988.805 -> This pencil next to it would allow me
to edit that range, but These will save
1993.435 -> with my workbook and always be here.
1995.065 -> I'd like to continue this process.
1997.125 -> I'm now going to come in and
highlight cells C4 through C7 and
2001.795 -> come back up and click on add a
range in the named ranges task pane.
2006.215 -> And I bet you can't guess
what I'm going to call this.
2008.015 -> Yeah, this is going to be
my February named range.
2010.385 -> I'm just going to keep it
February to keep it simple.
2012.965 -> And finally, I'm going to do
the same thing for D4 through
2015.965 -> D7 and add a range there.
2018.115 -> So when I'm done, I'll have.
2019.785 -> Three named ranges, one called Feb,
one called Jan and one called March.
2025.035 -> I'd love to have you go into your
spreadsheet and try this out.
2028.095 -> Final thing we want to do is actually
make these into a formula rather
2032.785 -> than using cell ranges and formulas.
2034.925 -> Like we've done previous in this
course, you can use your named
2038.135 -> range to replace the range of
cells that you'd normally use.
2041.975 -> Let's try this out.
2043.355 -> I'm going to close my named ranges
task pane and go to cell B8 where
2048.415 -> I want to put in my function.
2050.02 -> Remember, you start with an equal sign,
type in the function name that you want.
2054.28 -> I'm going to double click on it.
2055.68 -> Now, I know that I could use cell
references here, but instead,
2058.87 -> I'd like to use my named range.
2061.48 -> As I start typing it in, Google
Sheets recognizes that I want
2065.23 -> to use a named range here.
2066.41 -> Notice the icon next to
my January named range.
2069.55 -> This is the icon that represents a named
range as opposed to a cell reference.
2073.75 -> I'm going to click on it.
2074.99 -> I need to use my closing
parenthesis and hit enter.
2077.6 -> And it finishes the formula for me.
2079.78 -> I can do the same thing
in cells C8 and D8.
2082.93 -> Again, I hit an equal
sign, start my formula.
2086.59 -> In this case, I don't want to
use the named range that it's
2089.14 -> suggesting, so I'll just bypass that.
2091.41 -> Then I'll start typing in
the name of my named range.
2095.08 -> You'll see it bring up
the named range for me.
2097.3 -> Double click on it.
2098.38 -> And it finishes it.
2099.69 -> So why is this a good idea?
2101.69 -> Sometimes when you're working with groups
of people who may not be so familiar with
2105.9 -> formulas, this can help simplify and help
them understand what the formula is doing.
2111.76 -> Also, when you do formulas that
require absolute references, these
2116.91 -> named ranges are always absolute.
2118.85 -> And remember, if you need to
go back and edit them, all
2122 -> you do is go up to the data.
2124.07 -> drop down menu, come to named ranges and
your named ranges will be in this list.
2129.07 -> One final warning.
2130.17 -> If you accidentally delete one
of these named ranges, I'm just
2132.9 -> going to show you what happens.
2134 -> So if I come in and click on the trash
can next to one of my named ranges,
2138.5 -> you'll notice that your formula, if you
delete it, We'll continue to work, but
2143.78 -> what you're going to see right here is
in the formula bar, you're going to get
2146.61 -> a number sign reference error message
because the computer is continuing to
2150.89 -> do it, but the actual formula itself
cannot recognize the correct range.
2156.57 -> So it will continue to show you the
right total, but if you need to edit
2159.85 -> this, you'd have to fix your formula.
2161.58 -> So that's one thing to be
careful about with named range.
2166.795 -> Hey, everyone.
2167.445 -> I want to show you one
of my favorite functions.
2169.395 -> It's called an if function.
2171.875 -> Now, if functions are very common
and they're based on the idea that
2175.935 -> a formula can have a true or false.
2179.005 -> So you start with what's
called a logical test.
2182.055 -> Is this greater than this?
2183.535 -> Is this equal to this?
2185.265 -> Then you have what to do if that's true.
2187.825 -> And then if it's not true,
you have a false portion.
2191.605 -> So if functions in their simplest form
have Three different parts, the logical
2196.465 -> test and the what to do, if that's true
or what to do, if that's false, they're
2200.825 -> very popular now here in my practice file.
2203.425 -> That's called if functions.
2205.065 -> And remember, we'd love
to have you try this out.
2207.205 -> So find it with the practice files.
2209.325 -> I need to calculate if my sales reps are
going to get a bonus if they've sold over
2214.565 -> 7 million of product, they get a bonus.
2217.265 -> But if they haven't,
they don't get anything.
2219.775 -> But I'd like to put a text message in
the formula that will say no bonus.
2224.495 -> So to start, I'm clicking in cell 03, and
I'm going to create my first if function.
2229.345 -> So I type an equal sign and the word if.
2233.145 -> Then of course I'm going to double click.
2234.615 -> Now the first part of this function
is going to be my logical test.
2238.875 -> My logical test is, was there
sales total, that's in N3.
2243.74 -> greater than or equal to, which
I'm going to, of course, type
2247.04 -> on my keyboard, 7 million.
2251.8 -> That's me counting zeros.
2253.25 -> Then I'm going to do a comma.
2254.84 -> Now, if that's true, then I
again want to take their sales
2257.82 -> total and multiply it by 5%.
2260.59 -> That will be their commission rate.
2262.93 -> Now, What if they don't sell
more than 7 million in product?
2267.135 -> They haven't met the logical test.
2269.625 -> So now we have to say
what to do if it's false.
2272.265 -> Well, I could just put in a zero, but
it's easier for me to understand no bonus.
2277.675 -> So to enter a text string, I'm
going to do a quote and then type
2281.705 -> in the text message, which is no
space bonus, all contained within.
2287.875 -> Quotes.
2289.125 -> So you'll see here that I
have the name of the function.
2292.455 -> The logical test is N3 greater
than or equal to 7 million.
2297.535 -> Then I have what to do.
2298.635 -> If that's true, take N3
and multiply it by 5%.
2302.745 -> If it's false inside quotes, because
it's a text string, I will put no bonus.
2308.735 -> Each of the portions of the
functions are separated by commas.
2313.125 -> When I'm done, I'll hit enter.
2314.95 -> Now, based on that, you're going
to see right here that it's already
2317.76 -> telling me, well, it looks like this
salesperson is getting a 400, 000 bonus.
2322.92 -> Now, this is a function that you
can drag down using the autofill
2326.58 -> handle, which I'm going to do.
2328.08 -> And you'll see again, when they meet...
2330.17 -> The true portion of the formula, they get
the bonus when they are under 7 million
2335.31 -> in product, then they don't get the bonus.
2338.02 -> And this text message is in a lot
of ways easier for me to understand.
2342.19 -> And so again, it's a great formula
because you can have a true and a false.
2347.15 -> So try it out.
2350.62 -> Welcome back.
2351.3 -> I want to show you how
to do a nested function.
2353.58 -> Now I'm in a practice file called nested
functions, so please feel free to open
2357.68 -> it up and use it for this activity.
2359.9 -> In this spreadsheet, I'm trying to
figure out how to do my bonus schedule.
2364.59 -> I've started in column E
by using an if statement.
2368.06 -> So if we open this up, we'll
notice that based on their order
2371.33 -> total, if it's greater than 2,
000, they'll get a 5% bonus.
2375.62 -> If not, no bonus for them.
2377.805 -> We can see it's working fine, but
I've also noticed right here that the
2381.825 -> average of all my order totals is 2100.
2385.515 -> So I'm wondering if I could just use that
to calculate whether someone gets a bonus,
2390.175 -> and I want it to just say yes or no.
2393.035 -> A different approach.
2394.695 -> So what I'm going to do is I'm
going to click in cell F1, and I'm
2397.445 -> going to start by my first order.
2400.715 -> Function, which will be the if statement.
2402.275 -> So we're going to do an equal
sign and type the word if.
2405.115 -> And then of course an opening parenthesis.
2407.215 -> Now right here I need my logical test, but
this is where I'm actually going to start
2411.555 -> getting ready to do my second function.
2413.935 -> So we know that it's based on
the order total, which is d2.
2417.255 -> And it needs to be greater than the
average of all the order totals.
2422.295 -> Well, I'm going to actually
begin another function.
2425.665 -> I'm going to type the word average.
2427.92 -> Notice that it already will give me an
opening parentheses if I'm using those
2431.8 -> helps that Google Sheets provides.
2433.81 -> Now how do I reference this?
2435.7 -> Well, I'm going to highlight
the range of cells that need
2438.28 -> to be averaged, D2 through D10.
2441.45 -> This does need to be included
inside parentheses because
2444.38 -> it's the innermost function.
2446.44 -> Now I need to finish out my if
function, so I'm going to type a comma.
2450.81 -> Now if that's true, and they
are going to get the bonus,
2453.6 -> I'm going to type the word yes.
2456.24 -> Because, again, it's a text statement,
so it needs to be included in quotes.
2461.13 -> Now, if that's false, if they didn't
order a total over the average,
2465.6 -> I'm going to type the word no.
2467.56 -> So now I'm finishing out my second
formula, which is the if statement.
2472.48 -> The other thing that's really important
is that you have the same number of
2475.685 -> parentheses going in that you end
with that same number of parentheses.
2479.665 -> I have two parentheses going in and
I have two parentheses going out.
2483.795 -> When I'm done, I'll hit enter.
2485.675 -> Now we'll notice that based on
that, I guess this particular
2488.305 -> person did not make the average.
2491.265 -> So I'm going to go ahead and
drag this function down and then
2493.935 -> we'll just look at it briefly.
2495.735 -> You'll notice that if again, I look
at the function, the two people that
2500.165 -> met the average total are right here,
and it says yes for both of them.
2504.575 -> So if we look at the formula, we're going
to see again, if is the first function,
2509.765 -> then I have the beginning of the logical
test, but then part of the logical test
2514.115 -> is the second nested function, followed
by parentheses, and then my logical test,
2520.825 -> which is yes, for true and no for false.
2524.285 -> So this is the beginnings of
what nested functions look like.
2527.185 -> These can get really complicated but
they're a really powerful way to do
2531.755 -> multiple calculations all in one formula.
2536.695 -> Howdy!
2537.135 -> I want to show you one of my
favorite Google Sheets functions.
2540.095 -> It's called VLOOKUP.
2541.925 -> It stands for vertical lookup.
2544.595 -> Now let me explain this function to you.
2546.495 -> It has Five primary arguments.
2549.395 -> The goal of VLOOKUP is to allow you
to look a value up out of a range
2554.045 -> based on something to the left.
2556.205 -> So for example, in my practice
file, which is called vlookup, feel
2560.015 -> free to use it to try this out.
2562.265 -> You're gonna see that I have
customer numbers in column A.
2565.57 -> What I'd like to do is to be able to
come in and type in a customer number
2569.15 -> here in column M, cell M2, and then I'd
like it to tell me the yearly cost of
2575.78 -> tickets from the yearly cost column.
2578.77 -> So the goal is that the VLOOKUP will
actually come into the table at the
2582.52 -> left, find customer number 12, count over
nine columns and return exactly what's
2590.39 -> in the same row in the ninth column,
a vertical look up through columns.
2596.2 -> Let's try it out.
2598.16 -> I'm going to put my formula in cell n
Two, feel free to click there and try
2602.765 -> it out as well in the practice file.
2604.595 -> You always start with an equal
sign like we always have.
2607.325 -> And then the word V lookup
again, notice how Google sheets
2611.125 -> always has lots of helps.
2612.765 -> As you go through, you'll
have an opening parentheses.
2616.045 -> Now the first part of this function is
going to be called what's called your
2619.875 -> search key, which is over here in cell M.
2623.495 -> So I'm going to click there, or
an M2 I should say, and it enters
2627.365 -> that my lookup value or my search
key will be the customer number
2631.585 -> that I've typed to the left.
2633.165 -> Next I'm going to put in a comma
because I now need my range of
2636.925 -> cells that I'll be looking through.
2638.825 -> So I'm going to come in and highlight
all the data in the table at the left.
2641.965 -> It's going to be cells A1
all the way down to J23.
2646.39 -> Now the third portion of my function
is going to be the index column.
2651.38 -> This has to be a positive
integer or number.
2654.67 -> So I can't put in I, which is
the column the yearly cost is in.
2658.88 -> I have to just count over so I
can provide a positive number.
2662.74 -> So 1, 2, 3, 4, 5, 6, 7, 8, 9.
2667.16 -> The value I want returned or
the information is in column 9.
2671.74 -> So that will be my column index number.
2674.805 -> Now, there's still a fourth argument
that's very important with VLOOKUP.
2679.595 -> Am I trying to find an exact
match or an approximate match?
2684.025 -> In my case, it needs to be an exact
match because this is a client number.
2688.075 -> It's not something that
will be repeated again.
2690.535 -> I don't have two customers
with the number 12.
2693.565 -> So if it's an exact match, You
need to enter the word FALSE.
2697.74 -> If it was an approximate match, you
could actually just type in TRUE or
2702.31 -> not even put it into the formula.
2704.91 -> But if you need an exact match,
you must enter the FALSE.
2708.78 -> Very important.
2709.66 -> So again, what is my function
doing if we look at it?
2712.77 -> Well, it's coming in, it's going to
the left hand column of the range
2716.99 -> and finding my customer number.
2719.43 -> Then it's counting over nine columns and
returning exactly what's on the same row.
2724.55 -> Now, could I type in a different
customer number, like 15 for
2727.92 -> example, and would that work?
2729.6 -> Let's check it out.
2730.8 -> Here's 15, and if I count over 9
columns, the yearly cost is going
2735.66 -> to be returned by my VLOOKUP.
2737.75 -> These are very popular and very
common, so try them out, because
2741.33 -> chances are you will run into these.
2745.31 -> Hi, welcome back.
2746.33 -> In this lesson we're going to combine
two functions that we've already used
2750.21 -> to create SUMIF and AVERAGEIF functions.
2754.36 -> Now just as the name implies, these
functions will only SUM something if
2759.23 -> certain criteria is met, or only IF.
2761.925 -> average something if
certain criteria is met.
2764.895 -> I'm in a practice file
called SUMIF and AVERAGEIF.
2768.595 -> Feel free to use it so
you can follow along.
2771.165 -> I'm going to start with
the SUMIF function, so I'm
2773.425 -> clicked right here in cell K2.
2776.485 -> To start this function, you do an
equal sign and type the word SUM.
2780.985 -> If all one word.
2783.405 -> Now this particular function
has three basic arguments.
2787.325 -> The first thing we need is the range
of cells that our criteria is based on.
2791.895 -> For me it's the destination.
2794.165 -> Notice in column E that I
sorted the destinations.
2797.965 -> It makes it easier to check and see
if the function is really working.
2801.615 -> So I want to select all those
destinations but not include cell E1
2805.775 -> because it's To do this, I love the
keyboard shortcut CTRL SHIFT DOWN ARROW.
2811.795 -> Notice it selects all my destinations
down to the bottom of the spreadsheet,
2815.715 -> but nothing underneath of that.
2817.765 -> Now the next thing I
need to do is a comma.
2820.225 -> I now need my criteria.
2822.265 -> This is Cancun.
2823.795 -> Because it's text, I need
to include it in quotes.
2826.755 -> If it was a value, I wouldn't
need to do that, or a date.
2830.875 -> The third thing I need is my sum range.
2833.665 -> What column am I going to be setting?
2835.28 -> summing.
2836.13 -> In this case, it's everything below H1.
2838.73 -> So I'm going to click in H2 and
do the same keyboard shortcut
2842.37 -> control shift down arrow.
2844.77 -> Now, of course, I need to enclose
the whole thing in parentheses.
2847.47 -> I'm going to hit enter.
2848.87 -> If I scroll up a little bit, I'll
see here that my total is 2972.
2854.78 -> Now here's what you can do.
2856.15 -> I can actually come into my
spreadsheet and highlight those cells
2859.49 -> E13 to E30, E21 because that's the
range of my Cancun destinations.
2865.1 -> And then I'm going to come over
and highlight the total column
2868.68 -> as well for all of Cancun.
2870.14 -> And then just highlight those values.
2872.98 -> If I come down to the bottom right hand
corner where Google Sheets does its auto
2877.36 -> calculations, I'm going to see that.
2879.67 -> Indeed, the sum of all the
Cancun destinations is 2952.
2885.01 -> So this is letting me see
that the function worked.
2887.46 -> Let's try it with an average.
2889.89 -> This time we want to find the average
of the total of tickets sold to Boston.
2895.95 -> Very similar formula.
2897.84 -> I'm going to click in cell N2, hit an
equals sign, And type the word average.
2904.15 -> And again, it does the same thing,
but this time we're going to
2907.21 -> be averaging based on criteria.
2910.23 -> The next thing I need
is my criteria range.
2913.27 -> Again, it's going to be based
on destination, so I'm going
2916.25 -> to select E2 through E63.
2918.95 -> Hit a comma.
2919.97 -> Now I need my criteria.
2921.63 -> In quotes, I'll type Boston,
making sure that I spell it
2925.3 -> correctly, followed by a comma.
2927.59 -> Now what am I averaging this time?
2929.74 -> It's actually the same column,
column H under the total.
2933.33 -> So I'll do control shift down arrow
to get the range of H2 through H63.
2939.02 -> Then I'll hit enter because
there's only one parenthesis.
2942 -> I actually don't need to
put the ending one on.
2944.33 -> Now I can see the total it's
giving me for the average.
2947.7 -> Again, I'm going to come into the
spreadsheet and highlight all my
2950.43 -> Boston destinations and go over
to include the total column.
2954.99 -> Then if I just highlight the Boston
destinations here, you'll see in the
2959.24 -> bottom right hand corner it shows
me that it's auto calculating a sum.
2963.09 -> But if I click on that, I can
also have it do an average.
2967.14 -> Now down here it rounds.
2968.925 -> Which is 531, but up here in the
spreadsheet, I can see that if
2972.625 -> I rounded this, it would be 531.
2975.255 -> So this is a fantastic way to
only sum, or average, based on
2981.005 -> if certain criteria are met.
2983.955 -> Alright, let's try out
something a little bit trickier.
2986.585 -> This is a function that is combining
SUM and IF, but gives you the ability
2991.545 -> to have two different criteria.
2993.725 -> It's called SUMIFS.
2995.875 -> Now there is a practice file for
this in the practice files folder, so
2999.425 -> click on the link in the description.
3001.145 -> It's called SUMIFS and AVERAGEIFS.
3004.365 -> I'm going to be on the
SUMIFS sheet for this.
3007.675 -> So I want to come in and click in cell B5.
3010.025 -> Let me describe this
spreadsheet to you briefly.
3012.485 -> You're going to see that it's
about grocery delivery totals.
3015.435 -> One column are the items that have
been purchased, the second one is
3018.805 -> the amount, and the third is going
to be the status, whether they're
3021.955 -> delivered, in transit, or canceled.
3024.985 -> I need to come in and anywhere where
toilet paper has been delivered, I need
3028.905 -> to total the amount that's been purchased.
3031.705 -> This is a good example of using a...
3034.685 -> Because I have two different criteria.
3037.015 -> The item has to be toilet paper,
and it has to be delivered.
3040.255 -> I'm going to click in cell B5
to create my SUMIF function.
3043.905 -> I'm going to hit an equal sign,
and then this is all one word.
3047.445 -> SUMIF S.
3050.64 -> Now remember that Google Sheets does a
great job with giving you suggestions
3054.54 -> so don't be afraid to use them.
3056.49 -> The first thing it's telling
me here is I need a sum range.
3060.27 -> Well, the range of cells I'm summing
are going to be B8 down to B18.
3065.05 -> Now I'm going to hit a comma.
3067.115 -> The next thing I need is
my first criteria range.
3070.165 -> This is the item type, so I'm going
to highlight B8, in this case A8 to
3074.395 -> A18, and then you're going to see
that it's going to ask me for a third
3078.835 -> thing, which is the actual criteria.
3081.795 -> In this case, it's toilet paper,
and I have this listed in cell B3,
3086.275 -> so I'm going to click up there.
3087.375 -> So that's my first collection of criteria.
3090.435 -> Now I need to set my second one,
which is based on the status.
3094.245 -> So again, I'm going to highlight column C.
3096.59 -> C8 through C18, do a comma, and
again the criteria for this is
3101.05 -> whether it's been delivered.
3102.68 -> So I'm going to click in
cell, in this case B5.
3106.45 -> And then I need to enclose the
entire thing in a parenthesis,
3110 -> and I'm going to hit enter.
3111.8 -> Now what I see here is that
when the two criteria are met.
3115.68 -> That it's toilet paper and it's
been delivered, it adds those
3119.35 -> two amounts up, which is 200.
3121.65 -> Now toilet paper is also listed
here, but notice that in that case
3125.11 -> it's cancelled or it's in transit.
3128.36 -> So therefore it only sums where it
equals the two criteria, that it's
3134.43 -> toilet paper and it's been delivered.
3136.45 -> Super fun function.
3140.485 -> Hey everybody.
3141.235 -> I wanna show you another function that
combines the ability to have two criteria
3146.245 -> before something can be averaged.
3148.285 -> This is called the average if S function,
it's very similar to the sum if ss, but
3154.015 -> it does an average rather than a sum.
3156.495 -> I'm also in the practice file called
SUM If and Average If, and I'm on
3160.785 -> the second sheet called Average if S.
3164.175 -> Now, what I have in this
sheet are a list of products.
3167.215 -> quantity sold, and then a sales
rep in column C and the actual
3171.165 -> product name in column B.
3173.015 -> Here's my scenario.
3174.255 -> I only want the products to
be averaged if Tom was the
3178.255 -> salesperson and they were apples.
3181.425 -> Pretty simple, but there is a
function that will do all that for me.
3185.995 -> So I'm going to come down to cell C9
and type an equal sign and then begin
3189.885 -> my function name, which is AVERAGEIFS.
3193.285 -> All one word, and don't forget
the S, it's really important.
3196.595 -> Then, the next thing I need to do...
3199.13 -> is get what I'm averaging.
3201.51 -> In this case it's going to be A5 to A8.
3205.03 -> That's my average range.
3207.16 -> The next thing I need
is my first criteria.
3210.88 -> Now this is if the product was apples.
3213.31 -> So first of all I need to highlight
my products which are B2 to B8.
3218.505 -> Then do a comma, and then in
parentheses, I need to type what
3221.945 -> the criteria is, which is apples.
3224.435 -> The reason it's in quotes
is because it's text.
3227.235 -> If it was just a value, I
wouldn't need to put it in quotes.
3230.645 -> Now I need to do my
second set of criteria.
3233.595 -> And remember that Google Sheets has all
these built in helps to let you know
3237.215 -> what portion of the function you're on.
3239.775 -> I'm on my second criteria range.
3242.635 -> Now in this case, it's the sales reps.
3244.705 -> So I'm going to do C2 to C8.
3248.475 -> And now I'm at the part of the
function where I need my second
3251.045 -> criteria within that range.
3253.145 -> In this case, it's if
the sales rep was Tom.
3256.815 -> So there are two criteria.
3258.905 -> Is the product apples?
3260.895 -> Was Tom the sales rep?
3262.51 -> Then average it.
3263.87 -> I'm going to hit enter.
3265.35 -> Now we're going to see right here,
the average based on that is 4.
3269.06 -> 5.
3269.54 -> Let's check it out.
3270.76 -> Here I can see that Tom sold
apples for a total of 5 and 4.
3275.94 -> If we average that, it would come to 4.
3278.46 -> 5.
3278.82 -> So again, it's a simple function, but
because it can be based on criteria,
3283.405 -> you can allow it to let you only
average based on two different items.
3290.575 -> Hey everybody, let's talk about a way
to make sure that when there might be
3294.825 -> an error that occurs inside one of your
Google Sheets functions or formulas.
3299.69 -> that you can actually control the
error message that comes back.
3303.52 -> Now we've probably seen
this in Google Sheets.
3305.57 -> If you haven't, it's common for there to
be error messages like you're seeing in
3309.8 -> the spreadsheet on my screen right now.
3311.82 -> You can also see other ones.
3313.5 -> They usually start with again, a hashtag
symbol followed by the particular
3318.27 -> error message that's being displayed.
3320.3 -> This one is happening because I'm
dividing by zero, which we know
3324.42 -> equals zero, but Again, Google
Sheets thinks I'm making a mistake.
3328.35 -> This is the practice file called if error,
and I want to show you how you can control
3332.68 -> these error messages by actually putting
in your own if error when they occur.
3338.23 -> Now, if error will return the first
argument if it is not an error value.
3342.84 -> Otherwise, it returns the second one.
3345.1 -> So you're going to see right here
that again, I'm getting the divide
3347.44 -> by zero error message because I'm
taking A3 and dividing it by B3, and
3353.68 -> we know that when we divide something
by an empty cell, we get zero.
3357.37 -> So hence I get this divide by zero
error, but instead what I'd like
3361.56 -> to do is edit this and put in the
if error instead so that if this
3367.065 -> does happen it just shows a zero.
3369.715 -> To do this I'm going to go to my first
formula and actually edit it even though
3373.415 -> it's working and then drag it down and it
will fix my divide by zero error message.
3378.465 -> So I'm going up to the first formula
in the formula bar and in front of the
3382.345 -> equal sign I'm going to type if error.
3384.675 -> Then I'm going to enclose
it in parentheses.
3387.57 -> And again, we're going to see right
here, if it's divided by zero, then I'm
3391.7 -> going to put a comma, rather than doing
the error message, just put in a zero.
3397.05 -> So if there is an error,
then put in the zero.
3401.34 -> I'll hit enter.
3402.35 -> Now the formula works
fine, so there is no...
3404.61 -> error message involved.
3405.8 -> But I'm going to copy the formula down.
3409.41 -> Notice now instead of the again
divide by zero error message I
3414.14 -> just see a zero because that might
be exactly what I want it to do.
3418.52 -> There are many other
use cases for if error.
3421 -> This is just a simple example of a way
to override those built in formula error
3426.29 -> messages with something that you want.
3428.19 -> In this case a zero because
that's what it should be.
3433.575 -> Hi, everyone.
3434.285 -> I want to show you how you can use
functions to reshape your data.
3438.655 -> So I'm right now in a practice file called
left and right, and we're actually going
3442.775 -> to use both of those functions to pull
specific pieces of data out of a column.
3448.325 -> I'm going to come into column A and
insert a new column by right clicking
3452.435 -> on the left hand side of that.
3453.795 -> And I'm going to call
this new column a month.
3456.655 -> Now notice that my months are only
January through June, so they're only
3460.895 -> one character in the short date format.
3463.515 -> And there are date time functions, by
the way, that do this, but I want to
3466.805 -> show you how you can also use the left
function to accomplish a similar thing.
3471.615 -> I'm going to start my function
in cell B2 by hitting an equal
3474.615 -> sign, and then the word, left.
3477.33 -> Then I'm going to do
an opening parenthesis.
3479.63 -> Now the first thing I need to do is
tell it what cell it's working with.
3482.93 -> In this case it's A2.
3483.91 -> to do a comma and tell it how many
characters from the left to return.
3488.55 -> I only need one because it's the month.
3490.82 -> Then I'll hit enter.
3491.91 -> And you'll see that for each month,
and my autofill actually comes up.
3495.75 -> By the way, if this doesn't happen,
you can always come in and just use
3499.55 -> your fill handle to drag it down.
3501.39 -> It's returning the first thing at the
left hand side of each of my cells.
3506.08 -> Now, what if there's
something on the far right?
3508.27 -> For example, it's not uncommon
to have, for example, numbers
3511.66 -> combined with information in cells.
3514.03 -> Right here you're seeing my office
name, column, column E, but it also
3517.78 -> actually includes the office number,
location at the end, and I need that.
3523.03 -> So I'm going to insert a new column to
the left of column F by right clicking.
3527.35 -> Then I'm just gonna call office number.
3530.385 -> And then I'm going to use what's
called the write function to
3534.105 -> get the last four characters off
the right hand side of a cell.
3538.525 -> So again, I start with an equal sign.
3540.565 -> I'm clicked in cell F2, and I'm going to
type in the name of the function, write.
3546.005 -> I need to start with the
cell that I'm going to be
3548.465 -> collecting the characters from.
3550.355 -> So I click in cell E2 in this case.
3553.06 -> followed by a comma, and then the
number of characters in from the
3556.59 -> right hand side of the cell that I
will be again returning, which is 4.
3561.62 -> I could do a closing parenthesis, but
as you know at this point we don't
3564.9 -> need it because there's only one set.
3566.87 -> I'll hit enter.
3567.93 -> Now again, the autofill
comes up, and if I take it...
3570.555 -> suggestion, it's now pulling the last
four characters out of each of the
3575.325 -> cells to the left and putting them
into my office number column for me.
3580.135 -> Feel free to use these formulas
to help you reconfigure data
3584.275 -> in spreadsheets so you're not
spending time having to type it in.
3590.515 -> Hi everyone.
3591.385 -> Let's look at how you can rework data
in your spreadsheets by combining
3595.435 -> two columns of data together using an
awesome function called concatenate.
3600.055 -> There's a practice file for this called
concatenate, and this function can
3604.495 -> save you a tremendous amount of time.
3607.105 -> Here's my goal.
3607.98 -> Column B has the first names,
column C has the last names.
3612.595 -> I want them to be together in one
column, so the first step is to
3616.255 -> right click on column header D and
insert a new column to the left.
3620.34 -> This column, I'm just going to
type as full name, because that's
3623.93 -> what I want here in cell D2.
3626.54 -> I'll create my first
concatenation function.
3629.25 -> I'm going to hit the equal sign to
start my function and then start
3632.84 -> typing in the word concatenate.
3634.92 -> It's a little tricky to spell, so make
sure you pick the correct function
3639.12 -> name if you don't know how to spell it.
3640.98 -> The first thing I need to do is select
the first cell that I will be bringing
3644.6 -> together, which in this case is cell B2.
3647.465 -> Then I'm going to do a comma.
3649.365 -> Now the thing that's tricky here
is I need a space between my
3652.635 -> first text string and my second.
3654.685 -> The formula does not assume that space.
3657.095 -> So in quotes, I'm actually going to put
a space to represent that before the
3662.165 -> first text string and the second text
string, I need a space between them.
3667.225 -> Then I need a comma.
3670.34 -> And my second cell, which is C two.
3673.9 -> Now this should all be enclosed
in parentheses and I'll hit enter.
3677.2 -> Now my autofill comes on,
which is mighty convenient.
3680.38 -> If it doesn't remember, this is a
formula, so if you click on the cell
3685.54 -> with the formula, get your cross hair
and drag down it, auto fills it in.
3690.66 -> Let's just look at the
function again, really briefly.
3693.57 -> Concatenate.
3695.06 -> Then take the first cell, comma, if
you need a space between the text
3699.38 -> strings, you have to allocate that
in quotes, and then the second cell.
3703.85 -> And voila, it combines them.
3705.51 -> Can you do more than one column?
3707.37 -> Absolutely.
3708.72 -> Great way to save yourself
having to retype everything.
3712.49 -> The long way.
3714.91 -> Hi, everybody.
3715.66 -> I'm in Google Sheets, and I want
to be able to share a file with
3718.44 -> someone who also uses Google.
3720.66 -> The great thing about Google Sheets
is because all your files saved
3723.95 -> to Google Drive automatically,
it's really easy to share them.
3727.9 -> So the file I'm in right now
is called Practice Share.
3730.57 -> Feel free to use it to try this out.
3732.71 -> You will need someone that you
know that you can try to share
3735.61 -> the file with to test drive.
3737.7 -> To share the file, I'm going to go to the
top right hand corner of my open Google
3741.32 -> Sheets and click on the share button.
3743.46 -> The share file box opens up.
3745.95 -> The first thing I need to do is either
type in the email address or select
3750.31 -> from an existing group of people that
I've already shared the file with.
3753.98 -> You could also paste in the email
address and I've already shared with this
3757.25 -> person before so I select their name.
3760.05 -> Over on the right, I need to select the
type of sharing that I'm going to do.
3764.46 -> Viewer means the file will
be shared in read only mode.
3768.295 -> Commenter means they can leave comments
on the file, but not actually change it.
3772.285 -> An editor gives them full editing rights.
3775.015 -> I can also include a message
3780.355 -> and then I'm going to click on send.
3782.675 -> Now the file's been shared after
a file's been shared, it will tell
3786.605 -> me to access has been updated.
3788.585 -> If I ever need to go back into the
file and see who it's been shared with.
3792.475 -> All I need to do is go to the top right
hand corner, click on the share button.
3796.325 -> It'll open up and show me
currently that I'm the owner.
3799.565 -> And whoever I've shared it with
will be right here in the list.
3803.115 -> And again, if I ever need to edit those
sharing rights, notice I can come right
3806.725 -> here, change the type of sharing that I'm
doing or remove access to the sharing.
3811.695 -> Try sharing your files through
Google Sheets and Google Drive.
3815.345 -> It's really easy.
3820.25 -> Hi, everyone.
3820.9 -> I want you to actually see what
real time collaboration looks
3824.09 -> like inside Google Sheets.
3825.75 -> This means that you've shared a file
with someone and they are also in
3829.07 -> Google Sheets in the file with you.
3831.34 -> I'm currently in the
practice share practice file.
3834.54 -> I would need to have shared this
file with someone and have them
3837.45 -> open it up at the same time.
3839.3 -> They inbox.
3841.405 -> and they'll see the shared
Google Sheets file in that link.
3844.745 -> All they need to do is click on
the link and Google Sheets will
3847.265 -> open and the file will open.
3849.235 -> Now, how do I know that
someone else is in this file?
3851.875 -> Well, I'm going to see that
there are two active cells.
3854.655 -> So the person I'm playing right
now is here in cell a one, but
3858.055 -> my coworker is right here in
cell, in this case in column D.
3862.345 -> Now if this coworker comes in
and starts making changes in my
3865.495 -> spreadsheet, I'll see their changes.
3867.605 -> So for example, I'm going to
have them make a change down in
3870.535 -> column, or in this case, row 15.
3872.835 -> Notice that as that change
happens, I see it real time.
3875.955 -> And if, for example, they add
another particular comment,
3883.285 -> I'll see it as they type.
3885.125 -> Now, do we have to worry
about saving things?
3887.295 -> We don't because
everything saves as we go.
3890.595 -> The other way that I can know who
exactly is in the Google sheet with me
3894.375 -> is to go to the top right hand corner.
3896.275 -> I'll see my co workers name in
a small circle or their initial.
3900.465 -> And then I can also see myself as well.
3902.385 -> There's a chat pane where we
could come in and add chats to
3906.075 -> the file that we're working in.
3907.695 -> But everything saves.
3909.115 -> And if I were to close the Google
sheet, My person that I've shared it
3912.645 -> with could continue to work in the file
and remember, anytime you want to stop
3917.065 -> this real time collaboration and file
sharing, you can always go back to the
3921.065 -> share button and manage the rights you've
provided to the file from this menu.
3925.925 -> But it's a fantastic way to work and
it works in all the Google suite.
3929.945 -> So not only Google Sheets, but also
some of the other Google applications.
3934.515 -> So try it out.
3938.515 -> Hey, everybody.
3939.525 -> Have you ever wanted to have additional
functionality inside Google Sheets?
3943.115 -> Well, you can.
3944.465 -> The great thing about Google is it's
open source, so it means that there
3947.565 -> are a lot of other applications
that you can bring into your Google
3950.335 -> platform to really maximize your
experience from any Google Sheets file.
3954.995 -> If you go up to the drop down
menus and click on Excel.
3958.065 -> extensions.
3959.155 -> You're going to see the add ons button.
3961.195 -> This is going to allow you to
access the add ons that are
3964.105 -> available through Google sheets.
3965.665 -> And this is a list that continues to grow.
3968.415 -> I'm going to come in
and select get add ons.
3971.115 -> It takes me into the Google
workplace marketplace.
3974.305 -> Now some of these costs and some
of them are free, but what you
3977.075 -> can do is come in and notice under
price, you can say free of charge.
3981.405 -> If you just want to see free add ons.
3984.13 -> In addition, when you find one that
you'd like to try out, you click on
3988.34 -> it, there will be an install option.
3991.39 -> This will then ask you to allow you to
add it to your Google Sheets experience.
3996.28 -> You install the application.
3998.25 -> You also have to make sure that you
allow it for your correct Google account.
4002.6 -> Once it's installed, you'll see that
it'll be available to you to ever
4006.73 -> manage add ons that you've added.
4009.06 -> You can come to the extensions
menu and come down to I'll now
4013.02 -> see the different extensions.
4014.4 -> I just added right here are add ons.
4016.81 -> But if I need to manage my
add ons, I can go to add ons.
4020.435 -> Then manage add ons, and
I'll see them in this list.
4023.525 -> If I'd ever like to uninstall something
I've added, I click on the dots and
4027.305 -> say uninstall, and it will remove
it from my Google Sheets experience.
4032.255 -> This is an area where you can explore
tons of great additional tools that
4036.655 -> will really make your experience inside
Google Sheets more useful and easy.
4042.04 -> Feel free to check out the add ons and
again, it's a list that continues to grow
4045.97 -> and change as more tools are created.
4048.49 -> So definitely try it out.
4053.52 -> Hi everybody.
4054.45 -> Let's say that you want to be able to
create your own custom content from
4058.78 -> Google Sheets or in Google Sheets.
4061.235 -> To do this, you need to access an
area called Google Apps Scripts.
4066.255 -> Now, Google Apps Scripts is an
addition to your Google Sheets.
4070.465 -> What it does is it provides a rapid
application development platform
4074.445 -> that lets you create your own
applications that, again, integrate
4078.385 -> with the rest of the Google Workspace.
4080.605 -> You can write your code in modern
JavaScript and have access to libraries
4084.865 -> that allow you to get access to great
applications that work within Gmail,
4089.375 -> Calendar, Drive and even Google Sheets.
4092.085 -> Now, where do you go to access this area?
4094.295 -> You're going to come into extensions in
the drop down menus and go to apps script.
4100.395 -> This will open up a separate window.
4102.74 -> Again, where you can actually create
your own custom modules of JavaScript
4106.8 -> code that could either add customized
apps to your Google Sheets application or
4112.36 -> to other parts of the Google workspace.
4114.81 -> Now, if you're not quite sure what
these look like or what they could do
4118.18 -> for you, I'm going to close the tab
that again, my Apps Scripts project has
4122.6 -> opened in and go back to Google Sheets.
4124.93 -> A good way to just explore possibilities
here is to go to Extensions
4129.04 -> again, come down to App Sheet.
4132.54 -> And you'll see that there are
available samples that you can view
4136.51 -> that have been created by others.
4138.37 -> This will actually take you into the app
templates, and you can see here that you
4142.41 -> can create, for example, a Kanban board.
4144.86 -> or a workspace booking app.
4147.29 -> You can see here that they'll give you
opportunities to explore the app or even
4150.9 -> to copy it and make it into your own.
4153.37 -> These are considered a low code,
no code tool where you don't
4157.68 -> really have to know javascript.
4159.72 -> But again, this is an
area where I do recommend.
4162.19 -> Learning some of the code, because when
you start customizing the app, you can
4166.61 -> see that it's going to get a little
bit tricky if you don't have knowledge.
4169.89 -> So this is definitely an area that
will take a tremendous amount of
4173.06 -> time to skill up on, but it's really
exciting that Google's opened their
4177.22 -> applications and their platform to
allow you to bring in custom content.
4181.62 -> So definitely think of it as an
opportunity to upskill and really
4185.19 -> learn some powerful tools to help you
customize your Google Sheets experience.
4191.12 -> Hey, everyone.
4191.85 -> Thank you so much for joining
us on our part two of Google
4195.25 -> Sheets Learning Experience.
4196.73 -> We've done everything from
sorting and filtering to creating
4200.58 -> pivot charts and pivot tables.
4202.63 -> We've also explored some advanced
functions like VLOOKUP and IF
4206.38 -> statements and even SUMIF and AVERAGEIF.
4209.5 -> Don't forget real time collaboration
inside of Google Sheets and even how you
4213.59 -> can further extend what Google Sheets can
do with add ons and even Google Scripts.
4219.09 -> Now as always, please like and
subscribe to these videos and
4222.5 -> sign up and attend more of the
learn at any time video courses.
4226.38 -> They're a great way to upskill and
expand your workplace opportunity.
4233.6 -> Thanks for watching to earn certificates
and watch our courses without ads.
4237.895 -> Check out LearnItAnytime.
4239.325 -> com.
Source: https://www.youtube.com/watch?v=W_ffJShpgiQ