Section 21.1. Summary Tables Revisited

21.1. Summary Tables Revisited

When you analyze large amounts of data, you can look at the same information in many different ways. How you organize and group the data often determines whether you find or overlook important trends.

Note: In Chapter 15, you saw how to tame large tables of data with features like grouping and outlining. Pivot tables offer another approachthey're a little more work to set up, but they give you much more flexibility in rearranging your tables and calculating different summary information on the fly.

Consider the small table of information shown in Figure 21-1. This table lists all the customers of a small business, along with information about their gender, the city they live in, and their level of education. Looking at this table, an important question comes to mind: is there a relationship between these different pieces of information and the amount of money a customer spends?

Figure 21-1. In this example, there are only 10 records, so patterns aren't difficult to spot. However, if you extended this list to hundreds or thousands of rows, then you would definitely need a summary table to spot any existing relationships.

To look for trends and patterns in the customer list, it helps to build a summary table a table that tallies key amounts, like the average amount spent for a customer in a specific city, education level, or gender. However, there are several potentially important relationships, and, therefore, several types of summary tables that you could create. Pivot tables are the perfect summary-building tool because they give you almost unlimited flexibility when you want to figure out different relationships. But before you learn about how to build pivot tables, it first helps to understand what life is like in Excel without thembecause only then can you see why pivot tables make sense and decide whether you need them in one of your own workbooks.

21.1.1. Life Without Pivot Tables

The most basic way to calculate summary information is to use the SUMIF(), COUNTIF(), and AVERAGEIF() functions described in Chapter 13. To find the average annual purchases for an individual in New York using the worksheet shown in Figure 21-1, use a formula like this:

 =AVERAGEIF(C2:C11, "New York", E2:E11) 

This formula scans the City column (C2 to C11) looking for the text. Every time it finds a match, it adds the corresponding purchase amount from the Annual Purchases column (E2 to E11). The final result is the average of all these amounts.

Next, you can make this formula more generic so that you don't need to create a new version for each city. Instead, change the formula so that it retrieves the text it should match (the city's name) from a cell just to the left of where you're going to place the formula. For example, this formula gets the city name from cell A15:

 =AVERAGEIF($C:$C, A15, $E:$E) 

Note that in this formula, all the search ranges are fixed as absolute references using the dollar sign ($). The search text isn't fixedthat way, when you copy the formula to a new row, the formula uses the search text on that row.

Figure 21-2 shows the result of these "city" formulas, which reside in cells B15, B16, and B17. It also includes some formulas that total up the numbers using different criteria (like the values in the Education and Gender columns ).

Figure 21-2. These three summary tables retrieve their data from the table shown in Figure 21-1 and calculate average purchases by grouping the customers into different categories. The purchase-by-city summary shows that there isn't a significant amount of difference based on location (although Seattle customers tend to spend a little less). The purchase-by-education summary demonstrates a dramatic difference, with less educated individuals making smaller purchases. Finally, the purchase-by-gender comparison turns up no variance at all.

In this example, building each table isn't terribly difficult, but the situation clearly becomes more tedious the more ways you want to compare the same data. It's not difficult to imagine a more realistic scenario where you may want to look at customer purchases based on age bracket , income level, and the answers to different customer survey questions. To get the full picture with this information, you need to build each table from scratch.

In fact, in some of these more complex scenarios, you may need to group and then subgroup your information. Figure 21-3 shows a more advanced example of a table that calculates the variance in average annual purchases by city, and then shows the subdivided totals in each city by gender.

Figure 21-3. This summary table is a little more advanced than the one shown earlier in the chapter. It groups and then subgroups data, which lets you find hidden trends. In this case it identifies New York females as the best purchasers . Of course, there are far too few rows in this example for the results to be truly persuasive. In a table with thousands of rows, however, a grouped and subgrouped breakdown becomes much more meaningful.

The summary in Figure 21-3 performs a two-dimensional comparison. In other words, it compares two different groupingsone by city in different rows, and one by gender in different columns. This is a step up from the one-dimensional summary tables you saw earlier, but it's also more difficult to correctly calculate. You could use the AVERAGEIFS() functions (Section 13.1.4), or create the table in two steps (first grouping the records by city, and then totaling them by gender). In either case, life gets much more complicated, and that's when pivot tables really shine .

21.1.2. Life with Pivot Tables

With a pivot table, you can create summary tables like the ones shown so far just by choosing the columns you want to compare. And once you've built your summary, you don't need to stick with itinstead, you can transform a purchase-by-education table into a purchase-by-city table just by dragging and dropping. You can even drill down into the details, apply filter criteria, and apply advanced subtotaling calculations. The following sections describe all of these techniques.

Pivot Tables vs. Grouping and Subtotals

When should you use pivot tables instead of other Excel features? In Chapter 15, you learned about two techniques for grouping and organizing data:

  • Grouping . Excel's grouping tools let you collapse detailed information in a table so that only the summary information remains. This technique is the perfect solution when you have a worksheet that contains both detail and summary information and you want to make it more manageable. However, you still need to write all the summary formulas and decide how you want to organize the information.

  • Automatic subtotaling . Automatic subtotaling generates subtotals for grouping data according to a column that you choose. Automatic subtotaling is a good technique for building a quick-and-dirty summary report, but it doesn't provide a lot of flexibility. It also breaks up the structure of your list so that you can't effectively edit it any longer.

Compared to either of these features, pivot tables are more flexible and more dynamic. A single pivot table can group and subtotal the same list in different waysall you need to do is drag and drop your columns. Thanks to this flexibility, pivot tables are a much more flexible tool for analyzing your information and discovering hidden relationships.

Excel 2007[c] The Missing Manual
Excel 2007[c] The Missing Manual
ISBN: 596527594
Year: 2007
Pages: 173 © 2008-2017.
If you may any questions please contact us: