Leveraging AI for Enhanced Excel and Google Sheets Functionality

Another project idea is using AI in Excel or Google Sheets. So first you’ve got to find a spreadsheet to work with. You could have course use your own Excel files from your company or Google Sheets if you're using Google, or we do have some Excel files—just a couple of those—in our class files that we provided as well.

Another interesting idea is to go to kaggle.com, where there are many datasets that you can download. These are typically, a lot of times, CSV files and those are easily opened in Google Sheets or Excel. You can find something you're interested in and think of what your goal is—what columns you might want to add, what charts you might want to create.

This could be anything from doing some sort of calculation, like maybe you're trying to calculate commissions based on sales. You're trying to project future trends, so you could be looking at past data and want it to generate projections of what it might do in the future. Identifying top performers—you can have it do things like highlight certain values, like maybe identify the lower performers or the things where you lost money—so you could do conditional highlighting.

So it could be Excel formulas or Google Sheets formulas, or it could be task instructions. The result of these types of queries might be a formula, or it might be an instruction list on how to do those things. Now the question is: Do you use ChatGPT or do you use Copilot? If you're paying for Copilot and have it integrated into the Excel app, of course, and you're using the chat in Excel, it can actually do things for you.

So it can actually insert the columns, it can actually do some conditional highlighting. As opposed to if you're just using the chat experience—either just the free chat of Copilot or ChatGPT—they’re not going to be able to do this stuff, but they will be able to tell you the formulas to insert and the ways that you could do conditional formatting. So they'll tell you the step-by-step instructions on how to do those things. Same thing if you want to create charts or Pivot Tables to learn some information.

If you're using Copilot in Excel, you'll be able to actually have it do it for you, whereas if you're in the chat experience, it's going to have to tell you how to do it, and then you'll have to actually go do it. With Google Sheets, it's always going to be giving you the formulas and telling you how to do things. You would have to be using Google’s Gemini, which is built into Google Sheets, if you were doing things there. If you do have that, it could also be a challenge as well.

Can you do the same types of things we learned with ChatGPT and Copilot? Could you do those with Gemini built into Google Sheets? Now, if you are using the chat experience or ChatGPT, one idea is you could upload—let’s say—an Excel file, or you could do a screenshot. Let’s say you’re using Google Sheets with ChatGPT. You could do a screenshot of the data.

You don’t have to screenshot it all, but just screenshot the headings and the column labels—like is it column A, B, C? Rows 1,2, 3? Take a screenshot so that it can understand which columns your data is in and what kind of data you have. That way you don’t have to describe, “I’ve got this kind of data in column A, I’ve got this other kind of data in column B, my records start in the second row because my headers start in the first row”—those sorts of things. It’s a lot easier if you just give it a screenshot and it can interpret the information because it can read text in screenshots.

Now, after you have gotten the recommendation—whether that’s a formula or a specific set of instructions to go do something—you have to try to implement that solution and see if it works. So first of all: Does it work? But also, look at the solution and say, is this correct? Do what I call the sniff test. Does everything smell right? Does it seem right? Does something smell off? Do the calculations seem correct? Actually look at the formulas and try to understand them—especially look at the calculations it’s trying to do and see if that makes sense.

Don’t just blindly trust the AI doing it. And if you can’t understand the formula, you can also ask it to explain the different parts of the formula. Say, “Please explain this, break it down step-by-step so I can understand what’s going on and make sure that it makes sense.” That also goes for if you were given an Excel file or Google Sheet document and you want to try to figure out what a formula does that somebody else wrote.

You can also copy and paste that into ChatGPT or Copilot and say, “What does this formula do? Break it down for me.” That way, you can understand somebody else’s solution as well. So try this out yourself and see what you can get out of Excel or Google Sheets.