all right so in this video i'm going to talk about top five best features in google sheets
for advanced users now i've realized this is subjective so your top five may not be
the same as my top five let me know in comments what are your top five features if you disagree
now if somebody asked me what's the best thing about google sheets compared to any other
spreadsheet application that exists currently that would be without even thinking about it a race
now there are obviously arrays in excel and in other spreadsheet applications
it's just the way google sheets handles a race is amazing so a quick example of that would be
let me just copy paste some data here
so if i have all of this stuff thrown around like this using array in google sheets we can actually
build our combined data on the fly so i can just start my array bracket like this and then
i'm gonna take these two headers i'm gonna do a comma and then i'm gonna click on this header to
add it as another column next to this and then i'm gonna do semicolon to go to my rows and then
i'm going to grab this first column of dates comma and then i'm going to add to that these
pieces right in here close my brackets hit enter and if you look here i can basically
just piece it all together within my array structure now any one of these pieces could be
instead of just a range could be a function that returns a range or whatever else you do
but that's how flexible google sheets is with a race so you can basically just piece things
together just like this so in this case commas would separate my columns and semicolons would
be my row separators now if you have a different setting in your spreadsheet your separators could
be a little different so maybe in your case instead of commas you might have to use this
to get the same result there you go that's our google sheets arrays now the second
feature in google sheets is the fact that it actually understands the difference between an
empty cell and a zero so if i simply just create a formula and do equals and click on an empty
cell and hit enter what i'm gonna get is an empty cell i'm not gonna get a zero which may not seem
like a big deal to a lot of people but for me as a data person this is a big deal compared
to for example how excel will return a zero if i do something like this let's get rid of this
so the next one in my list number three is going to be filter function so filter function is pretty
straightforward generally so what it does it takes a range so we can just for example grab
this range of data comma and then it takes one or more conditions to filter rows out
of this data so for example i could select this column of sales reps and check if that equals to
jennifer close parentheses and hit enter
and again you can see how filter function handles our blanks as blanks doesn't return zeros
so by itself this function seems pretty straightforward there is no particular magic
but it seems to me like every time i'm working on a project this function always comes up somehow
so if you need to learn more about this i have a video covering filter function by itself
feel free to watch it that would be our number three so number four feature in google sheets for
me is the ability to do open references so what i mean by that if i was trying to for example sum
a column of values in this case let's actually zoom in a little more i can do equal sum open
parenthesis and select this range of numbers close parenthesis hit enter that should give me my total
the thing that google sheets allows us to do is to take this and drop this
end reference 20 and then it basically says start from this cell h8 and then go all the way down
which means now if i add more numbers down here it will become a part of this total
so again that's open references and open references can be done
not only for rows you can also do them for columns so to give you a very quick example of this
let's say i have some numbers here and i'm planning to add more numbers going to the right
and i want to start getting my sum from here and go right so i can do equal sum open parenthesis
and then again i'm just going to select this range close parenthesis and now what
i'm gonna do i'm just gonna drop this e and that basically means start from this b
one cell and then go all the way right in this first row and again it just works fine
we can go here and add more numbers and you're gonna see how that becomes a part of this total
so that's open references in google sheets and finally number five is gonna be
scripting implementation so for scripts in google sheets we have javascript as a language
now the reason that's great it's not because javascript is the greatest language in the
world it's just because javascript is so popular there is a lot of code that was already written
so there are a lot of javascript libraries you could use in here if you wanted to without having
to spend the time to develop the whole thing from scratch yourself what's also great is the way that
implementation of classes objects and methods is done and what i mean by that is that when we go to
our editor the way that all of these methods are created and the way they're named is so great that
you can get away without going to documentation every five seconds to do what you need so there
is a pretty consistent logic behind this when you do like all this get methods then also
you have all the set methods so generally speaking if you're trying to get something
within your instance or object whatever it is most of the time you can just type get
and look at some of this get methods and just by looking at the name of these methods
it makes sense right so we have our get range and then inside of our get range you can do
your get and see you can get the background you can get the a1 notation you can get the column
all of these things when you look at them most of them will make sense without having to go and try
to read the docs to figure out what's going on and the same way if i do set then we have all the set
methods so just that saves so much time compared to a lot of other places where if you have to
write a script you find yourself constantly going to the docks and trying to figure out how it's
supposed to work and what the method is called and all of that and finally what's possibly my
favorite part about this is that we have a nice way to call external apis by using url fetch app
so this allows us to post data to external places and also get data from external places by having a
pretty easy to use api now again for this function i have videos covering it if you want to learn
more about it you can check out my videos for example for import json function that would be a
good place to get started with something like this to understand how this works but again javascript
a lot more pleasant to work with compared to something like visual basic and that's my top five
thanks for watching and let me know what i should have included here that i didn't