How to Use AI Like ChatGPT with Google Sheets

hello this is Matt with modern school bus in this video I'm going to show you some cool ways that you can use AI with Google Sheets so we are going to cover using AI to do research and generate tables and Google sheets I'm going to cover how you can use AI to actually learn to do things in Google Sheets that you don't know how to do already and then lastly I'm going to cover how you can write formulas with AI when it comes to using AI with Google Sheets you have a few different options so there's the ever popular chat GPT from open AI there's Google's Bard which you can access by going to bard.google.com and then they also have some add-ons that will allow you to use AI directly inside Google Sheets however a lot of these add-ons cost money so what I'm actually going to use in this video is Google's Bard one of the benefits of using Bard is that you can actually export tables directly into Google Sheets so this is what I'm going to use for this video but you can use whichever one you prefer one useful thing that we can do with AI is do research that we can then use to generate a table that we can put into our spreadsheet so in this example what I have is these four cookie types and maybe I want to create a table that has the average calorie content and the average carbs of these cookies and so what I can do is go to our Ai and I can generate a prompt here that will return a table so I'm just going to ask this to return a table that I can paste into Google Sheets that has the average calories and carb content of these four cookie types and so then in quotations I'm just going to write out my four cookie types chocolate chip oatmeal raisin peanut butter and sugar cookie so then I press enter and sometimes this takes a few seconds to generate so now it has finished generating and there's a couple ways you can put this into Google Sheets so there is an export to Sheets button so if I select this it's going to create a new spreadsheet for me and then I can open this and it will create a new sheet with that data so that is one of the benefits of using Bard another way that I can do this is I can just copy and paste this because this isn't a format that I can paste into my spreadsheet so if we go back to Bard it says that they gather this data from multiple sources and these are just averages the actual content will depend on the recipe the ingredient and the size of the cookie so this is just one example of how you can use AI to do research and then put it into a table in your spreadsheet there are a number of different ways that you can use this and ultimately it depends what you are trying to accomplish now one of the more useful things that you can do with AI is use it to learn to do things that you don't know how to do currently so if I go back to my spreadsheet maybe what I want to do now is create a chart with this data and I don't know how to do that I can go back to my Ai and ask it how to create a chart in Google Sheets for this data so what I'm going to do is just type my question here how can I create a chart with this data in Google Sheets so now this has returned a step-by-step list of what I need to do to create a chart with the table so it says open Google Sheets and create a new spreadsheet and then paste the table from my previous response now I already have my table here so I can actually skip those two steps but next it says select the entire table including the headers and then click insert chart so what I'm going to do is select the entire table insert chart so now my chart has been created and what I can do is go back to barn and it provides some additional tips on how I can edit my chart here I'm not going to spend any time on that but I just wanted to show how you can use AI to learn to do things that you don't know how to do currently in Google Sheets and so you can apply this to anything you can even use this to write formulas and functions to accomplish tasks so that's what I'm going to cover next okay I'm going to pause the lesson really quick and let you know about my free course if you are a beginner at Excel or Google Sheets I have a free course with the nine most fundamental lessons that you need to master click the link in the description of the video to get the course now so what I'm going to do next is use Bard to write me a formula so in this example I have a list of names and what I want to do is extract the first name from this cell so if you do not know how to do this you can actually ask Bard to write you a formula to accomplish something like this so all I'm going to do is come to Bard and write write me a formula that will take cell A2 and return everything to the left of the first space so just describe what you want it to accomplish usually the more specific your explanation the better the result is going to be and so here it has provided a formula as well as a breakdown of how the formula works so it looks like this is actually a combination of two functions the left function and the find function so what I'm going to do is copy this and come back to my spreadsheet I'm going to paste this and you can see this actually did work how we wanted it to so I'm just going to copy this and paste it down and this is now extracted the first name from these cells and so if you want to learn how this function works then you can read the explanation of what this is doing so now let's look at another example where I'm going to use AI to write me a formula so in this example what I have here is I have these different product types with sales and so I have colors of hat socks and shirts and what I actually want to do is total up all of the sales for anything containing red blue yellow or pink so this is a little bit more complicated than just returning everything to the left of a space so what I need to do is write a step-by-step breakdown of what I want this formula so what I'm going to do is write a step-by-step breakdown of what I want to accomplish so I have a sheet titled sales that contains sales revenue for different products in column A is the product name which includes the color of the product and in column B is the revenue I have a different sheet called color summary that I want to return the revenue to write me a function that I can paste and cell B2 of my color summary sheet that will take the color and cell A2 of sales and return the total revenue of all cells and column A that match that color so let me just paste this into my Ai and we will see if this accomplishes what we want it to accomplish now doing more complex things like this are always a little bit tricky sometimes you do have to troubleshoot and go back and forth if it did not provide the correct result so it looks like this is the formula that it has given me so what I'm going to do is come and paste this in here and we'll see if this works so this returns zero having a little bit of knowledge of Google Sheets I can see it it's just taking this cell A2 and looking this up here now this isn't an exact match what I actually want to do is when there's a partial match in this column from cell A2 so if this contains red then I want it to return the sum so right now it's just doing an exact match so what I might need to do then is provide that explanation into Bard so let's try that so I'm going to say I need the formula edited so that color summary A2 returns total revenue from sales from sales column A if column a contains the value in A2 it won't be an exact match bought a partial match so let me just fix this misspelling and we will see if this formula then corrects the issue so I can now copy and paste this new formula and it looks like 10 512 for red so let's just verify this works I'm just going to highlight all of the red ones and that actually worked as intended so what I can do now is copy and paste this down and this did accomplish what I wanted to accomplish so you can see that was a little bit more of a tricky formula to break down and have Bard write me a function but it did work so I would recommend any time that you are creating a formula like this with AI reading through this to actually understand how the formula works because that is what is going to improve your Google Sheets abilities now I have covered a lot in this video but really what I wanted to show is just a few different ways that you can use AI to learn and master Google Sheets there are a ton of other ways that you can use this and it is going to require a little bit of creativity on your part to come up with other uses specific to what you are trying to accomplish but that is it for this video thank you for watching and I hope that you found this helpful thank you for watching the video if you haven't already make sure to hit that subscribe button I'm also going to place a link in the description of the video where you can subscribe to my email newsletter to get free updates and lessons sent straight to your email let me know if you have any questions or comments

Channel: Modern School Bus

Views: 2,839

Keywords: ai google sheets, chatgpt google sheets, bard google sheets

Id: FZLMESufdfM

Length: 12min 9sec (729 seconds)

Published: Sat Sep 30 2023