5 Google Sheets Tips Every User Should Know!

Google Sheets can transform your data in so many ways. But do you know these 5 spreadsheet tips? In this video, Scott Friesen shares his favorite ways of managing and editing data within Google Sheets.
If you are looking for the formula for Tip #4 (QR Code), please see the pinned comment below.
00:00 Introduction
00:22 Use Sparkline to insert small graph
02:06 Share comments and notifications directly to others
04:20 Validate email addresses in Google Sheets
06:42 Create QR Codes within Google Sheets
09:38 Clean up and trim name fields
---
► SUBSCRIBE to the Simpletivity channel: kzbin.info...
► Get your FREE productivity guide: www.simpletivity.com/free
► Visit the website: www.simpletivity.com
Let's Connect!
Facebook: / simpletivity
LinkedIn: / scottdfriesen
Twitter: / scottdfriesen
Instagram: / scottdfriesen
Scott Friesen is a productivity specialist and founder of Simpletivity training and consulting. He is also a prolific public speaker, workshop leader, and time management coach.
​​Icons made by Freepik from www.flaticon.com
#simpletivity #googledrive #googlesheets

[@Simpletivity]

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("chart.googleapis.com/chart?chs=200x200&cht=qr&chl="&A1&"")

[@mysamdinarvand2749]

This code is developed of your code with High error correction and can detect text and Url: =IMAGE("chart.googleapis.com/chart?chs=500x500&cht=qr&chld=H&chl="&A1&")")

[@mysamdinarvand2749]

+simpletivity please view my reply

[@krackr8089]

The QR code to is useful. Thanks 👍

[@koleksidoank]

How to add user id in the google sheet automatically when those user id make any changes in the cell?

[@rastatekk]

@@koleksidoank it's done automatical by google. Rightclick in any cell with content, in the meneu is a option called Changehistory (shows history of that cell) Or if you want to see history of the whole document go to file versionhistory. (Not sure if the names are perfectly correct - i use the german UI )

[@scottbelcher3654]

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.

[@TheTruthSeeker756]

Amen. I noticed that the QR code was different

[@PhilHibbs]

Ctrl-shift-v will paste as values

[@robinherrick2177]

​@@meilinchen138 paste values is only a command within Sheets

[@learnspreadsheets]

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!

[@johnnyc6628]

Your channel is pure GOLD. After finding it, I am like a sponge absorbing all the possibilities of Google Suite. My company is now making the transition from Microsoft to Google, and you can imagine how happy I am that I found your channel. You are very easy to follow and understand in a practical way, which is not so often to seen on product reviewers online. Keep it up with the good job👍🙏

[@robertpothier]

Thanks for some cool tips. I love the array formulas. You have to edit your traditional formulas to work with it but it really streamlines your sheets and helps then run more efficiently and it works with most your other formulas. Eg. On the Trim formula you would need to show "how" to expand it by including the colum: =trim(A2) becomes =arrayformula(trim(A$2:A))

[@BappyGolder]

Thanks, Scott. I use macros to save time. You can automate a lot of repetitive tasks.

[@Simpletivity]

That's a great tip. Thanks for sharing.

[@jerryh1937]

another great tip. Thanks, Scott.

[@LanaLoveOfficial]

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.

[@SuperMaximoss]

Lately, In google sheets, I have been putting functions in array formulas to avoid dragging down cells whenever I add new data. I like the sparkling, the QR code, and the trim functions that I learned in this video.

[@BirdSTL]

I’m only on number 2, and I must say, you’ve already provided value. Thank you, buddy

[@Simpletivity]

You are very welcome!

[@angelasmutny7698]

I am just now figuring out the many different ways I can look at my data in sheets. 2 of the most handy things I have found is the 'Explore' button in the lower left corner which will do like 5 different functions with highlighted cells and will make charts off of suggested data. The other is filter view for sorting and cleaning up data. I can conditionally highlight things and then use filter view to sort the data in a way that tells me stuff or so that I can move it because I don't know how to use more advanced lookup or query functions yet.

[@SarahH-oz9cv]

Thank you for having such a detailed video description. Great info 👍🏼

[@julianweiland3501]

Wow that Trick with the QR Code is amazing!! Thanks for the inspiration!

[@bishalkumar]

Thanks for sharing. Other favorites from my list (and reflexing at this moment) Combination of Filter with Sum countA, importrange, substitute, iferror+arrayformula+vlookup Keep Sharing. 👍

[@Simpletivity]

Thanks for sharing!

[@maritvansplunter6924]

Great tips! Especially the cleaning up email and names, super handy.

[@Simpletivity]

Glad you liked it!!

[@GrowWithWill]

Yup, love these. Didn’t know the first one but that’s really cool.

[@Roycosb]

the trim is what i look for. ! thank you so much

[@theproofreader3204]

Amazing! I learn something new today. Thanks so much for sharing!

[@cheewurz]

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 !?

[@jeannettelawson3671]

Awesome stuff, I'm new to Google docs and this was very helpful!

[@vloveg80]

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!

[@Simpletivity]

Good point. Thanks for sharing!

[@jacksonmacd]

@@Simpletivity and when you forget to do that... Ctrl-z to the rescue

[@JohnDeir]

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.

[@Simpletivity]

Thanks for sharing!

[@elvischong9334]

John Deir Thanks for sharing these 3 formula. It's really very useful. Thank you.

[@ahmedam77]

Sparkline trick is insane!

[@Simpletivity]

Glad you liked that one.

[@Trusted_Advisor]

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

[@Simpletivity]

Great suggestions. Thanks for adding!

[@omarkhan3491]

You can use the IMPORTRANGE function to copy an entire sheet You can combine IMPORTRANGE with the ADDRESS function to pick a specific call. There are a whole host of IMPORT functions built into Sheets that are very nifty and useful.

[@michelelee890]

I've already have done this.

[@LuisAzcona]

Use "define range" and "IMPORTRANGE"

[@rogersledz6793]

Thank you so much for uploading this video. It is helping me get through the pandemic!

[@Simpletivity]

You're so welcome!

[@RobsonReisSP]

This tips are awesome. I use a lot Select, If and Vlookup functions

[@bebelynsemiho3143]

Thank you so much for this video. I learn a lot.

[@brighnquisitive6217]

These were completely unexpected! Thank you for the valuable data

[@mattboyle5572]

Love the QR code. Next level!

[@dhaval1489]

@simpletivity - a tips video on youtube that actually has useful tips that a new concept, I like it keep going

[@yuugohachiken5875]

Thank you very much... I love your tips with QR Code :)

[@iobtax]

Wow these just keep getting better. Way to go Scott

[@Simpletivity]

So glad that you are enjoying my videos, Richard. Thanks for watching!

[@Vu.ng1411]

Thank you for sharing, It's really helpful and meaningful too

[@realsdsk]

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!

[@Simpletivity]

So glad you enjoyed this one!

[@genysisonline8496]

Hi there. I will not mind teaching you some of the basics to get you started with Google Sheets...

[@realsdsk]

@@genysisonline8496 thank you for the offer but I will stick Excel for now.

[@chrishart8099]

Thank you for letting me know

[@chrishart8099]

@@Simpletivity not now

[@JarredKalweit]

I'm a big user of query functions. Making a query based on drop-down dependent lists is neat. Creating your owns formulas in GAS is so powerful as well. Love hopping in and typing something up that I need.

[@anandavanderwesthuizen1484]

Brilliant tips! Thank you

[@masterbetongplankton]

so glad you are doing this for us bless u!

[@ajaym6795]

Very Interesting. Thanks for those tips... I was surprised by the QR code trick

[@Simpletivity]

You're welcome!

[@pabloclp87]

Most "top 5 videos" may have one or two useful tips. This might be the first time every single one will be put to use. Thanks!

[@Simpletivity]

Awesome, thank you!

[@b199uybas3]

If I already have an image file name from Google Drive on my sheets, can it automatically using formula display images on my sheet not manually? thank you

[@moustafanafady7064]

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.

[@md.imranrezasuman5633]

Love the QR code one!

[@TiffannyDoll]

Tip 4 is definitely my favorite as well. Would you know how to do it for internet access and password

[@sumataher3293]

Super helpful!! Thank you

[@spb1179]

I like the script editor and being able to use JavaScript in my spreadsheet

[@TheMangoCouple]

Wow, amazing tricks. bar code generator is my favorite from this video.

[@Simpletivity]

Great to hear!

[@DarkMoonDroid]

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!

[@LanaLoveOfficial]

Great idea!

[@rajnishverma942]

QR code Really awesome. Thanks Man

[@LightRhyme]

Wow! Thank you so much for such great tips!

[@Simpletivity]

You are so welcome!

[@kishorroy8234]

WOOOOOOW. Crazy things. Thank you SIR

[@Simpletivity]

You are very welcome

[@ramanabalachandhran]

Thanks a ton for all this, Scott. Btw, an out-of-topic question: What font is being used in the sheet at 05:30 ? Looks beautiful.

[@nirmalaileperuma1741]

Great tips.. very useful. My favourite sheet function is CONCAT

[@FedericaFestaCaputo-oj6jl]

What a blast! Can't wait to try it out!!!!

[@Simpletivity]

Have fun!

[@granadosvm]

I have been using Trim since Lotus 1-2-3. The rest of the information in this video is really very useful

[@Simpletivity]

Thanks for sharing, Victor.

[@wlapp1107]

Its like high school all over again, some guy bragging about using Trim.

[@ExcelComputerInstitute]

superb speech & dialogues delivery timing.... Great.

[@Simpletivity]

Thanks a lot

[@Nigromancy]

Google finance formulae are some of my favourites for stock markets.

[@nirmalasenerath]

Amazing! simply amazing,,, so useful,,,, thanks so much

[@edus5466]

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

[@jenniferfong5606]

This is really great stuff. I wish to learn how to do more on Google Sheet

[@Simpletivity]

So glad you liked this one, Jennifer.

[@viralmehta3866]

Really loving explaining

[@krishnachaitanya9024]

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

[@Simpletivity]

I really appreciate that! Thanks for taking the time to share.

[@pgparkshealthwellness6730]

I would love to use google sheets for a payroll tracker. Do you have any templates or suggestions?

[@morganblarue]

This is really helpful! Love this video. Thank you.

[@Simpletivity]

Thanks so much, Morgan.

[@mrslesleyburdine]

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.

[@Simpletivity]

Thanks for sharing, Lesley.

[@HoltHamiltonFilms]

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.

[@ahmadshaqeeq7439]

That was really useful. Every one of it. 😊👍🏼

[@alejndraalmirowitsch4897]

thanks scott this is really useful

[@Simpletivity]

You are very welcome!

[@deCopra]

that was something informative as well as interesting!!

[@ATeamReilly]

Great info. Thank you

[@chris_ea]

Heavy content!! Thanks!

[@Simpletivity]

You're welcome!

[@campillius]

Subscribed! Thanks!

[@GalloperLeon]

Thanks. Very helpful

[@user-ek2du2fc4v]

Thank you for the Google sheet and Simple tivity.

[@Simpletivity]

You are very welcome!

[@nurlasmini]

Thanks for the amazing tips! The best feature for me are query and importrange

[@Simpletivity]

Great to hear!

[@iruns1246]

Query is great. Especially if you have some experience with sql.

[@kewaldangal8750]

Thank you very much bro.

[@ignaciodebustamantefort]

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

[@lucasvgc96]

i think =ARRAYFORMULA if the most usefull, never have to pull the formula down, have formula in a single cell affect all cells bellow automatically, and it's easy to convert most formulas to an ARRAYFORMULA: instead of referencing A2 for example, reference the range A2:A when inside a ARRAYFORMULA function and it will work. Just don't forget to check if the cell is not empty first, or it will process all cells unnecessarily and fill the cells with garbage =ARRAYFORMULA(IF(A2:A"",TRIM(A2:A),"")) this will check if there's data on anything down starting A2 and apply TRIM to it (formula must be pasted at line 2 because we specified A2 as the starting point) or you could do it like this (even better) ={"TRIMMED",ARRAYFORMULA(IF(A2:A"",TRIM(A2:A),""))} ={"HEADER",ARRAYFORMULA(IF(A2:A"",FORMULA(),"Value if input at column A (A2:A) empty")) to have if at line 1, so it becomes the column header for your data AND your formula logic for that entire column, this is how i always make my formulas 👀 Also, didn't know there was a =IMAGE function, but i did know about the charts API for generating QRcodes

[@Simpletivity]

Thanks so much for sharing!

[@marldanes2212]

Great info. Tnx! But it's kinda lengthy for the topic.

[@mohamedashiq4831]

Hi, Thanks for the great video. I have 2 Google sheets files using the same template[which means the column names are same in both of them]. But they contain different records/data. How do I find the difference between them in an easy, quick way? Attaching a sample file[which is in MS Excel]for clarity. Regards

[@itsmeemzVlog]

Love all your videos ,really helpful .

[@Simpletivity]

So nice of you

[@yoyohoneyeminemGOD]

You are a life saver!

[@rickharold69]

These are cool! Thx!

[@leechengng1412]

Great tips! Thank you for sharing.

[@Simpletivity]

You are very welcome. Thanks for watching!

[@MichaelDaniels]

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

[@Simpletivity]

Thanks so much for sharing this great tip!

[@hbasumatary1995]

hi.. i need one help.. while typing any formula i dont see formula description while using them in that sheet only. Help me

[@ericamartin74]

Great info! Thanks for sharing!

[@Simpletivity]

Thanks for watching!

[@alfredyt]

This is the best content now

[@chrishanson1738]

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

[@nethsovann]

Very useful. Thanks so much. I wonder if you could do a tip on getting emails and storing them in a Google spreadsheet. Looking forward to that.

[@Simpletivity]

Great suggestion!

[@InspiringThings101]

Amazing tips! I knew three of those already ha! But great work regardless, the QR was a gift. Thank you! You got yourself a new subscriber haha

[@Simpletivity]

Awesome, thank you!

[@annaelle.anfisa]

Nice !!! Love it . Thank you 🙏🏻

[@Simpletivity]

Glad you like it!

[@udaykiranblogging]

These are very amazing tips for first time i am seeing ...QR code is crazy ..Thank you very much ❤️

[@Simpletivity]

You are very welcome! Thanks for watching!.

[@jamineearlpauline5235]

Super helpful! Thanks!

[@Simpletivity]

You're welcome!

[@jeffhadley2752]

Very cool. Thanks

[@leeden8193]

Wooot, it saves a lot of effort

[@SirDexteros]

Great tips, also great voice. Sounds like a younger version of the narrator in "The Stanley Parable".

[@salemastery]

Very helpful tips, by the way, how to merge the 2 different sheets (in the same file ) together?

[@georgeevreniadis2377]

What if you want to qrcode a merged sell with string data is that feasible , thank you

[@cmndll]

Hi. Thanks for your video. I need a function to get a url address of images that I put in cells of my sheet, Do you know, by any chance, how can I do this ?

[@engati]

Liked your content. Subscribed!