Microsoft Excel is one of the most popular and powerful spreadsheet applications in existence.
That's why over the past few decades, millions of businesses,
organizations, and individuals have relied on Excel to get insights about their data.
Of course, to be able to get insights from your data, the barrier to entry is fairly high.
To use Excel effectively, you need to know about formulas and functions, pivot tables,
conditional formatting, and the list goes on and on. Of course, if you want to learn about those,
you can check out my course right up above. Microsoft Excel is pushing the boundaries to
make it even easier and quicker to use by introducing artificial intelligence or AI.
In this video, we're going to look at how you can use AI in Excel to get some incredible insights
about your data. And best of all, you only need to know how to click on one button or
how to ask the right question. Let's check this out.
Here I am in Excel and I have confidential sales data for the Kevin Cookie Company.
If you'd like to follow along, I've included the data down below in the description.
To analyze the data here, I could use formulas and functions. I could insert a pivot table.
I could also apply conditional formatting, but all of that requires some knowledge of how to use
Excel. Instead, let's go to the top tabs and make sure the one titled home is selected,
and all the way over on the right-hand side, it's somewhat tucked away. Let's click on the button
that says analyze data, and this opens up the analyze data pane. To expand it, you could click
over here and you could drag it a little bigger. This way you could see it better.
And this pane has so much power as long as you know how to ask the right question.
Let's actually test this out. I'm interested in knowing how many units we sold per day
across all of these different products. Using formulas and functions would take a little bit
of time, but here I simply need to phrase my question. Here I'll say how many units sold
per day, and I want to see it as a line chart. Here I'll press enter and look at that. Here
Excel automatically generates a chart for me that shows me the sales across all these different
products, and all I had to do was type in this one question. If I want to keep this view, here
I'll simply double click on this chart and that inserts a new pivot table and a chart showing me
the answer to my question. As a pivot table and with a chart here, I can now modify these pivot
table fields to get the view exactly how I want it. And if you want to learn more about how to
use pivot tables, be sure to check out the video right up above. Instead of modifying the pivot
table, I can also just go back to the original analyze data view and I can modify my question.
So let's say that maybe instead of seeing it per day, maybe I want to see it aggregated by month.
Here I'll simply change the word to month and then I'll hit enter. And here I get a chart
automatically generated that now aggregates it down to the month. Now, so far we just see a line
chart, but what if I want to see it as a, let's say a column chart. Here, I'll remove line and
type in column, hit enter, and it automatically generates a column chart showing me the answer
to my question. I could even get it back as a table. So instead of a column chart, here
I'll change this to say table and hit enter, and here I see it by month in a table format.
So this makes it so easy to analyze data as long as I have the right question in mind.
Up to now, we've had to ask all of the questions, but I want to see if maybe Excel could come up
with some insights for me, and specifically, I want to know if during all these months of the
year, are there any outliers or maybe trends in the months? Right here, I'll click on the X to
remove my previous question, and here I'll type in, are there any date trends? And I'll press
enter. It's now analyzed all of my data. And look at this, it looks like unit sold has outliers
on December 3rd and also December 12th. So here I can see that December tends to be a higher month
than all of the other months of the year, and also there are these two dates that really stand out.
This really helped me analyze all the data very quickly so I know that December is an extremely
important month for the cookie business. It's good to know that December is an important month,
but let's see if there are any other insights. Here, I'll go back up to the top. Let's remove
this question and let's see if maybe there are any product insights. Here, I'll type in the
question, are there any product insights? And then click on the go icon. And right here,
I see an answer. It says that the product chocolate chip has noticeably higher units
sold compared to all of the other cookie types that we sell here at the Kevin Cookie Company.
It looks like chocolate chip is definitely the big seller here, so we need to make sure that
we maintain the quality of that recipe. Another thing that I want to know is which customers
should we wine and dine the most? So which ones bring us the most revenue? I'll go back to the
top and here I'll remove that question. Instead of typing in a full question, I could also just type
in revenue by customer and hit enter, and here I see the revenue by all of our different customers.
So it looks like Stop and Snack brings us the most revenue. Now, I actually meant to ask for
the average revenue by customer ID, and here if I look down below, Excel also helps me refine my
question. Here I see average revenue by customer ID. I'll select this, and here I see that view.
So when we look at the average revenue, it actually turns out that Wholesome Foods is
our best customer. So, maybe we should wine and dine with them and see if we can get them to order
a little bit more since the average revenue is so good. Now, before you think you're out of a job
and Excel could analyze just about everything, there are some shortcomings. Here I'll zoom out
just a little bit and let's say that I want to know, well, what was the total profit?
The total profit is all of the revenue minus all of the cost. Right up here, I'll ask another
question and I'll say, what is the total profit, and then I'll hit enter. And here it simply
returns the sum of the revenue. That's the sum of this column. So it doesn't sum up the cost
and then subtract it from the revenue. Let's try one more thing. Over here, I'll type in total
revenue minus total cost, and then I'll press enter. And here it returns me the sum of revenue
and the sum of cost, but now I need to subtract them. So it gets me close, but it doesn't quite
get me all the way there. Maybe in the next iteration, you'll be able to ask questions like
that. A lot of what we've looked at so far requires that you have a good question in mind, but
sometimes you might not know what to ask. Here I'll click on the back button and here we see some
sample questions that you could click on. So maybe one of these questions gives you what you need.
And here, if we scroll down a little bit here, you can see some example answers or insights about
your data. So here I see the revenue by customer ID. Here I see that insight, that unit sold in
December is higher than all the other months. And here I see that insight about chocolate chip is
higher than all the other cookie types. Down at the very bottom, I could also click on show more,
and here I see many other insights about my data. So, even if I don't have inspiration for a question,
I could just look through these and see maybe if there's an interesting view of my data
that I hadn't thought of. In general, to get the best possible results, it helps to have
a single header up on top. Also, when you phrase your question, right up at the top,
you want to make sure that it's as specific as possible. That way you'll get the results
that match exactly what you're looking for. All right, well, that's how you can start leveraging
AI in Excel, and I think you'll agree with me that AI makes Excel so much easier to use.
Instead of worrying about how to use the tool, you can instead focus on asking the right questions.
To watch more videos like this one, please consider subscribing, and I'll see you in the next video.