Hands on: Excel Pivot Tables explained

The first thing that needs fixing, however, is the format of the winnings’ values. Probably the easiest way to do this is to highlight all of them ­ – simply right-click and choose Format Cells, Number, Currency, just as you would on an ordinary worksheet.

To change the colour scheme of the whole Pivot Table, click anywhere in the table. Under the Pivot Table Tools contextual tab, choose the Design tab. To the right of this ribbon are seven light styles. You can click an arrow beside them to view a wide choice of light, medium and dark styles.

To view even more styles, under the Pivot Table Tools contextual tab, click the Page Layout tab and then on the Themes button.

If you want to see how a style will look, choose the Office button, Excel Options, Popular, and then check Enable Live Preview.

Another feature of Excel 2007 is that its Pivot Tables promise to maintain their formatting when they are refreshed to include new data. Previous versions tended to lose their formatting easily. With enough changes to a Pivot Table, this promise can be broken, though.

Back to our pigeon racers. Although the Members weren’t listed in alphabetical order in the original list, they are on the new Pivot Table, and the results for all of them are displayed. Click within the Pivot Table. Under Options, click Headers to display the label down arrows ­ now you can easily separate the winners from the featherweights, even within a month.

Click the arrow beside ‘Race date’. Check the dates in July. Click the arrow by Column Labels and choose ‘Value Filters Greater than’ and enter 100. This cuts the example list down to Dot, Meg, Pam and Sam. Those members were the leading prize winners in July.

To return to the full table, click on the arrow next to Race date and select All. Next, click the arrow by Columns Labels and then choose Value Filters, Clear Filter.

Supposing you want to see the average prizes rather than the totals. Click in the Pivot Table to see the Pivot Table Tools contextual tab, then on the Options tab. Click the Field List button. Click the arrow by ‘Sum of Winnings’ in the Values box.

Choose Value Field Settings. Select Average and then OK. The Pivot Table will show the average winnings for both bred and purchased pigeons for each member. Backtrack through those actions to return to totals

Incidentally, if you have a huge Pivot Table, with thousands of records that take a long time to refresh, you can check the Defer Layout Update box in the Pivot Table Field List dialogue box. The Pivot Table won’t refresh until you are ready.