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&")")
@@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.
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👍🙏
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.
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.
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!
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
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.
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!
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. 👍
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.
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 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.
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
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"
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.
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.
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.
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
Brent Gifford I think I might not have explained very well. I’m trying to make a column that looks like this: 12/02/19-12/08/19 12/09/19-12/15/19 ... As you might imagine entering all those dates manually is a big pain. I’m pretty sure entering a date and dragging down in the corner would just make a column of the same dates.
Thank you for your explanations! Can you please tell me if there is a way to send a text message notification to assorted individuals when a change is made to a Google Sheet?
Very helpful tips. Esp QR & Sparkline functions. Btw, isemail() function does the syntax checking to determine the bad email id, but there are many different ways email can go bad in real-time, so check out Clearout For Sheets in G-Suite marketplace.
![[Live สด] การออกรางวัลสลากกินแบ่งรัฐบาล งวดวันที่ 16 พฤศจิกายน 2567](http://i.ytimg.com/vi/wPd7VFfWHIA/mqdefault.jpg)
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!
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.
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.
Sparkline trick is insane!
Glad you liked that one.
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
I’m only on number 2, and I must say, you’ve already provided value. Thank you, buddy
You are very welcome!
superb speech & dialogues delivery timing.... Great.
Thanks a lot
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!
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"
Wow these just keep getting better. Way to go Scott
So glad that you are enjoying my videos, Richard. Thanks for watching!
Thanks, Scott. I use macros to save time. You can automate a lot of repetitive tasks.
That's a great tip. Thanks for sharing.
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
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.
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!
Thank you so much for uploading this video. It is helping me get through the pandemic!
You're so welcome!
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!
Wow that Trick with the QR Code is amazing!! Thanks for the inspiration!
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.
Thank you for having such a detailed video description. Great info 👍🏼
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".
This is really helpful! Love this video. Thank you.
Thanks so much, Morgan.
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!
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.
Great suggestion!
so glad you are doing this for us bless u!
Thank you so much for this video. I learn a lot.
the trim is what i look for. ! thank you so much
Splendid, useful, and ignore those others: Just Enough Talking.
Thanks, Tim!
QR code
Really awesome.
Thanks Man
Great tips! Especially the cleaning up email and names, super handy.
Glad you liked it!!
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.
I’ve been using Sheets for over a decade and, aside from Tip #1, I didn’t know about any of these!
Thank you for sharing, It's really helpful and meaningful too
Yup, love these. Didn’t know the first one but that’s really cool.
Love the QR code. Next level!
Great tips.. very useful. My favourite sheet function is CONCAT
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.
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 !?
Love the QR code one!
What a blast! Can't wait to try it out!!!!
Have fun!
Love all your videos ,really helpful .
So nice of you
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 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!
another great tip. Thanks, Scott.
love spreadsheets, one day I will be a spreadsheet wizard
I'm cheering for you!
Really loving explaining
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"
Google finance formulae are some of my favourites for stock markets.
Tip 4 is definitely my favorite as well. Would you know how to do it for internet access and password
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.
Trim function is amazing
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.
Thank you very much... I love your tips with QR Code :)
Amazing! simply amazing,,, so useful,,,, thanks so much
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.
excellent video! i would like to know how i can open my sheets and open in the last line written! thank you!!
Wow, amazing tricks. bar code generator is my favorite from this video.
Great to hear!
Amazing! I learn something new today. Thanks so much for sharing!
Thank you for the Google sheet and Simple tivity.
You are very welcome!
You are a life saver!
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.
Thanks for sharing, Lesley.
Great usefull tricks, Thanks very much for your video .
You are welcome!
Very helpful for me. Plz create more content like this!!!
You got it! Thanks for sharing.
Thanks. Very helpful
A google sheet with a web app linked via an API is the ultimate automation tool.
A Classical Tips. Keep On...
You are extra genius 👏
Great
Good and very informative video
Thank you very much bro.
Love these tips!!! Is there a function that will split a first and last names into separate columns?
SPLIT is the command. Can be a little tricky. This gentlemen explains things so clearly. I would appreciate his explanation.
You can try to separate into columns similar to the option in Excel.
Ya using the split function in the data section is one way to do it. Using the split function on the space will separate the first and last name.
I would use a combination of proper, trim and split: eg =split(proper(trim(A3), " ")
Great video! Best features for me are =QUERY and Macros!
So glad that you enjoyed those two!
Awesome! Had no idea. G Sheets seems like a totally unexplored territory to me.
Have fun exploring Sheets!
Super helpful! Thanks!
You're welcome!
I like the script editor and being able to use JavaScript in my spreadsheet
This tips are awesome. I use a lot Select, If and Vlookup functions
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
enter a date, select the cell, grab the bottom right corner and drag
Brent Gifford I think I might not have explained very well. I’m trying to make a column that looks like this:
12/02/19-12/08/19
12/09/19-12/15/19
...
As you might imagine entering all those dates manually is a big pain. I’m pretty sure entering a date and dragging down in the corner would just make a column of the same dates.
@@UltraHuman type those dates you showed me in 2 rows and 2 columns, select the 4 cells, grab the bottom right corner and drag
Brent Gifford I will try it! Thank you 😊
These are very amazing tips for first time i am seeing ...QR code is crazy ..Thank you very much ❤️
You are very welcome! Thanks for watching!.
Awesome stuff, I'm new to Google docs and this was very helpful!
Very Interesting. Thanks for those tips... I was surprised by the QR code trick
You're welcome!
Wooot, it saves a lot of effort
Thank you for your explanations! Can you please tell me if there is a way to send a text message notification to assorted individuals when a change is made to a Google Sheet?
I didn't know there was a way to send an email from a comment. Good video.
These were completely unexpected! Thank you for the valuable data
Wow! Thank you so much for such great tips!
You are so welcome!
Thanks, Scott for sharing
You are very welcome!
Thank you for this video. I AM A LOVER OF SIMPLICITY
You are very welcome!
great video! but i'm having issues copy pasting the image of the QR code to programs outside of sheets, or is that impossible?
Regarding the QR code, is the QR dynamic? Meaning if I change the targeted cell and the QR will always take reference to the latest URL?
Thanks, A lot bro
You're welcome!
This is the best content now
Great info. Tnx! But it's kinda lengthy for the topic.
This is really great stuff. I wish to learn how to do more on Google Sheet
So glad you liked this one, Jennifer.
Import range and Vlookup is great.
Very helpful tips. Esp QR & Sparkline functions. Btw, isemail() function does the syntax checking to determine the bad email id, but there are many different ways email can go bad in real-time, so check out Clearout For Sheets in G-Suite marketplace.
Thanks for the info!
the image changed when you pasted it. I think that might have happened because you were in the same sheet... it changed the reference cell.
Noticed that as well. He should have Pasted "Values Only" in order to duplicate the image.
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.
Nice !!! Love it . Thank you 🙏🏻
Glad you like it!
Very helpful tips, by the way, how to merge the 2 different sheets (in the same file ) together?