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.
Use Sparkline to insert small graph
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.
Share comments and notifications directly to others
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 follow-up 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.
Validate email addresses in Google Sheets
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.
Create QR Codes within Google Sheets
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 text, 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 images 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 lots of 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?
Clean up and trim name fields
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 altogether, 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.