There have been some really cool developments in
AI recently, especially with opening up ChatGPT to the public. This made me think: How could
we use ChatGPT when we're stuck writing an Excel formula? Let me show you some really cool
examples. Okay, so what I'm going to do is take some of the questions you've asked me and see if
ChatGPT can give you the correct answer. In case you're not familiar with ChatGPT is, it's basically
an AI chatbot. You can ask it questions and it answers. It's currently free. All you need to do
is go to OpenAI.com. You can scroll down here, go to ChatGPT, and then try ChatGPT or just go
to this URL directly. As you can see, there is currently high demand for this because it's such
an impressive tool and it's super easy to use. All you need to do is type in your question here
and it's going to answer it for you. Let's take a look at our first question. Here's a comment from
Steel Graves underneath this video and he asked, "I have a question and hoping you have a way to
do this easily. Is there a formula for calculating the number of words in an Excel sheet? I see VBA
but looking for something simpler. Hoping for your wiz brain." Well, it's not going to be my brain,
it's going to be ChatGPT's, so let's see what ChatGPT has to say to this. I'm just going to copy
this part of the question, Ctrl + C, let's go to ChatGPT and see what it has to say. Okay, I'm just
going to paste it in and see what we get. Okay, since "there is no built-in formula in Excel that
you can use to calculate the number of words in a sheet, however you can use the LEN() function to
calculate the number of characters in a cell and then divide the number by an average number per
word to estimate the number of words." That's not really what I want to do. Now, it gets interesting.
You can also use SUBSTITUTE to replace spaces with nothing. Then use the LEN function to count
the number of letters and then add 1 to get the number of words. Okay, so assuming that you want
to check the number of words in cell A1. Okay, so now, it went back to a single cell. I don't
want just a single cell. Okay, so alternatively you can use VBA or macros to do the same. Now you
also have to keep in mind that every time you ask it a question, it can be the exact same question,
it can give you different answers. Look, it's going ahead and writing a VBA code even though we
actually said we didn't want that. We just wanted something simpler. You have defined the function,
you can call it by entering the formula. Oh, it's written a function in VBA, that's also great.
But yeah, I didn't want the VBA version, I want the formula version. So let's see if I can tell
it exactly what I want. I'd like to go with the formula version, update it to apply to range A1 to
C100 and exclude blank cells. Those ones shouldn't be counted. Okay, you can use the following
formula to estimate the number of words in a range of cells while excluding blank cells. Okay,
this formula uses the SUMPRODUCT function...[reading] ...and divide by 5 to estimate the number of words.
You don't want any estimation, we actually want to get the number of words. This formula uses
the same idea of replacing spaces with nothing and counting the number of letters and adding one
to get the number of words, but this time it also makes sure that only non-black cells are counted
by using this. Okay. This seems good, let's just test it out. I'm going to copy it, let's go to
Excel. I'm going to paste it in here, okay, so A1 to C100. Right now I get 0, there's nothing
in there. So let's... Five words. That looks great. That seems to work. And this formula is valid for
older Excel versions as well, and you can update it as you need to depending on your range. Now I
try to get ChatGPT to rewrite this using the new Office 365 function like the TEXTSPLIT function,
and it tried. We had a few back and forth so it even recognizes that TEXTSPLIT is a newer function
that was introduced in Excel 365. So if you're using an earlier version, it's not going to work.
But the problem was that this function wasn't working, so we had a bunch of back and forth
and none of them worked. You just have to keep in mind though that ChatGPT doesn't have access to
the internet and it only has limited information after 2021. But if it knows the TEXTSPLIT function, it
must know the TEXTJOIN function because that got introduced before. But it wasn't able, at least
in this iteration that we were going through, to combine these functions to give me the result that
I wanted, so I ended up writing my own version. In case you're interested, it's this one here. We
can test it out, if I just add other texts, it is both versions give us the same results. It's just much
easier and simpler to write the second version, of course, only if you have Office 365 or Excel 2021.
But the great thing is that the first version it gave me did work. Now let's take a look at another
question. This comment is from Cary, and she asks "I've been trying to use data validation and
cells that have formulas in them, but it seems like it doesn't work even in the latest Excel
versions. For example, cell A2 contains formula B2 plus C2. If I set the validation on cell A2
saying that the sum shouldn't be greater than 10, any number could still be put into cells B2 and
C2, and A2's error message won't be triggered even if their sum is greater than 10." And yes,
that's true. Data validation doesn't work on cells that are driven by formulas. It works on
input cells. Now let's see what ChatGPT has to say about this. Okay, so I'm just going to paste
in the comment and I'm going to remove this part, "just wondering if you already have a video for
this," and let's see what ChatGPT has to say: "Excel's data validation feature is intended to
validate the data that a user enters into a cell, rather than the results of a formula." It's smart; it knows that. "Because the result of formula depends on the values in other cells, the data validation rule
is applied to the formulas input cells." Okay. "To validate the results of a formula, you can
use a conditional formatting rule that checks the result of the formula and highlights the cell
if it does not meet the criteria. For example, you could create conditional formatting that
checks if the value in cell A2 is greater than 10 and applies a red fill color to the cell if it is. This
way, when the values in cells B2 and C2 cause the sum to exceed 10, the cell will be highlighted
in red indicating an error." That's great. It says, "You can also use custom VBA script for
this. Alternatively, if you have advanced formulas in complexity, you might consider using Excel
add-ons." Okay, so we have different suggestions, but one suggestion would also be to add an "IF"
statement right to check if A2 is greater than 10, then it should return error, otherwise valid. Now
let's say we are interested in the "Conditional Formatting" method, but we don't know how to go
about this. So let's see if ChatGPT can tell us. Okay, "To set up conditional formatting for
a range of cells in Excel, use the "New Rule" option. Here's an example of how to set up a rule
that applies a red fill color if the cell value is greater than 10." Okay, so first we need
to select it, go to "Conditional Formatting", select "New Rule", use the formula, and use
this function, and then format it to have a red fill, press OK to close. "You can also
apply a more complex formula, for example you can compare the value in cell A2 with
the sum of values in cells B2 and C2, or use and/or operators for multiple conditions."
Okay, we're just going to go with the simple one. We're going to go with this one and see if we
can get it to work. So let's go to Excel here, then we're going to highlight these cells. It said
go to Home > Conditional Formatting > New Rule. Use a formula, we're going to paste this in, and
format the cell to have a red fill, click on OK and OK. Now let's check this out. We're going
to go above 10 here, looks great. Let's copy these down, and I'm just going to add some random numbers to
these, that's amazing; that works. Next question is about nested "IF" functions that we all struggle
with. So this comment is from Free Pilot which was under the "IF" formula video and they asked
"'VFR' if ceiling is greater than 3000," I think these should be zeros, "and visibility less
than 5 miles, but 'MVFR' if ceiling is between this, and/or visibility is this, but this." So just
reading this can make us a bit dizzy. Now, to be able to solve this, we will have to break it down
into simple steps. Let's see if ChatGPT is able to break it down faster than we can to be able to
generate the nested "IF" function. So I'm just going to copy this, let's bring up the browser and
paste this in ChatGPT and see what we get. Okay, so it looks like you're trying to build a system
for classifying weather conditions based on the ceiling cloud cover and visibility. I had no idea
that's what it was about. And now, it's generating us the code in Python. Yeah, we never told it
which language or which application we need the code for. So let's see if we can specify
that, and let's see if it can turn this into Excel. I'm just going to put in make this into
an Excel formula. Here is how you could create an Excel formula to classify weather conditions
based on cloud cover and visibility. Okay, so it's going on and giving us quite a detailed
explanation of what it's doing there, but for now, I'm just going to ignore that. I just want to
copy this formula and see if it works. Okay, so let's copy code and bring up Excel. I already
have some sample data added here. We're going to test the function on the C column. Now, because
the formula that GPT wrote us was referencing A1 and B1, I'm going to paste it in C1 so that we can
drag this down. And now, let's check if we get the right result. So, "VFR" if ceiling is greater than
3000, it is, it's 3100, and visibility is less than 5 miles, it's 2, we get 'VFR". "MVFR," so we have "MVFR"
here. If ceiling is between 1000 - 3000, it's 1200, that's true,
and/or visibility is three to five miles, so we have four. But here's the thing, the
condition that's being asked is and/or. In Excel, we either have an AND condition or an OR
condition, and in this case, I think it makes sense to have an AND condition. So, both of these
conditions have to be true for us to get MVFR, and that's what ChatGPT has designed as well. It's
written in an AND condition for it. Now, for these last two, I just get "IFR". I'm not seeing "LIFR".
So, what's the condition for that? Well, "LIFR" if ceiling is less than 500, it is, it's 400 here. And/or,
so let's assume AND, visibility is less than one, it is, but I'm not getting "LIFR", I'm getting "IFR",
why? Well, take a close look at the formula here. That last condition never gets executed. Why?
Because, whenever we have a value less than 500, it's also less than 1000, and whenever we have a
value less than one, it's also less than three. So, the formula reaches this part, the condition
is met, and it leaves the formula. It never comes here. So, what's the solution? Well, we just have
to put this condition before the other one. So, I'm just going to cut this one, go carefully
before this condition, and paste the other one in. And now, let's see if we get the correct answer.
We get "LIFR" here. Now, I'm not 100% sure if that's what Free Pilot wanted to see, but with a little
bit of tweaking, we can get this to work. This last one is a common question from our students.
We want to create a VBA code that creates a PDF report for the tab that we select here. So,
if Productivity is selected in this cell, I want the macro to go to the tab Productivity,
generate a PDF file, and save it in the same place that this Excel workbook is saved in. And
if I switch this to Game and run the macro, I want it to go to the Game tab and generate a PDF
and save it to the same location as this file. How do we do that? Let's see what ChatGPT thinks. We
just have to make sure that we give ChatGPT proper instructions. So, I've typed in here: "Write a VBA
Excel macro that looks at the value in cell C2 and finds the worksheet with the exact name. Then
create a PDF and save it to the same location as this Excel file is saved in." So, let's see
what we get. Okay, so here's an example of a VBA macro that does what you described and it's gone
ahead and created the code for us. And it's even adding a message box at the end to inform us that
the PDF was actually saved. So, it also tells us that we can run the macro by opening the Visual
Basic Editor and pasting the code in there. Now, if you don't know how to do this, you can
ask it for proper instructions. In this case, I'm just going to go ahead and copy the code and
let's go to Excel and see what we get. Let's first bring up the Visual Basic editor. So, you can use
the shortcut key ALT + F11 or I'm just going to right-mouse-click on a tab and select View Code.
This is going to bring up the Visual Basic Editor. So, let's insert a new module. Just going to go to
this drop-down, insert a module. Now, let's just paste in the code that we got. The syntax looks
great. We could either directly run it from here or we can attach it to a button or a shape. I want
to attach this to a shape. Notice also, the moment I added my Visual Basic code, it tells me that
these projects must be saved in macro-enabled workbooks. To resume, autosave, save, save to
a macro-enabled file type. So, we have to save this file as an ".xlsm" file. Now, I want to add
a shape to attach my macro to. Let's go to Insert, Illustrations, Shapes. I'm just going to go with
the square. Add this in. Let's assign the macro, Create PDF. Click on OK and let's just quickly
give it a name. Okay, so now let's see if it works. I have game selected. So, this tab should
be saved as a PDF document in the same directory as this Excel file is saved in. Let's run it.
PDF saved as Game.pdf in the same location as the Excel file. Click on OK. Is that true? Game
is right here. Let's open it up and it's there. Now, let's switch this to productivity. Create
PDF, Productivity.pdf in the same location and Productivity.pdf is right here. Isn't that amazing
that we got ChatGPT to write a VBA Macro for us? Okay, so to wrap up, GPT did a great job in
helping us out with our Excel questions. But, you have to be cautious. I've noticed that there
are one or two things that can be a bit off with the answers that we get. So, for example here for
our VBA code, it tells us make sure that you have the add-in for Microsoft Office installed in your
machine for this to work. But, what does it mean by add-in for Microsoft Office? We don't need
that. So, sometimes you get these fishy stuff in the middle of the answers, and sometimes you
can get completely wrong answers. For example here, this is another comment from YouTube.
Someone asked, "I have a question about the #SPILL error. I'm taking error when I'm using index
or unique or filter formulas in the table. Where do I fail? Can't we use these formulas in the
table." And ChatGPT says that the #SPILL error can occur when using certain formulas in a table
if the formula is trying to return a result that is larger than the number of rows in a table. So,
in a way it is correct that it can occur in the table and it does. But, this part is wrong. It
returns the #SPILL error if the range is bigger than a single cell, not bigger than the number
of rows in the table. Okay, so you just have to use it with care. Now, am I worried whether
chat gbt is going to replace me? Honestly, I'm super excited about these developments.
I don't think AI is going away. We just have to find a way to make it work for us. I'm
a teacher, so I'm happy that ChatGPT can help people getting unstuck. So, if you're stuck on a
formula, it can help you figure out the problem. But it can't create, at least not yet, a Power BI
hands-on course from scratch. Right? So, professions are constantly changing with technology, we just
have to adjust the way that we bring value into this world. Let me know in the comments what you
think about this and how you think it might affect your job. Thank you for being here, thank you for
watching, and I'm going to see you in the next video.
