Advanced Excel - Using "Recommended Pivot Tables"

In this short tutorial, I'm going to show you the easiest, simplest way to add pivot tables to your Excel spreadsheets. And this feature is called Recommended Pivot Tables. And it's only available in the more recent versions of Excel, so you'll have to check to see if your version of Excel has it. And the place to go to check is here, in the upper left corner. Just click on Insert, and you should see, next to Pivot Table, you should see Recommended Pivot Tables. Now, if you haven't already done so, you really should watch my previous video that focused just on regular pivot tables, and how to set them up, how to arrange it to get the data that you want to show up using pivot tables. So please watch that video before you watch this one, or at least in addition to watching this one. And in that previous video, I used this same spreadsheet with a list of bands, and the spreadsheet is supposed to be an inventory for a hypothetical synth-pop music store. And so we've got a list here of some amazing synth-pop bands and their most recent CD releases. And it's arranged in a way that's pretty typical for an inventory. You've got an item number, you've got the price, you've got four quarters of the year, you've got total copies sold, and the total sales. In other words, the total amount of money brought in. But if I wanted to pull out specific data from this spreadsheet and look at it in a new way, I could use the Pivot Table tool to do that. But next to the Pivot Table tool, there's something called Recommended Pivot Tables, and this is the easiest way to do it. If you just click on Recommended Pivot Tables, you get a pop-up, and I'm going to see if I can stretch that out a little bit. And Recommended Pivot Tables is just what it sounds like. Excel here is recommending, based on the data that I have in the spreadsheet, it's recommending possible Pivot Tables to me. So I could have a Pivot Table report of the sum of price by genre. And so this would help me to know, for each genre that I sell in my store, what the price is. But is that really what I want to know? I mean, this is the sum of the price of the items that might be sold, but they haven't necessarily been sold yet. And so I don't know if that's really what I want. I don't think it is. So I'm going to click down here on Sum of Sales by Genre. Now this might be more useful to me. Now I can look and identify which genre of music that I sell in my hypothetical store performs best with my customers. And my hypothetical customers have amazing taste in music, and so you can see, Synthpop, that's the one that's generating the most sales for me. So that Recommended Pivot Table might actually be a useful Pivot Table for me. Next, there's Sum of Item Number, and Sum of Copies Sold, and Sum of Quarter by Genre. For me, this isn't that helpful. I don't really need to know the item number and, you know, maybe the sum of copies sold. That would be useful, but I don't like that one. So I'm going to go down and pick the next one. Okay, so the idea is that you can just go down and select one of these Recommended Pivot Tables. Excel has already done the work for you, basically. You're just looking at it to see, oh, is that useful to me or is it not useful to me? Here's Sum of Copies Sold by Band. That's pretty useful, I think. It also lists the genre that goes with that band, so that could be pretty useful. And then the Sum of Quarter by Band. So it's adding up the quarters. Notice that they all come to the same number, except for Brandon Flowers, because I only put his CD in for one quarter. So this is another example of a Pivot Table report. Even though it's recommended, it's pretty useless. It's not really telling me information that I want or need. So in this case, for this example, I'm just going to pick Sum of Copies Sold by Band. I click on it, click OK, and notice what it did. It added a sheet for me where I can see this Pivot Table report and I can glean the information from it that I want. If I want to tweak this report, if I want to maybe add to it or change it in some way, over here on the right, I have the Pivot Table Fields panel and I could adjust it. I could add quarter in as a column or do any number of changes. And if you're interested in learning more about how this works, please watch my previous video called Advanced Excel Introduction to Pivot Tables. So I really like Pivot Tables and most often I do just make them myself. I just use Insert, Pivot Table. That's what I do most often because I usually know exactly what I want in my Pivot Table report, but other times I'm not exactly sure. And so I do Insert, Recommended Pivot Tables, and then I just look and see, oh, I wonder if this would be helpful to me. I wonder if that piece of information would be helpful to me. And so it's a great way to get ideas about how a Pivot Table might benefit you and draw out information about your data. So thanks for watching. I hope you found this video to be helpful. If you did, please click the Like button below and please consider connecting with me on my social media services like Facebook, Pinterest, and Twitter. And definitely do subscribe to my YouTube channel for more videos about technology for teachers and students. And watch for another video from me at least every Monday. And if you're interested in learning more about any of these bands and their most recent CDs, look in the video description below and I have links to more information about each of them.