My favorite function that was not mentioned here is =Query(). This function lets you use Query Language (kinda like SQL, if not exactly like SQL). That may seem a little scary at first if you don't know what SQL is, but it's actually pretty simple! You can basically create pivot tables by using this function and then leverage that in other functions. A simple example of how you can manipulate a table of data by using the Query function: Let's say you have 3 columns of data and you want to represent the data in another sheet but without column B, for example (that would look like this: =Query(A1:C, "Select A,C"). Or maybe you want to show columns in a different order; A,C,B, and order them by C and maybe filter out any cells that have a value of 1 in Column B. This last example would look like this: =Query(A1:C, "Select A,C,B Where B !=1 Order By C"). If you take a little bit of time to learn the syntax for this function, it goes a long way!
Use it all the time. Might have to make a follow up video. At the time of making this video I thought it would be a bit advanced but I think its too useful not to use
Very Inspirational, John. I Really Loved Listening To You Talk For 17 Minutes And 29 Seconds. I Had a Joyous Time. My Favorite Was The Sum. My Eyes Have Been Opened To The World Of Google Sheets. Thank You Master.
Thank you for taking the time to include the time stamps. I was able to go directly to the part of the video I needed help with. I appreciate you helping me save time!! Very thorough and knowledgeable!
I have been using spreadsheets for a long time; this was the simplest explanations I have heard, read and seen of some formulas that I have used before and always had to go back to review to understand. Thank you!
Never have I ever thought that I would be as intersted as I am listenign to you educate us on the fumctions!!!! I can not decide which is my favorite. Sparkline is the coolest i think but then again i love the googletranslate!!!! I need tutoring lessons lol let me know
Such nice feedback. Thank you for this. It really made my day! I'm glad to hear you found the functions shared helpful, and yes, sparklines are pretty neat to work with
Excellent explanation, I think one of the very powerful and great functions you left behind is the Query option. But your way of explaining all the functions and the examples are great, thanks for your effort, and keep up the good teaching :)
Thank you for that ! Definitely adding that one to my follow up video! Look like there are many great examples of functions still to be added. Mind you, query is so powerful it might need its own video.
Not really sure if I’m gonna get any response to this but disclaimer I am a dyslexic person so just doing this through S2T mean about 500 other people play dungeons and dragons online using Google sheets I am currently trying to come up with some formulas that would automatically calculate scaling class features based off of character level we use a lot of homebrew rules for advance progression in the game I am confused on how to make an =IF() that could read the characters level for some reason it is still coming up as false this is what I have so far =IF(char lvl cell=“>22”,10+(char lvl cell-20),ROUNDUP(char lvl cell/2)) what I wanted to accomplish is if you are 20th level or lower it takes your level divided by two rounded up to give you your sneak attack dice amount if your 21st level it would just stay at 10 and the progression starts at 22 giving you one for your previous level and the one that you’re currently in and then plus one per level Confused on how to make it do 22 or higher not sure if the server progression is it going to go past 30 so I don’t want to make an equation limited to that I would really love some help it would be very appreciated I am very new at doing this and my learning disability does not help finding proper resources
Is there a way I can change the highlight colour and line thickness to show the current cell. The current blue outline is proving quite hard for me to see. thanks !
Not what I was looking for exactly, but a great video explaining some neat functions with excel! Would really love some functions dealing with statistical significance, like seeing what functions have the greatest impact
Very interesting indeed. Thank you. My favorite function is Google Translate. I was not even aware this existed. When translating from English to Japanese it works beautifully, not just for single words but phrases as well. However, the translation becomes the native Japanese kanji characters system as it should. Much of the Japanese Kanji character system I can read and recognize but sometimes I can not. Q1. Is there a way to convert kanji characters into hiragana characters (simplified phonetics) into an adjacent 3rd column? Q2. Is it possible to activate text to voice as an alternative? I see you also have a flash card video as well. I will have a quick review of that to see if that might answer my inquiry. Thanks. Jerry. Tokyo, Japan.
Nice stuff for beginners and advanced users, however instead of vlookup I would recommend using a combination of index and match. Formula is a bit longer but much easier to read and maintain, it can also do vertical, horizontal and 2D array lookup and it allows for more than one lookup parameter. To the list I would also add iferror().
This is so much helpful. Can you also share a link where I can use the following abbreviation, not so sure if my question is correct say for example "EN" for English, ES for Spanish. Would love to try other language to translate too, Thank you:).
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
Great video thanks. Do you know how to show a progress bar in a cell, but from using text from dropdown options? So I have a column that I manually select what I want from my dropdown box options (I previously set and those are stages through a process we have for a work task), then I want another column to reflect how far complete we are depending of what dropdown option is selected. I hope that makes sense. I've been searching far and wide for months now but found nothing helpful yet. I'd love it if you already have a video for this or could help me out? Thanks in advance
Hello sir I'm from INDIA Thank you very much for influencing our knowledge, after seeing your helpful videos I'm doing better in my office work But I request to you please make video on all the Google sheet functions including engineering, batabase, web , math and all those are in the functions list Please sir 🙏🏼
I tried to put something in there for everyone, so hopefully nobody has to feel they've wasting any time. Glad you got to learn about vlookup. It amazingly powerful.
Google translate function is amazing for world travellers. Just make a list of food you like for each country you are visiting - and show it to restaurants. Especially useful in Chinese or languages with Cyrillic letters
very informative, indeed. I hope these few little things would be added on your next video (not even sure if they are possible in google sheets) calendar in google sheets (dates auto change when the month & year is changed) calendar in google sheets (where you can add appointment/meetings/notes, etc. in particular dates, and will auto hide when the month & year is changed) thanks a bunch!
i want to add numerous apple stock buys 6 + 3 +4 which would be like a ledger . ADD all the apples stock buys and then add to existing stock number on another sheet. How do you do that.?
Hello so ive been trying to get a couple of functions to collect data on the lottery... What would work for tracking a number or numbers and how many games it skips before it plays again?
There's simple functions that's google sheets have but excel haven't, such as: UNIQUE, ARRAYFORMULA, IMPORTRANGE, and SORT. This example below are a simple way to do awesome vlookup with google sheet but you can't do it with excel: Reverse Vlookup Return Value from Below: ua-cam.com/video/yaD8PD7SObQ/v-deo.html
The filter function. I use this all the time I do not use vlookup or hlookup. It will bring back just what you want with adding different criteria to pin point what you looking for. The spark I never seen any use for it it really does not give any info. Maybe you might know I am wanting to insert a chart in a cell that is dynamic.
Thank you for leaving a reply. We actually had a chart during a school activity which updated every couple of second by having it in a separate sheet inside the same workbook which was dynamic. However published charts update much slower and this made it less user friendly. Dynamic charts can be added into sheets with drop-down menus etc.. (using data validation together with vlookup and charts) I will add it to my to do list of videos. In the meantime check out Google data studio. I personally find it much easier to create dynamic charts using data studio that look professional.
Very informative. I need to transfer all of my information from Microsoft. I just found google sheets. One thing I struggle with is adding all the information and then finding I need to put in after I input functions. If that makes any sense. Do you all of the functions just keep moving down?
I use googlefinance a lot, as an example, you can automatically get any stock's (e.g. google, apple etc) historical stock price for the period you specified, as well as current financial information of the company .
I am not a classroom teacher and so I am a true beginner who felt a bit slow and lost during your video which went rather rapido. I am an artist trying to use Google sheets for different purposes, currently keeping track of my expenses and sparse income. I need to know how best to create my sheet to be easier to use, and I want to learn HOW to use functions and all those other processes you speedily referenced. As a classroom teacher, you probably understand ADHD which I am working with as an adult. I feel so impatient with myself.
Thank you so much for leaving a comment and letting me know what tou are working on. I am more than happy to guide in the right direction and as someone who has been diagnosed with adhd myself I more than understand what you are describing. The vast majority of functions has an excellent explanation as and when you start typing them up. That being said, I have been playing with the idea of a more in depth and slower series on functions and sheets. I think your comment may just have convinced me to add it to the list. In addition to this sheets has some built in templates for budget tracking you may fins interesting to explore. Go to sheets.google.com and find the templates option. Then you will find budget templates.
@@FlippedClassroomTutorials thank you for your immediate response ! I feel like I've already taken. Oh my God 3 hours? Just to do this one single task. I need to just quickly total up my expenses and income for each year 2019 and 2020 to hand to our family accountant to do our taxes and I have only discovered how much more I have to learn. Part of me just wants to add it up with pencil and paper in my calculator. I might be easier but if I want to make it easier in the future I do eventually need to learn all this stuff
If all you are doing is adding up and totalling numbers you would be best to use the sum formula. Type = them SUM and between the () you select all the numbers you want totalled together. It's a bit fiddly the first time but after getting the hang of it it really speeds up your workflow and will help to keep things organized.
Hi, maybe you can help. I currently have a spreadsheet where I am tracking dates. If I have columns for (start date),(Week 4), and (Week 8)" instead of manually checking the calendar and enter dates manually for the Week 4 and Week 8 columns, is there a way that those two columns will auto populate the exact date on when is the 4th week and 8th week after entering a date in Start Date? Hope you can help. Thanks!
i can't believe i didn't know about sum if... so many collumns can be combined now... some other great functions as well but im going to be using this a lot in the next few days as i update my sheets
My favorite function that was not mentioned here is =Query(). This function lets you use Query Language (kinda like SQL, if not exactly like SQL). That may seem a little scary at first if you don't know what SQL is, but it's actually pretty simple! You can basically create pivot tables by using this function and then leverage that in other functions. A simple example of how you can manipulate a table of data by using the Query function: Let's say you have 3 columns of data and you want to represent the data in another sheet but without column B, for example (that would look like this: =Query(A1:C, "Select A,C"). Or maybe you want to show columns in a different order; A,C,B, and order them by C and maybe filter out any cells that have a value of 1 in Column B. This last example would look like this: =Query(A1:C, "Select A,C,B Where B !=1 Order By C"). If you take a little bit of time to learn the syntax for this function, it goes a long way!
This one's good. I'm amazed. Thank you for sharing
Use it all the time. Might have to make a follow up video. At the time of making this video I thought it would be a bit advanced but I think its too useful not to use
@@FlippedClassroomTutorials Thank you both.
Very Inspirational, John. I Really Loved Listening To You Talk For 17 Minutes And 29 Seconds. I Had a Joyous Time. My Favorite Was The Sum. My Eyes Have Been Opened To The World Of Google Sheets. Thank You Master.
Thank you for taking the time to include the time stamps. I was able to go directly to the part of the video I needed help with. I appreciate you helping me save time!! Very thorough and knowledgeable!
Glad it was helpful! I try to add timestamp to all my recent videos for just that reason! Hope you stick around for more videos.
@@FlippedClassroomTutorials Very smart decision seeing that UA-cam has now implemented time stamps into the video player. Appreciate the help!
Spreadsheets and I, just like weed eaters have never gotten along with each other, until this video. Great explanatory video. Thanks!
Translate... I'm a homeschool mom and that tidbit was everything I didn't know I needed. Thanks!
Great to hear you found it helpful!
Super-video, Sethi!!! IMPORTRANGE is one of the functions that I often use to insert data from other sheets or other books and process them together.
Brilliant! Adding it to my comment submissions list ;-) feels like a follow up video might be the right thing... Thank you for sharing.
@@FlippedClassroomTutorials Agree, I'd also add QUERY
I'm not a teacher, but thankfully you must be and I'm finding this very informative. Thank you.
Great to hear you found this helpful!
I have been using spreadsheets for a long time; this was the simplest explanations I have heard, read and seen of some formulas that I have used before and always had to go back to review to understand. Thank you!
Never have I ever thought that I would be as intersted as I am listenign to you educate us on the fumctions!!!! I can not decide which is my favorite. Sparkline is the coolest i think but then again i love the googletranslate!!!! I need tutoring lessons lol let me know
Such nice feedback. Thank you for this. It really made my day! I'm glad to hear you found the functions shared helpful, and yes, sparklines are pretty neat to work with
that last one 16:29 seems very useful when you need to grade students and you are too busy.
lol hhhhhhhhhhhhhh
Excellent explanation, I think one of the very powerful and great functions you left behind is the Query option. But your way of explaining all the functions and the examples are great, thanks for your effort, and keep up the good teaching :)
Thank you for that ! Definitely adding that one to my follow up video! Look like there are many great examples of functions still to be added. Mind you, query is so powerful it might need its own video.
This is my favorite Google Sheet tutorial so far. Simple but so helpful!
Translate and Sparkline! I went "Aaaaaww!" when I actually tried them.
Great to hear that!
Not really sure if I’m gonna get any response to this but disclaimer I am a dyslexic person so just doing this through S2T mean about 500 other people play dungeons and dragons online using Google sheets I am currently trying to come up with some formulas that would automatically calculate scaling class features based off of character level we use a lot of homebrew rules for advance progression in the game
I am confused on how to make an =IF() that could read the characters level for some reason it is still coming up as false
this is what I have so far
=IF(char lvl cell=“>22”,10+(char lvl cell-20),ROUNDUP(char lvl cell/2))
what I wanted to accomplish is if you are 20th level or lower it takes your level divided by two rounded up to give you your sneak attack dice amount if your 21st level it would just stay at 10 and the progression starts at 22 giving you one for your previous level and the one that you’re currently in and then plus one per level
Confused on how to make it do 22 or higher not sure if the server progression is it going to go past 30 so I don’t want to make an equation limited to that
I would really love some help it would be very appreciated I am very new at doing this and my learning disability does not help finding proper resources
This video was FANTASTIC. Thank you so much. Very easy to follow along.
Thank you!
This entire video is full of quality content. Thank You for this.
Glad you enjoyed it!
Well, I've been in computing for 40 years an I learnt something! :o)
I very much like how you explain these functions. I'm hoping to find more. Thank you.
All 15 were very useful. Thank you
Brilliant! Thank you!
amazing! Sparkline and translate blew my mind
Brilliant! Thank you for sharing.
I like "google translate" function. Thank you for knowledge.
Is there a way I can change the highlight colour and line thickness to show the current cell.
The current blue outline is proving quite hard for me to see.
thanks !
I use the Sparkline function for various aggregate data in my work.
For example, it's very convenient to put a graph into a Cell row by row.
That sparkline is so useful, i love that one XD
Not what I was looking for exactly, but a great video explaining some neat functions with excel! Would really love some functions dealing with statistical significance, like seeing what functions have the greatest impact
Filter functions changed my life.
It's brilliant!
Never heard of the function Randbetween! Definitely helpful to create an example data base to test certain formulas. Thank you for the video!
Fantastic information! This will be my first time using Google Sheets on this level. Can't wait to give all of this a shot!
Loving the gtranslate function!!
All 15 were very useful. Thank you. Well explain and never have enough of your video..
I'm new learning basics BUT I can say this is a good knowledgeable video with good varieties . Thank u.
Very interesting indeed. Thank you. My favorite function is Google Translate. I was not even aware this existed. When translating from English to Japanese it works beautifully, not just for single words but phrases as well. However, the translation becomes the native Japanese kanji characters system as it should. Much of the Japanese Kanji character system I can read and recognize but sometimes I can not.
Q1. Is there a way to convert kanji characters into hiragana characters (simplified phonetics) into an adjacent 3rd column?
Q2. Is it possible to activate text to voice as an alternative?
I see you also have a flash card video as well. I will have a quick review of that to see if that might answer my inquiry. Thanks. Jerry. Tokyo, Japan.
Well explain and never have enough of your video.
Thank you! Glad you find them all helpful.
Super easy to understand. More please. 👏👏👏
Thank you!
Sparkline is incredible!! 11:22
Thank you! This is cool. The sparkline is my favorite-- didn't imagine that or the translation
Nice stuff for beginners and advanced users, however instead of vlookup I would recommend using a combination of index and match. Formula is a bit longer but much easier to read and maintain, it can also do vertical, horizontal and 2D array lookup and it allows for more than one lookup parameter.
To the list I would also add iferror().
I agree with you Match and Index is lot easier
you can embed a Match() function in place of the VLOOKUP index parameter. That way the Match function looks for the values in the column headers.
I love the little graph in "Sparkline"! Now I just have to make a sheet doc that has numbers like that...
Haha brilliant :-) let me know when you get round to it :-)
Date() was my favorite function. Thank you!
This is so much helpful. Can you also share a link where I can use the following abbreviation, not so sure if my question is correct say for example "EN" for English, ES for Spanish. Would love to try other language to translate too,
Thank you:).
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
Sparkline was the best in this video.....Thanks.
I love the If function. Thank you sir!
Nice video and I appreciate the time stamps.
Glad you liked it!
That was very very helpful dear sir!! Thank u so much
Great video thanks. Do you know how to show a progress bar in a cell, but from using text from dropdown options? So I have a column that I manually select what I want from my dropdown box options (I previously set and those are stages through a process we have for a work task), then I want another column to reflect how far complete we are depending of what dropdown option is selected. I hope that makes sense. I've been searching far and wide for months now but found nothing helpful yet. I'd love it if you already have a video for this or could help me out? Thanks in advance
Very good crash course in some Google Sheets tricks. Thanks for the video!
Hello sir
I'm from INDIA
Thank you very much for influencing our knowledge, after seeing your helpful videos I'm doing better in my office work
But I request to you please make video on all the Google sheet functions including engineering, batabase, web , math and all those are in the functions list
Please sir 🙏🏼
After sitting through most of this video thinking it's a waste of time... I got to VLOOKUP function and my life will be forever changed. Thanks!
I tried to put something in there for everyone, so hopefully nobody has to feel they've wasting any time. Glad you got to learn about vlookup. It amazingly powerful.
I'm new learning basics BUT I can say this is a good knowledgeable video with good varieties . Thank u.
Thanks for awesome 15 fuctions
You're welcome
Sparkling is pretty cool
I have to agree! So much it deserves its own video really
This was such a helpful video! Thank you so much!
Very clear explanations. I would like to know more about Index and Match.
Thank you for all your help!
You're very welcome!
I love the Google Translate and sparkline. Very educational video :)
Thank you for Your very good tutorial, if Chinese what code to be use ? I have tri use CH, is not working. thank you
wow! very useful information. I learned new function key like sparkline, unique and randbetween. thank you so much...
Google translate function is amazing for world travellers. Just make a list of food you like for each country you are visiting - and show it to restaurants. Especially useful in Chinese or languages with Cyrillic letters
Thank you so much! I thought all that was going to be so much harder than that. I just made my first weekly/monthly tip tracker. So easy!
This was so helpful, thanks!
You're welcome
your videos are so easy to understand.Thanks!
for me, the most mindblown formula is importrange :D I just learned it and truly understand it, how it helps me to link data between files.
Thank you very much for tips👍
No problem 👍
Thank you for this very helpful video.
My favorite function is Sparkline.
Sparkline!!! Blows my mind!! Wow! I never knew!
Thank you for this class it will be very helpful
How can add & copy more rows on Google sheets app? A shorts on that would be great
More mobile shorts are coming! And that one is one of the next :-)
You are awsome, my friend. Great information presented in an easy to understand manner.
Cool 😎 I like sparkline , Google translate , but overall ... All are nicely presented.
Thanks! 😃
Thank you for taking the time to do this video.
great explanation. thanks,. greetings from Ouarzazate, Morocco
very informative, indeed. I hope these few little things would be added on your next video (not even sure if they are possible in google sheets)
calendar in google sheets (dates auto change when the month & year is changed)
calendar in google sheets (where you can add appointment/meetings/notes, etc. in particular dates, and will auto hide when the month & year is changed)
thanks a bunch!
That was great to have these functions explained so well,thanks .
You are amazing!! M grateful to God for you.
thanks for the "sparkline".
Always! Love me a sparkline ;-)
Brilliant presentation. Thanks for providing the list of topics with links. Great presenter.
The date trick was great
Thanks!
awesome video. My favorite was google translate, but all very helpful, definitely going to be trying to use some of these! :) Thank you!!
Thank you very much for your excellent explanation.
i want to add numerous apple stock buys 6 + 3 +4 which would be like a ledger . ADD all the apples stock buys and then add to existing stock number on another sheet. How do you do that.?
Didn't expect Google Translate as a function. Thank you!
Excellent! Very valuable video. Thank you
Hello so ive been trying to get a couple of functions to collect data on the lottery... What would work for tracking a number or numbers and how many games it skips before it plays again?
Google Translate!!! That's awesome!
Agree! Love that one.
Thanks for these classes
Sparkling is amazing
I agree!
I have been trying to figure out #9 since I was born. Thank you.
Thank you for the vlookup instructions...
There's simple functions that's google sheets have but excel haven't, such as: UNIQUE, ARRAYFORMULA, IMPORTRANGE, and SORT.
This example below are a simple way to do awesome vlookup with google sheet but you can't do it with excel:
Reverse Vlookup Return Value from Below:
ua-cam.com/video/yaD8PD7SObQ/v-deo.html
The filter function. I use this all the time I do not use vlookup or hlookup. It will bring back just what you want with adding different criteria to pin point what you looking for. The spark I never seen any use for it it really does not give any info. Maybe you might know I am wanting to insert a chart in a cell that is dynamic.
Thank you for leaving a reply. We actually had a chart during a school activity which updated every couple of second by having it in a separate sheet inside the same workbook which was dynamic. However published charts update much slower and this made it less user friendly. Dynamic charts can be added into sheets with drop-down menus etc.. (using data validation together with vlookup and charts) I will add it to my to do list of videos. In the meantime check out Google data studio. I personally find it much easier to create dynamic charts using data studio that look professional.
I totally forgot about filter function! Thank you for adding that. Might have to do a follow up video with submissions from this comment section!
great video!! Thanks!!
I didn't know about Google Translate...amazing!
Very informative. I need to transfer all of my information from Microsoft. I just found google sheets. One thing I struggle with is adding all the information and then finding I need to put in after I input functions. If that makes any sense. Do you all of the functions just keep moving down?
Great tutorial and very helpful!
Thank you
Great and thanks well... I wanna know colunm chart in cell, like you shows spark line chart. Is it possible column chart instead of line chart.
Thank you, I am going to share it to the other people
Thanks! Glad you find this useful!
I use googlefinance a lot, as an example, you can automatically get any stock's (e.g. google, apple etc) historical stock price for the period you specified, as well as current financial information of the company .
I am not a classroom teacher and so I am a true beginner who felt a bit slow and lost during your video which went rather rapido. I am an artist trying to use Google sheets for different purposes, currently keeping track of my expenses and sparse income. I need to know how best to create my sheet to be easier to use, and I want to learn HOW to use functions and all those other processes you speedily referenced. As a classroom teacher, you probably understand ADHD which I am working with as an adult. I feel so impatient with myself.
Thank you so much for leaving a comment and letting me know what tou are working on. I am more than happy to guide in the right direction and as someone who has been diagnosed with adhd myself I more than understand what you are describing. The vast majority of functions has an excellent explanation as and when you start typing them up. That being said, I have been playing with the idea of a more in depth and slower series on functions and sheets. I think your comment may just have convinced me to add it to the list. In addition to this sheets has some built in templates for budget tracking you may fins interesting to explore. Go to sheets.google.com and find the templates option. Then you will find budget templates.
@@FlippedClassroomTutorials thank you for your immediate response ! I feel like I've already taken. Oh my God 3 hours? Just to do this one single task. I need to just quickly total up my expenses and income for each year 2019 and 2020 to hand to our family accountant to do our taxes and I have only discovered how much more I have to learn. Part of me just wants to add it up with pencil and paper in my calculator. I might be easier but if I want to make it easier in the future I do eventually need to learn all this stuff
If all you are doing is adding up and totalling numbers you would be best to use the sum formula. Type = them SUM and between the () you select all the numbers you want totalled together. It's a bit fiddly the first time but after getting the hang of it it really speeds up your workflow and will help to keep things organized.
Split() is really useful.
Hi, maybe you can help. I currently have a spreadsheet where I am tracking dates. If I have columns for (start date),(Week 4), and (Week 8)" instead of manually checking the calendar and enter dates manually for the Week 4 and Week 8 columns, is there a way that those two columns will auto populate the exact date on when is the 4th week and 8th week after entering a date in Start Date? Hope you can help. Thanks!
i can't believe i didn't know about sum if... so many collumns can be combined now... some other great functions as well but im going to be using this a lot in the next few days as i update my sheets