Excel Pivot Tables for Beginners: Learn them in 20 Minutes

1. Intro: why Pivot Tables?: in this class, we're going to cover the topic of pivot tables and mark stop checks out. Pivot tables are featuring Excel that allow you to summarise the information from a data set. What that means in practice is that you can gain information and insights from a large amount data quickly and easily, in my opinion, pivot tables of one of the best features of Excel, and you might even want to how you lived without them. Once you've learned how to use them, the word ever is used in the name as you can rearrange the data to highlight or find out particular information. If all of this seems a little unclear, don't worry, as it will become a lot clearer when we get started creating a pivot table with some example data. We're going to be using this spreadsheet for this class on this is available view to download as the same. Crunchy is also used for the class project. Thanks for enrolling in this class and let's get started 2. Class Data and Getting Started: Okay, So in this video, we're just going to do an introduction into the data to see what's in there. Andi, show you how you scare about setting up a pivot table to get started with. So data that we're working with is some dummy sales data. So we've got the order date down here, which is when the sale was actually made the sales rep down here. So the person that made the sale, whether it was Liam Harper Avery Lucas van etcetera at the country of, say, over its France, Germany and Spain, the product that was sold, whether it was a keyboard and monitor or laptop or printer, the number of units that were sold, and then the cost of the unit, it was agreed by the sale draft. Um, you won't. You'll notice that the data is not an enormous amount. It's just going from sort of Ray one all the way down to Road 54. So it's not a huge amount data, but is big enough for us to sort of illustrate how pivot tables work. What I am going to do is I'm just gonna convert this data to a table. It just a bit easier to work without you, Adam. You'll notice that the moment that there's no filters at the top and there's no kind of formatting. So convert data like this to a table, you just press control a and then control T. And then my table has headers. Yes, We're gonna leave that selected press OK on yet you'll see it. We've got this formatting now with the filters at the top, which is just gonna be easier to work with when we refer back to it. So how do you create a pivot table? So to create appear table we have to do is make sure you're in a cell within your table or within your range of cells, and then you come up to insert on those two options recommended pivot tables where they will kind of pre recommend or just pivot table. We'll just go this one for now and then we're going do everything ourselves. So gonna select pivot table, then next up, you get this little box here on it says, choose the data that you want to analyze on. That's kind of pre selecting the data which we were clicked into. So it's it's selected table five and you'll see these little green dashes moving round our table of data, which is absolutely fine. So we believe that as is. And then you've got two options here new worksheets or existing watching. We're going to be working in a new worksheet, but I will just show you how you put it in the existing worksheet. So you select existing worksheet on, then in this location option here, you just want to select the cell that you'd like your period table to go. So let's just sell, act cell hate three, for example, you see populated there patiently select. Okay. And then we get our pivot table here on our pivot table fields over here. As I said, we're gonna work in a new worksheet, so I'll just delete that on creating. You want to insert a pivot table, and I leave this and then we're gonna leave that as default new worksheet select. Okay. And then what you'll notice is we get sheet to at the bottom. Here, appearing. So now we've got our pivot table ready to go here, the pivot table fields over here, which will come on to in the next video on day. One thing like Do you just want to show you before we sort of move on? Is what happens when you change your source day to say you want to add extra columns, for example, with more information, what happens to the pivot table view to set up? So if we go back to our data, let's say we want to add a revenue column, so that's just at that end. So I'm just gonna copy that. Just change this to Revenue Gavin. But on then, revenue, all that's gonna be is the units times that Cosby units I've selected. The unit cell climbs up by Cosby in center because we're in a table. It's filled those down automatically nice and easy. And then what you'll notice is is that this is not in our pivot table fields at the moment , which is all the columns. So to get that in or you have to do is right. Click on this on select refresh on. Then you'll notice that it popped in here with bar table fields. We've got everyone in there now as well. If you didn't have your data in a table format, so if it was just in regular cells and you hadn't done that control a control T that we did at the beginning. When you're changing the range of cells that your pivot table refers to you come up to pivot table, analyze change data source change data source on. Then you would select the new Reign yourselves, but because we've got our data in the table, it's it's kind of realized that it needs to expand across when we hit. Refresh. But that's also how you can change your pivot table date source If you expand your, um, range of data, just just so you're aware. Okay, so that's about all for now, I'm just gonna rename this tab to pivot. So then we've got one tab with pivot in. This is our pivot table tab. And then we've got our raw data tab over here on. Yeah, in the next class and next video, we're gonna go through the pivot table fields and what this is all about and start actually building our pivot table 3. Building a Pivot Table: Okay, So in this video, we're going to start diving in on actually creating a pivot table that shows the information in ways that we kind of interested in. Because at the moment we've obviously got this table of data here on. There's lots of insights which we could gain from here. For example, we could start to look at the number of keyboards which was sold in total. Or we could look at the number of sales which were made in different countries, which sales rep made the most sales along these types off sort of questions you could you can look out with a pivot table and finding out those answers by manually filtering or by writing kind of summits and countess formulas and all that kind of thing takes a lot of time, which is where, in my opinion, you know, pivot tables really come in on why they are kind of one of the best features in exile. I personally feel so what we have here in our pivot table tab is over here. We got the pivot table fields and all of these are the table headings. So order date sales were countries sell etcetera on, then this is where you build the pivot table. These four boxes down here filters, columns rose values on. You can even click on these and you'll see they start appearing in there or you can drag them in. So let's just say we want to look at the the volume of units that were sold by each sales rep so we can bring sales rep down to the rose, and that's gonna put them in rows like this. You could also move that across. The columns just kind of show you the two different options there. Let's keep it. And right now and then, if we look at units and we bring that into the values box in the bottom right there and then what you'll notice is, Is that our pivot table Over here? We've got the different sales reps were going down there to zoom in there on a lot of their units here, So if we just sort of look at an example of this Eva 1768 So where is that data actually coming? From what? Let's just go back to our table. Let's filter for Eva, and then if we high level the unit cells we see in the bottom there you might not see. But it says 1768 So all it's done that the pivot table it's summed up that total. And it's done that all the sales rep so instantly. We've got this information really quickly and easily to see without having to write out different formalism. In a nutshell. You know that is how you build a pivot table, and that is why they're really useful. Obviously, there's a lot more you can do a pivot table, so let's start to look at some of the other options. So we've got this filters box up here on what that does is it allows you to specify which units you want so considered in the pivot table. So if we were to bring products down to filters, you'll notice that the product filter appears up here on by default. All of our products are selected keyboard, laptop, monitor, printer. However, let's say we just wanted to look at keyboard so we can select that on select Okay, and then obviously the numbers reduced quite substantially. Savers. Now on 392 we go back to the data. We just felt for Eva on keyboard High like those 392 so you can see that what the pivot table is doing. It's looking just at this product because we've added the filter in looking at the different sales reps and then how many units off that product they've sold? Um, now something else we could do. We have the pivot table is we could add, in country of sale to make this information a bit mawr kind of granular and details of Let's Bring Country sailed down to Rose. Now what you'll see is that here we've got the sales where they were made, both the total number on the country number. Now, as it happens, thes sales reps only sold this product in one particular country for each of them. But let's say we selected multiple criteria on you can do that by taking this box. Select multiple items on Let's say we do keyboard, laptop and monitor, believe printer off Um, and then you'll notice under stream out. You'll notice here the, um, we've now got like, say, two rows for most of the cells were upset. Ben sold 656 items in total, just considering the keyboard, laptops and monitors on then in France, that was 286 of those sales in Spain, there was three engine 70 of those cells that you can start to get mawr granular and start to find out these answers to quite, you know, filtered specific questions very quickly. With a pivot table, you can also add, actually units into the pivot table by dragging MAWR into the value. So let's add the revenue into the values field, and then we can start to see you know how, how much total revenue, each of the sales rep selling in different places. And we can start to see that like Ben, for example, you know there was much more money made in France than that wasn't Spain. Where is for Harper? There was more made in Spain, Germany, etcetera, um, being cost the unit in there as well. Now, cost per unit doesn't really make sense to be represented as a some figure. So where's we've got some of units on some of revenue? Cosby unit would probably better is an average now to change that. What you can do is come down to the right here. And then you just click on the value that you want to change, select it and then go to value field settings. And then you've got a range of options here, such some count average, etcetera. So let's just select average flecked. Okay, And then now we get the average. So across these products. So keyboard, laptop monitor, Harper in Germany, the average cost The unit sales price that was agreed was 151 euros for Liam. In France, it was 160 euros. So on and so forth. So that's just another way in which you continues pivot tables and the way in which you can sort of change the data to give you the exact kind of information that you want something else to mention just appeared tables is with the information itself. You can sort of reorder things and change things around. So let's just go back to our original list. What, you know, serious that at the moment, these air in alphabetical order. But we could also have these changed by largest to smallest, for example. So we've got Harper 2275 of the top and then bend 656 at the bottom. So that's just another thing to be aware of. A pivot tables two on in the next class, we're gonna come on Thio more features, a pivot tables and some of the other things that you can do. 4. Grouping Dates, Timelines, and Slicers: in the last video we looked at how to create pivot tables and how you can start to rearrange the field so that you can get the exact insights and answers that you're interested in. In this year, we're gonna look a few extra features of pivot tables that are useful to be aware of as well. So we've are sort of simple pivot table here where we've just got the sales reps on the some of units that they've solved. Um, useful to do would be useful thing to do would be to bring in the order date on, pop it in there on. What you see now is that it's reverted to having ALOF. The sales wrapped information broken out by month. So, for example, in January we've got a ver made 165 units worth of sales where is in March Lee and May 386 etcetera. Now, something to be aware off with the date values and pivot tables is that you can actually group them up in ways and ungroomed them too. So let's say we wanted to have a look at this information in financial quarters rather than month, so to do that. You right click on the date value, go to group And then we changed this from months to quarters. And there's other options here as well. Select. Okay, and then you see it's gone. Teoh Quarter. So it's grouping up. Say, January February, March 2/4 1 etcetera On. As it happens, this is a bit of an easier way to see. It does appear a table is a bit smaller, and it's slightly less granular information. But you know, depending on what you're interested in, you can change this. Teoh, get the answers that you want. Another useful thing to be aware off with dates and pivot tables is the timeline feature. So let's just remove order date from the pivot table from now on. Now, if you come up to pivot table, analyze and then select insert timeline at the top and then you'll get the order date going into here because Excel is recognized that this is an eligible field. So you select that and then select OK, and then what you get is this really useful kind of interactive tool appears on. What you can do is you can drag these sort of this slider along to select the months that you're interested in on what you notice is the data is actually changing their. So we could just, for example, just highlight April when then it will obviously reduced and one of the sales reps actually disappeared there on you can highlight mawr or less, depending on what you want to see on. Then you can also change this from months to quarters. And then there's also options for days and years, so we could just select quarter one on. Yeah, you can. You can sort of without having to right click and do these other things you can really easily see the information that you want on this is useful. Live your sending on a spreadsheet to someone on. You know, they you might not be interested. You might not be aware off what they're interested in, but you know that they're interested in looking at the data in certain ways. And if you give them a spreadsheet that's got timeline in with the pivot table, it allows them to kind of view what they want to see without them having to filter or right formulas. So it's a really useful feature of pivot tables to be aware off another really useful feature, a pivot Tables to be aware off is the slicer feature. So again, if we go up Teoh Hibbert tape of Analyze and then next to the timing option, there's this one here insert slicer. So let's select that now. We've got more options here. Andi, Let's, for example, do country of Sale and then select OK, now, in this case, what the slicer is. It's kind of like it turns your field into a series of buttons on. By clicking on those buttons, you get to see the value that you're interested in. So if it was just Spain that we wanted to say, we just click on that, and then you can also do multi select so we could do Spain and Germany, for example, or you can un select them by just clicking on them against it, just France and Germany. And then you can clear all of the filters by selecting this one here and then we have all our information and again so again, slices are like a really nice, user friendly feature of pivot tables where you could give this spreadsheet someone and they can easily navigate themselves to see what they're interested in, so another really useful feature of pivot tables to be aware off. 5. Formatting Best Practices and Tips: in this video, we're gonna have a look at some of the formatting best practices and tips for pivot tables . So we've got a pivot table here using our source data. We've got the sales rep on then their revenue that they've generated broken out by country . Now, one thing will most here is that the revenue will notes in euros. We don't actually currently have any indication of that in these cells. There's no currency symbols, so you can just highlight those cells. And then with the hometown, you could go to come up here and go to currency or accounting. We're gonna go currency and then what your nose is because I've got my Excel set up in English. Former It's gone to pound symbols, but I can easily change that. You could just do control one the same as when you're formatting outside of pivot tables, um, currency. And we just can you select euros? So just scroll down a mortgage, pick a year olds option eso that we g o. Okay, and then we'll see that changes two euros. You can also increase the decrease decimals as you can outside of parent table, so we'll just decrease those to make it look a bit cleaner and then with the pivot table formatting, it's worth being aware of the design tab, which is where kind of all the options really are. So you've got different style options here so we could select like this one here, and we get orange of different bordering the yellow on. There's a whole range of different options, depending on you know, how you like your data to represented. Let's just stick with this one for now on. Then there's further options to the left of this pivot table style section. So we've got sub total. For example, eso. At the moment you'll notice we've got these subtitles and now there's a few different options there. We could, you know, not show them shovel sub totals at the bottom of the group or top of the group. Let's just remove them to see what happens when you notice they disappear, obviously kind of less information on the table now, but perhaps you prefer the design of that, Um, and then let's just bring those back at the top of the group grand totals. You'll notice we've got this grand total here on do you know there's different options, depending on the pivot table that you have? Let's just go with this one here. Off rows and columns on it will take off the grand total of the bottom there and then report layout. So there's a few different types, so we select tabular form, for example. What you'll notice is is that the sales reps name has moved to this column here, and we've actually got an additional column. So if we just go back Control Z, we'll have them all in the same column again. And then there's also options for blank rows. So insert black line after each item, for example, and we get this blank line s so there's a whole range of different options here. It's worth kind of having an explorer of them and seeing how you like your pivot tables to be presented. But, yeah, it's it's useful to be aware of all these formatting options, especially if you need to make a pivot table look kind of smart and tidy of your sending it someone else 6. How to Create Pivot Charts: in this video, we're going to look at the pivot chart feature, so pivot charts are a really useful feature that's useful to be aware off, as you can make your data look a lot more interesting and easy to extract meaning and information from. So we've got a simple pivot table here. It's just got the sales reps with their total summit revenue. And to create a chart from this data pivot chart. Just click into your pivot table and come up to the pivot table, analyzed tab and then go along to pivot chart of the right here. Onda. We get various different options here. Um, some of these aren't valid for the data that we've got in our pivot table. So let's just go of the basic Clustered column chart select. OK, on. There we go. There's a pivot chart now, something that's really useful about pivot charts is that you can actually add slices in on . They will change the way in which the chart represents the information. So if we come up to pivot, chart, analyze and then we dio insert slicer, we could select, for example product AnAnd select Okay on. Then we get all of our products at the side here on as before, we can select different products, and it changes the information in that chart. So this is really useful. If you want to create a kind of interactive spreadsheet that people can easily use Teoh see the information they want equally. We could click back into the chart, and we could also add a timeline so we could have ordered a timeline and then select that there. I just see amount of bits. Um, now it's going on two years, but we could change that Teoh mumps, for example. And then we can drag these around and again. It will change the information in our pivot chart so you can see how these tables the columns move around and the actual number of sales reps even changes as well. So these air really useful features to be aware off, and it's a great way to make your data more interactive. On more interesting 7. Next Steps: Thanks for watching this class, and I hope you found it useful. Let me know if you have any questions and you can find the class project instructions below this video to test what you've learned. If you want to learn more about excel, check out my other glasses and follow me. So you're the first to hear about my news classes. Thanks again and see you next time.