Analyze data in Excel
Imagine you start work, open your email, and you see this from your boss asking, "See attachment and provide: Quick summary, How tobacco's doing, and something I should know." You open the attachment and you think, "You know what? Let me quickly get this out of the way before I grab a coffee because it is that quick." You know what makes it quick and painless? It's this feature, Analyze Data in Excel. It's sitting right here on the Home tab. So this is my data set. Let's just check how many rows of data we have. Just a little over 200 rows, and each column has a header. So, we have Category, Render, Date, and Cost. So, for Date, we're collecting data on a monthly basis, and we get to see the first of every month here. Now, if I was going to create a pivot table on this, I would have to think, "Where do I need to go to insert a pivot table? What do I need to drag to the rows? What should I drag to the values or columns?" It's a lot of thinking involved, especially if it's in the morning. Thankfully, we have Analyze Data, so all I have to do is come here and select Analyze Data. It's picked up the correct range, and I get to ask it a question about my data. If I'm not sure what to ask, I can try one of these options, or I already get some suggestions here. It tells me, "Cost by Date." I could insert this as a pivot table. I have the date here and the sum of cost. My numbers also look like they have perfect formatting. Down here, I get, "Cost by Category." I could just insert this pivot chart, and it's going to insert it on a separate sheet. Here I have some additional insights. So, "For Vendor: Treasury Wine Estate, cost decreases over time." Well, this could be interesting for my boss. I wasn't specifically looking for this. Also for this vendor, cost decreases over time. I can go to show more and get more insights from my data. Maybe find out something that is going to surprise me and something that I might need to follow up on.
But right now, I don't need to worry about this because my boss asked for a summary. This is a good summary to have, so let's insert a pivot table and see what we get. We got a new tab, Date and Sum of Cost. So, I'm going to change that to be Total Cost. Now I also know that my boss is interested in finding out what the average cost 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 Cost and drag and drop it in the Values. So, I have it two times here, and for the second time, I want it to show the average. Average value. So, I'm just going to double click, go to Average, change this to Average Cost, and click OK. All I have to do is update the formatting. Right-click, Number Format, Number, use a thousand separator, and that's it. We're done. Now, what about that chart that was interesting as well? Because we want to take a look at the cost by category, so that's the one I want. But I'd like it to be the average cost per category. So, let's see if we can ask it to specifically create that chart for us. Average Cost by Category. I think that should do. 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'm just going to keep it somewhere. I'll probably hide this in the end. Let's quickly just update this so it's clear. This is the average cost by category. Now, what if you also want a line chart here? Let's go back. Let's just type in "Cost Trend". Let's see if it understands. Well, it's giving me some specific things, "Cost over Months". Yeah, that's 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'll have to format these accordingly if you need to. Let me also quickly clean things up. I'm going to Ctrl + X this and bring it here. So, all the data is in one place and I'm just going to remove this. Now, what about specific insights? So, if we go back here, let's take a look at tobacco. So, I'm just going to type in "Tobacco". It's actually easier to pick it from here than to type it in. And let's go with "Insights". Okay, so for category tobacco, February had the maximum cost. Yeah, this is definitely something my boss is going to be interested in. The cool thing is, it's also conditionally formatted it by highlighting the maximum point here. All I have to do is insert the pivot chart and I have it right here. What about if we wanted the vendors as well? So, let's go and see "Tobacco Vendor Cost Breakdown". Okay, I spelled that wrong, but it still got it and it looks sorted as well. What if I wanted to sort it the other way around? Let's do "Sorted Ascending". That works as well. Okay, let's insert a pivot table and here's our report. The category tobacco was already added as a filter to our pivot table. How great is that! Now, what about some specific insights? So, let's go back and ask for "Cost Insights". Actually, we see it right here. So, let's just select that "Insights for Cost". That's what we saw before as well. So, let's just add some of these. I even get "Frequency of Cost". Okay, this one is interesting. This is interesting as well. What if I wanted to see the vendors that have a cost above a certain threshold? Let's see if we can get that done, "Vendors with Costs Higher than 7 million". It picked up the 7 million 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 and my task is done.
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 "Food" here. Let's change one of these numbers. 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. So, where was that for food? Right, if I just update it here, just so that we can visually see it change, I'm going to refresh, and this changes, but it's not only this table, it's all the other tables that were 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 Tables will automatically update the moment you press refresh and you don't have to update your range. Isn't that amazing that with just a few clicks we get to create a functioning report? Alright, so that's one place AI can help us analyze data in Excel. Use it and let me know what you think. Thank you for watching, thank you for being here. Subscribe if you aren't subscribed yet, and I'm going to catch you in the next video.
But right now, I don't need to worry about this because my boss asked for a summary. This is a good summary to have, so let's insert a pivot table and see what we get. We got a new tab, Date and Sum of Cost. So, I'm going to change that to be Total Cost. Now I also know that my boss is interested in finding out what the average cost 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 Cost and drag and drop it in the Values. So, I have it two times here, and for the second time, I want it to show the average. Average value. So, I'm just going to double click, go to Average, change this to Average Cost, and click OK. All I have to do is update the formatting. Right-click, Number Format, Number, use a thousand separator, and that's it. We're done. Now, what about that chart that was interesting as well? Because we want to take a look at the cost by category, so that's the one I want. But I'd like it to be the average cost per category. So, let's see if we can ask it to specifically create that chart for us. Average Cost by Category. I think that should do. 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'm just going to keep it somewhere. I'll probably hide this in the end. Let's quickly just update this so it's clear. This is the average cost by category. Now, what if you also want a line chart here? Let's go back. Let's just type in "Cost Trend". Let's see if it understands. Well, it's giving me some specific things, "Cost over Months". Yeah, that's 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'll have to format these accordingly if you need to. Let me also quickly clean things up. I'm going to Ctrl + X this and bring it here. So, all the data is in one place and I'm just going to remove this. Now, what about specific insights? So, if we go back here, let's take a look at tobacco. So, I'm just going to type in "Tobacco". It's actually easier to pick it from here than to type it in. And let's go with "Insights". Okay, so for category tobacco, February had the maximum cost. Yeah, this is definitely something my boss is going to be interested in. The cool thing is, it's also conditionally formatted it by highlighting the maximum point here. All I have to do is insert the pivot chart and I have it right here. What about if we wanted the vendors as well? So, let's go and see "Tobacco Vendor Cost Breakdown". Okay, I spelled that wrong, but it still got it and it looks sorted as well. What if I wanted to sort it the other way around? Let's do "Sorted Ascending". That works as well. Okay, let's insert a pivot table and here's our report. The category tobacco was already added as a filter to our pivot table. How great is that! Now, what about some specific insights? So, let's go back and ask for "Cost Insights". Actually, we see it right here. So, let's just select that "Insights for Cost". That's what we saw before as well. So, let's just add some of these. I even get "Frequency of Cost". Okay, this one is interesting. This is interesting as well. What if I wanted to see the vendors that have a cost above a certain threshold? Let's see if we can get that done, "Vendors with Costs Higher than 7 million". It picked up the 7 million 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 and my task is done.
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 "Food" here. Let's change one of these numbers. 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. So, where was that for food? Right, if I just update it here, just so that we can visually see it change, I'm going to refresh, and this changes, but it's not only this table, it's all the other tables that were 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 Tables will automatically update the moment you press refresh and you don't have to update your range. Isn't that amazing that with just a few clicks we get to create a functioning report? Alright, so that's one place AI can help us analyze data in Excel. Use it and let me know what you think. Thank you for watching, thank you for being here. Subscribe if you aren't subscribed yet, and I'm going to catch you in the next video.