Version 4.1.6.2

In the last tutorial we looked at how to create a Pivot Table. Now we will build on that a bit to show you some things you can do.

Filter the results

In our sample data set for the pivot table we created last time (which you can download here we had 3 Regions, 11 Sales Representatives, and 5 products. The resulting pivot table displayed the data for all them, but you have filter options to reduce the display to a narrower set. Region was the Page Field, and in our best pivot table we had Item as the Column Field and Representative as the Row Field. We said this was best because it allowed us to easily get sub-totals by item or by representative. And of course the Data Field was Total, which here meant the total amount for each order. You can always filter on Page Field, Column Field, and Row Field by clicking on the drop-down next to each label. They are showing All when you start, but if you only wanted to see sales for the Northeast region, you could go to drop-down next to Region, and you would see this window pop up:

 

 

 

 

 

 

 

 

 

Right now, there is a checkmark for all three regions, and a checkmark in All at the bottom of the window. Also, the region Northeast is currently highlighted, which makes it the current item. You could manually uncheck everything else, but there are two useful buttons on the bottom. The first says “Show only the current item”, and if you clicked it this would uncheck every other region for you. The other button is “Hide only the current item”, and it does the opposite. If you clicked this it would remove Northeast from your table and leave the other regions. And for the Item and Rep you have a similar window for filtering.

This is the kind of simple filtering we have seen previously, but supposed you wanted to do more? There is a cell at the top, A1, which has the word Filter. If you click on it, you get a window that looks very similar to the Standard Filter window we saw previously when we looked at filtering data in Calc.

 

 

 

 

Here you can create filter that use multiple criteria to simultaneously filter the data, and you can use conditions, such as only showing orders where the total was great than $100. We covered this kind of filtering in more detail in Data Manipulation 2: Standard and Advanced Filters, so I will refer you there if you need more help in using this filter.

Sorting

The same drop-down that we used for Filtering ca also be used for Sorting if you are working with a Row or Column field. On the top of the window you have the usual Sort Ascending and Sort Descending options. There is also a Custom Sort that uses the Sort Lists we discussed in Fills, an Introduction, which you can consult for more information. In short, this lets you take things like the names of days of the week, or of months, and sort them in the logical order by time. And you can create custom sort lists as needed.

Drilling

By design, the data in a pivot table gets summarized by the row and column fields. But sometimes you may want to see the details that make up the summarized total. This is known as drilling. For example, if I look at the representative named Gill, I see that this person had sales of 1,132.74 for binders. But what went into that? If I double-click on the cell that displays 1,132.74, a new sheet opens that tells me it came from an order on 1/15/11 for 413.54, and another order on 5/31/11 for 719.20. You could also get this result by going to Data–>Group and Outline–>Show Details, but I think double-clicking on the cell is somewhat easier.

Editing the Table

At the end of the last tutorial we saw that you can re-open the DataPilot Window to change your layout. That is the most reliable way, and in some cases the only way, to make major changes. But you can do some things just by dragging fields around in the table itself. For example, we did a first pass by putting both Rep and Item as Row Fields, but saw that this was not the best way to do the analysis. Of course, reopening the DataPilot is a good way to solve this, but there is a quicker way. At the top of the column for Item is the column label, which is the cell that says “Item”, of course. You can click and drag this column label to a new position. If you click on it you will see your cursor become a horizontal rectangle in gray. This is an indication that it is a Row Field, because rows are horizontal. But drag this one place to the right, and it becomes a vertical rectangle, which means that if you release it here it will become a Column Field. If you keep dragging it, it becomes a black “x”, indicating that you cannot move it here. If you release it in the Column Field position, the Pivot Table becomes just like our second example from the last tutorial.

You can also drag a row or column off of the pivot table altogether, which removes that field completely. But that is a “permanent” change. You can re-add the field only by going back to the Data Pilot and adding the field there.

Refreshing the Data

The pivot table contains the data that was in the original spreadsheet at the time it was created. But sometimes you make changes or add data to a spreadsheet. Depending on which it is, you have two choices. If you added or removed records from the original data set, the only way to bring the pivot table up to date is to create it again. Fortunately, that is not difficult. But if you merely changed a value in one of your records, you can right-click within the results area of the pivot table and it will recalculate. As an example, take a quantity from one of the existing orders, change it, and then refresh. You should see the change.

This is an introduction to a very useful technique, but we need to move on to the next topic, which gets us into our old friends Styles and Templates.

Listen to the audio version of this post on Hacker Public Radio!