So far we have mostly focused on how the data can be grouped and organized, but there is more information to be discovered by changing the way the data is totaled.

In our latest example, each intersection of a row and column represents total sales. If you remember, we created this by placing the amount button in the data field area and choosing to sum the values.

What if we wanted to see more than just the total sales? What if we wanted to show the number of items sold as well? We could do this by creating another summary to count the items.

Select the item field and drag it to the data field area. Notice that the calculation defaults to sum – that’s not what we want in this case. We need to change the summary method from sum to count. To do this, double click on the item button.

The next screen you see lets you tell Calc how to compute a total. There are several options (in addition to sum) including average, count, standard deviation, min, max and others.

In our case we want to count the occurrences of an item so we'll select count.

If we were to hit the OK button now, our data pilot table would show both the total sales, and the number of items sold for each combination of sales rep and promotion.

The final thing to cover in this section is the concept of adding multiple fields to a row or column. To keep things simple, we'll remove the calculation we just created so that the data pilot table only shows sales amount by promotion by rep.

Suppose we wanted to further break down promotion by category. Just drag the category button into the row field area, just below the promotion button.

Where we used to have just one row per promotion we now see a row for each promotion plus additional rows for each category that exists in a promotion.

Our table now represents sales by promotion, broken down by category, broken down by sales rep.

If you've never worked with a tool like this before this might seem a bit confusing, but it's easy to see how powerful the data pilot tool is, and how quickly you can generate sophisticated tables to answer your questions.

By the way, when you have more than 2 or more fields as row fields you can drill from one field to the details of another field.

Just double-click on an item in the first row field and the details of the second field will be hidden for that item. Double click it again to make the details reappear.

You can hide the details for all items and expand and collapse them as you wish.

To enable this feature, expand the data pilot interface by clicking on the “more” button then make sure the “enable drill to details” button is checked.

Notice the total columns and total rows check boxes. Checking these boxes will let you show or hide totals for the rows or columns.

We'll leave the totals, but to simplify things for the next module we'll remove the category field.

We'll create the data pilot in a new worksheet. And finally we get to click the OK button.