Years ago, I tried to design a database query that would return the total sales of each salesperson, by month, in an easy-to-read format. I slaved over the project, running multiple queries and never getting the results I wanted.
Then a friend said, “What you need is a cross-tab query!” He spent a few minutes showing me what a cross-tab was and how to build it, and suddenly I saw the light and the impossible mission became easy.
The pivot table feature in Excel 97 is built on the cross-tab concept, and once you learn how to use pivot tables, you’ll be able to accomplish in just a few minutes what it would have taken you hours to do using Subtotal or Sumif functions. For those of you who have never used the pivot table feature, here’s a tutorial to help you understand what a pivot table is for and how it works. We’ll refer to sample data during this tutorial, and you can follow this link to download the SamplePivotTable Excel 97 worksheet . (That download also contains a copy of this article in Word 97 format.)
Cross-tabulations made easy
To illustrate how pivot tables work, we’ll start with a typical scenario that would call for a cross-tab report. Figure A shows sample raw data we’ll use in this tutorial. (We’re using the farmer’s market model for this exercise, but you can apply these lessons to whatever data you need to analyze.)
|We’ll use a pivot table to analyze the data in this worksheet.|
Your mission: Your boss asks you to create a report showing these figures:
- The total quantity sold
- The average quantity of each sale
- The total dollars sold
- The average value of each sale
At this point, you’re probably envisioning spending many hours sorting, summing, and graphing. So you ask a colleague for help—someone who’s been using Excel for six months or so—and that person says, “Oh, that’s easy. Just use the Subtotal function.” Using that approach yields results like those shown in Figure B.
|Using the Subtotal function is a good start, but it still doesn’t generate the report your boss requested.|
Fine-tuning the data
The problem is that your boss wants the data totaled by month, and the automatic subtotals are breaking every day. To subtotal by month instead, your co-worker suggests sorting the data by date, inserting rows at the end of each month, and then using a Sumif function to grab only the rows that have that particular product. To do this, you use a formula that looks something like this:
And after a few more minutes, your worksheet looks like the one shown in Figure C.
|We used the Sumif function to generate these subtotals, but it’s not the easiest way to get the results we need.|
You’re getting closer, but you know there must be a better way. Then your hallway Excel guru says, “What you really need is a cross-tab table!” So you press [F1] to look for “cross-tab,” but there’s nothing in the Help file that looks helpful. Your Excel guru explains over your shoulder, “The cross-tabs in earlier Excel versions became the Pivot Table in Excel 97.”
Your friendly Excel guru points you to the Overview topic in the Help file, and there you read: “A PivotTable is an interactive table that quickly summarizes, or cross-tabulates, large amounts of data. You can rotate its rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details for areas of interest.”
Making your very own Pivot Table
Excel 97 makes it easy to create a pivot table. Here are the steps you follow.
1. Start the Pivot Table Wizard. If you’re working with our sample file, make sure you’re working in the RawData worksheet. Open the Data menu and choose Pivot Table Report. After you say “no” to the offer of help from “Clippy” the office assistant, you’ll see the dialog box shown in Figure D. (Normally, you’ll choose the first option, but you can also build a pivot table from an external database or from multiple ranges in an Excel workbook.)
|The PivotTable Wizard will guide you through the process of creating your report.|
2. Tell Excel where the data is that you want to cross-tabulate. If the dialog box is in the way, you can shrink it by clicking on the button at the end of the text entry box. Choose the range you want by dragging and click Next. (You can also choose the range before you fire up the Wizard.) Figure E shows what your screen will look like when you’ve selected the raw data in our sample worksheet.
Note: If you’re working from our sample sheet, the Wizard will ask if you want to use the existing pivot table and tell you that doing so will save memory. Say no when that prompt appears so you can create an independent pivot table.
|When you select your data range, be sure to include column headers.|
3. Decide what columns you want to use for the report’s row and column headers. This part is one of the coolest features of the Wizard. Excel reads the headers on your data range and presents them as buttons you can drag into place. In this case, we want to see the data “by product by month,” so we dragged Product to the Row area and SaleDate to the Column area. Then, we dragged Quantity, Price, and TotalSale to the Data area in the middle. Excel automatically changes the buttons to “Sum of” to indicate that it is going to use a Sum function on each data point, as shown in Figure F.
|You drag the column header buttons into the appropriate regions in the report layout.|
4. If needed, choose other functions for your data. In our sample scenario, the boss also wants to see Averages. Fortunately, it’s easy to do. Just drag Quantity and TotalSale into the Data area a second time. When you do, the button names change to Sum of TotalSale2 and Sum of Quantity2.
Just double-click on the second field button, and you’ll see a dialog box that lets you choose a number of functions other than Sum. In this case, select Average, and the wizard automatically changes the name associated with that field, as shown in Figure G. For this example, we created fields for the Average of the TotalSale and Quantity values. Figure H shows what our final layout looks like.
|Double-click on a field in the Data region to select a different function from this dialog box.|
|Here’s what our finished report layout looks like.|
5. Decide where to put the finished PivotTable. At this point, you’re almost done. Click Next and you’ll see the final Wizard dialog box, which asks where you want to put your new Pivot Table. Choose the new worksheet option or navigate to an area from your raw data and click Finish. After your computer grinds away for a moment, you’ll see the finished pivot table. Figure I shows what our sample table looks like after we formatted some of the values as currency.
This pivot table looks like what you get when you create automatic subtotals. This table breaks out the subtotals at each change of date, however, and the manager in our fictional scenario wants those values grouped by month. Read on to find out how to change the way the values are grouped.
|Our sample pivot table contains a detailed analysis of our raw data.|
6. Group as needed. It’s easy to change the way your pivot table groups your data. Simply click on the Sale Date button in the Pivot Table and then click on the Group button on the Pivot Table toolbar. (It’s the arrow that’s facing to the right.) When the Grouping dialog box appears, select the Month option as shown in Figure J. Figure K shows what our sample table looks like after the values are grouped by month.
|The Grouping dialog box lets you change the way your pivot table subtotals values.|
|With just a few mouse clicks, we grouped our records by month instead of by date.|
Use the chart wizard next
After you get your pivot table results the way you want them, use the Chart Wizard to create a graph or custom chart. That way you can look for interactions and relationships among the data. Your boss will love it.
Have you used Excel’s pivot table feature to create a particularly clever or complex report? If so, share your experience by posting a comment below or by sending us a note.
Bruce Maples is an author, trainer, speaker, and consultant living in Louisville, KY. Follow this link to write to Bruce .