(mechanical noises) - [Scott] Do you wanna learn some new ways to get the most out of Google Sheets? Do you wanna learn some new functions so you can manipulate your
data in an easier way? Well then today's video is just for you. Hello everyone, Scott
Friesen here at Simpletivity helping you to get more
done and enjoy less stress. And let's get things
started with tip number one. Sometimes when you're dealing
with an awful lot of numbers, and you know when you're
dealing within a spreadsheet you've got rows and rows and
columns and columns of numbers. Sometimes it could be hard to decipher what exactly is going on here. Well of course, we could create a chart within Google Sheets. But sometimes a chart is overkill and you don't wanna take
up all this real estate somewhere else in your spreadsheet. Sometimes you just want a quick view of the data that is going on here. So I wanna introduce you to
something called Spark Line. Now you can put this in any
cell within your spreadsheet. In this example I'm
just gonna use this cell just below these sales figures 'cause I'd like to see if there's a trend, if there's a spike, what's going on here, and remember, if this column was hundreds, maybe even thousands of cells, I wouldn't be able to see that. So what I'm gonna do is
I'm gonna press equals to bring up the functions here, and I'm gonna type in sparkline. That's right, sparkline. I'm gonna select it and then all I have to do is highlight the area, the cells that I would like to include. I'm gonna hit enter, and look what's happened here. Let me expand that cell just to make it a bit more square like. It's giving me a visual, just a snapshot, a visual line chart of what's
going up here in the sales. So I can see that there's
been sort of three spikes including this last one, and maybe that's gonna help me zero in on what I should be looking at. So again that is sparkline. All you have to type
in is equals sparkline, then select the cells that you'd
like included in that data, and you're gonna get
this nice little visual, which of course is dynamic. If I change anything here then that visual's gonna change as well. Tip number two, this
has to do with sharing, and in particular letting others know that you've added a comment. You probably already recognize
by using Google Drive or Google Sheets one of the benefits is that we can add a comment
and share that with others. So if I right click on a
cell I can select comment and maybe I want to
ask someone, like what, what happened here. Like why did we get no
support calls that day. Can select comment and
there's a little icon here letting me know as I hover over there that there is a comment included. Whoever, if I'm collaborating with others, this isn't gonna notify anyone else that I've added a comment. They're gonna have to come in here and maybe the next time
they're within this sheet they'll just hover over and happen to stumble upon this comment. Well, there is a better way. I'm gonna say delete so we
can start off from scratch. I'm gonna go back to that cell and once again I'm gonna insert comment. And you know what, I am
gonna say what happened here. I'm puzzled. But this time around I'm
gonna use the plus symbol. I'm gonna use the plus sign, and that's gonna bring up
my email directory here. So now what I can do is I
can start typing in a name or an email address. I'm gonna use this one
here, my test account, and it's going to notify this
individual of this comment. Now it gets better. Down below you can see the plus mention. We'll add people to this discussion and send them an email directly. But I can go one step further and I can actually check this box which will assign this
comment to that individual, so they will be responsible
for marking it as done. Now this example, I'm gonna
actually uncheck that, but I'm gonna select comment. And what's happening
right now is that Google is automatically sending
an email notification with this comment and a link to this sheet so I can ask this person
this particular question. I don't have to email them
secondarily with a followup email or, "Hey can you please
review my comments?" Or something like that. It's actually built right in. So don't forget the
plus symbol if you want to call out someone directly
within your Google Sheet. Now tip number three, we're
gonna stick with emails. But in this case what we wanna do is we want to clean up our emails and see if we actually
have valid email addresses. There's a good chance that
you're dealing with information that is being submitted and
maybe you wanna use that in a bulk email. Maybe you have a mail
client such as Mailchimp that you want to import this information, or maybe you just want to copy and paste a large number of emails
into your own email client, such as Gmail or Outlook. Well if you take a quick glance here you can see that some
of these email addresses are not formatted correctly. This one doesn't have anything
in front of the at symbol. This one down below does not have a domain name at the end of it. And this is gonna make
things really tricky when we go to import or when we go to actually email someone, if we want to copy and
paste all of these emails. So I wanna validate
this particular column. You can see here I've added a title here. I call it Valid Email, and what we're gonna churn out here is just a list of true or false. What Google Sheets is going
to do with this function, it's gonna take a look at
everything here on the left and tell us is there anything
that is a false email address? And then we can maybe follow
up with the individual directly or see if our forms
are formatted properly, maybe as we're collecting
this information. So the function this time around, we'll start with the very first cell. Again, we're gonna start
with that equal sign, and it is isemail, -I-S-E-M-A-I-L. So I'm gonna select isemail, and I'm just gonna start by selecting the one to the left here. All right, so this one it says true. Yeah, that makes sense, right? It's got something in
front of the at symbol. It's got a dot URL. Now what I can do is I can
just take this little icon here in the bottom right-hand corner
and drag it all the way down and now it has analyzed
everything here to the left. And one, two, three, four, it looks like I've got
four false email addresses. This is gonna spit back an error. It might not even let
me send that bulk email if these are included in the to line. So I can either get rid of them. I can go back and see if I've got the correct email address somewhere. A nice and easy way to validate a long list of email addresses. Now, tip number four
might just be my favorite in this tip five list. And this time around it
has to do with QR Codes. Until recently I had no idea that you could do this within Google Sheets. And you know, QR Codes
are making a comeback. I think it was about 10 years ago when we first started seeing QR
Codes and started using them. And then they seemed to fade away. But recently I'm seeing
them a lot more in posters, in marketing material. It's so easy to scan a
QR Code on your phone and then go directly to
a website or a webpage. So in this example here I've got about five website addresses. These are different pages
on my own personal websites, simpletivity.com. And maybe I would like to create QR Codes, a unique one for each and every page as a part of my marketing material. Well, all I need is this
special string here. We're gonna use the function
which is called image, and then what we're gonna
use is a special Google API to help us create a QR Code. Now I don't expect you to jot this down or memorize what this
full function is here. I'll be sure to include it
in the description down below so you can copy and paste it yourself. But first, let me show you how it works. So in this first cell, first
what I need to do is copy, right? I need to copy this information here. So I'm gonna copy this string
of texts, this function. And then I'm going to paste
it into this cell below. And to make sure that I can activate, I'm gonna eliminate that
space at the beginning. And the last thing that
I'm gonna do is that it's actually referencing cell A1. In my case, the cell that
I need to reference is A2. That's the first cell that I want to use. So I'm gonna hit enter there. And boom, I've got a unique QR
Code for my website address. Just like we did before, when you drag a function or a formula, it's going to apply to everything below. If you drag everything
here in this column, it's gonna apply to everything on the left-hand side of the column that the first one was referencing. So again, I'm just gonna take this, I'm gonna drag it to
the bottom of my list. And now I've got five unique QR Codes associated to all of these webpages. Because these are image I
can just right click and say copy and now I can paste this anywhere. I can paste it into a document. I can paste it into an email. If I'm creating a poster, if I need to send it to a
designer who's gonna include it I can put it there as well. So lotsa great stuff. Let's just, just for fun let's just paste it over here in this cell. There's that image so
I can put it anywhere. Can you imagine having a long
list of website addresses or something else here and
you can instantly create new QR Codes for everything that you want in that initial column. All right, well last but not least let's move on to tip number five, something maybe you didn't know how to do here within Google Sheets. Often we're dealing with
a lot of information, and in this case we're looking at names. And of course we wanna have
as clean of information, clean data as much as possible. And just by taking a quick
glance of this short list, you can see I've got things like this first one's got some extra spaces. I got a couple of things
with extra spaces in front. I've got someone like
this one, Alvin Jimenez, has got too many spaces
in between his name. I think the same thing is
happening here with Serena. This is just hard to deal with. It's hard to look at as well, right? As I'm scanning through these names. So what we're gonna do is we're going to clean up these names here. And the function that we're
gonna use is trim, T-R-I-M. Once again, this first
cell I'm gonna hit equals and type in the function trim, which removes spaces
within the characters. Now it's not gonna remove all the spaces because it's gonna recognize
that these are names. So it's not gonna move
the moon, the surname, directly into the e of Elaine. It recognizes that there's a capital. They know that it's names. So we're gonna say remove
spaces so it can look proper. So I'm gonna select this one, and let's start by selecting
that first one again. Of course, you could
add the whole row here, but I like showing it as an example just how it's gonna
deal with the first one and then how we can drag for the rest. I'm gonna hit enter. And look at that. The spaces are removed from
the beginning of Elaine. I'm gonna highlight that cell, and let's just apply it to
everything else in this list, and now we've got a very proper, a very clean looking column here, where you see all of
those extra spaces here in Serena and Alvin have been removed. All the spaces have been removed as well. I can go ahead and just get rid of this column all together, if like, because I've got these cleaned names here. Well, I hope you enjoyed those tips. But I would love to learn from you, and I'm sure there are
others watching today's video who would love to know what are some of your favorite functions or some of your favorite
tips within Google Sheets. Be sure to share them down
below in the comments section. Remember, being productive
does not need to be difficult. In fact, it's very simple.
