From the course: Excel Tips Weekly

Using the Analyze Data command (formerly IDEAS button) on the Home tab - Microsoft Excel Tutorial

From the course: Excel Tips Weekly

Start my 1-month free trial Buy for my team

Using the Analyze Data command (formerly IDEAS button) on the Home tab

“

- [Instructor] Excel's Analyze Data button is a valuable tool for analyzing information. This is a feature that used to be called Ideas, and you'll see in the panel here, a brief description of it from Microsoft. To better represent how Ideas makes Data Analysis simpler, faster and more intuitive, the feature's been renamed to Analyze Data. And on the worksheet we've got a list off to the left, it's about 900 rows or so, and I've activated the feature to show some of the things that I might be interested in. I might be very adept at pivot tables or maybe I've never used them at all, but I'm seeing something to the right that already has captured my attention, amount by customer and sales salesperson. We're seeing which salespersons are selling more to this customer or that customer. Already, that intrigues our interest and also, something else off here to the right, amount by product. So let me eliminate this and talk about how we start the process. And we don't need this panel here, I'll just delete it too. So I've got this list here in no particular order at the moment. It doesn't have to be. And you could imagine a scenario where maybe I've used a pivot table once or twice or maybe I've never used them, I've heard about them. I've got a lot of information here. I know how to sort, I know how to filter. I've gotten some valuable information already, but what can I get from this Analyze Data feature? On the home tab off to the right, you'll see the Analyze Data button. Click it and you see analyzing your data. There's usually a slight pause there. And this is what we're seeing here. Now again, it says insert pivot table. I'm interested in this idea, so I'm going to click this. Now, because it is a pivot table, default idea is it will be on the separate sheet. So watch the bottom of the screen or on the new sheet. We've got some data here, I'll click within it. And again, maybe I'm not too adept with pivot tables, but I'm seeing some valuable information already. Now, if I am familiar with this, I might say, "Gee, pivot table analyze? Isn't there a way here to twist this off to the right, there's a field list. Yeah, there it is, I think I've seen this." Salesperson, customer. There we are. Oh yeah, that's a better layout. Now, I'm playing as if I'm slightly naive perhaps about pivot tables, but just the idea, this was created quickly and easily by analysis from that tool, Analyze Data. What else do we see over here under Analyze Data off to the right? Amount by product. Gee, I guess I thought I knew that televisions sold more, but that's pretty clear, but we're selling more refrigerators than we are some of the other items. Maybe I thought dishwashers was a bigger item, it isn't. Next one, not too valuable. But what are we seeing here? Pivot charts, pivot tables, and you're likely to see those two entries most often. But here's a chart that isn't very valuable. Not all these are, of course. I'm scrolling up and down and below this, we see a choice, show all 35 results and it goes on and on and on. And some of these are worth looking at, some are not. By date there. More televisions again. See a pie chart, pivot chart there too. Tons of choices here. And again, not everyone's going to be just right, just the one we need, but lots of them out here. Even on the smaller list, I'm working off the appliance worksheet in this workbook. Off to the left, you'll see a profits sheet. That's not very much data. Let me click within there and come back to the Analyze Data button. The first choice here is pretty much the same list all over. It's just sort of descending order. So that's not too helpful. But there's a chart and once again, playing myself being a little naive, maybe I don't know much about charts or having to work with them. I'm going to insert a chart and move that around and again, I've got a chart immediately. Now, granted, most of us who've worked with Excel frequently know how to do that pretty quickly, but on the other hand, that's really helpful. And maybe I mostly use bar charts and hadn't thought of it and well, here's a column chart. That's good. And down below another chart maybe. That's a pivot chart, but nevertheless, that could be valuable too. I'm not saying that every feature, every suggestion here is going to be super valuable, but lots of choices, lots of things to look through here, lots of options to consider. You can also provide a question. Let's go back to another list here. All employees. And I'll click in here and also analyze data. If you're in the process of looking at this and you change some information, for example, in this list here, what if I were to say, you know, I don't need the hire date here, so I'm going to delete the hire date. Or maybe it's years of service, 'cause this has formulas in it. As I right click and delete column E, looks like you're editing the data. We see off to the right, there's a pause and then it reconsiders the new data here. So any other suggestions that might have been there about years of service is no longer in this list. Sales by department and status, that looks pretty good to me. How about a pivot table? Wow, just like that. I don't mean to oversell it by saying that, but that's pretty amazing when you think about it, and it gives you that kickstart that you need sometimes to begin analyzing your data. And if you are an analytical person, I think it just gives all kinds of insight that you never would've expected. Sometimes you're not that much of an analytical person and you get ideas from this. And I've got different lists here and one-by-one as we switch lists here and go back to analyze data, Excel reconsiders the current list and comes up with some other suggestions. One there, obviously I would don't want this insert chart here with employee name. Why would I ever want that one? But on the other hand, something else. You probably would know this if you knew your lists reasonably well. Full-time accounts for a majority of years. Again, notice pivot chart. There's a plane chart pivot table. So the emphasis is on pivot table and pivot charts mostly, but it shows many, many results here and we go on and on and on here. It even indicates that there are outliers here too. We can also try our own, we can ask questions too. In a list here, we might say average salary by department. That's pretty immediate too. We're not seeing the labels there, so we'll have to insert the chart to get the labels and have to know a little bit about charting here. Probably understand that making this taller, we could get all the labels in place there. There we go. Well, that's good to know too. So it's amazingly fast and easy. You can see how definitely it's a tool to experiment with as well, because you want to be trying some other questions here. So the Analyze Data button found on the home tab, a viable tool for analyzing information as you work with Excel.

Contents