Pivot Table Toolbar - Pivot Tables

Pivot Table Menu Button Format Report

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 29 Pivot Tables are extremely flexible. After placing the initial fields to be viewed in the Pivot Table, they can be resituated to another position in that Pivot Table or removed from the Pivot Table.

Additional fields can also be added to a Row, Column, Data, or Page field area.

ReArranging Pivot Table Fields

Steps:

Re-Arranging Pivot Table Fields

⇒ Point over the Pivot Table field heading

⇒ Drag field to another field area in that Pivot Table (i.e. Row to Column area)

Removing Pivot Table Fields

⇒ Point over the Pivot Table field heading

⇒ Drag field back out of the Pivot Table area

Adding Additional Pivot Table Fields

⇒ Click on the Pivot Table toolbar Show Field List button (if necessary)

⇒ Select field and drag into the preferred Pivot Table area (i.e. Row to Column area)

Moving Pivot Table Fields - Before

Moving Pivot Table Fields - After

C H A P T E R

By default, Pivot Tables do not update as data changes in the underlying list. If you want to make sure the Pivot Table displays the latest data from the source flat-file list, manually update the Pivot Table.

Updating Pivot Tables

Steps:

⇒ Click into the Pivot Table

⇒ Click the Refresh button on the Pivot Table toolbar

Manually Update a Pivot Table

New data added to the bottom of the list will not be included in the Pivot Table. To avoid having to reset the original cell range the Pivot Table is based on, insert a row within the original cell range to add the new record(s).

You can then sort the list to re-order the data.

Great Tip!

2

Creating Pivot Tables

Note: Pivot Table toolbar is only visible when the user has activated the Pivot Table by clicking into it. If the toolbar is still not visible after clicking into the table it can be opened using the View menu:

Toolbars command.

Not Included In Pivot Table

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 31 Pivot Table Options Button

The Pivot Table Options dialog box allows the user to set controls on the Pivot Table. These include setting refresh options, turning off Grand Totals, Preserving formatting, and how to display empty cells and error values.

C H A P T E R

By default, Data Fields summarize their data using the Sum function. Excel allows Data Fields to be summarized with a group of other functions such as Average, Count, Min, Max, and StdDev.

The Field Settings dialog box contains the function options. In addition to the function, this dialog box allows the user to rename the field, format field values, and change the relationship of summarized data to the other data in that field.

Changing The Functionality of A Data Field

Steps:

⇒ Click a value in the data field

⇒ Click the Pivot Table button on the Pivot Table toolbar

⇒ Click Field Settings command

⇒ Select a different function, if necessary

⇒ Click into the Name box and rename data field, if necessary

⇒ Click on the Number button and format field, if necessary

⇒ Click OK

Field Settings Dialog Box

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 33 Changing The Functionality of A Data Field

Field Settings Menu Command

Changing The Functionality Of The Data Field

C H A P T E R

Changing The Relationship Of Summarized Data

As mentioned previously, the Field Settings dialog box contains the function options. In addition to the function, this dialog box allows the user to change the relationship of summarized data to the other data in that field. By default, the values in the Data Field display as they are. Specifically, a value of 100 displays as 100, independent of any other values.

By clicking the Options button in the Field Settings box and changing the Show Data As option, the data can be viewed as it relates to other values. For example, the value can be displayed as the Difference From a selected value in that Data Field. Other options include showing data as a percentage to the row field or the column field or total.

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 35 Changing The Relationship Of Summarized Data

Pivot Table With Data Field Displaying Percentage of Row Item

(See next 2 pages for breakdown of data field options)

Note: Zero Values are hidden in this example. The command to hide zero values is the Tools menu: Options command. On the View tab, uncheck Zero Values option.

Great Tip!

C H A P T E R

Changing The Relationship Of Summarized Data

Function Result

Difference From Displays all the data in the data area as the difference from the value for the specified Base field and Base item. The base field and base item provide the data used in the

custom calculation.

% Of Displays all the data in the data area as a percentage of the value for the specified Base field and Base item. The base field and base item provide the data used in the

custom calculation.

% Difference

From Displays all the data in the data area as the difference from the value for the specified Base field and Base item, but displays the difference as a percentage of the base data.

The base field and base item provide the data used in the custom calculation.

Running Total In Displays the data for successive items as a running total. You must select the field for which you want to show the items in a running total.

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 37 Changing The Relationship Of Summarized Data

% of row In a Pivot Table report, displays the data in each row as a percentage of the total for each row. In a Pivot Chart report, displays the data as a percentage of the total for the category.

% of column In a Pivot Table report, displays all the data in each column as a percentage of the total for each column. In a Pivot Chart report, displays the data as a percentage of the total for the series.

% of total In a Pivot Table report, displays the data in the data area as a percentage of the grand total of all the data in the report. In a Pivot Chart report, displays the data as a

percentage of the total of all data points.

Index Displays the data by using the following calculation:

((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))

Function Result

C H A P T E R

The Pivot Table displays all items in the field that is placed in the table. The data in the Pivot Table can be filtered to display only the required items in that field. Data can be filtered by hiding items in a row or column field. In addition, data can be filtered by placing a field in the Page Field area and selecting an item in that field to display. All other items in that Page Field will be hidden.

Filtering Data In Pivot Tables

Filtering Data In A Row/Column Field Steps:

Filtering Data In A Row/Column Field

⇒ Click on the list button on the field name

⇒ Uncheck any field to be hidden

⇒ Click OK

Redisplaying Data In A Row/Column Field

⇒ Click on the list button on the field name

⇒ Check (Show All)

⇒ Click OK

Filtering Data In A Page Field

⇒ If necessary, drag a field from the field list to the Page Field area

⇒ Click the list button on the field name

⇒ Select the item to be displayed

⇒ The Pivot Table will now display only those records from that item

Redisplaying Data In A Page Field

⇒ Click the list button on the field name in the Page Field area

⇒ Check (All)

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 39 Filtering Data In Pivot Tables

Filtered Data - Before

Filtered Data - After

C H A P T E R

Filtering Data In Pivot Tables With Page Fields

Page fields allow you to filter the entire Pivot Table report to display data for a single item or all the items. More than one field can be displayed as a page field.

Steps:

To Add a Page Field

⇒ Drag the field from the field list to the Page Field Area of the Pivot Table

To Filter a Pivot Table with a Page Field

⇒ Open the Filter button in the Page Field

⇒ Select an entry to act as criteria

2

Creating Pivot Tables

Page Field List

Page Field Page Field List Button

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 41 Filtering Data In Pivot Tables With Page Fields

Page Field Filtered Data - Before

Page Field Filtered Data - After

C H A P T E R

Sorting Data In Pivot Tables

Data in a Pivot Table displays in the order that data appears in the source flat-file list. However, data can be sorted automatically or manually at any time after the Pivot Table is created.

Steps:

To Automatically Sort Data In A Row/Column Field

⇒ Click on an item in the required row or column field

⇒ Click Sort Ascending or Sort Descending buttons

To Manually Sort Data In A Row/Column Field

⇒ Click on an item in the required row or column field

⇒ Drag to the required position

⇒ Repeat for each item as necessary

Sorting Data Before

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 43 Sorting Data In Pivot Tables

Sorting Data After

C H A P T E R

Grouping Data In Pivot Tables

Items in a Row or Column field can be grouped in order to view and analyze data in a higher level summary format. Groups of data can be collapsed to view the data as a set of data not available from the source flat-file list.

Steps:

To Group Selected Items In A Row/Column Field

⇒ If necessary, sort the items in the field in the preferred order

⇒ Select the items needed to create the first group

⇒ Click the Pivot Table button on the Pivot Table toolbar

⇒ Click Group And Show Detail, Group command

⇒ Repeat the above 3 steps as needed

To UnGroup Selected Items In A Row/Column Field

⇒ Select the items needed to un-group

⇒ Click the Pivot Table button on the Pivot Table toolbar

⇒ Click Group And Show Detail, UnGroup command

⇒ Repeat the above 3 steps as needed

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 45 Grouping Data In Pivot Tables

Grouping Data Before

Grouping Data After

C H A P T E R

Renaming Groups In Pivot Tables

The names of the groups can be customized to reflect the data. In addition, the label for the group field can be customized.

Steps:

To Rename Groups In A Row/Column Field

⇒ Click on the name of the group (i.e. Group1)

⇒ Type a new name

To Rename The Group Field In A Row/Column Field

⇒ Click on the name of the group label (i.e. Line No2)

⇒ Type a new name

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 47

Naming Groups

Renaming Groups In Pivot Tables

C H A P T E R

Drilling Down On Data In Pivot Tables

Groups of data can be collapsed to show just the totals for that group and then expanded to display the detail data again.

Steps:

To Drill Down In A Row/Column Field

⇒ Double-Click on the name of the group (i.e. Northern) -- OR --

⇒ Click on the name of the group

⇒ Click the Hide Detail button on the Pivot Table toolbar

⇒ The group data will collapse to show summary data for group

To Expand Data In A Row/Column Field

⇒ Double-Click on the name of the group (i.e. 300 Series) -- OR --

⇒ Click on the name of the group

⇒ Click the Show Detail button on the Pivot Table toolbar

⇒ The group data will expand to show detail for group

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 49 Drilling Down On Data In Pivot Tables

C H A P T E R

Breaking Down Data Fields

Pivot Tables summarize data in the Data Field. A value in the Data Field can represent hundreds of records in the underlying data list.

You can view the detail of the summarized data in the Data Field by double-clicking a value. Excel will create a new worksheet with a list of the records that make up that summarized value.

Steps:

⇒ Click into the Pivot Table

⇒ Double-Click on a Data Field

(A new worksheet will appear with the detail records that make up that data field value)

To Build Reports Based On Data Fields

Double-Click

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 51 Breaking Down Data Fields

Build Reports Based On Data Fields - Before

Build Reports Based On Data Fields - After

C H A P T E R

Note: Choose the

preferred page field (there could be several) and click OK.

Building Pivot Tables Based From Page Fields

You can build new Pivot Table reports based on Page Fields. These new reports create new worksheets containing Pivot Tables displaying data from each of the items in that Page Field.

Steps:

⇒ Click into the Pivot Table

⇒ Click the Pivot Table toolbar button

⇒ Select Show Pages

⇒ Click OK

To Build Reports Based On Page Fields

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 53 Building Pivot Tables Based From Page Fields

New Pivot Table As A Result Of Show Pages Command

C H A P T E R

Charting Pivot Tables

Pivot Tables can be charted at the same time as they are created or any time after. The default chart type is a Stacked Column. This is a very efficient way to display the chart as many times the data in a Pivot Table is not consistent (there might not be any). There may be many values in the Data Field. Typical column or line charts do not display large amounts of data well.

The chart is linked to the Pivot Table. Pivot charts contain row, column, data, and page field areas just as in the table.

Any changes to fields in the Pivot Table effect the chart. As well, any changes to the fields in the chart effect the Pivot Table. Pivot Table charts can be formatted just as any chart created in Excel.

That includes chart type, chart options, formatting series, legends, and data labels.

Steps:

⇒ Click into the Pivot Table

⇒ Click the Pivot Table button on the toolbar

⇒ Select Pivot Chart command

⇒ Edit the chart as necessary

To Chart Pivot Tables

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 55 Charting Pivot Tables

C H A P T E R

AutoFormat Pivot Tables

Pivot Tables can be formatted just like data in any worksheet.

Font, number, shading, and border formatting can be added to areas of the Pivot Table. The Pivot Table Options dialog box contains an option to Preserve formatting that will retain user-added formatting when the table data is refreshed.

In addition, formatting can be added automatically using the Format Report command in the Pivot Table toolbar button. The formatting is organized by Report-types and Table-types of formatting. It is important to note that the Report-type formatting will change the orientation of the Pivot Table. They move the column fields into the row field area creating a vertical orientation.

Table-type formatting will retain the vertical/horizontal orientation of the existing Pivot Table.

Steps:

⇒ Click into the Pivot Table

⇒ Click the Pivot Table button on the toolbar

⇒ Select Format Report

⇒ Choose a layout from the Report-types or the Table-types

⇒ Click OK

To AutoFormat Pivot Tables

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 57 AutoFormat Pivot Tables

AutoFormatting With Report-type Formats

Pivot Table Vertical Orientation With Report Format

C H A P T E R

AutoFormat Pivot Tables

AutoFormatting With Table-type Formats

Pivot Table Horizontal/Vertical Orientation With Table Format

2

Creating Pivot Tables

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 59

Notes

C H A P T E R

Each time you create a new Pivot Table or Pivot Chart report, Microsoft Excel stores a copy of the data for the report in memory and saves this storage area as part of the workbook file. Thus each new report requires additional memory and disk space. However, when you use an existing Pivot Table report as the source for a new report in the same workbook, both reports share the same copy of the data. Because you reuse the same storage area, the size of the workbook file is reduced and less data is kept in memory.

Location requirements To use a Pivot Table report as the source for another report, both reports must be in the same workbook. If the source Pivot Table report is in a different workbook, copy the source report to the workbook where you want the new report to appear. Pivot Table and Pivot Chart reports in different workbooks are separate, each with their own copy of the data in memory and in the workbook files.

Page field settings The source Pivot Table report cannot contain any page fields that are set to query for external data as you select each item. Reports with this setting don't appear in step 2 of the wizard. To check the setting, double-click each page field, click Advanced, and make sure Retrieve external data for all page field items is selected.

Creating Multiple Pivot Tables From Same Source

3

Additional Pivot Table Information

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs 61 Changes affect both reports When you refresh the data in the new report, Excel also updates the data in the source report, and vice versa. When you group or ungroup items in one report, both are affected. When you create calculated fields or calculated items in one report, both reports are affected.

Pivot Chart reports You can base a new Pivot Table or Pivot Chart report on another Pivot Table report, but not directly on another Pivot Chart report. However, Excel creates an associated Pivot Table report from the same data whenever you create a Pivot Chart report, so you can base a new report on the associated report.

Changes to a Pivot Chart report affect the associated Pivot Table report, and vice versa. If you want to be able to change the layout or display different data without these changes affecting both reports, create a new Pivot Table report based on the same source data as the Pivot Chart report rather than basing it on the associated Pivot Table report.

In document Pivot Tables (Page 32-66)