5 Google Sheets Tips Every User Should Know!

5 Google Sheets Tips Every User Should Know!


(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.


92 thoughts on “5 Google Sheets Tips Every User Should Know!

  1. Love these tips!!! Is there a function that will split a first and last names into separate columns?

  2. Scott … thanks again for some great tips … your QR code example reminded me that I had seen an example like this many years ago that creates one for name, phone & email from 4 columns … Check it out … =image("https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl=BEGIN:VCARD%0AN:" & A2 & "%20" & B2 & "%0ATEL;CELL:" & C2 & "%0AEMAIL:" & D2 & "%0AEND:VCARD") It looks like I got this from Jordi Pakey-Rodriguez

  3. Amazing tips! One of the reasons I don't use Google sheets as much as Excel as because I don't know all of its functions. Thank you!

  4. very cool. please continue expanding on this. You might get into how to connect sheets and specific cells of specific sheets.

  5. excellent video! i would like to know how i can open my sheets and open in the last line written! thank you!!

  6. Could The Qr Images created in google e pasted into an Excel Worksheet and function there?

  7. LEFT(A2,FIND(" ",A2)-1), Get the first word from a string. EOMONTH(TODAY(),-1)+1, First day of a month. EOMONTH(C2,0), last day of month. Seems I'm using these more as I get skilled more with Google Sheets. Nice tutorial, Thanks! Oh, and I keep a list of formulas/functions/app scripts I use often. Good idea for other users of Sheets to keep.

  8. This is great! Just don’t forget to copy- Paste values before deleting the source column! Trim seems more powerful in Google Sheets than the last time I used it in Excel – it didn’t read the extra spaces between words. Sometimes, data cleaning takes longer than actually using or analyzing data!

  9. FYI, you can double click the bottom right dark square of your first square. This fills in formulas for you for every cell to the left. You would do this instead of dragging down to fill in the formulas

  10. Is there a way to check the status of a list of URLs? For example, flag those which return a 404 error?

  11. Excellent tips, will become regular arrow in my quiver ! Can you help with creating labels in gmail – android OS ?!? ( no create label option under change label menu )

  12. Please review "Join" that is cross-platform and chrome extension that connects computers and phones to pass info, SMS messages, copy clipboard to all your systems, copy websites to another computers screen.. It's really an amazing productivity tool. @t

  13. There are a Couple of things it would be nice to do in Google Sheets such as Auto Expanding a Table and Auto Tables…any Ideas !?

  14. The QR code tip is not working on my iPad…. I got the error message “formula parse error “ what should I do?
    The suggested format for =IMAGE I got is “=IMAGE( url, [mode], [height], [width])

  15. Unrelated Question about google spreadsheet:
    Is there a way to have a text in a cell and another one underneath? Basically hiding a complicated Text required for a formula and overwriting it with something else.

    https://imgur.com/a/eNOmznV The ETR:TL0 is required for google finance to recognice the stock but I want to label it simply as Tesla. How can I do that?
    Thank you!

  16. Unrelated Question about google spreadsheet:
    Is there a way to have a text in a cell and another one underneath? Basically hiding a complicated Text required for a formula and overwriting it with something else.

    https://imgur.com/a/eNOmznV The ETR:TL0 is required for google finance to recognice the stock but I want to label it simply as Tesla. How can I do that?
    Thank you!

  17. The QR code is interesting. Sometimes I screenshare with someone in a skype and it would be great if I showed her a sheet with links in it and she could just aim her phone at her screen and load the page. This is wonderful!
    Thank you!

  18. When I copy a link from my googlesheet into a video I post on youtube so that anyone who visits the video in my youtube channel can download the google sheets link. Go, but why would anyone who downloads my links below have the ability to disable or open my googlesheet links?

  19. Great tips, Scott! Just a point on copying the QR codes. If you copy and paste this QR code, you'll be pasting the formula, which would be taking the values from a different relative cell and therefore generate a different QR code. You would need to right-click, Paste Special, Paste Values Only. Then you get the exact copy of the original QR code you created.

  20. I am having difficulty in google sheets because it is not recognizing a string of numbers as a date. one digit month, 2 digit day, four digit year (all with no space; eg: 4211932 (Apr 21st, 1932) how can i solve this problem??

  21. Two possible errors: (1) the QR code inserted to the right does not appear to match the original, it is probably referencing the cell to its left and (2) if you delete the original names column the "cleaned" column will be REF errors

  22. I learned so much from this video that I can apply right now in marketing and running my business. Thanks so much for the great tips. Keep 'em coming! I like to find free ways to increase productivity and efficiency so I can use them myself and help others by sharing.

  23. Loved the video but the copy for hint number 4 can't be copied as is. YouTube created a link. I went to the link, then copied it and added the quotes, A1, and parentheses where needed: =IMAGE("https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl="&A2&"") An apostrophe before the string makes it viewable for reference use with the title lines.

  24. Love the QR code one! For tip 5 they created a new command called “trim white spaces” no formulas, copy and paste values, which is lovely!

  25. For those who need help with the QR code example. The sample in the description is not complete. Try
    =IMAGE("https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl="&A1&"")

  26. As others have pointed out, the QR code tip doesn't work. The code is copy/pasted, not the image (you can see the pasted QR code is different). Is there a way to force it copy/paste the image instead? That would be really useful.

  27. Great tips! I am trying to figure out how to quickly make a column of dates with a range of one week. I am paid weekly on a contract delivery job and need to be able to record my tips and mileage in my own spreadsheet. Entering the weekly dates manually is a giant pain, hope there are some quick functions for that. Thank you so much for your help

  28. Hey Scott.. nice tutorial.. I have one query for which I am searching answer for long.. that is..
    How to filter cells by color coding in Google sheet.?

  29. Scott, thanks– these are really helpful (and in some cases, quite unexpected.). Two quick things though on the QR code. The formula info you pasted in description is missing part of the function. Full formula is =image("https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl="&REPLACE-THIS-WITH-CELL-ADDRESS&""). Also, the QR code you pasted in @ 9:23 is different than the one you copied! Confused me at first– then I saw it now references E3. Anyhow, thanks for the tips!

  30. Here is the formula you will need to use for Tip #4 (QR Code). Just remember to replace 'A1' with the cell you want to reference:
    =IMAGE("https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl="&A1&"")

  31. Thanks for Tips.
    I tried to proceed some new functions in Excel 2016, 2019 , for instance (Filter, Unique,…etc), but it does not work.
    However it works properly in Google Sheet. My question now how can i utilize it in google sheet then copy it back in excel 2016 as i can not completely depend on Google sheet.

    Or what is the alternative process to overcome this problem (to use new functions in excel 2016).

    awaiting for your kind support.

  32. Thanks for Tips.

    I tried to proceed some new functions in Excel 2016, 2019 , for instance (Filter, Unique,…etc), but it does not work.

    However it works properly in Google Sheet. My question now how can i utilize it in google sheet then copy it back in excel 2016 as i can not completely depend on Google sheet.

    Or what is the alternative process to overcome this problem (to use new functions in excel 2016).

    awaiting for your kind support.

  33. Great information. Would love to get a bullet point in your description of what each point is and where it is on the timeline. I don't have time for the whole 12 minutes, but can quickly grab what value I need and move on. Good for me, good for you, good for all. 🙂 Thanks for putting this together. Grabbed 2 tips and put them into my work flow.

  34. Can anyone tell me how to add a number per day that goes by. So say I started something today and six days go by it would indicate that it has been six days since that was last checked?

  35. You gotta love that pure white-bread pronunciation of Jimenez at 10:09 — "Jim in nez" LOL. I'm not even Hispanic & I know that it's pronounced "He men es"

  36. Collaboration for notifying others:
    it's better to use @ sign rather than + sign as more people are used to that as they use it in WhatsApp often.

  37. I often use the "concatenate" formula to add certain characters and spaces necessary to email addresses that follow certain conventions in order to be able to copy/paste them for mass emailing purposes to my entire faculty.

  38. I have been watching your videos for quite some time now & I really appreciate your explanation & the tips you share. I like it keep up the good work

  39. Hello! I would like to request a video on how to use CheckBoxes? I have a couple of ideas I wanna try with my budget spreadsheet however I can't seem to pin-point relevant answers. I'm sure you have quite a few tips and tricks when it comes to using this formula! Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *