Today, we're going to be exploring ways you
can leverage ChatGPT to transform the way you work with Excel. Imagine having formulas
effortlessly crafted for you and uncovering shortcuts to make everything simpler. But
before we dive into our first example, I've got a quick question for you. What's the
one Excel task or formula that always seems to stump you? Share your answers in the comments.
I'm curious to see what challenges you face. Here I have a dataset containing
the sales for a coffee shop, selling six types of coffee. Now let's say I want to find out what the maximum sales revenue
was from any coffee, but I have no clue which Excel function to use. Let's open ChatGPT
side by side Excel. I'll use the Windows shortcut, key, windows and the left arrow to dock Excel
to the left side and then select ChatGPT for the right side. In the message box, I'll ask my
question, "What Excel formula should I use to find the maximum value in a
column?" And just like that, it replies with the answer. And it says here, replace A1 to A10 with the actual range where your data is located. Let's try
it, I'll copy the code and paste it into Excel and replace this range with
the cells in my table. And there we go. Of course, now I want to know what the best-selling coffee
was, so let's ask ChatGPT. "What formula would I use to find the product that had the maximum
sales where the product is in cells C4 to C9 and the sales values are in D4 to D9, I'm using
Microsoft 365?" Specifying the cell references here saves me having to modify the formula that
ChatGPT returns and telling it what version of Excel I have, helps it know what functions I can
use. It suggests the INDEX and MATCH functions, which can be a bit daunting. So let's ask it if
there's an easier formula I can use. Okay, it's suggesting XLOOKUP and that'll partly be
because I told it I have Microsoft 365 and it knows this function is available to me. So
you can see that follow-up questions to ChatGPT are a great way for beginners to rapidly
become experts at Excel. Here, within seconds we found two solutions to one problem, something
that can often take many years to discover on your own. Alright, I'll copy
the formula and enter it in my spreadsheet and I can see the best-selling coffee is Frappé. Chances are you run into formula
errors working with Excel, I've definitely had my fair share. It happens to all of us. The great
thing is ChatGPT can help us out. It's not only good at creating formulas, but also
at fixing those pesky errors. Suppose beginning the new year, our
coffee shop is going to add four more types of coffees to its
menu. I've created a spreadsheet to record the sales. There are four columns in my
table and I've calculated the average revenue per coffee in column D, But as you can see, the
four new coffee types have no prior sales. So my formula returns a #DIV/0! error. Now the error
will resolve when I enter sales for those coffees, but the errors are off-putting, so let's see if
we can hide them. "In my sales data table. I'm trying to calculate the average revenue per item
in range D4 to D13. The formula in D4 is B4/C4, but it's giving #DIV/0! error when sales are
zero. How can I fix this?" Okay. It's suggesting using the IFERROR function. Let's copy it and
I'll paste it into my table. Job done! Now we have "N/A" instead of ugly #DIV/0! errors. Of course,
you could also replace now with a 0 if you prefer. Returning the appropriate formula or debugging a
formula is just the beginning. ChatGPT is capable of much more. Imagine the new coffee shop has now
expanded into five countries. We've gone global! And now my spreadsheet contains consolidated sales
for the last two months. There's also another sheet someone else inserted that only shows sales
from Canada for Frappé, which was our bestselling coffee, remember? It uses a FILTER formula, but
it looks a bit complicated. So let's ask it to explain this Excel formula step by step and then
I'll copy the formula and paste it into ChatGPT. Specifying step by step in the prompt ensures
I'll get a breakdown of each component rather than just a general overview. Okay, point 1 says
FILTER(Data!A:D,..., is the main structure of the formula which filters a specified range based on
certain conditions. Point 2 explains this part of the formula is filter criteria Data!A:A, checks
if the values in column A equal "Canada" and Data!B:B checks if the values
in column B equal "Frappé". 2 boolean arrays are multiplied together using an * which acts as a logical AND operation. This
means that only rows that both conditions are true will pass through the filter and point 3
"Canada has no Frappé sales" is value to display If no rows meet the filter criteria and then it
goes on to summarize further. But now at least it doesn't look so daunting, and I think I can
modify it to suit my requirements. For example, let's change it to Turkish Coffee and I'll add
another condition where sales on the Data sheet column C are greater than 10,000. There we go!
Remember, if you make a mistake, you can always ask ChatGPT to fix it for you. As you can see,
ChatGPT is extremely helpful when you're working on a spreadsheet you inherited from someone
else, making it super quick to get up to speed. Speaking of help, if you're still finding Excel
a bit daunting, I've got the solution for you. Check out my range of Excel courses designed to take you from beginner to
pro in no time. You can find them all by clicking the first link in the video
description. One of the easiest ways to save time in Excel is with keyboard shortcuts, but with over
200 shortcuts. It's a lot to remember. But ChatGPT can recall them in seconds, for example, AutoSum
is one of the most versatile shortcuts, but if you don't use it regularly, it's easy to forget.
I'll ask ChatGPT, "What's the Excel keyboard shortcut to quickly add up
numbers in a column in Excel?" The answer is ALT+ =. Notice, it's also given me the Mac shortcut as
a bonus. Let's try it out ALT+ = Tada! If you like shortcuts, be sure to download my "Periodic Table of Shortcuts" from
the link in the video description. There's a version for Windows
and a version for Mac. Macros function as your dedicated Excel helpers, automating repetitive tasks.
They're written using Visual Basic for Applications or VBA for short, and they're capable of handling tasks ranging from basic formatting to complex
calculations, with the click of a button. I've created a PivotTable to help
me quickly analyze my global coffee shop empire, and I share this report with some investors.
I have an assistant updating the source data, but I want to make sure the PivotTables are
always up to date and that requires a manual click of the refresh button. And while it's not hard
or time-consuming, it's critical that it's not forgotten. The last thing I want is my investors
getting a report that's out of date. Now, I'm sure I can do this with VBA, but I'm not
a programmer, so let's ask ChatGPT to help. "Create VBA code to refresh PivotTable1 located
in the VBA_Pivot sheet when the Data sheet in the same workbook is no longer active. I'm a beginner and I need to know the steps
to save the VBA in my file." Again, I'm telling you the names I think it
will need to reference in the VBA code that way I have less edits to make and in mentioning
my level of ability it'll give me more detailed instructions to ensure I can use the code that
it provides. Alright, it's written some VBA code and it explains how to add it to my workbook.
So, let's follow the steps and see if it works. I'll copy the code, and then I'm back in Excel. Step 1 is ALT+ F11 to open
the Visual Basic Editor. Step 2 is open the Project
Explorer with Control + R. Step 3, expand the workbook,
but mine's already expanded. Step 4 is right-click 'ThisWorkbook' and select
'View Code'. Step 5 is paste in the VBA code and then close the VBA editor. Okay, to test it
out, I'll edit the values for Canada for January, so make a note of the amount here and on the
Data sheet, I'll change the value for Canada Cuban Espresso. And as I navigate away from
the date sheet, the pivot tables should update. And you can see the amount for Canada for January
has changed. Just keep in mind that when you add VBA to your file, you have to save it as
a .xlsm file type. As it explains here. Now, VBA is the original way to automate tasks in
Excel and it still used a lot, but it can't execute in Excel online. Whereas the newer way to
automate tasks using Office Scripts can. So check out this video next. Don't forget to like and
subscribe and I'll see you in the next video.
