Data Analysis: How to use Analyze Data in Excel (AI Creates Pivot Tables and Charts)

Invest Insight
9 followers
Imagine you start your workday, open your email and see a message from your boss asking,
see the attachment and provide a quick summary of the data, how the product's doing and other
important insight of the data that I should know. You open the attachment and think,
I can get this done quickly before grabbing a coffee because it's that simple. Do you know
what makes it quick and painless? It's this feature, Analyze Data in Excel. It's sitting
right here on the right side of the home tab. So this is the data set that I received from my boss.
Let's just check how many rows of data we have. Almost 200 rows and each column has a header.
So we have company name, country, sales, date, product, quantity and revenue. If I were to
manually create a pivot table, I would have to think, where do I need to go to insert a pivot
table? What should I drag to the rows? What should go in the values or columns? It's a lot to think
about, especially first thing in the morning, of course. Thankfully, we have the Analyze Data feature.
All I need to do is select Analyze Data and it automatically picks up the correct range.
I can ask it a question about my data or choose from the suggestions provided. For example, it
shows sales by company and product, which I can insert as a pivot table with perfectly formatted
numbers. It also offers a revenue chart, which I can insert as a pivot chart on a separate sheet.
Here I have some additional insights. So here we have the quantity and revenue by date. This could
be interesting for my boss. I wasn't specifically looking for this. I can click on show more to get
additional insights from my data, possibly discovering something surprising or something
that might require follow up. So let's see what else we have here. We have the venue increases
over time chart. And we also came to know that ShineBright company accounts for the majority of
the revenue. We can scroll down and down to see all of these charts. And if we need something from
here, we can simply click on insert pivot table. But right now, what I want is missing from here.
So for that, I can write here total revenue by product. Okay, so here we have got a chart. I can
click on insert a pivot table. And here it drops us in a new tab. A new tab appears with the product
and sum of revenue. Now I also know that my boss is interested in finding out what the average
revenue is. Since I already have my pivot table and I have my layout right here. It's quite easy
to do that. All I have to do is grab the revenue and drag and drop it in the values. So I have it
two times here. And for the second time, I wanted to show the average. Average value. So I'm just
going to double click. Go to average. Change this to average revenue. And click okay. All I have to
do is update the formatting. Right click. Number format. Number. Use a thousand separator. And that's
it. We are done. We have got the chart as well. As well as the data here to show to my boss.
I will rename this sheet as data summary. Now what about that chart that was interesting as
well. The chart right here that says sales by company and product. We can also insert this.
It's a good summary of the data. So I'll click here and we have got pivot table for this chart
too. Okay, now let's see what it provided us with. Here we can see our company names and the product
and the sum of their sales. We also get the grand total. Okay, one thing that would be important
here is to add revenue by company. So for that I will go back to the data and write here revenue
by the company. And here we get this chart. Yeah, we get our pivot chart. Now what if I wanted it
as another chart type. So what if I said as a column chart. Does that work? Yeah, that works
as well. So this is better. Let me just insert it. I get a new tab with a pivot table. That's
the underlying data for my chart. I'm just going to cut this and paste it here. So this ultimately
is going to be my summary sheet. This is going to be my underlying data. So I am just going to
keep it somewhere. I will probably hide this in the end. Let's quickly just update this so it's
clear. This is the revenue by company. We have revenue by the product and company along with
each other. Perfect. Now what if you also want a line chart here. Let's go back. Let's just type
in the revenue trend. Let's see if it understands. Well, it's giving me two charts. Revenue increases
over time and for the achieve more company revenue changed significantly. This one is better.
That's what I want. So let's go and insert the pivot chart as well. And we could grab that
and bring it over to our summary sheet. Right. So you will have to format this accordingly
if you need to. Let me also quickly clean things up. I am going to control x this and bring it here.
So all the data is in one place and I am just going to remove this. Now what about specific
insights. So if we go back here let's take a look at Apple. So I am just going to type in
tell me the trend of Apple. I want to see the trend of Apple product and let's go with insights.
Okay. So for the product Apple sales don't change over time. Yeah. This is definitely something my
boss is going to be interested in. Now what about some specific insights. Let's go back and ask for
sales insights and we got multiple charts for sales insight here. The first one is saying
that the product banana has an outlier sales show a repeating pattern. So let's just add some of
these. Okay. This one is interesting. This is interesting as well. What if I wanted to see the
product that has a revenue above a certain amount. Let's see if we can get that done. Product with
revenue higher than 10,000. I can write here product with revenue higher than 10,000. It's
it has picked up the number correctly. So all I have to do is insert this pivot table and then
insert some of the other ones that I got from the inside of my data and my task is done. I will
place this chart right here. Now you might be wondering are these dynamics. So if my data changes
here is everything going to change. Well let's try it out. I'm going to go to the venue here.
Let's change one of these numbers. I will just add an extra zero. I'll just add an extra zero.
Okay. So here it tells me it looks like your data is changing and it's re-analyzing my data.
So that's great. I don't have to do any specific type of refresh. But if I want my pivot tables
to update as well I have to do a refresh. Right. If I just update it here just so that we can
visually see it change. I am going to refresh and this changes. But it's not only this table. It's
all the other tables that we have created as well. So this one changed as well. So did all
my other pivot charts. Now if you want to add data to the bottom of this. It's best if you format
your data set here as an official Excel table. This way your pivot table will automatically
update the moment you press refresh and you don't have to update your range. Isn't it amazing that
with just a few clicks we can create a functioning report. That's one way AI can help us analyze
data in Excel. Give it a try and let me know what you think. And if you have watched this video till
the end I recommend you to check the video description for links to our other data analysis
tutorials. They will further enhance your skills in this field. Thank you for watching this video
and thank you for being here. If you haven't subscribed yet please do and I'll see you in my
next video.
