In this lesson, we will learn about how ChatGPT can help with spreadsheets, including formulas, custom functions, data analysis, shortcuts, data visualization, spreadsheet organization and navigation, and error handling.

We will look at some sample data and play around with different prompts to see how we can troubleshoot and solve problems using ChatGPT.

The training applies for both Microsoft Excel and Google Sheets.

Transcript

In this lesson, we’re going to cover spreadsheets and how Chat GPT can help you with them. And I know about you, but I use spreadsheets all the time. I love spreadsheets. And in business, you’re going to be using them all the time. You need to be looking at all of your data, your sales figures. People use them for leads and storing all kinds of information. So we’re lost without them. But now, with the help of ChatGPT, we can do things that were previously very difficult, or we just didn’t know how to do. Maybe we had to outsource them. So we’ve got an amazing assistant now who can say, Oh, no, this is how you do it. So how can ChatGBT help you with spreadsheets? First of all, it can help you with formulas. So I know some basic formulas, some very basic ones that I use for my spreadsheets, but anything outside of the basics, and I’m just stuck, and I’ll have to go on Google and try and find a reputable website that does the exact thing that I want it to do. But with ChatGBT, we can literally troubleshoot any formulas that we’ve used in Google or Excel.

So this is the other thing. You can use ChatGBT for either of them. It doesn’t have to be just Excel. It knows Google Sheets just as well. And that’s what I’m going to be using in my demonstration here because we’re going to also go and try out a few different prompts, and we’re going to play around with some sample data. So really good for helping you with formulas. Custom functions, like really complicated ones that use scripting languages. So for example, with good old Microsoft Excel, you can do macros and things like that using Visual Basic way outside of the capability of a normal person. You’re now into being a programmer, really, to be able to do that stuff. And with Google, they have their own version of scripting Google app script that you can use to automate repetitive tasks and basically add new capabilities as it says there. Data analysis. Right now with Chat GPT at the time of doing this video, you need to be talking to it and explaining what’s going on in your spreadsheet and asking for help back. But with the addition of the coding app, for example, or the coding plug in that they’re going to be having, you’re going to be able to just drop CSVs and things into Chat GPT, and it’s going to be able to look at all that data and produce graphs for you and do all of that stuff.

So it’s already incredible. But with the addition of the plug ins that are coming to everybody, hopefully very soon, you’re going to be able to do so much more and so much more easily. So really good improvements coming. But it’s great to be analyzing data as well. And that’s more geared up to, as I say, what’s coming with the next plug in. Shortcuts. Now, it knows just about every single shortcut that’s ever been made for Excel or Sheets, and so it can help you if you’re doing something repetitively and you just want to have a shortcut to get that feature or that implementation done really quickly, just ask it, what’s the shortcut to do this? What’s the shortcut to do that? And it will help you out. So now let’s actually go and have a look have a little look at a sample spreadsheet right here. We’ll get rid of our slides and we’ll have GPT right next to us ready to help us out. And I’ve got just some sample data here. I’ve just got month, like a typical sales data, very simple data for, say, an online business. You take payments by stripe and PayPal, put in your figures there, and then you’ve got your months there like that.

So just some very simple sample data for us to have a look and play around with. Then also with this lesson, as with many of my others, I’ve also created a mass of prompts for you for all kinds of different things that you can request from ChatGBT. And I’ve categorized them all as well. So we’ve got formula creation, for example, pivot tables, data analysis, creating graphs, advanced functions, data cleaning, conditional functions, formatting and conditional functions. So things like this, look, help me apply conditional formatting to highlight cells greater than a specific value in a certain column. That’s really useful, and it might take you quite a while to work that stuff out if you were just doing it with no help. But we can take something like this prompt, and why don’t we do that just for fun here? Let’s do that very first one. So this is column A. I’m going to say, actually, let’s do column B. So if I go to chat GPT now and I want to turn each one of these into a different color, or actually, why don’t we change this to a minus? I’m going to change that to a minus, and I’m going to change this to a minus, and I’m going to have it color in anything in that column that is a minus as red and anything that’s a positive value as green.

So why don’t we just try and do that now? And we’ll put column B, Help me apply a conditional formatting to highlight cells greater than a specific value in column B in my Google Sheet. I’ll add that to it. And obviously, you can put that in Excel. And let’s see what it’s going to do to achieve that. Here we are. So open your Google Sheet, done that. Select the range of cells in column B where you want to apply the conditional formatting. Well, that’s from here to here. So that’s B2 to B13. And then it tells you how to select the entire column, which would just be clicking on B. And then click on Format in the top menu bar. So we can do that. And then what’s it saying now? Then it’s saying Conditional Formatting just here. Oh, I see. Okay. And next up, it’s saying in the Conditional Format Rules pane that appears in the right hand side of your screen, you’ll see that. And then we can choose a Formatting style and so on. Okay, now I’m going to just rather than just leave that, I suspect you can do this. Here we go.

Yes. So greater than or equal to or is less than. So if we’re put is less than zero, then we can change it to red. There we go. And that’s done that really easy. And we can easily add another rule by the looks of things and just say that if it’s greater than zero, we will change it to go green. There we go. And that’s it. That’s done exactly what I wanted. And I didn’t know how to do that until the good old ChatGBT had done its thing. So that was just a really quick example of how we can use ChatGPT for instantaneous help on any a spreadsheet task. So more on the different prompts that we’ve got here as well. We’ve got some on collaboration and sharing, some general help like, help me share my Google Sheet with a specific email address. Show me how to protect a range of cells in Excel, and then explain how to import data from a CSV file, which could be very, very useful. We’ve got error handling. So if you have a particular formula that’s not right or it’s just not doing what you wanted, then we can always go into here and then paste in the problem.

So let’s create a problem. So I’m going to say in this D column here, I’m going to put equals sum, which is a normal formula that you used to add up. And I’m just going to put something totally incorrect there, like a 1 plus, let’s say, b2, which is a number. So I’m trying to add deliberately a word to a number and get the sum. And so I’ve got an error here. And what we need to do now is we need to give that error right there to chatGPT and ask it to explain it. I have just tried to add two cells contents together in Google Sheets and got the following error. Can you please help? There we go. So there’s the error. And now it’s saying the error message you’re encountering indicates that you’re trying to add a text value, which is month, with a number which is not possible in Google Sheets. To resolve the issue, ensure that both cells you’re trying to add together contain numerical values, and then it’s going to actually give a demonstration of how to do that. So click on the cell that you’re trying to add where you want the sum to be displayed.

So let’s delete what we’ve got there in the way of an error. Then it’s saying to enter the formula equals a 1 plus b1, and then replace with the appropriate cell. So equals b 1 and then it’s saying plus c 1 in our instance. In fact, no, we want two, don’t we? So we’ll just get rid of that. Hang on, there we go. Now we’ve got our two numbers, that’s it. And then we just press Enter and that’s it done. And then, of course, we can also use the autofill, which will just go along and do the same thing for everything, which is right there. And that gives us our total. So let’s put that in there at the top. There we go. And I’m just such a fusspot. I got to have it the same size. There we go. All right, next up, time Saving Tips and Shortcuts. As I said, it just knows just about everything here. Help me learn some useful keyboard shortcuts in Google Sheets. Let’s do that. So we’ll put that straight on in that prompt there. It doesn’t matter if you go and put in an emoji, by the way, that’s no problem.

And here we go. So Move One cell up is the up arrow, Move One cell down is the down arrow. And then you could just copy this and print it out as well. If you’re using Google Sheets or Excel regularly, this is going to be really handy for you. Also good if you ever wanted to create a blog or something on handy hints for Excel or Sheets. Data visualisation. These are really useful too. Now, you’ll notice that most of these prompts will either say help, show, teach, explain, or guide. And that’s the best way to get help from ChatGBT is to put that first word in there that’s most important for you. Show me how to create a dynamic chart title in Google Sheets. Wow. I’m going to just scroll down to the next bit. Here we go. Macros and scripting. Help me record a simple macro. That’s good. And then spreadsheet organisation and navigation. Show me how to rename, add or delete sheets in my workbook. Teach me to freeze rows and columns. Why don’t we do that? Let’s copy that one there. And we’ll stop it generating because it is absolutely going off on one with all of these time saving shortcuts, as you can see.

But I’ll just paste it in there. So freezing rows and columns in Google Sheets is a useful feature that allows you to keep certain rows or columns visible while scrolling through the rest of the sheet. Here’s how to do it. Open your Google Sheet, but let’s go to our sample data here. Click on the row header. You want to freeze up. I’m going to freeze the top here like this. And then go to the top menu and click on View. Okay. Oh, there it is. Freeze. One row. And that’s what it said there. And if you want to freeze more than one row, you can do that too. There we are. And then it even goes on. So we should be able to… There we go. If we scroll now, you can see it’s just frozen that. That’s really good. That’s it. So now it’s going on as to how to freeze a particular column as well. I would imagine it’s the same way, but I’m not sure, but it’s just going to tell us anyway. So now that we’ve done those few little things with the help of Chat GPT and our sheet here, I’d like to understand how I could create a chart based on, say, all of this data, the total sales using the months there on the left hand side.

Not sure how to do it, so let’s see if we can get ChatGPT to do that with another prompt. I’d like to create a chart in my Google Sheet. From the data in column A, cells 2 to 13, and the sales figures in D2 to 13. Right, let’s see what it does here. To create a chart in Google Sheets using data from B, cells B2 to B13 and column D, open your Google Sheets, select the data range for your chart by clicking on cell B2, holding down the left button. Okay, we’ll do that. So holding down the button there from B2. Okay, no, it’s actually it was actually A that we wanted, wasn’t it? That’s fine. So A right there. So I’ve done what it’s just said. All right, I think we have now successfully selected all of those different columns that we need to. Next thing it’s saying is to then go from the drop down menu, we’re going to go to Google Sheets, choose a type based on a selected data. Drop down menu, choose chart. Okay, I don’t know if we’ve got chart here. Format, insert, and chart. There we go. So I’m going to insert a chart, and there we are.

It’s actually done a chart perfect, exactly like I wanted it. And we’re just going to take that chart now, we’ll put it there. There we are. Total versus the Month. So we’ve got all of our months going down the bottom. And then we’ve got our sales figures going across the top there like that. And it’s also suggesting that we could customise the chart further by adjusting the chart title, axis labels, legend, and other things under “Customise.” So if we click on this, we can edit the chart right there. And customize, there and customize. There we go. Chart style. So we can change the border color. We could make it 3D like that. That’s pretty cool. Never done this stuff. So this is really, really useful. We can bold stuff and we can choose the legend, horizontal axes, and we can change all of the colours, basically, and make things bold like that. So that’s really, really useful. And it has just helped me do that in minutes, really. So that’s another example there of what you can do using Chat GPT. But I think the bottom line is that you just need to ask it what you would like to achieve.

Make sure that you are giving it the correct cell and column information, and it will just make it so much smoother because it will just literally publish it. So the other thing we’ll do now is we’ll get it to create a formula which will tell us how much money on average per month that we have got. So I’ll say now then, I’m just going to give it the problem. I want to work out the average amount of sales in dollars per month I received in a year. Can you create a formula that would take data from column D, cell 2 to 13, and give me the average, please? Here we go then. To calculate the average sales amount per month using data from cells D2 to D13, you can use the average function and here’s the formula you can use. So we can take that code right now and just click on it and then just go to here, paste in the code that it’s given us, and there it is. Done, 19,000. And there we are. And it’s given us a full explanation of exactly what to do. And it’s displayed the average sales for us just right there it is.

So that’s just a quick overview, really, of just some of the skills that Chat GPT has to help you create things with Google Sheets or Microsoft Excel as well, and do things that perhaps that you might have struggled to do without its help. And then don’t forget that we’ve got all of these example prompts here to set you off on the right path and give you some examples of the things that you can ask ChatGBT in all of these different areas of using a spreadsheet, data visualisation, macros, organisation, all of these different things right here. And then if you’ve got any questions, then please do add a comment, and otherwise, I’ll see you in the next training video.