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.
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!
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.
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.
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
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 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 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))
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 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 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.
Could you help me? I would like to input a time in minutes and seconds, and have it display in just seconds for printing. For example if I type in 0:10:06 or 10.6 (as in 10min 6seconds) I want 606 to be displayed in that cell. If yes, could you also do this with hours?
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.
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.
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/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.
I’m only on number 2, and I must say, you’ve already provided value. Thank you, buddy
You are very welcome!
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
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.
Wow these just keep getting better. Way to go Scott
So glad that you are enjoying my videos, Richard. Thanks for watching!
Sparkline trick is insane!
Glad you liked that one.
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"
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
Thanks, Scott. I use macros to save time. You can automate a lot of repetitive tasks.
That's a great tip. Thanks for sharing.
Wow that Trick with the QR Code is amazing!! Thanks for the inspiration!
Thank you so much for uploading this video. It is helping me get through the pandemic!
You're so welcome!
superb speech & dialogues delivery timing.... Great.
Thanks a lot
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.
This is really helpful! Love this video. Thank you.
Thanks so much, Morgan.
QR code
Really awesome.
Thanks Man
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.
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!
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.
so glad you are doing this for us bless u!
Thank you so much for this video. I learn a lot.
Thank you for having such a detailed video description. Great info 👍🏼
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!
What a blast! Can't wait to try it out!!!!
Have fun!
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!
Love the QR code. Next level!
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! Especially the cleaning up email and names, super handy.
Glad you liked it!!
Thank you for sharing, It's really helpful and meaningful too
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!
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.
another great tip. Thanks, Scott.
Love the QR code one!
the trim is what i look for. ! thank you so much
Yup, love these. Didn’t know the first one but that’s really cool.
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!
Love all your videos ,really helpful .
So nice of you
Thank you very much... I love your tips with QR Code :)
Great tips.. very useful. My favourite sheet function is CONCAT
Thank you for the Google sheet and Simple tivity.
You are very welcome!
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!
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.
Amazing! simply amazing,,, so useful,,,, thanks so much
Great tips, also great voice. Sounds like a younger version of the narrator in "The Stanley Parable".
Really loving explaining
Amazing! I learn something new today. Thanks so much for sharing!
You are extra genius 👏
Great
Good and very informative video
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!
Great usefull tricks, Thanks very much for your video .
You are welcome!
You are a life saver!
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.
love spreadsheets, one day I will be a spreadsheet wizard
I'm cheering for you!
Wow, amazing tricks. bar code generator is my favorite from this video.
Great to hear!
Super helpful! Thanks!
You're welcome!
Awesome stuff, I'm new to Google docs and this was very helpful!
Great video! Best features for me are =QUERY and Macros!
So glad that you enjoyed those two!
Google finance formulae are some of my favourites for stock markets.
Very Interesting. Thanks for those tips... I was surprised by the QR code trick
You're welcome!
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"
These were completely unexpected! Thank you for the valuable data
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.
Thanks, Scott for sharing
You are very welcome!
Thanks. Very helpful
Wow! Thank you so much for such great tips!
You are so welcome!
Very helpful for me. Plz create more content like this!!!
You got it! Thanks for sharing.
I’ve been using Sheets for over a decade and, aside from Tip #1, I didn’t know about any of these!
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 !?
Splendid, useful, and ignore those others: Just Enough Talking.
Thanks, Tim!
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.
Thank you very much bro.
excellent video! i would like to know how i can open my sheets and open in the last line written! thank you!!
A Classical Tips. Keep On...
Thank you for this video. I AM A LOVER OF SIMPLICITY
You are very welcome!
This is the best content now
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), " ")
This tips are awesome. I use a lot Select, If and Vlookup functions
Wooot, it saves a lot of effort
Tip 4 is definitely my favorite as well. Would you know how to do it for internet access and password
Trim function is amazing
Nice !!! Love it . Thank you 🙏🏻
Glad you like it!
Thank you❤
You are most welcome!
Thanks, A lot bro
You're welcome!
Could you help me? I would like to input a time in minutes and seconds, and have it display in just seconds for printing. For example if I type in 0:10:06 or 10.6 (as in 10min 6seconds) I want 606 to be displayed in that cell. If yes, could you also do this with hours?
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!.
Great video! Thanks for it! :D
You're welcome, Jake.
Subscribed! Thanks!
WOOOOOOW. Crazy things. Thank you SIR
You are very welcome
I didn't know there was a way to send an email from a comment. Good video.
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.
thanks scott this is really useful
You are very welcome!
Heavy content!! Thanks!
You're welcome!
That was really useful.
Every one of it. 😊👍🏼
Very cool. Thanks
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 😊
Liked your content. Subscribed!
Brilliant tips! Thank you
I like the script editor and being able to use JavaScript in my spreadsheet