Chapter 38: Using PivotTables to Describe Data

Overview

• What is a PivotTable?

• How can I use a PivotTable to summarize grocery sales at several grocery stores?

• What are the different PivotTable layouts available in Excel 2007?

• Why is a PivotTable called a PivotTable?

• How can I easily change the format in a PivotTable?

• How can I collapse and expand fields?

• How do I sort and filter PivotTable fields?

• How do I summarize a PivotTable by using a PivotChart?

• How do I use the Report Filter section of the PivotTable?

• How do I add blank rows or hide subtotals in a PivotTable?

• How do I apply conditional formatting to a PivotTable?

• How can I update my calculations when I add new data?

• I work for a small travel agency for which I need to mass-mail a travel brochure. My funds are limited, so I want to mail the brochure to people who spend the most money on travel. From information in a random sample of 925 people, I know the gender, the age, and the amount these people spent on travel last year. How can I use this data to determine how gender and age influence a person’s travel expenditures? What can I conclude about the type of person to whom I should mail the brochure?

• I’m doing market research about Volvo Cross Country Wagons. I need to determine what factors influence the likelihood that a family will purchase a station wagon. From information in a large sample of families, I know the family size (large or small) and the family income (high or low). How can I determine how family size and income influence the likelihood that a family will purchase a station wagon?

• I work for a manufacturer that sells microchips globally. I’m given monthly actual and predicted sales for Canada, France, and the United States for Chip 1, Chip 2, and Chip 3. I’m also given the variance, or difference, between actual and budgeted revenues. For each month and each combination of country and product, I’d like to display the following data: actual revenue, budgeted revenue, actual variance, actual revenue as a percentage of annual revenue, and variance as a percentage of budgeted revenue. How can I display this information?

• What is a calculated field?

• How do you use the Report Filter?

• How do you group items in a PivotTable?

• What is a calculated item?

• What is “drilling down”?

• I often have to use specific data in a PivotTable to determine profit, such as the April sales in France of Chip 1. Unfortunately, this data moves around when new fields are added to my PivotTable. Does Excel have a function that enables me to always extract April’s Chip 1 sales in France from the PivotTable?

  • What is a PivotTable?

  • In numerous business situations, you need to analyze, or “slice and dice,” your data to gain important business insights. Imagine that we sell different grocery products in different stores at different points in time. We might have hundreds of thousands of data points to track. PivotTables let us quickly summarize our data in almost any way imaginable. I call this “slicing and dicing data.” For example, for our grocery store data, we could use a PivotTable to quickly determine the following:

    • Amount spent per year in each store on each product

    • Total spending at each store

    • Total spending for each year

  • In the travel agency example, for instance, you would like to slice the data so that you can determine whether the average amount spent on travel is influenced by age or gender or by both factors. In the station wagon example, we’d like to compare the fraction of large families that buy a station wagon to the fraction of small families that purchase a station wagon. In the microchip example, we’d like to determine our total Chip 1 sales in France during April, and so on. A PivotTable is an incredibly powerful tool that can be used to slice and dice data. The easiest way to understand how a PivotTable works is to walk through some carefully constructed examples, so let’s get started! We begin with an introductory example and then illustrate many advanced PivotTable features through subsequent examples.

  • How can I use a PivotTable to summarize grocery sales at several grocery stores?

  • The Data worksheet in the file Groceriespt.xlsx contains more than 900 rows of sales data. (See Figure 38-1.) Each row contains the number of units and revenue sold of a product at a store, as well as the month and year of the sale. The product group (either fruit, milk, cereal, or ice cream) is also included. We would like to see a breakdown of sales during each year of each product group and product at each store. We would also like to be able to show this breakdown during any subset of months in a given year (for example, what the sales were during January–June).

    
    Figure 38-1: Data for the grocery PivotTable example

  • Before creating a PivotTable, we must have headings in the first row of our data. Notice that our data contains headings (Year, Month, Store, Group, Product, Units, and Revenue) in row 2. Place your cursor anywhere in your data and on the Insert tab, in the Tables group, click PivotTable. Microsoft Office Excel will open the Create PivotTable dialog box and try to guess your data range. (In our case, Excel correctly guessed that our data range was C2:I924.) (See Figure 38-2.) By selecting Use An External Data Source, you can also refer to a database as a source for your PivotTable.

    
    Figure 38-2: The Create PivotTable dialog box

  • After clicking OK, you will see the PivotTable Field List dialog box shown in Figure 38-3.

    
    Figure 38-3: The PivotTable Field List dialog box

  • You fill in the PivotTable Field List dialog box by dragging PivotTable headings or fields into the desired boxes, or zones. This step is critical to ensuring that the PivotTable will summarize and display the data in the manner you wish. The four zones are as follows:

    • Row Labels. Fields dragged here will be listed on the left side of the table in the order they are dragged. For example, we dragged to the Row Labels box the fields Year, Group, Product, and Store, in that order. This will cause Excel to summarize data first by Year; then for each product Group within a given a year; then by Product within each group, and finally break down each product by Store. You can at any time drag a field to a different zone or reorder the fields within a zone by dragging a field up or down in a zone or by clicking the arrow to the right of the field label.

    • Column Labels. Fields dragged here will have their values listed across the top row of the PivotTable. To begin, we will have no fields in the Column Labels zone.

    • Σ Values. Fields dragged here will be summarized mathematically in the table. We will drag Units and Revenue (in that order) to this zone. Excel tries to guess what kind of calculation you want to perform on a field. In our example, Excel guesses that we want to sum Revenue and Units, which happens to be correct. If you want to change the method of calculation for a data field to average, count, or something else, simply click the data field and choose Value Field Settings. I will give an example of how to use Value Field Settings later in the chapter.

    • Report Filter. In Excel 2007, Report Filter is the new name for the old Page Field area. For fields dragged to the Report Filter area, we can easily pick any subset of the field values so the PivotTable will show calculations based only on that subset of field values. In our example, we dragged Month to the Report Filter area. Then we can easily select any subset of months, for example January–June, and our calculations are based on only those months.

  Our completed PivotTable Field List dialog box is shown in Figure 38-4. The resulting PivotTable is shown in Figure 38-5 and in the All Row Fields worksheet of the workbook Groceriespt.xlsx. Before discussing the PivotTable, I’ll give some advice on navigating worksheets (like this one) containing many worksheets. In the lower-right corner (to the left of the worksheet names) of your screen, you will see four arrows. Clicking the left-most arrow takes you to the first worksheet; clicking the right-most arrow shows the last worksheet; and clicking the other arrows moves you one worksheet to the left or right.

  
  Figure 38-4: Completed PivotTable Field List dialog box

  
  Figure 38-5: The Grocery PivotTable in compact form

  To see the Field list, you need to be in a field in the PivotTable. If you do not see the Field list, right-click any cell in the PivotTable and select Show Field List.

  Our resulting PivotTable is in the All Row Fields worksheet. (See Figure 38-5.) In row 6, we see that 233,161 units were sold for $702,395.82 in 2007. In row 30, we find that 2719 units of Ben and Jerry’s ice cream were sold in the west store for $9,627.41 in 2007.

  What are the different PivotTable layouts available in Excel 2007?

  The PivotTable layout shown in Figure 38-5 is called the compact form. In the compact form, the Row fields are shown one on top of another. To change the layout, place your cursor anywhere within the table, and on the Design tab, in the Layout Group, click Report Layout. and choose one of the following: Show In Compact Form (see Figure 38-5), Show In Outline Form (see Figure 38-6 and the Outline Form worksheet), or Show In Tabular Form (Figure 38-7 and the Tabular Form worksheet).

  
  Figure 38-6: The outline form

  
  Figure 38-7: The tabular form

  Why is a PivotTable called a PivotTable?

  We can easily “pivot” fields from a row to a column and vice versa to create a different layout. For example, by dragging the Year field to the column field, we find the PivotTable layout shown in Figure 38-8 (see the Years Column worksheet).

  
  Figure 38-8: The Years field pivoted to the column field

  How can I easily change the format in a PivotTable?

  If you want to change the format of an entire column field, simply double-click the column heading and select Number Format from the Value Field Settings dialog box. Then apply the desired format. For example, in the Formatted $s worksheet, we formatted the Revenue field as currency by double-clicking the Sum of Revenue heading and applying a currency format. You can also change the format of a value field by clicking the arrow to the right of the Value field in the PivotTable Field List dialog box. Then select Value Field Settings followed by Number Format, and you can reformat the column as desired.

  From any cell in the PivotTable, you can select the Design tab on the Ribbon. Then many PivotTable styles become available.

  How can I collapse and expand fields?

  Expanding and collapsing fields is a great new Excel 2007 feature. In Figure 38-5, you see minus (–) signs by each year, group, and product. Clicking the minus sign will collapse a field and change the sign to a plus (+) sign. Clicking the plus sign will expand the field. For example, if you click the minus sign by cereal in A6, you will find that in each year, cereal is contracted to one row, and the various cereals are no longer listed. See Figure 38-9 and the Cerealcollapse worksheet. Clicking the plus sign in cell A6 will bring back the detailed or expanded view including all the cereals.

  
  Figure 38-9: The cereal field collapsed

  We can also expand or contract an entire field! To expand or contract an entire field, go to any row containing a member of that field and select PivotTable Tools Options on the Ribbon. Then click either the green Expand Entire Field button (labeled with a plus sign) or the red Contract Entire Field button (labeled with a minus sign) from the Active Field group on the Ribbon. (See Figure 38-10.)

  
  Figure 38-10: The Expand Entire Field and Contract Entire Field buttons

  For example, suppose you simply want to see for each year the sales by product group. Pick any cell containing a group’s name (for example, A6), select PivotTable Tools Options on the Ribbon, and click the Collapse Entire Field button. You will see the result shown in Figure 38-11 on the next page (Groups worksheet collapsed). Selecting the Expand Entire Field button would bring us back to our original view.

  
  Figure 38-11: The Group field collapsed

  How do I sort and filter PivotTable fields?

  In Figure 38-5, the products are listed alphabetically within each group. For example, chocolate is the first type of milk listed. If we want the products to be listed in reverse alphabetical order, simply move the cursor to any cell containing a product (for example, A7of the All Row Fields worksheet) and click the drop-down arrow to the right of the Row Labels entry in A5. You will see the list of filtering options shown in Figure 38-12. Selecting Sort Z To A would ensure that whole milk is listed first for milk, plums is listed first for fruit, and so on. Our current table displays results first from 2007, then 2006, then 2005. If we wanted to see the Year 2005 first, simply move the cursor to any cell containing a year (for example, A5) and choose Sort Smallest To Largest from the available options.

  
  Figure 38-12: PivotTable filtering options for the Product field

  Note that from the bottom of the filtering options dialog box, we can also select any subset of products to be visible. You may want to first clear Select All and then select the products you want to show.

  For another example of filtering, look at the file Ptcustomers.xlsx, shown in Figure 38-13 on the next page. The worksheet data contains for each customer transaction the customer number, amount paid, and the quarter of the year in which payment was received. After dragging Customer to the Row Labels box, Quarter to the Column Labels box, and Paid to the Σ Values box, the PivotTable shown in Figure 38-14 on the next page is displayed (see the Ptable worksheet in the Pcustomers.xlsx file).

  
  Figure 38-13: The Customer PivotTable data

  
  Figure 38-14: The Customer PivotTable

  Naturally, we might like to show a list of just our top 10 customers. To obtain this layout, simply click the Row Labels arrow and select Value Filters. Then choose Top 10 items to obtain the resulting layout shown in Figure 38-15 (see the Top 10 cus worksheet). Of course, by selecting Clear Filter, you can return to the original layout.

  
  Figure 38-15: Top 10 customers

  Suppose you simply want to see the top customers that generate 50 percent of your revenue. Select the Row Labels filtering icon, select Value Filters, Top 10, and fill in the dialog box as shown in Figure 38-16.

  
  Figure 38-16: Configuring the Top 10 Filter dialog box to show customers generating 50 percent of revenue

  The resulting PivotTable is in the Top half worksheet. (See Figure 38-17.) Thus, our top 14 customers generate a little more than half our revenue.

  
  Figure 38-17: The top customers generating half of the revenues

  Now let’s suppose we want to sort our customers by their Quarter 1 revenue (see the Sorted q1 worksheet). We right-click anywhere in the Quarter 1 column, point to Sort, and then click Sort Largest To Smallest. (See Figure 38-18.) The resulting PivotTable is shown in Figure 38-19. Note that Customer 13 paid us the most in Quarter 1, Customer 2 paid us the second most, and so on.

  
  Figure 38-18: Sorting on the Quarter 1 column

  
  Figure 38-19: Customers sorted on Quarter 1 revenue

  How do I summarize a PivotTable by using a PivotChart?

  Excel makes it easy to visually summarize PivotTables by using PivotCharts. The key to laying out the data the way you want it charted in a PivotChart is to use methods such as sorting data and collapsing or expanding fields. In our grocery example, suppose we want to summarize the trend over time of each food group’s unit sales. See the Chart 1 worksheet in the file Groceriespt.xlsx. Then we should move the Year field to a Column field and delete Revenue as a Σ Values field. We also need to collapse the entire Group field in the Row Labels zone. Now we are ready to create our first PivotChart. Simply click anywhere inside the table and select Options, PivotChart. You can now pick the chart type you want created. We chose the fourth Line Graph option, which displays the chart in Figure 38-20. For example, the chart shows us that milk sales were highest in 2005 and lowest in 2006.

  
  Figure 38-20: PivotChart for unit group sales trend

  How do I use the Report Filter section of the PivotTable?

  Recall that we placed Months in the Report Filter section of the table. To see how we use the Report Filter, suppose that we want to summarize sales for the months January– June. By clicking the Filter icon in cell B2 of the First 6 months worksheet, we can select January–June. This results in the PivotTable shown in the First 6 Months worksheet, which summarizes the number of units sold by product, group, and year for the months January–June. (See Figure 38-21 on the next page.)

  
  Figure 38-21: A PivotTable summarizing January–June sales

  How do I add blank rows or hide subtotals in a PivotTable?

  If you want to add a blank row between each grouped item, simply select PivotTable Tools Design on the Ribbon, click Blank Rows, and then click Insert Blank Line after Each Item. If you want to hide subtotals or grand totals, select PivotTable Tools Design on the Ribbon and then select Subtotals or Grand Totals. After adding blank rows and hiding all totals, we obtain the table in the Blank rows no totals worksheet. (See Figure 38-22.) After right clicking when in any PivotTable cell, you may select PivotTable Options, thereby bringing up the PivotTable Options dialog box. From this dialog box, you can replace empty cells by using any character, such as an underscore (_), or by using a 0.

  
  Figure 38-22: Grocery PivotTable without totals

  How do I apply conditional formatting to a PivotTable?

  Suppose we want to apply data bars to the Units column of our PivotTable. The problem is that our subtotals and grand totals will have large data bars and also make the other data bars smaller than they should be. We want the data bars to apply to all product sales, not the subtotals and grand totals. See the Cond form worksheet of workbook Groceriespt.xlsx. To apply the data bars to only the unit sales by product, begin by placing the cursor in a cell containing a product (for example, chocolate milk in B8) and then select the Sum Of Units column data (cell range B7:B227). Now, on the Home tab of the Ribbon, click Conditional Formatting, followed by Data Bars, and choose More Rules. You will see the New Formatting Rule dialog box shown in Figure 38-23 on the next page.

  
  Figure 38-23: New Formatting Rule dialog box for using conditional formatting with PivotTables

  By selecting All Cells Showing “Sum of Units” Values For “Product” we ensure that our data bars will apply to only cells listing unit sales for products. This results in the data bars shown in the Cond form worksheet and Figure 38-24.

  
  Figure 38-24: Data bars for a PivotTable

  How can I update my calculations when I add new data?

  If the data in your original set of rows changes, you can update your PivotTable to include the data changes by simply right-clicking the table and selecting Refresh. You can also select Refresh after choosing options.

  If you want new data to be automatically included in your PivotTable calculations when you refresh, then you should name your original data set as a table (see Chapter 24, “Tables”) by selecting Ctrl+T. Then when you add new data and refresh, your new data will automatically be included in the PivotTable calculations!

  If you want to change the range of data used to create a PivotTable, you can always select Change Data Source from the Options tab. You can also move the table to a different location by selecting Move PivotTable.

  I work for a small travel agency for which I’m about to mass-mail a travel brochure. My funds are limited, so I want to mail the brochure to people who spend the most money on travel. From information in a random sample of 925 people, I know the gender, the age, and the amount these people spent on travel last year. How can I use this data to determine how gender and age influence a person’s travel expenditures? What can I conclude about the type of person to whom I should mail the brochure?

  To understand this data, we need to break it down into the following:

  • Average amount spent on travel by gender

  • Average amount spent on travel for each age group

  • Average amount spent on travel by gender for each age group

  • Our data is included on the Data worksheet in the file Traveldata.xlsx. A sample of the data is shown in Figure 38-25 on the next page. For example, our first person is a 44-year-old male who spent $997 on travel.

    
    Figure 38-25: Travel agency data showing amount spent on travel, age, and gender

  • Let’s first get a breakdown of spending by gender. To obtain this breakdown, we begin by selecting Insert PivotTable. Excel extracts the range A2:D927. After clicking OK, we put the cursor in the table so the field list appears. Next, we drag the Gender column to the Row Labels zone and drag Amount Spent On Travel to the Σ Values zone. This results in the PivotTable shown in Figure 38-26 on the next page.

    
    Figure 38-26: PivotTable summarizing the total travel expenditures by gender

  • We can tell from the heading Sum Of Amount Spent On Travel that we are summarizing the total amount spent on travel, but we actually want the average amount spent on travel by men and women. To calculate these quantities, we double-click Sum Of Amount Spent On Travel and then select Average from the Value Field Settings dialog box, shown in Figure 38-27 on the next page.

    
    Figure 38-27: You can select a different summary function in the Value Field Settings dialog box.

  • We now obtain the results shown in Figure 38-28.

    
    Figure 38-28: Average travel expenditures by gender

  • We find that, on average, people spend $908.13 on travel. Women spend an average of $901.16, whereas men spend $914.99. This PivotTable indicates that gender has little influence on the propensity to travel. By clicking the Row Labels arrow, you can show just male or female results.

  • Now we want to see how age influences travel spending. To remove Gender from the PivotTable, simply click Gender in the Row Labels portion of the PivotTable Field List and remove it from the Row Labels area. Then, to break down spending by age, drag Age to the row area. The PivotTable now appears as it’s shown in Figure 38-29.

    
    Figure 38-29: PivotTable showing the average travel expenditures by age

  • We find that age seems to have little effect on travel expenditures. In fact, this PivotTable is pretty useless in its present state. We need to group data by age to see any trends. To group our results by age, right-click anywhere in the Age column and choose Group. In the Grouping dialog box, you can designate the interval by which to define an age group. Using 10-year increments, we obtain the PivotTable shown in Figure 38-30 on the next page.

    
    Figure 38-30: Use the Group And Show Detail command to group detailed records.

  • We now find that 25–34 year olds on average spend $935.84 on travel, 55–64 year olds spend $903.57 on travel, and so on. This information is more useful, but it still indicates that people of all ages tend to spend about the same amount on travel. This view of our data does not help determine who we should mail our brochure to.

  • Finally, let’s get a breakdown of average travel spending by age, for men and women separately. All we have to do is drag Gender to the Column Labels zone of the Field List resulting in the PivotTable shown in Figure 38-31.

    
    Figure 38-31: Age/gender breakdown of travel spending

  • Now we’re cooking! We see that as age increases, women spend more on travel and men spend less. Now we know who should get the brochure: older women and younger men. As one of my students said, “That would be some kind of cruise!”

  • A graph provides a nice summary of our analysis. After moving the cursor inside the PivotTable and choosing PivotChart, we select the fourth option from Column Graphs. The result is the chart shown in Figure 38-32. If you want to edit the chart further, select PivotChart Tools. Then, for example, if you choose Layout, you can add titles to the chart and axis and make other changes.

    
    Figure 38-32: PivotChart for the age/gender travel expenditure breakdown

  • We see that each age group spends approximately the same on travel, but as age increases, women spend more than men. If you want to use a different type of chart, you can change the chart type by right-clicking the PivotChart and then choosing Chart Type.

  • Notice that the bars showing expenditures by males decrease with age, and the bars representing the amount spent by women increase with age. We can see why the PivotTables that showed only gender and age data failed to unmask this pattern. Because half our sample population are men and half are women, we found that the average amount spent by people does not depend on the age. (Notice that the average height of the two bars for each age is approximately the same.) We also found that the average amount spent for men and women was approximately the same. We can see this because, averaged over all ages, the blue and red bars have approximately equal heights. Slicing and dicing our data simultaneously across age and gender does a much better job of showing us the real information.

  • I’m doing market research about Volvo Cross Country Wagons. I need to determine what factors influence the likelihood that a family will purchase a station wagon. From information in a large sample of families, I know the family size (large or small) and the family income (high or low). How can I determine how family size and income influence the likelihood that a family will purchase a station wagon?

  • In the file Station.xlsx, you can find the following information:

    • Is the family size large or small?

    • Is the family’s income high or low?

    • Did the family buy a station wagon? Yes or No.

  • A sample of the data is shown in Figure 38-33. For example, the first family listed is a small, high-income family that did not buy a station wagon.

    
    Figure 38-33: Data collected about income, family size, and the purchase of a station wagon

  • We want to determine how family size and income influence the likelihood that a family will purchase a station wagon. The trick is to look at how income affects purchases for each family size and how family size affects purchases for each income level.

  • To begin, we choose Insert Pivot Table, and then select our data (the cell range B2:D345). Using the PivotTable field list, we drag Family Size to the Row Labels area, Station Wagon to the Column Labels area, and any of the three fields to the Σ Values area. The result is the PivotTable shown in Figure 38-34. Notice that Excel has chosen to summarize the data appropriately by counting the number of observations in each category. For example, 34 high-salary, large families did not buy a station wagon, whereas 100 high-salary, large families did buy one.

    
    Figure 38-34: Summary of station wagon ownership by family size and salary

  • We would like to know, for each row in the PivotTable, the percentage of families that purchased a station wagon. To display the data in this format, we right-click anywhere in the PivotTable data and then choose Value Field Settings, which displays the Value Field Settings dialog box. In the dialog box, click Show Values As, and then select % Of Row in the Show Data As list. We now obtain the PivotTable shown in Figure 38-35.

    
    Figure 38-35: Percentage breakdown of station wagon ownership by income for large and small families

  • From Figure 38-35, we learn that for both large and small families, income has little effect on whether the family purchases a station wagon. Now we try to determine how family size affects the propensity to buy a station wagon for high-income and low-income families. To do this, we move Salary above Family Size in the Row Labels zone, resulting in the PivotTable shown in Figure 38-36.

    
    Figure 38-36: Breakdown of station wagon ownership by family size for high and low salaries

  • From this table, we learn that for high-income families, a large family is much more likely to buy a station wagon than a small family. Similarly, for low-income families, a large family is also more likely to purchase a wagon than a small family. The bottom line is that family size has a much greater effect on the likelihood that a family will purchase a station wagon than does income.

  • I work for a manufacturer that sells microchips globally. I’m given monthly actual and predicted sales for Canada, France, and the United States for Chip 1, Chip 2, and Chip 3. I’m also given the variance, or difference, between actual and budgeted revenues. For each month and each combination of country and product, I’d like to display the following data: actual revenue, budgeted revenue, actual variance, actual revenue as a percentage of annual revenue, and variance as a percentage of budgeted revenue. How can I display this information?

  • In this scenario, you are a finance manager for a microchip manufacturer. You sell your products in different countries and at different times. PivotTables can help you summarize your data in a format that’s easily understood.

  • The file Ptableexample.xlsx includes monthly actual and predicted sales during 1997 of Chip 1, Chip 2, and Chip 3 in Canada, France, and the United States. The file also contains the variance, or difference, between actual revenues and budgeted revenues. A sample of the data is shown in Figure 38-37 on the next page. For example, in the U.S. in January, sales of Chip 1 totaled $4,000, although sales of $5,454 were predicted. This yielded a variance of –$1,454.

    
    Figure 38-37: Chip data from different countries for different months showing actual, budget, and variance revenues

  • For each month and each combination of country and product, we would like to display the following data:

    • Actual revenue

    • Budgeted revenue

    • Actual variance

    • Actual revenue as a percentage of annual revenue

    • Variance as a percentage of budgeted revenue

  • To begin, select a cell within the range of data we’re working with (remember that the first row must include headings) and then choose Insert PivotTable. Excel automatically determines that our data is in the range A1:F208.

  • If we drag Month to the Row Labels area, Country to the Column Labels area, and Revenue to the Values area, for example, we obtain the total revenue each month by country. A field you add to the Report Filter area (Product, for example) lets you filter your PivotTable by using values in that field. By adding Product to the Report Filter area, we can view sales of only Chip 1 by month for each country. Given that we want to be able to show data for any combination of country and product, we should add Month to the Row Labels area of the PivotTable and both Country and Product to the Report Filter area. Next, we drag Var, Revenue, and Budget to the Σ Values zone. We have now created the PivotTable that is shown in Figure 38-38.

    
    Figure 38-38: Monthly summary of revenue, budget, and variances

  • For example, in January, total revenue was $87,534 and total budgeted sales were $91,831, so our actual sales fell $4,297 short of the forecast.

  • We want to determine the percentage of revenue earned during each month. We again drag Revenue from the field list to the Σ Values area of the PivotTable. Right-click in this data column, and then choose Value Field Settings. In the Value Field Settings dialog box, click Show Values As. In the Show Values As list, select % Of Column and rename this field as Sum Of Revenue2, as shown in Figure 38-39.

    
    Figure 38-39: Creating each month’s percentage of annual revenue

  • We now obtain the PivotTable shown in Figure 38-40 on the next page. January sales provided 8.53 percent of revenue. Total revenue for the year was $1,026,278.

    
    Figure 38-40: Monthly revenue breakdown

  • What is a calculated field?

  • Now we want to determine for each month the variance as a percentage of total sales. To do this, we will create a calculated field. Select a cell anywhere within the data area of the PivotTable, and then choose Formulas from the Option tab. Next choose Calculated Field to display the Insert Calculated Field dialog box. As shown in Figure 38-41, enter a name for your field, and then enter your formula. The formula we’re using in this example is =Var/Budget. You can enter the formula yourself or use the list of fields and the Insert Field button to add a field to the formula. After clicking Add and then OK, we obtain the PivotTable shown in Figure 38-42.

    
    Figure 38-41: Creating a calculated field

    
    Figure 38-42: The PivotTable with calculated field for variance percentage

  • Thus, in January, our sales were 4.7 percent lower than budgeted. By displaying the Insert Calculated Field dialog box again, you can modify or delete a calculated field.

  • How do you use the Report Filter?

  • To see sales of Chip 2 in France, for example, you can select the appropriate values from the Product and Country fields in the Page Fields area. With Chip 2 and France selected, we would see the PivotTable shown in Figure 38-43.

    
    Figure 38-43: Sales of Chip 2 in France

  • How do you group items in a PivotTable?

  • Often, we want to group headings in a PivotTable. For example, we might want to combine sales for January–March. To create a group, select the items you want to group, right-click the selection, and then choose Group And Show Detail, Group. After changing the name Group 1 to Jan-March, we obtain the PivotTable shown in Figure 38-44 on the next page.

  • 
    Figure 38-44: Grouping items together for January, February, and March