Analyzing Data with PivotTables

1
Analyzing Data with PivotTables

• Applications of Spreadsheets

2
PivotTables and PivotCharts

• A PivotTable is an interactive table that
  summarizes a data source such as a list or
  another table.
• You can also create a PivotChart that plots the
  data in a PivotTable.
• Before you begin to create PivotTables and
  PivotCharts, you can use Excels onscreen Help to
  get an overview of analyzing data interactively.

3
Working with PivotTables and PivotCharts
4
Creating a PivotTable Report
Open EX0940.xls
5
Creating a PivotTable

• The PivotTable and PivotChart Wizard guides you
  through the steps to make a custom report from a
  list of data.

6
Creating a PivotTable

• The initial steps include
• Specifying the data source
• Specifying the type of report
• PivotTable
• PivotChart
• Specifying the location of the report
• a new worksheet
• the existing worksheet

7
Creating a PivotTable

• To complete the design of the PivotTable, specify
  whether you want to
• use a Layout dialog box
• Make selections directly on the worksheet
• You can easily set up a PivotTable directly on
  the worksheet by dragging the names of fields
  listed in the PivotTable toolbar to the
  appropriate areas of a PivotTable shell.
• Allows you to view the data while you arrange the
  fields

8
Creating a PivotTable Report PivotTable and
PivotChart Wizard Step 1 of 3
9
Creating a PivotTable Report PivotTable and
PivotChart Wizard Step 3 of 3
10
Creating a PivotTable Report
PivotTable toolbar
The PivotTable Field List window displays the
field columns from a worksheet list
11
Expanding a PivotTable

• You can greatly expand a PivotTable by
• dragging more fields to a PivotTable on the
  worksheet
• dragging more fields to the row, column and data
  areas of the Layout dialog box
• using the PivotTable and PivotChart Wizards
  Layout dialog box.

12
Adding Field Items to the PivotTable Area
Field items already added to the PivotTable area
appear in boldface
13
Changing the Order of Summary Data

• The order in which the field buttons appear in
  the Row, Column, and Data boxes of the Layout
  Dialog box determines the order in which data
  displays in the PivotTable.
• To change the order
• Open the Layout dialog box
• Drag a field button to the desired location.

14
Customizing a PivotTable Report
The Gender field button was dragged from the
column field area to the page field area in the
PivotTable report
Education field button
15
Pivoting a PivotTable Report
Education is now a row field button as opposed to
a column field button
16
Removing, Hiding, and Showing Data

• As your information needs change, you may want to
  display more or less summary data in a
  PivotTable.
• The process for removing a field is opposite that
  of adding a field.
• Drag the field away from the row, column, or Data
  area in the Layout dialog box.
• Showing or hiding detail in a field is as easy as
  pulling down a list of items in a field on the
  PivotTable and checking or un-checking the field.

17
Filtering Results in a PivotTable Report
Each field button contains a list of unique
values that you may use to limit the data
displayed in a PivotTable report
18
PivotTable Field Settings Dialog Box
Select the type of calculation to perform in the
data area
19
Pivoting the PivotTable Report
The number formatting remains intact even after
pivoting the PivotTable report
20
Applying Predefined Formats to a PivotTable

• A variety of predefined formats can be applied to
  PivotTables.
• These formats not only improve the aesthetics of
  a table, they focus the readers attention on
  different areas of the table.

21
Predefined Formats for a PivotTable

• PivotTable Classic the default format
  automatically assigned to new PivotTables.
• Ten different table formats, also known as
  nonindented formats.
• Ten different report formats, also known as
  indented formats.
• Some formats work better than others, depending
  on the layout and complexity of the PivotTable.

22
Removing a Predefined Format

• To remove a predefined format (and any other
  character and cell formats applied manually
• Display the gallery of predefined formats
• Choose None
• You may not get the results you want because
  Excel simply removes effects such as borders,
  shading, italics and changes in font size.

23
Removing a Predefined Format

• Because cell contents remain in place, the action
  does not
• move any fields
• change display to indented or nonindented format
• remove blank lines inserted between items in
  rows.
• You can also reproduce the original layout of a
  PivotTable by saving the workbook just before you
  apply a predefined format.
• Then, if you use the None option and it doesnt
  work well, you can close the workbook without
  saving your changes.

24
AutoFormat Dialog Box for a PivotTable Report
An indented Report AutoFormat style
An non-indented Table AutoFormat style
25
Displaying a PivotTable Report using the Report 6
AutoFormat
26
Previewing Printing a PivotTable Report
27
PivotTable Options Dialog Box
These two check boxes affect how the PivotTable
report appears when printed
28
Changing the PivotTable Reports AutoFormat Style
29
Refreshing a PivotTable

• If you change data in a worksheet and that data
  impacts a summary calculation in a PivotTable,
  Excel does not automatically update the
  PivotTable.
• After making changes to the worksheet, you must
  refresh (recalculate) the PivotTable.

30
Refreshing a PivotTable

• To refresh a PivotTable
• Use the Refresh Data option on the shortcut menu
  that displays when you right-click within a
  PivotTable.
• Choose the menu sequence Data, Refresh Data
• Click the Refresh Data button on the PivotTable
  toolbar.

31
Refreshing a PivotTable

• Because you are so used to Excel recalculating a
  worksheet automatically, it is easy to overlook
  refreshing any PivotTables that incorporate the
  changed data.
• In complex PivotTables, errors in summary amounts
  are difficult to detect visually.
• It is important to acquire the habit of
  refreshing PivotTables after any change in
  worksheet data.

32
Deleting a PivotTable

• Before you can delete a PivotTable, you must
  select it using a three-step process
• Right-click within the PivotTable
• Choose Select from the shortcut menu
• Click Entire Table.
• After selecting the table, choose Edit, Clear,
  All.
• When you delete a PivotTable, the source data is
  not affected.

33
Creating a PivotChart from PivotTable Data

• A PivotTable provides informative summary data in
  rows and columns.
• Creating a chart based on PivotTable data can be
  an effective means to interpret that data.

34
Creating a PivotChart

• You can create a PivotChart by
• right-clicking within an existing PivotTable, and
  then selecting PivotChart from the shortcut menu.
  
• using the PivotTable and PivotChart Wizard to
  create both a chart and its related table at the
  same time.
• For either method, Excel automatically creates a
  column chart on a separate sheet.
• You can then edit the PivotChart report just as
  you would any Excel Chart.
• You can use any chart type except xy (scatter),
  bubble, and stock.

35
More on PivotTable Data and Charts

• If a chart is based on PivotTable data, such a
  chart is updated only when its associated
  PivotTable is refreshed.
• If you delete a charts related PivotTable, that
  chart can no longer be modified.
• You can create a static chart (nonpivoting chart)
  based on PivotTable data.
• You must first copy the PivotTable to another
  area and use Paste Special as Values to make the
  PivotTable data static.

36
Creating a PivotChart Report
37
Customizing a PivotChart Report
38
Displaying PivotTables on the Web

• Using Excel 2003 you can make a PivotTable
  accessible to others through the Internet or an
  intranet site.
• If you select Excels Add Interactivity option,
  users of the web page can change layout and data
  fields in the PivotTable.

39
Displaying PivotTables on the Web

• Excels Add Interactivity option is only
  available if users have Office Web Components
  installed and are using version 4.01 or later of
  Internet Explorer.
• If interactivity is not an option, you can still
  save the report in HTML format and publish it as
  a noninteractive table can be viewed but not
  changed.

40
Displaying PivotTables on the Web

• An interactive PivotTable on a web page is called
  a PivotTable list.
• This list does not have worksheet row and column
  labels, but other features are similar to those
  available for PivotTable reports in Excel.
• Onscreen Help provides extensive information
  about feature differences between PivotTable
  lists on Web sites and PivotTable reports in
  Excel.

41
Creating a Web-Based PivotTable
Click this button to customize the publishing
options for the Web page
Enter a name for the Web page
42
Publish as Web Page Dialog Box
When the Add Interactivity with check box is
selected, you can choose which component to use
Select this check box to immediately display the
result in your Web browser software
43
Viewing a PivotTable in a Web Browser
44
Interactive PivotTables

• If you check the Add interactivity with box on
  the Publish as Web Page dialog box, you can
  choose between these two functionalities
• Spreadsheet functionality allows change of data
  and formulas
• PivotTable functionality allows change in
  number and location of fields.

45
Interactive PivotTables

• If PivotTable functionality is active, you can
• select or deselect field items in a PivotTable on
  a Web page by using drop-down lists
• Drag and drop fields
• Expand or collapse the level of detail provided.