Now here's a couple examples that come up all the time in Excel and people struggle with these.
So I'm going to go to a sheet that's called customer list and we have a list of all the
customers. I have the customer ID, the company name, the contact name and so on. Now I'm going
to go to a sheet that's called order list. Here we have the order ID and the customer ID and the
employee ID and so on. I want to see if there's any orders that have a customer ID that's on this
sheet that's not on the other sheet. So that's a common task that we run into trying to compare
the two sheets. So let's do that one first. I'm going to say if there are any customer IDs
that are on this sheet but are not on the customer list sheet then highlight them in red.
So I'm being very specific here. I'm using the column headers and the sheet names. So I said if
there are any customer IDs that are on this sheet but are not on the customer list sheet, I want to
highlight them in red and we'll send it. We're trying to compare the two sheets. Now if none show up for
you, you might have an older copy of the workbook but I put in a couple that are on this
sheet that are not on the other sheet and it just made them in red. Did you see what just happened
there? So these two are on this sheet but they're not on the other sheet. So that's a pretty typical
request. But to do that manually it really takes a lot of time. Copilot was able to do that for us.
Now I want to get the company name from the other sheet. So this is a lookup. If you know the Excel
formulas, they're called Excel, I mean VLOOKUP and XLOOKUP. But many people struggle with those
formulas. Let's see if Copilot can help us. So I'm going to say get the company name from
the customer list sheet and put it next to the customer ID for each row. That's what we're
already trying to do. So that's exactly what you type in the Copilot. I should get the company
name from the customer list sheet and put it next to the customer ID for each row. Keep an eye on
column C. So it's trying to work that out. Now it might have put it way over here on the right. It
says it's done. So I'm going to scroll to the right and column P was not there a couple minutes ago.
So it got the company name and put it over in column P. Now I could just, you know, copy and
paste that, but it gave us what I was looking for. If I click on any of the items in column P,
then it has the real formula. See how it's using the VLOOKUP formula. That's a pretty common
situation. Either we have to match the two sheets and find the ones that are on one sheet that are
not on the other, or look up information from the other sheet. Copilot was able to help us.
I'll click on the word done there and we're looking good. Of course I can move that column,
but Copilot did the lookup for us. This time the lookup table is on the same sheet.
Sometimes it's on a different sheet. Sometimes it's on a different workbook. If that were the
case, then you would make sure that both workbooks are open. And then in your prompt, you would tell
it to, you know, look in the other workbook for the table. But this time we're just going to go
Copilot and we're going to say, get the grade for each score. Now, if that table was on a different
sheet at this time, I would say the lookup table is on such and such a sheet. If it was on a
different workbook, I would say the lookup table is on a different workbook. Now both of those
workbooks should be open at that time. So that's important, but it'll do it for you. I say get the
grade for each score. Watch column C. It just filled them in. If I click on one of the letters in column
C, it has the real formula in there as well. Now the computer I'm working with, it does have
Windows 11 and it has 32, 32 gigabytes of RAM. So I would, I mean, you can't use 8 gigabytes anymore.
So get at least 16. If you can get 32, that'll be better. If you can get 64, that's even going to be
better. So, you know, it's time to get rid of those old computers. Okay. I'm not trying to sell you
computers. I'm trying to tell you that you really have to upgrade to make the AI tools work better.
So I'll click on the word done. Now the next couple of sheets, we have some more lookup examples.
There's another lookup that's called an H lookup, which is similar to the V lookup. So let's go to
the sheet that's called H lookup. And H means horizontal because the table is going across
this time. So I'm just going to say, get the tax rate for the salary in cell B2. In essence, that's
what we're trying to do. I said, get the tax rate for the salary in cell B2. Now let's see if it's
going to include the formula this time. It may just give us the number or it may give us the
formula. Let's see. It says 28% there in cell B3. That was not there a minute ago. Now notice how
it has the H lookup formula. So that really means if the salary in cell B2 changes, then it'll look
it up in the table and that is the correct formula. So get really good at describing what you want
and Copilot in many cases will do it for you. I'm not going to say in all cases,
but in many cases it will.
