Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)

Welcome to Excel Campus! My name is John, and this is the first in a series of videos about pivot tables. So I want to introduce you to my friend Andy. Andy is new on the job, and he has been asked by his boss to create some reports and ultimately a dashboard for an upcoming presentation. All Andy was given is this giant sheet of data and a list of reports that his boss needs. Although he's excited about the challenge, Andy is wondering how he's going to turn this data into a nice looking dashboard. Of course, he wants to save the day and become the superhero of the office. So what Andy really needs is to learn how to use pivot tables. In this video series, I'm going to show you how we're going to solve Andy's problem. We're going to take this sheet of data and turn it into a nice looking interactive dashboard using pivot tables and pivot charts. We are going to build each of these reports using pivot tables and then bring it all together to create the dashboard. Now don't worry if you think this looks complicated. Pivot tables are very easy to use and don't require any complex formulas or VBA macros. Just simple drag and drop. Throughout this video series, I'm also going to be showing you an add-in I developed called PivotPal. PivotPal makes it easier to work with some aspects of pivot tables and will save you a lot of time. But you don't need to have PivotPal to create these pivot tables and dashboards. Everything you need is already built into Excel. Alright, so in this first video, we're going to cover the basics of building a pivot table. I'm going to show you how to create this pivot table here that's a summary of revenue by sales rep and also this pivot chart over here which just displays that information visually. So we know that Andy has this set of data to work with. This is really all he's been given. And this is what we're going to use to create our pivot tables. This will be considered our source data. And before you start building a pivot table, it's a good idea to familiarize yourself with source data, especially if you haven't seen it before. So this data was exported from our general ledger software. And basically, it contains order details for December. So each row in the data here contains information for a specific order. So you can see we have the order date, some customer information here, the salesperson, region. We've got some shipping information as I scroll over. And we also have some product information and then some information about the price of the product, the revenue, quantity, all that stuff. So it's good information. The other thing you want to do is make sure that your data is in a tabular format. And that just means that you have a row of headers here at the top of the data set. And each of these headers describes the column of data below. And I provided a checklist so you can go over this in more detail. The other things you want to look out for is that you want to make sure there's no blank columns within your header row here and no blank rows within your data set, especially if you have a column of dates. You want to make sure there's no blank rows or cells in your date column. You also want to look out for any merged cells. A lot of times when you're exporting data, the software will automatically merge some cells together. And you want to make sure you unmerge all the cells. So once your data is cleaned up, and this data looks pretty good, go to the Insert tab on the ribbon and then click the Pivot Table button. And that will bring up this Create Pivot Table window. And basically it's asking us to select the table or range. Now you can see that Excel has automatically detected the range for me. And that's because I had a cell selected inside the data set before I clicked the Create Pivot Table button. So as long as you do that, select a cell inside the range, click this button, and Excel will automatically detect the range for you. If it doesn't, you can just click this blue button over here and go and select your range. The next step is that we want to choose where we're going to put the pivot table. In this case, we'll put it on a new worksheet. So I'll go ahead and click OK. And now you'll see that's automatically added a new sheet to our workbook here and created this pivot table area. You'll also notice over on the right side that this pivot table fields list has appeared. And this is basically the window we're going to use to create the pivot table. So here's a list of all the fields in our data set here. And then down below is the areas that we can drag those fields into to create a pivot table report. So the first thing I'm going to do is find that salesperson field. And here it is here. And I'm going to drag that into the rows area. So I'm going to left-click and hold, drag it into the rows area of the pivot table, and drop it there. And you'll notice now that in the pivot table, it's listed our sales reps in the rows area of the pivot table. So each row contains a sales rep name. Now I want to add the revenue to this report as well so I can get a summary of revenue. So I'm going to find this revenue field here in the field list and then drag it into the values area of the pivot table. When I drag it into the values area and drop it there, you'll notice that Excel has automatically calculated the sum of revenue for each rep that's listed in the rows area of the pivot table. So this is really the power and the magic of the pivot table. It has automatically calculated the sum of revenue for each rep just almost instantly as we drag the field into the values area. And it's a good idea to get an understanding of what actually happens there. So let me try and explain this a little better. So here we can see Andrew has sales of 12,368 here in the pivot table. And basically what the pivot table has done, if I go back to the data tab, I'm going to filter the salesperson field here for Andrew. So we're just seeing the sales that Andrew's done. And you can see here that he's done eight sales in the month of December. Now if I go over to the rows column, and I'm just going to select these revenue numbers here, you can see here's our sum of revenue, 12,368 for Andrew. So basically the pivot table has done this filter and calculation for us. It's basically filtered the data set for each rep, each unique item in that field, and then calculated the sum of revenue for each rep. So it's a very powerful tool because with just a few clicks, you can see that we've automatically created this nice summary report from our data set. And I also have a pivot table diagram here that just kind of shows you where each of these areas are within a standard pivot table. So you can see this is kind of a standard looking report. We have our sales rep here on the left side. We have quarters across the top. And then the sum of revenue here in the middle section. And up here we have the year, a filter for the year. So these are basically the different areas that you can drag fields into in the pivot table. And each of them displays information a little differently. So throughout this video series, I'll explain how each of these areas within the pivot table works. Okay, so let's continue building out our pivot table. So probably the first thing you notice is that these numbers are not formatted. When we drug the revenue field into the values area, it did not format those numbers. So we want to format those. And the easiest way to do that is if you just right click on any cell in the values area there and then go to value field settings. That will bring up this value field settings for the revenue field, sum of revenue. And we're going to click on the number format button. That will bring up the format cells dialog. This one you're probably familiar with. I'm just going to choose currency. No decimals. Click OK. And then click OK here. And you can see now that our numbers are formatted and they look a little nicer. The next thing I'm going to do is sort this information. So I want to put the top performing rep at the top of the list here. So basically I'm just going to select any cell in the values area and right click and then go to sort and sort largest to smallest. And now that's sorted our list here from largest to smallest. So we can see our top performing rep, which is Nancy, at the top of the list. And then our bottom performing rep, which is Jan, down at the bottom. So this now gives us some really good information. We can kind of see who's doing well and maybe who struggled this month or for some reason didn't do as well. And I'm more of a visual person, so I want to see this information in a chart as well. So as long as I have any cell selected in the pivot table, I can go up to the analyze tab or the options tab and click pivot chart. And that will bring up this chart window. And I'm going to insert a bar chart and click OK. So now we've automatically created a bar chart here that just allows us to visualize this information. And I'm going to quickly show you how we can clean this up because the default chart formatting out of the box is kind of ugly, honestly. So we're going to clean this up a bit. So the first thing I typically do is just hide all these field buttons. These field buttons allow you to apply filters, but we don't really need that for our dashboard. So I'm going to hide those. You'll also notice that these names here are in the opposite order of what they are here on the pivot table. So we need to reverse the order of this. So I'm going to right-click here, go to format access. That will bring up this format access window. It looks a little different in Excel 2010, but the options are the same. Go to access options, and then I'm going to check this box, categories in reverse order. And that's going to reverse the order of these categories here so they match what's on the page. I'm going to close that. The other thing I want to do, well, I'm going to get rid of these labels here. So I'm just going to left-click to select those and hit the delete key on the keyboard. I'm also going to get rid of the legend. So click that and delete on the keyboard. And I want to get rid of these vertical lines. So I'm going to left-click on those, delete. And then I also want to add some labels here. So in Excel 2013, you can just click on this plus button, go to data labels. That will add our data labels there. I'm going to make my plot area a little smaller so the data label doesn't overlap the bar. And now I also want to make these bars wider. So to do that, I'm going to again right-click, click on the bar, right-click, go to format data series. That will bring up the format data series window. And in the series options, we're looking for a gap width. And if I just make that smaller, that will make the gap between the bars smaller and make the bars bigger. So now we can see that we have some bigger-looking bars. And this is starting to look pretty nice. Of course, we want to add a title here. So we're going to go sales by rep for December 2014, something like that. So now with just a few clicks, we have this great-looking chart that allows us to visually see our report here, our sales by rep for the month of December. So hopefully you've kind of seen the power of the pivot table here. And it's not really that scary of an item. It's actually very easy to use and extremely powerful and allows you to quickly summarize and visualize your information. All right. So I want to quickly show you how you can use PivotPal to create this same pivot table report and how it makes it a little faster to do so. So the first thing I'm going to do is I'm going to clear out this report so we can start fresh. And to delete fields out of your pivot table, you can go over here to the pivot table fields list and just left-click and drag them out of the area until you see that X next to the mouse icon and then let go. And that will delete the field out of the area right there. So now we have this blank pivot table. And I'm just going to go up to the Excel Campus tab on the ribbon and click the PivotPal button. Once you install PivotPal, you'll see this Excel Campus tab up here. And basically, PivotPal is an add-in that I've developed that makes it faster and easier to work with some aspects of pivot tables. So you'll notice one thing when we were building the pivot table. We basically were scrolling through this list over here to try and find our field that we wanted to add to one of these areas. And PivotPal has a built-in search that makes this much faster. So if I want to find salesperson, I can just start typing salesperson. You'll see as I start typing, the results are narrowed down. I can choose salesperson here, hit Enter. That will select it down here in the field list for me. And then I can use these buttons or keyboard shortcuts to place the selected field in the area of the pivot table. So I want to put salesperson in rows. And now it's just added to rows. I also want to put revenue into my values area. This time I'm going to use keyboard shortcut Alt-V. And that will put the revenue in my values area very quickly. The other nice feature of PivotPal is that it automatically formats the values area for you. So you notice when we added this revenue field, I'll just go ahead and delete the values area out. When we add the revenue field into the values area using the field list, the numbers are not formatted. You can see that they're not formatted. We have to take a few extra steps to format them. However, when we do this with PivotPal, again, I'll add the revenue to the values area. This with PivotPal, the numbers automatically format it. And that's because PivotPal detects the source formatting. So there's a formatting dropdown here in PivotPal. And it detects the source formatting of the field. So if I go to the data set, and this button allows you to jump back to the data, you can see our revenue field has this currency format already applied to it. And PivotPal will automatically format that field for you like that. And if your source data is not formatted or you want to change the format, you can do that very quickly with PivotPal as well. You can just select from any of these predefined options here and format the values area very quickly. And of course, you can change this list here and add your custom formats to it as you like. So in the next video, we're going to learn a few more great features of PivotTables. We're going to take a look at some of the different calculation types and also how to add more data to your data set and apply filters and a whole lot more. So as you can see, Andy's pretty excited about PivotTables and I hope you are too. But I'd love to know what you think. So please leave a comment below with any questions or suggestions. Thanks again for watching and I'll see you soon.