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 it'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 pivottables and pivotcharts We are going to build each of these reports using pivot tables and then bring it all together to create the dashboard And 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 ad and I developed called pivot Pal Pivot Pal 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 pivot [pal] 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 specific Order so you can see we have the order date some customer information here the salesperson Region we 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 reading [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 unmiss Data looks pretty good We go to 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 automatically detected the range for me And that's because I had a cell selected inside the pivot table I'm sorry inside the data set Before I click to 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 all a list of all the fields in our data set here And then down below is the areas that we can dress 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 I'm going to drag that into the rows area [so] I'm 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 Our sale 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 in [to] 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 in the magic of the pivot table is 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 [andruw] has Sales of Twelve thousand three hundred [sixty-eight] here in the pivot table and basically what the pivot table is done So 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 Andrews 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 twelve 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 its Standard pivot 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 could 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'll] 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 we 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 Fields sum of revenue And we're going to click on the number format button that'll 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 rap 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 rap 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 and 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 Click ok So now we've automatically created a bar chart here. 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 isn't It's kind of ugly Honestly, so we're going to clean this up a bit so the first thing I do is just hide all these filled buttons these filled 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 or 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 [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 I in excel 2013 You can just click on this plus button go to data labels that'll add our data labels there. I want 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 [a] bar right click go to format data series That will bring up the format data series window, and we're looking a series options We're looking for a gap width and if I just make that smaller that'll 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 total or I'm sorry 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] are sales by rep for the month of December So hopefully you kind of seen the power of the pivottable 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 pivot Pal 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 field 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 aerial 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 pivot [Pal] button Once you install pivot Pal You'll see this excel campus tab up here and basically pivot Pal 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] pivot Pal has a built-in search that makes this much faster so if I want to find sales person 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 B 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 two rows. I also want to put revenue into My values area this [time] We'll use keyboard Shortcut alt V and that will put the revenue in my values area very quickly The other nice feature of pivot Pal is 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 there not formatted We have to take a few extra steps to format them however when we do this with pivot Pal the again. I'll add the revenue to the values area this with pivot [Pal] the numbers automatically [formatted] and that's because pivot Pal detects the source formatting so there's a formatting [Drop-down] here in Pivot Pal 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 Pivot Pal 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 pivot [pals] well you can just select from any of these predefined options here and Format, the values very 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 pivot tables 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 pivot tables [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
