From the Report Layout options you can select one of the three possible report layouts: Compact, Outline or Tabular — each shows the data in different ways. From Grand Totals list you can select whether totals appear for rows, columns, or both, and from the Subtotals list, you can select whether group totals appear above or below the group — in our example we don't have groups so this isn't appropriate.
The Options tab includes tools for formatting and working with your pivot table. It includes a Refresh button which you need when the data that the pivot table is based on changes. The pivot table will not update to show changes in the data unless you manually refresh it by clicking the Refresh button.
Also from the Options tab you can select PivotChart to create a chart of your pivot table data. You can create any type of chart except XY (Scatter), Bubble, or Stock chart types. The pivot chart has its own PivotChart Filter Pane which allows you to filter the data that shows in the pivot chart. Changes to the chart are reflected in the pivot table.
»
Working With Your Data
You can change the data that shows in the pivot table by selecting options from the various dropdown filter lists on the pivot table. For example, from the Report Filter in the top left hand cell of the pivot table you can select to show both Half years or one or the other of them.
From the Column Labels you can select a salesperson's data to view or view two or a subset of the salespeople's records. From the Row Labels you can select to see one customer, all or any combination of them.
The actual options available in the Column Labels and Row Labels lists depend on the arrangement of fields in your table. Notice too that when a subset of the data is selected the filter arrow changes to a filter sign indicating that you are no longer viewing the entire data set.
To change the way the data is laid out and, in consequence, change the results you see in the pivot table, you can drag and drop fields into different areas of the PivotTable Field List. Changing whether a field appears in the Column Labels or Row Labels area or changing its order in one of those boxes changes the summary.
You can also sort the data in the columns or rows by right clicking an entry and choosing Sort and then choosing the Sort A to Z or Sort Z to A options. You can also sort manually by dragging the items to rearrange them yourself into your desired order.
While the summarized values currently show summed data, you can change the calculation by right clicking a value and choosing Summarize Data By and then selecting the calculation which includes Sum, Average, Max, Min and Count in addition to some statistical calculations.
In other circumstances you may want to stay with summing the data but you may be interested in the percentage that each summed value represents of the total value. You can display percentages instead of summed values by right clicking a value and choosing the Value Field Settings > Show Values As tab and then selecting an option from the Show Values As dropdown list such as % of Row or % of Column.
This feature allows you analyze your data in ways that would be much more difficult to do if you were dealing with the raw data and to do so without needing to write any formulas.
Pivot tables and pivot charts are a valuable tool for summarizing vast quantities of data. With each version of Excel they become easier to use and Excel 2007 is no exception.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. She blogs at http://www.projectwoman.com/blogger.html.
Be sure to check out all of Helen's articles in the Exploring Office 2007 series:
