5 Google Sheets Tips Every User Should Know!
HTML-код
- Опубликовано: 8 июн 2024
- 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: ruclips.net/user/simpletivit...
► 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
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&"")
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&")")
+simpletivity please view my reply
The QR code to is useful. Thanks 👍
How to add user id in the google sheet automatically when those user id make any changes in the cell?
@@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 )
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.
Amen. I noticed that the QR code was different
Ctrl-shift-v will paste as values
@@meilinchen138 paste values is only a command within Sheets
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👍🙏
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!
Sparkline trick is insane!
Glad you liked that one.
another great tip. Thanks, Scott.
Thank you for having such a detailed video description. Great info 👍🏼
I’m only on number 2, and I must say, you’ve already provided value. Thank you, buddy
You are very welcome!
the trim is what i look for. ! thank you so much
Great tips! Especially the cleaning up email and names, super handy.
Glad you liked it!!
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.
Amazing! I learn something new today. Thanks so much for sharing!
Thanks, Scott. I use macros to save time. You can automate a lot of repetitive tasks.
That's a great tip. Thanks for sharing.
Yup, love these. Didn’t know the first one but that’s really cool.
Wow that Trick with the QR Code is amazing!! Thanks for the inspiration!
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))
Great tips, also great voice. Sounds like a younger version of the narrator in "The Stanley Parable".
Thank you so much for this video. I learn a lot.
Awesome stuff, I'm new to Google docs and this was very helpful!
Love the QR code. Next level!
Thank you very much... I love your tips with QR Code :)
Love the QR code one!
Wow these just keep getting better. Way to go Scott
So glad that you are enjoying my videos, Richard. Thanks for watching!
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.
Really loving explaining
This is really helpful! Love this video. Thank you.
Thanks so much, Morgan.
Thank you for sharing, It's really helpful and meaningful too
very cool. please continue expanding on this. You might get into how to connect sheets and specific cells of specific sheets.
Great suggestions. Thanks for adding!
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.
I've already have done this.
Use "define range" and "IMPORTRANGE"
superb speech & dialogues delivery timing.... Great.
Thanks a lot
Thank you so much for uploading this video. It is helping me get through the pandemic!
You're so welcome!
QR code
Really awesome.
Thanks Man
so glad you are doing this for us bless u!
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 !?
These were completely unexpected! Thank you for the valuable data
Super helpful!! Thank you
@simpletivity - a tips video on youtube that actually has useful tips that a new concept, I like it keep going
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.
Thanks for sharing!
John Deir
Thanks for sharing these 3 formula.
It's really very useful.
Thank you.
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. 👍
Thanks for sharing!
Amazing! simply amazing,,, so useful,,,, thanks so much
Great tips.. very useful. My favourite sheet function is CONCAT
Thanks. Very helpful
What a blast! Can't wait to try it out!!!!
Have fun!
Thank you very much bro.
You are a life saver!
Very Interesting. Thanks for those tips... I was surprised by the QR code trick
You're welcome!
That was really useful.
Every one of it. 😊👍🏼
This is the best content now
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!
Good point. Thanks for sharing!
@@Simpletivity and when you forget to do that... Ctrl-z to the rescue
Subscribed! Thanks!
Brilliant tips! Thank you
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.
WOOOOOOW. Crazy things. Thank you SIR
You are very welcome
Wow! Thank you so much for such great tips!
You are so welcome!
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.
This tips are awesome. I use a lot Select, If and Vlookup functions
Heavy content!! Thanks!
You're welcome!
that was something informative as well as interesting!!
Wow, amazing tricks. bar code generator is my favorite from this video.
Great to hear!
I like the script editor and being able to use JavaScript in my spreadsheet
Love all your videos ,really helpful .
So nice of you
Very cool. Thanks
Google finance formulae are some of my favourites for stock markets.
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!
So glad you enjoyed this one!
Hi there. I will not mind teaching you some of the basics to get you started with Google Sheets...
@@genysisonline8496 thank you for the offer but I will stick Excel for now.
Thank you for letting me know
@@Simpletivity not now
A Classical Tips. Keep On...
You are extra genius 👏
Great
Good and very informative video
Super helpful! Thanks!
You're welcome!
Trim function is amazing
Tip 4 is definitely my favorite as well. Would you know how to do it for internet access and password
Wooot, it saves a lot of effort
Great info. Thank you
Nice !!! Love it . Thank you 🙏🏻
Glad you like it!
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
Thanks so much for sharing!
Thanks for the amazing tips! The best feature for me are query and importrange
Great to hear!
Query is great. Especially if you have some experience with sql.
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!
Awesome, thank you!
Thank you.
Thank you for the Google sheet and Simple tivity.
You are very welcome!
This is really great stuff. I wish to learn how to do more on Google Sheet
So glad you liked this one, Jennifer.
Liked your content. Subscribed!
Great info. Tnx! But it's kinda lengthy for the topic.
Great usefull tricks, Thanks very much for your video .
You are welcome!
Very good!
thanks scott this is really useful
You are very welcome!
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
Awesome, thank you!
I have been using Trim since Lotus 1-2-3. The rest of the information in this video is really very useful
Thanks for sharing, Victor.
Its like high school all over again, some guy bragging about using Trim.
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.
I didn't know there was a way to send an email from a comment. Good video.
Amazing!!!!
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
I really appreciate that! Thanks for taking the time to share.
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!
Great idea!
These are cool! Thx!
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
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
Thanks so much for sharing this great tip!
excellent video! i would like to know how i can open my sheets and open in the last line written! thank you!!
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.
The comment tips really new for me
Thank you
I would love to use google sheets for a payroll tracker. Do you have any templates or suggestions?
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.
Import range and Vlookup is great.
Great tips! Thank you for sharing.
You are very welcome. Thanks for watching!