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.
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
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
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 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))
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.
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.
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.
It would be helpful for viewers to know using the trim command that you need to copy/paste the trim command column over on top of itself before deleting column A.
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.
Instead of dragging down a cell millions of lines you could simply construct the cell address of the target cell using functions, for ex cell A1, then choose all the cells in that column where you want the function to show its result and paste the formula in.
