Join Mark Swift for an in-depth discussion in this video Pivot tables, part of Excel 2003 Essential Training.
- View Offline
Possibly the most advanced and complicated, if not the most misunderstood part of Microsoft Excel has long been the Pivot Table. My poor friend, the Pivot Table is grossly underused because most people are afraid, myself included, to try and tackle a Pivot Table, and then understand the results. Essentially a Pivot Table is a very sophisticated way of looking at your data. You can build a Pivot Table with just a few clicks and without entering any formulas at all.
You can achieve what would take you maybe hours if you were working with strictly formulas. Let's go our Student Files, and open up 17 Pivot Tables. Now this is a sample file that I downloaded from Microsoft.com in their free templates section. This template is half useful and half tutorial and when you open it, here we go, you'll see I have one tab marked SourceData, originally there were three tabs and they completed a lot of the work for us. So I got rid of those tabs, so we can go right from scratch to make this the most useful example that I possibly can. I know you yourself, when you start getting into building your own data tables and wanting to try to create a Pivot Table, it's going to be important to start from scratch. Again I've been very impressed with the amount of material that's available on Microsoft.com. It's a great place to go to continue your learning and to find great examples of finished projects that you can utilize. So here we are. We're looking at a data table that describes sales in the UK and the US, various sales people over dates, the order numbers associated with each sale and the order amounts. There are 800 rows of sales recorded here in this file. That was another thing that motivated me to download this template; 800 items are a lot to enter. Let's go back up to the top, and we're going to indicate to Excel that we want to create a Pivot Table. Let's go up to the Data menu, choose Pivot Table and Pivot Chart Report.
We have options to pull from an Excel database, in other words, the information we're using is on another sheet, or we can pull information from an external source like an Access database. We also need to choose at this time whether we're going to create a Pivot Table or a Pivot Chart Report. A Pivot Chart Report is going to combine a Pivot Table with a graph so you can visualize your data all in one move. Let's use the Pivot Table for now and keep it as simple as possible. We'll click Next. Now it's asking us to specify the range that we want to look at. Well I'm going to click here and simply click and drag 800 rows of data. Not the easiest way to do it, cause I have to stop right at 800.
There we go, and you can see the cell reference is referring specifically to this worksheet, from A1, and that's an absolute reference, to E800, another absolute reference. Let's close that off, and we see that it's brought that cell reference over beautifully. Let's go Next, and here we have a choice of building our Pivot Table in the existing worksheet or, this is our choice, we're going to select a new worksheet, and we'll say Finished. And here we are.
We have a brand-new sheet in our workbook, a Pivot Table ready to go. Now this is known as a Pivot Table template or a layout. What we're going to do is decide what information we want to compare and how we want it compared, and then begin dropping in our values. For example, the people that we want to compare should go here, so we'll drag the Salesperson to here. Overall, we'd like to be able to sort by country, whether it be sales in the UK or sales in the USA, so let's drag Country up here. And ultimately it's important that we look at the order information, so we'll drag the Order Amount here. And you can see that it's going to sum all of the order amounts for each salesperson. Let's begin using our Pivot Table, and yes that's right, with just a few clicks and a little bit of dragging information into place, we're ready to query our data and get results. For example, you can see all of the salespeople that were responsible for sales this year and they're all added up. Let's look at only the UK. There are the salespeople who have made sales in the UK. Now only the US. There are US salespeople. If the formatting's bothering you, let's go back to All and make sure that this formatting is set to Currency. We'll need to increase the size here a little bit, there we go, and we can even isolate individuals. We can look at a sales for, let's check the Show All, get rid of that. Let's look at Callahan, and there's a grand total summary for Callahan and again this Pivot Table is allowing me to maneuver all 800 rows of that data effortlessly. The hardest part to understand when you're working with a Pivot Table is what data to drag into what portion of the template. What you need to keep in mind is who are you trying to compare and for what data? Let's go back to looking at all salespeople again. You notice when I clicked away from my Pivot Table, the Pivot Table field list disappeared. Let's go back in there. We can further sort through this data by dragging in the Order Date. We can look at specific dates and see exactly what sales were made on those dates. Pivot Tables, although they take some work to understand can really make data analysis a beautiful thing.