Data analysis in Microsoft Excel has just gotten a LOT easier. A feature called “Analyze Data” uses AI (artificial intelligence) makes this process simpler, faster and more intuitive. This feature used to be called IIDEAS in Excel but it was renamed to “Analyze Data”. It automatically creates pivot tables for you so you don’t even need to think about what to put in the rows and what to put in the columns of the pivot tables. You’ll get pivot table and chart suggestions which you can immediately use. You’ll also get data insights you might not be actively looking for which could be important for your analysis. This tool simply makes analyzing data in Excel really easy.
Requires Excel for Microsoft 365.
00:00 Data Analysis Made Easy with Excel AI 00:23 Analyze Data with AI in Excel 03:02 Automatically Create Charts by Asking Questions 04:36 Use Natural Language to Get Insights Into Data 06:42 Update Insights When Data Changes 07:46 Wrap Up
🎬 LINKS to related videos: Excel What-If Analysis Data Table: • Excel What-If Analysis Data Table | E… Power BI Slicers - A Step-by-Step Tutorial for All Types to Improve Data Analysis: • Power BI Slicers - A Step-by-Step Tut…
👉 This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel #ai
Content
0 -> Imagine you start work, open your email,
and you see this from your boss asking,
4.56 -> "See attachment and provide: Quick summary,
How tobacco's doing, and something I should
9.54 -> know." You open the attachment and you think,
"You know what? Let me quickly get this out of
14.22 -> the way before I grab a coffee because it is that
quick." You know what makes it quick and painless?
19.68 -> It's this feature, Analyze Data in Excel. It's
sitting right here on the Home tab. So this
27.48 -> is my data set. Let's just check how many rows
of data we have. Just a little over 200 rows,
34.32 -> and each column has a header. So, we have
Category, Render, Date, and Cost. So, for Date,
40.56 -> we're collecting data on a monthly basis, and
we get to see the first of every month here.
45.72 -> Now, if I was going to create a pivot
table on this, I would have to think,
49.74 -> "Where do I need to go to insert a pivot table?
What do I need to drag to the rows? What should
54.78 -> I drag to the values or columns?" It's a lot
of thinking involved, especially if it's in the morning.
63.265 -> Thankfully, we have Analyze Data, so all
I have to do is come here and select
68.34 -> Analyze Data. It's picked up the correct range,
and I get to ask it a question about my data. If
74.58 -> I'm not sure what to ask, I can try one of these
options, or I already get some suggestions here.
80.46 -> It tells me, "Cost by Date." I could insert this
as a pivot table. I have the date here and the
87.24 -> sum of cost. My numbers also look like they have
perfect formatting. Down here, I get, "Cost by
93.78 -> Category." I could just insert this pivot chart,
and it's going to insert it on a separate sheet.
99.72 -> Here I have some additional insights. So, "For
Vendor: Treasury Wine Estate, cost decreases
105.42 -> over time." Well, this could be interesting
for my boss. I wasn't specifically looking
109.56 -> for this. Also for this vendor, cost decreases
over time. I can go to show more and get more
115.98 -> insights from my data. Maybe find out something
that is going to surprise me and something that
121.98 -> I might need to follow up on. But right now, I
don't need to worry about this because my boss
126.72 -> asked for a summary. This is a good summary to
have, so let's insert a pivot table and see what
132.36 -> we get. We got a new tab, Date and Sum of Cost.
So, I'm going to change that to be Total Cost.
140.64 -> Now I also know that my boss is
interested in finding out what
143.76 -> the average cost is. Since I already have my
pivot table and I have my layout right here,
149.46 -> it's quite easy to do that. All I have to do is
grab Cost and drag and drop it in the Values. So,
155.58 -> I have it two times here, and for the second time,
I want it to show the average. Average value. So,
160.56 -> I'm just going to double click, go to
Average, change this to Average Cost,
165.84 -> and click OK. All I have to do is
update the formatting. Right-click,
170.16 -> Number Format, Number, use a thousand
separator, and that's it. We're done.
175.74 -> Now, what about that chart that was interesting as
well? Because we want to take a look at the cost
180.54 -> by category, so that's the one I want. But I'd
like it to be the average cost per category. So,
187.38 -> let's see if we can ask it to specifically create
that chart for us. Average Cost by Category. I
195.12 -> think that should do. Yeah, we get our pivot
chart. Now, what if I wanted it as another chart
201.42 -> type? So, what if I said "as a Column Chart"?
Does that work? Yeah, that works as well. So,
207.12 -> this is better. Let me just insert it. I get a
new tab with a pivot table. That's the underlying
212.94 -> data for my chart. I'm just going to cut this
and paste it here. So, this ultimately is going
220.62 -> to be my summary sheet. This is going to be my
underlying data. So, I'm just going to keep it
226.38 -> somewhere. I'll probably hide this in the end.
Let's quickly just update this so it's clear.
231.9 -> This is the average cost by category. Now, what
if you also want a line chart here? Let's go back.
240.66 -> Let's just type in "Cost Trend". Let's
see if it understands. Well, it's giving
246 -> me some specific things, "Cost over Months".
Yeah, that's better. That's what I want. So,
253.32 -> let's go and insert the pivot chart as well
and we could grab that and bring it over
258.54 -> to our summary sheet. Right, so you'll have
to format these accordingly if you need to.
265.02 -> Let me also quickly clean things up. I'm going
to Ctrl + X this and bring it here. So,
271.8 -> all the data is in one place and
I'm just going to remove this.
275.7 -> Now, what about specific insights? So, if we
go back here, let's take a look at tobacco. So,
282.78 -> I'm just going to type in "Tobacco". It's actually
easier to pick it from here than to type it in.
288.72 -> And let's go with "Insights". Okay, so for
category tobacco, February had the maximum
295.56 -> cost. Yeah, this is definitely something my boss
is going to be interested in. The cool thing is,
300.84 -> it's also conditionally formatted it by
highlighting the maximum point here. All
307.2 -> I have to do is insert the pivot
chart and I have it right here.
310.92 -> What about if we wanted the vendors as
well? So, let's go and see "Tobacco Vendor
319.26 -> Cost Breakdown". Okay, I spelled that
wrong, but it still got it and it looks
325.86 -> sorted as well. What if I wanted to sort
it the other way around? Let's do "Sorted
333.66 -> Ascending". That works as well. Okay, let's
insert a pivot table and here's our report.
342.48 -> The category tobacco was already added as a
filter to our pivot table. How great is that!
348.78 -> Now, what about some specific
insights? So, let's go back
355.5 -> and ask for "Cost Insights". Actually, we
see it right here. So, let's just select
360.78 -> that "Insights for Cost". That's what
we saw before as well. So, let's just
366.42 -> add some of these. I even get "Frequency
of Cost". Okay, this one is interesting.
374.16 -> This is interesting as well. What if I wanted to
see the vendors that have a cost above a certain
380.94 -> threshold? Let's see if we can get that done,
"Vendors with Costs Higher than 7 million".
392.52 -> It picked up the 7 million correctly. So,
all I have to do is insert this pivot table
397.02 -> and then insert some of the other ones that
I got from the inside and my task is done.
402 -> Now, you might be wondering, are these
dynamics? So, if my data changes here,
406.62 -> is everything going to change? Well, let's
try it out. I'm going to go to "Food" here.
411.78 -> Let's change one of these numbers. I'll
just add an extra zero. Okay, so here,
416.58 -> it tells me it looks like your data is changing
and it's re-analyzing my data. So that's great,
422.34 -> I don't have to do any specific type of refresh,
but if I want my Pivot Tables to update as well,
428.82 -> I have to do a refresh. So, where was that for
food? Right, if I just update it here, just so
435.48 -> that we can visually see it change, I'm going
to refresh, and this changes, but it's not only
440.52 -> this table, it's all the other tables that were
created as well. So, this one changed as well,
447.24 -> so did all my other Pivot Charts. Now, if
you want to add data to the bottom of this,
453.24 -> it's best if you format your data set here as
an official Excel Table. This way, your Pivot
460.5 -> Tables will automatically update the moment you
press refresh and you don't have to update your
466.14 -> range. Isn't that amazing that with just a few
clicks we get to create a functioning report?
472.5 -> Alright, so that's one place AI can help
us analyze data in Excel. Use it and let
477.48 -> me know what you think. Thank you for
watching, thank you for being here.
480.42 -> Subscribe if you aren't subscribed yet, and
I'm going to catch you in the next video.
