Docstoc

Pivot Table

Document Sample
Pivot Table Powered By Docstoc
					V O L U M E      3

Microsoft Excel
 Pivot Tables
     For




              DASH DESIGNS
              PC Training and Consulting Services
Microsoft Excel Pivot Table Topics For The Haas School, UC Berkeley — Dash Designs Training
          Microsoft Excel Pivot Tables
                      For
         The Haas School of Business,
            University of California

                  Revised: January 29, 2006



                        Copyrights and Trademarks



            © 2006, Dash Designs, Jerry Maletsky
                    San Rafael, CA 94903
     email: jdashm@comcast.net • fax (415) 491-1490




Dash Designs gives permission to the Haas School of Business of
the University of California at Berkeley to reprint this training
manual for internal use only. No re-sale of this material or
renunciation of copyrights are granted by this author.



 Any mention or use of Microsoft®, University of California, or any
 third party products is hereby acknowledged by Dash Designs to
 be for the sole purpose of editorial and educational use of this
 training manual and for the benefit of the mentioned parties.



 Microsoft Excel Pivot Table Topics For The Haas School, UC Berkeley — Dash Designs Training
                Table of Contents
      Chapter 1: Working With Data ........ 2
      Reference Workbook: Data Analysis.xls
                            Sorting_Filtering Worksheet
                            Sub-Totals Worksheet
                            Auto Outline Worksheet
                            Group Manually Worksheet

          ♦     Overview of Excel Lists ...................... 2-3
          ♦     Sorting Data .................................... 4-7
          ♦     Filtering Data ..................................8-13
          ♦     Grouping Data............................... 14-17
          ♦     SubTotaling Data ........................... 18-19

      Chapter 2:Creating Pivot Tables .... 20
      Reference Workbook: Data Analysis.xls
                            Source Data Worksheet


          ♦     Creating Pivot Tables ..................... 20-28
          ♦     Re-Arranging Pivot Fields .....................29
          ♦     Updating Pivot Table Data............... 30-31
          ♦     Changing Data Field Functionality .... 32-33
          ♦     Changing Relationship Of Data ....... 34-37
          ♦     Filtering Pivot Table Data ................ 38-41
          ♦     Sorting Pivot Table Data ................. 42-43
          ♦     Grouping Pivot Table Data .............. 44-47
          ♦     Drilling Down Into Data .................. 48-53
          ♦     Charting Pivot Table Data ............... 54-55
          ♦     Auto Formatting Pivot Tables........... 56-60

      Chapter 3: More Pivot Info ...... 61-75
Microsoft Excel Intermediate Topics For The Haas School, UC Berkeley — Dash Designs Training
                                                                                                     CHAPTER
                                                                                                 1

       Microsoft
    Excel Pivot Table
      Training For




Jerry Maletsky
Dash Designs
Training And Consulting


Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   1
                                                                                                 Working With Data
 CHAPTER

           1          Reference Workbook: Data Analysis.xls
                                          Sorting_Filtering Worksheet

           Overview of Excel Lists
           An Excel list (also known as a database or flat-file) is organized into
           columns and rows just like a spreadsheet. A Spreadsheet consists
           of labels on the top row(s) and labels in the first column with
           numerical values in the intersection of each label. A list in Excel
           consists of a contiguous range of cells (no blank rows or
           columns) in which only the top row of the data contain the
           labels describing the information in the column below
           (fields). Every other row is considered a record of information
           describing that item.

           In Excel (and similar spreadsheet programs), they are called flat-
           files. Relational databases (i.e. Microsoft Access) allow the data to
           be organized into separate smaller lists (tables) and then, when
           required, allow the user to combine that data into one report. Flat-
           file programs require all the data to be in one contiguous range of
           cells on the same worksheet for the user to be able to work with
           that data together.

           Example of a Spreadsheet




Labels




           2    Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs
 Working With Data




                                                                                                              CHAPTER
                                                                                                  1
 Working With Flat-Files


 Example of a Flat-File List




                                   Labels (Only in the first row)




                                                                                                       Header Row/
                                                                                                       Field Names

                                                                                                       Record




                                                                                                      Field


Great Tip!         It is very important that when adding new records that they
                   be added in the next available row (i.e., A23) or in an
                   inserted row somewhere between the first and last record.
                   Otherwise, new data will not be considered part of the list.




 Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   3
                                                                                                Working With Data
CHAPTER

          1          Reference Workbook: Data Analysis.xls
                                         Sorting_Filtering Worksheet

          Sorting Data
          Sorting data is the process of rearranging data in a list or spreadsheet in
          particular order. A list is considered a contiguous range of cells in which
          the top row are the column headings (field names) and every other row in
          the range is considered a record. The list is separated from other data in
          the worksheet by at least one blank row or column.

          Data can be sorted one column at a time or up to three columns within
          one sort.

          Data in a list can be sorted in ascending or descending order. In an
          ascending sort, Excel uses the following order. (In a descending sort, this
          sort order is reversed except for blank cells, which are always placed
          last.)

          Numbers              Numbers are sorted from the smallest negative number
                               to the largest positive number.

          Dates                Dates are sorted from the earliest date to the latest date.

          Alphanumeric When you sort alphanumeric text, Excel sorts left to right,
                       character by character. For example, if a cell contains the
                       text "A100," Excel places the cell after a cell that contains
                       the entry "A1" and before a cell that contains the entry
                       "A11."

          Text and text with numbers are sorted in the following order:

          0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | }
          ~+<=>ABCDEFGHIJKLMNOPQRSTUVWXYZ

          Apostrophes (') and hyphens (-) are ignored, with one exception: If two
          text strings are the same except for a hyphen, the text with the hyphen is
          sorted last.

          Logical values          In logical values, FALSE is placed before TRUE.
          Error values             All error values are equal.
          Blanks                   Blanks are always placed last.



          4    Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs
Working With Data




                                                                                                         CHAPTER
                                                                                                 1
Sorting Data

                                Sorting on one column

Steps:
⇒ Click into a cell within the column the sort will be based
⇒ Click the Sort Ascending                               button or Descending
    button




                                                                                                  Sorting
                                                                                                 Ascending




                                                                                                   Sorting
                                                                                                 Descending




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   5
                                                                                                 Working With Data
CHAPTER

          1
          Sorting Data on Multiple Columns
          Excel allows any one sort to include up to 3 fields at a time. The
          sorting toolbar buttons cannot sort on more than 1 field at a time.
          However, the Data menu: Sort command can sort data up to 3
          fields.

          If more than 3 fields are necessary to sort, Sort the fields in groups
          of 3, using the least important fields first. Then sort the most
          important field last.

          For example, suppose a list containing First Name, Last Name,
          Department, and Region information needed to be sorted so that all
          the departments were organized by name and then region. The
          user would sort by Last Name, First Name, and then Region using
          the Data menu: Sort command. Lastly the user would sort by
          Department using the Sort Ascending or Sort Descending toolbar
          button.




          Steps:
          ⇒ Click anywhere in the contiguous cell range of the list
          ⇒ Click Data menu: Sort command
          ⇒ In the Sort By box, select the primary field to sort and
              whether ascending or descending
          ⇒ In the Then By box, select the second field to sort and
              whether ascending or descending
          ⇒ In the next Then By box, select the third field to sort
              and whether ascending or descending
          ⇒ Click OK




          6     Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs
Working With Data




                                                                                                     CHAPTER
                                                                                                 1
Sorting Data on Multiple Columns




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   7
                                                                                                Working With Data
CHAPTER

          1          Reference Workbook: Data Analysis.xls
                                         Sorting_Filtering Worksheet

          Filtering Data
          Filtering data provides a way to display only the preferred data
          while hiding the remainder of that data list. For example, in a list of
          hundreds of product sales containing product names, regional
          locations, dates, dollar amounts, etc., a user can just display only
          the records for a particular product or region.

          Autofilter is a process that allows the user to choose from a list of
          items in that column and hide all data except the chosen option.
          When activated, AutoFilter places drop-down lists at the head of
          each column. The user simply opens the list of the preferred data
          column and selects the item. In addition, a user can specify a
          range of values such as the Top 10 or Bottom 5% of records
          using the (Top 10…) option in AutoFilter. There is also a
          (Custom…) option in which a user can set a range of values (i.e. a
          field containing values from 0 to 100,000) between 5,000 and
          10,000.
          Steps:
                                     TO   ACTIVATE        AUTOFILTER
          ⇒ Click in any cell within the list
          ⇒ Click Data menu: Filter: AutoFilter command
          ⇒ Click a list button in a column heading
          ⇒ Select the preferred item on which to filter
                               TO   RETURN ALL RECORDS TO VIEW

          ⇒ Click that same list (blue) button and select (All)
                                     TO   TURN OFF        AUTOFILTER
          ⇒ Click Data menu: Filter: AutoFilter command




          8    Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs
Working With Data




                                                                                                     CHAPTER
                                                                                                 1
Filtering Data




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   9
                                                                                                  Working With Data
CHAPTER

          1
           Filtering Data Using The Top 10 Option
           Using the AutoFilter’s Top 10 feature allows the user to display a
           range of records in which its values are at the top or bottom range
           of that field. For example, a user could filter for only the records
           that contain the top 15% of sales.

           Steps:
           ⇒ Click in any cell within the list
           ⇒ Click Data menu: Filter: AutoFilter command
           ⇒ Click a list button in a column heading
           ⇒ Select the (Top 10…) option
           ⇒ In the dialog box, choose the following options:
               in the left-side box, select Top or Bottom
               In the center box, enter a value
               In the right-side box, select Percent or Items
           ⇒ Click OK
                                 TO   RETURN ALL RECORDS TO VIEW

           ⇒ Click that same list (blue) button and select (All)




          10     Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs
Working With Data




                                                                                                      CHAPTER
                                                                                                 1
Filtering Data



Records Filtered on top Total Sales field




            Note: when percent is selected the number of records that
            will be displayed will be equal to that percentage of the
            total records.




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   11
                                                                                                  Working With Data
CHAPTER

          1
           Filtering Data Using The Custom Criteria
           Using the AutoFilter’s Custom feature allows the user to display
           records in which its values fall into a range of values for that field.
           For example, a user could filter for only the records that contain the
           quantities sold between 25 and 50.

           Steps:
           ⇒ Click in any cell within the list
           ⇒ Click Data menu: Filter: AutoFilter command
           ⇒ Click a list button in a column heading
           ⇒ Select the (Custom…) option
           ⇒ In the dialog box, choose the following options:
               in the top left-side box, select a comparison operator
               In the top right-side box, type or select a value in that field
               In the bottom left-side box, select a comparison
               operator (if required)
               In the bottom left-side box, type or select a value in that
               field
           ⇒ Click OK
                                 TO   RETURN ALL RECORDS TO VIEW

           ⇒ Click that same list (blue) button and select (All)




          12     Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs
Working With Data




                                                                                                      CHAPTER
                                                                                                 1
Filtering Data Using The Custom Criteria


Records Filtered on top Total Sales field




Records Filtered on custom criteria




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   13
                                                                                                 Working With Data
CHAPTER

          1           Reference Workbook: Data Analysis.xls
                                          Auto Outline Worksheet

           Grouping Data
           Grouping data in a worksheet allows you to hide or display the
           detail but leave the calculated areas of your worksheet visible. This
           is particularly useful in a large itemized worksheet. For example, in
           a worksheet displaying monthly sales figures with quarterly
           subtotals, a user can hide the monthly detail and just display the
           quarterly subtotals along with the grand total.

           Data can be grouped automatically or manually.

           Grouping Data Automatically
           Steps:
           ⇒ Click anywhere in the worksheet
           ⇒ Click Data menu: Group and Outline: Auto Outline



           Un-Grouping Data Automatically
           Steps:
           ⇒ Click anywhere in the worksheet
           ⇒ Click Data menu: Group and Outline: Clear Outline




          14    Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs
Working With Data




                                                                                                      CHAPTER
                                                                                                 1
Grouping Data



Worksheet with AutoOutline Activated




Worksheet with AutoOutline Collapsed




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   15
                                                                                                Working With Data
CHAPTER

          1          Reference Workbook: Data Analysis.xls
                                         Group Manually Worksheet

           Grouping Data


           Grouping Data Manually
           Steps:
           ⇒ Select the Row or Columns to group
           ⇒ Click Data menu: Group and Outline: Group



           Un-Grouping Data Manually
           Steps:
           ⇒ Select the Row or Columns to group
           ⇒ Click Data menu: Group and Outline: UnGroup




          16   Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs
Working With Data




                                                                                                      CHAPTER
                                                                                                 1
Grouping Data
Worksheet with Columns Grouped




Worksheet with Column Group Collapsed




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   17
                                                                                                  Working With Data
CHAPTER

          1            Reference Workbook: Data Analysis.xls
                                           Subtotals Worksheet

           Subtotaling Data
           Excel provides a method to subtotal your data in a list automatically
           without interrupting the “contiguous” flow of the records in the list.
           The Subtotal command groups your data based on the field you
           choose and calculates a subtotal based on the function you choose
           (i.e. Sum Average, Count, etc.) and the field you choose to
           calculate.

Great Tip! IMPORTANT: THE LIST MUST BE SORTED BY THE FIELD YOU WILL BE
           GROUPING ON PRIOR TO ACTIVATING THE SUBTOTAL COMMAND.



           Subtotaling Data
           Steps:
           ⇒ Click into the list
           ⇒ Click Data menu: Subtotals
           ⇒ Set the field you want to group on in the
               “At Each Change In” box
           ⇒ Set the function you want to
               use to calculate in the
               “Use Function” box
           ⇒ Check the field(s) you want
               to calculate in the
               “Add Subtotal to” box
           ⇒ Click OK




           Note:
           To remove the Subtotals, click
           the Remove All button




          18     Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs
Working With Data




                                                                                                      CHAPTER
                                                                                                 1
Subtotaling Data



Worksheet With Data: Subtotals Activated

         Note:
         Excel provides Grouping buttons
         to hide or display levels of data




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   19
CHAPTER
          2       Creating Pivot Tables
                           Reference Workbook: Data Analysis.xls
                                               Source Data Worksheet

           Creating Pivot Tables




           A Pivot Table report is an interactive table that quickly combines
           and compares large amounts of data. You can rotate its rows and
           columns to see different summaries of the source data and you can
           display the details for areas of interest.

           Use a Pivot Table report when you want to analyze related totals,
           especially when you have a long list of figures to sum and you want
           to compare several facts about each figure. In the report illustrated
           above, you can easily see how the third-quarter golf sales in cell F3
           stack up against sales for another sport or quarter, or the total
           sales. Because a Pivot Table report is interactive, you can change
           the view of the data to see more details or calculate different
           summaries, such as counts or averages.




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



                                                                                                 2




                                                                                                      CHAPTER
Creating Pivot Tables

There are Four (4) types of fields in a Pivot Table:

                   ♦    Row
                   ♦    Column
                   ♦    Data
                   ♦    Page
In a Pivot Table report, each column or field in your source data
becomes a Pivot Table field that summarizes multiple rows of
information. In the example on the previous page, the Sport
column becomes the Sport field and each record for Golf is
summarized in a single Golf item.

A Row field is designated as the labels for each row. A Column
field is designated as the labels for each column. In choosing
which field(s) will be designated as Row or Column remember that
Excel is limited to 256 columns across the worksheet which may
influence the decision.

A Data field, such as Sum of Sales, provides the values to be
summarized. Cell F3 in the report above contains the sum of the
Sales value from every row in the source data for which the Sport
column contains Golf and the Quarter column contains Qtr3.

A Page field is designated if the user wants to filter the Pivot Table
for a particular item in that Page field.

To create a Pivot Table report, you run the Pivot Table and Pivot
Chart Wizard. In the wizard, you select the source data you want
from your worksheet list or external database. The wizard then
provides you with a worksheet area for the report and a list of the
available fields. As you drag the fields from the list window to the
outlined areas, Microsoft Excel summarizes and calculates the
report for you automatically.

After you create a Pivot Table report, you can customize it to focus
on the information you want: change the layout, change the
format, or drill down to display more detailed data.

Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   21
                                                                                              Creating Pivot Tables
CHAPTER
          2
           Creating Pivot Tables




           Excel Data List




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




                                                                                                      CHAPTER
                                                                                                 2
Creating Pivot Tables




Pivot Table Summarizing Data From Excel List




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   23
                                                                                               Creating Pivot Tables
CHAPTER
          2
           Creating Pivot Tables


           Create a Pivot Table (3-Step Wizard process)

           Steps:
           ⇒ Click anywhere in the contiguous area that makes up your list
           ⇒ Click Data menu: Pivot Table and Pivot Chart Report command
           ⇒ In Step 1 of the Pivot Table Wizard, select the source for the
               Pivot Table
                 (An Excel list or Database) and click Next
           ⇒ In Step 2, confirm the list range and click Next
           ⇒ In Step 3, Select the location (New Worksheet or Existing
               Worksheet) and click Finish

           Pivot Table Wizard - Step 1




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




                                                                                                            CHAPTER
                                                                                                       2
      Creating Pivot Tables

      Pivot Table Wizard - Step 2




Note: Above data range of source data is based on records added within the contiguous range of
      records on that worksheet. Therefore, it is important to add future new records by inserting
      new rows within the contiguous range of the list (see previous information on Page 2).



      Pivot Table Wizard - Step 3




      Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   25
                                                                                              Creating Pivot Tables
CHAPTER
          2
           Creating Pivot Tables


           Now you are ready to create the Pivot Table.

           Just drag the fields from the Pivot Table Field List box into the
           area of the Pivot Table you want to populate.

Great Tip! Note: Drag the Data Field item last as that will make it harder to
                   add the Row or Column field after that.

           Empty Pivot Table Layout




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




                                                                                                      CHAPTER
                                                                                                 2
Creating Pivot Tables




Populated Pivot Table




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   27
                                                                                                    Creating Pivot Tables
CHAPTER
             2
                Pivot Table Toolbar
                                                                                                     Always            Show Field
          Pivot Table Menu Button                 Format                                             Display             List
                                                                         Refresh Data                Items
                                                  Report




                                                         Hide Detail
                                                                                               Include         Field Settings
                                                                                               Hidden
                                                               Show Detail                      Items
                                                  Pivot
                                                  Chart




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




                                                                                                       CHAPTER
                                                                                                 2
ReArranging Pivot Table Fields
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.

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




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   29
                                                                                                 Creating Pivot Tables
 CHAPTER
           2
             Updating Pivot Tables

             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.


             Manually Update a Pivot Table

             Steps:
             ⇒ Click into the Pivot Table
             ⇒ Click the Refresh button on the Pivot Table toolbar




             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.


Great Tip!   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


                                                                                                                         Not Included In Pivot Table
             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.




                                Add New Records Within Original Data Source Range




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


                                                                                                 2




                                                                                                      CHAPTER
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.




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   31
                                                                                              Creating Pivot Tables
CHAPTER
          2
           Changing The Functionality of A Data Field
           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.


           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




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




                                                                                                      CHAPTER
                                                                                                 2
Changing The Functionality of A Data Field

Field Settings Menu Command




Changing The Functionality Of The Data Field




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   33
                                                                                              Creating Pivot Tables
CHAPTER
          2
           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.




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




                                                                                                                 CHAPTER
                                                                                                            2
           Changing The Relationship Of Summarized Data


           Pivot Table With Data Field Displaying Percentage of Row Item




Great Tip! 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.



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




           Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   35
                                                                                              Creating Pivot Tables
CHAPTER
          2
           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                 Displays all the data in the data area as the
           From                         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.




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




                                                                                                      CHAPTER
                                                                                                 2
Changing The Relationship Of Summarized Data



Function                        Result

% 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))




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

           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 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)
           ⇒ Click OK


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




                                                                                                      CHAPTER
                                                                                                 2
Filtering Data In Pivot Tables

Filtered Data - Before




Filtered Data - After




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   39
                                                                                                       Creating Pivot Tables
CHAPTER
              2
                 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



          Page Field                                             Page Field List Button


                                                                                                             Page Field List




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




                                                                                                      CHAPTER
                                                                                                 2
Filtering Data In Pivot Tables With Page Fields

Page Field Filtered Data - Before




Page Field Filtered Data - After




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   41
                                                                                              Creating Pivot Tables
CHAPTER
          2
           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




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




                                                                                                      CHAPTER
                                                                                                 2
Sorting Data In Pivot Tables

Sorting Data After




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   43
                                                                                              Creating Pivot Tables
CHAPTER
          2
           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




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




                                                                                                      CHAPTER
                                                                                                 2
Grouping Data In Pivot Tables

Grouping Data Before




Grouping Data After




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   45
                                                                                              Creating Pivot Tables
CHAPTER
          2
           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




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




                                                                                                      CHAPTER
                                                                                                 2
Renaming Groups In Pivot Tables




Naming Groups




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   47
                                                                                              Creating Pivot Tables
CHAPTER
          2
           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




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




                                                                                                      CHAPTER
                                                                                                 2
Drilling Down On Data In Pivot Tables




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   49
                                                                                                  Creating Pivot Tables
CHAPTER

           2
              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.


              To Build Reports Based On Data Fields

              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)




          Double-Click




              Creates New Worksheet



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


                                                                                                 2




                                                                                                      CHAPTER
Breaking Down Data Fields


Build Reports Based On Data Fields - Before




Build Reports Based On Data Fields - After




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   51
                                                                                               Creating Pivot Tables
CHAPTER
          2
           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.



           To Build Reports Based On Page Fields

           Steps:
           ⇒ Click into the Pivot Table
           ⇒ Click the Pivot Table toolbar button
           ⇒ Select Show Pages
           ⇒ Click OK



           Note: Choose the
           preferred page field (there
           could be several) and click
           OK.




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




                                                                                                      CHAPTER
                                                                                                 2
Building Pivot Tables Based From Page Fields




New Pivot Table As A Result Of Show Pages Command




Microsoft Excel Pivot Table Topics For The Haas School of Business, UC Berkeley - Dash Designs   53
                                                                                              Creating Pivot Tables
CHAPTER

          2
           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.


           To Chart Pivot Tables

           Steps:
           ⇒ Click into the Pivot Table
           ⇒ Click the Pivot Table button on the toolbar
           ⇒ Select Pivot Chart command
           ⇒ Edit the chart as necessary




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


                                                                                                 2




                                                                                                      CHAPTER
Charting Pivot Tables




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

          2
           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.



           To AutoFormat Pivot Tables

           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




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


                                                                                                 2




                                                                                                      CHAPTER
AutoFormat Pivot Tables

AutoFormatting With Report-type Formats




Pivot Table Vertical Orientation With Report Format




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

          2
           AutoFormat Pivot Tables

           AutoFormatting With Table-type Formats




           Pivot Table Horizontal/Vertical Orientation With Table Format




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


                                                                                                 2




                                                                                                      CHAPTER
                                             Notes




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

          3       Additional Pivot Table Information




           Creating Multiple Pivot Tables From Same Source
           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.




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


                                                                                                 3




                                                                                                      CHAPTER
Creating Multiple Pivot Tables From Same Source

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.

Pivot Table lists from Web pages You can export a Pivot Table
list from your Web browser to Excel and view and save the list as a
Pivot Table report. The new Pivot Table report and the Pivot Table
list both use the same source data but no link is maintained
between the list and the report.




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

           3
            Source Data Terminology
    Term                                                                       Description

    AutoFilter                                        Process of hiding records in a list that do not
                                                      match the selected item.

    Flat-File                                         A list of information in a contiguous range of
                                                      cells. The top row is called the Header Row
                                                      which contains the column headings. Each
                                                      row below is considered a record.




    Data Validation                                   Properties assigned to cells which limit what
                                                      data can be entered into those cells. They
                                                      can be displayed as drop-down lists
                                                      containing the possible entries.

    Pivot Table                                       An interactive cross-tabulated report that
                                                      summarizes and analyzes records in Excel
                                                      lists or external databases.

    Pivot Table Fields                                A Pivot Table consists of fields.
                        Row Fields                    Labels that describe each row.
                        Column fields                 Labels that describe each column.
                        Data field                    Values that are being summarized.
                        Page Field                    Fields that act as filters in a Pivot Table.


    Spreadsheet                                       Also called a worksheet. A spreadsheet
                                                      consists of cells that are organized into
                                                      columns and rows; a worksheet is always
                                                      stored in a workbook.),



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


                                                                                                 3




                                                                                                                                                                                                CHAPTER
Pivot Table Terminology
Source data




                                                                                                      Pivot Table Terminology content was taken from Microsoft ® and edited and re-formatted.
                                                     The underlying rows or database
                                                     records that provide the data for
                                                     a Pivot Table report. You can
                                                     create a Pivot Table report from
                                                     a Microsoft Excel list, an external
                                                     database,     multiple       Excel
                                                     worksheets, or another Pivot
                                                     Table report.


     Source data for the illustrations
              in this topic.

Field

                                    A category of data that's derived
                                    from a field in the source list or
                                    database. The Sport field, for
                                    example, might come from a
                                    column in the source list that's
                                    labeled Sport and contains the
                                    names of various sports (Golf,
 Region, Sum of Sales, Quarter, and
                                    Tennis) for which the source list
          Sport are fields.         has sales figures.



Item
                                                     A subcategory, or member, of a
                                                     field. Items represent the unique
                                                     entries from the field in the
                                                     source data. For example, the
                                                     item Golf represents all rows of
                                                     data in the source list for which
                                                     the Sport field contains the entry
                                                     Golf.


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

          3
           Pivot Table Terminology
           Row field

                                                                A Pivot Table report that has
                                                                more than one row field has one
                                                                inner row field (Sport, in the
                                                                example below), the one closest
                                                                to the data area. Any other row
                                                                fields are outer row fields
                                                                (Region, in the example below).
                                                                Items in the outermost row field
                  The Sport field is a row field.               are displayed only once, but
                                                                items in the rest of the row fields
                                                                are repeated as needed.


                                                                Region is an outer row field;
                                                                Sport is an inner row field.




           Column field
                                                                Field labels at the top of each
                                                                column in a Pivot Table. Just as
                                                                in a row field, there can be more
                                                                than one column field.



               The Quarter field is a column field




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


                                                                                                 3




                                                                                                                                                                    CHAPTER
Pivot Table Terminology
Page field




                                                                                                      Pivot Table Terminology content was taken from Microsoft ® and edited and re-formatted.
                                                     Page fields allow you to filter
                                                     the entire Pivot Table report to
                                                     display data for a single item or
                                                     all the items.



The Region field is a page field.


Data field                                           Data fields provide the data values
                                                     to be summarized. Usually data
                                                     fields contain numbers, which are
                                                     combined with the Sum summary
                                                     function, but data fields can also
                                                     contain text, in which case the
                                                     Pivot Table report uses the Count
                                                     summary function.

                                                     If a report has more than one
                                                     data field, a single field button
   The Sum of Sales field is a data
                                                     named Data appears in the report
               field.                                for access to all of the data fields.


Summary function

                                                     The type of calculation used to
                                                     combine values in a data field.
                                                     Pivot Table reports usually use
                                                     Sum for data fields that contain
                                                     numbers and Count for data
                                                     fields that contain text. You can
                                                     select additional summary
      Data field is using the Sum function.          functions such as Average, Min,
                                                     Max, and Product.




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

          3
           Pivot Table Terminology
           Drop areas

                                                            The blue outlined regions you
                                                            see when you finish the steps of
                                                            the Pivot Table and Pivot Chart
                                                            wizard. To lay out a Pivot Table
                                                            report, you drag fields from the
                                                            field list window and drop them
                                                            onto the drop areas.



           Field list

                                                            A window that lists all of the
                                                            fields available from the source
                                                            data for use in the Pivot Table
                                                            report. If a field is organized in
                                                            levels of detail, you can click or
                                                               to show or hide the lower
                                                            levels. To display the data from a
                                                            field in the Pivot Table report,
                                                            drag the field from the field list
                                                            to one of the drop areas.
           Field drop-down list

                                                            A list of the items available for
                                                            display in a field. If the field is
                                                            organized in levels of detail, you
                                                            can click     or    to see which
                                                            lower-level items are selected for
                                                            display. A double check mark
                                                            means that some or all of the
                                                            lower-level items are displayed.




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


                                                                                                 3




                                                                                                                                                                    CHAPTER
Pivot Table Terminology
Data area

                                                     The part of a Pivot Table report




                                                                                                      Pivot Table Terminology content was taken from Microsoft ® and edited and re-formatted.
                                                     that contains summary data for
                                                     the row and column fields. For
                                                     example, cell B5 contains a
                                                     summary of all of the sales
                                                     amounts for Golf in Qtr1.

 The values under qtr1 and qtr 2 are
           in the data area.

Classic format                                       Indented format




                                                     In a Pivot Table report in
                                                     indented format, the data for
                                                     each row field is indented. The
                                                     summarized figures for each
                                                     data field appear in a single
                                                     column.
Refresh                                              To update a Pivot Table report with
                                                     the most recent data from the
                                                     source list or database. For
                                                     example, if a Pivot Table report is
                                                     based on data from a database,
                                                     refreshing the report runs the query
                                                     that retrieves data for the report.
                                                     For reports based on worksheet
                                                     data, when you change the
                                                     worksheet data, you can click a
                                                     button to refresh the report with the
                                                     changes.

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

          3
           About calculations and formulas in Pivot Tables

           Pivot Table and Pivot Chart reports provide several types of calcula-
           tions. Data fields use summary functions to combine values from
           the underlying source data. You can also use custom calculations to
           compare data values, or add your own formulas that use elements
           of the report or other worksheet data.
           How PivotTables Summarize Data




               Source data

           The values in the data area summarize the underlying source data
           in the report.




           Pivot Table report made from the above source data

           The Month column field provides items March and April. The Re-
           gion row field provides items North, South, East, and West. The
           value at the intersection of the April column and the North row is
           the total sales revenue from the records in the source data that
           have Month values of April and Region values of North.




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


                                                                                                 3




                                                                                                                                                                    CHAPTER
About calculations and formulas in Pivot Tables


How the type of source data affects calculations




                                                                                                      Pivot Table Calculation content was taken from Microsoft ® and edited and re-formatted.
Calculations and options available in a report depend on whether the
source data came from an OLAP1 database or another type of database.

OLAP source data For reports that are created from OLAP cubes2, the
summarized values are pre-calculated on the OLAP server before Microsoft
Excel displays the results. Therefore, you cannot change how these values
are calculated from within the report. You cannot change the summary
function used to calculate data fields or subtotals, or add calculated fields
or calculated items. If the OLAP server provides calculated fields, known as
calculated members, you'll see these fields in the PivotTable field list. You'll
also see any calculated fields and calculated items that are created by
macros that were written in Visual Basic for Applications and stored in your
workbook, but you won't be able to change these fields or items. If you
need additional types of calculations, contact your OLAP database
administrator.

Other types of source data               In reports based on other types of
external data or on worksheet data, Microsoft Excel uses the Sum
summary function to calculate data fields that contain numeric data, and
the Count summary function to calculate data fields that contain text. You
can choose a different summary function— such as Average, Max, or Min—
 to further analyze and customize your data. You can also create your own
formulas that use elements of the report or other worksheet data, by
creating a calculated field or a calculated item within a field.

Hidden items in totals         For OLAP source data, you can include or
exclude the values for hidden items when calculating subtotals and grand
totals. For other types of source data, values for hidden items are
excluded by default, but you can optionally include the hidden items from
page fields.

1
 OLAP: A database technology that has been optimized for querying and
reporting, instead of processing transactions. OLAP data is organized hierar-
chically and stored in cubes instead of tables.

2
 CUBE: An OLAP data structure. A cube contains dimensions, like Country/
Region/City, and data fields, like Sales Amount. Dimensions organize types
of data into hierarchies with levels of detail, and data fields measure quanti-
ties.

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

          3
           Custom Formula Syntax In Pivot Tables

           You can create formulas only in reports that are not based on OLAP source
           data.
           Formulas are available in PivotChart reports and use the same syntax as
           those in PivotTable reports. For best results when working in a PivotChart
           report, create and edit formulas in the associated PivotTable report, where
           you can see the individual values that make up your data, and then view
           the results in the PivotChart report.
           Formula elements In formulas you create for calculated fields and cal-
           culated items, you can use operators and expressions as you do in other
           worksheet formulas. You can use constants and refer to data from the re-
           port, but you cannot use cell references or defined names. You cannot use
           worksheet functions that require cell references or defined names as argu-
           ments, and you cannot use array functions.
           Names in reports       Microsoft Excel provides names to identify the ele-
           ments of a report in your formulas. The names are composed of the field
           and item names. In the following example, the data in range C3:C9 is
           named Dairy.




           In a PivotChart report, the field names are displayed in the field buttons,
           and item names can be seen in each field drop-down list. Don't confuse
           these names with those you see in chart tips, which reflect series and data
           point names instead.
           Examples A calculated field named Forecast could forecast future orders
           with a formula such as the following:
           =Sales * 1.2
           A calculated item in the Type field that estimates sales for a new product
           based on Dairy sales could use a formula such as the following:
           =Dairy * 115%



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


                                                                                                 3




                                                                                                                                                                                                CHAPTER
About calculations and formulas in Pivot Tables
Formulas operate on sum totals, not individual records Formulas
for calculated fields operate on the sum of the underlying data for any
fields in the formula. For example, the formula =Sales * 1.2 multiplies the




                                                                                                      Pivot Table Calculation content was taken from Microsoft ® and edited and re-formatted.
sum of the sales for each type and region by 1.2; it does not multiply each
individual sale by 1.2 and then sum the multiplied amounts. Formulas for
calculated items, however, operate on the individual records; the calcu-
lated item formula =Dairy *115% multiplies each individual sale of Dairy
times 115%, after which the multiplied amounts are summarized together
in the data area.
Spaces, numbers, and symbols in names In a name that includes
more than one field, the fields can be in any order. In the example above,
cells C6:D6 can be 'April North' or 'North April'. Use single quotation
marks around names that are more than one word or include numbers or
symbols.
Totals Formulas cannot refer to totals (such as March Total, April Total,
and Grand Total in the example).
Field names in item references You can include the field name in a
reference to an item. The item name must be in square brackets— for ex-
ample, Region[North]. Use this format to avoid #NAME? errors when two
items in two different fields in a report have the same name. For example,
if a report has an item named Meat in the Type field and another item
named Meat in the Category field, you can prevent #NAME? errors by re-
ferring to the items as Type[Meat] and Category[Meat].
Referring to items by position You can refer to an item by its posi-
tion in the report as currently sorted and displayed. Type[1] is Dairy, and
Type[2] is Seafood. The item referred to in this way can change whenever
the positions of items change or different items are displayed or hidden.
Hidden items are not counted in this index.
You can use relative positions to refer to items. The positions are deter-
mined relative to the calculated item that contains the formula. If South is
the current region, Region[-1] is North; if North is the current region, Re-
gion[+1] is South. For example, a calculated item could use the formula
=Region[-1] * 3%. If the position you give is before the first item or after
the last item in the field, the formula results in a #REF! error.
In calculated item formulas, if you refer to items by their position or rela-
tive position, any options you have set under Top 10 AutoShow and
AutoSort options in the PivotTable Sort and Top 10 or PivotTable
Field Advanced Options dialog boxes are reset to Off or Manual, and
the options become unavailable.

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

          3
           Create Calculated Item In A Pivot Table Or Pivot Chart

           Steps:
           ⇒ If items in the field to which you want to add the calculated item are
               grouped, ungroup them.
           ⇒ Select the field or an item in the field to which you want to add the calcu-
               lated item.
           ⇒ On the PivotTable toolbar, click PivotTable or PivotChart, point to For-
               mulas, and then click Calculated Item.
           ⇒ In the Name box, type a name for the calculated item.
           ⇒ In the Formula box, type the formula for the item.
           ⇒ To use data from an item in the formula, click the field in the Fields box,
               click the item in the Items list, and then click Insert Item.
                  Note: You can include only items from the same field in which you are
                           creating the calculated item.
           ⇒ Click Add, and then click OK.
                  Note: If the items were originally grouped and you ungrouped them in
                           step 1, you can group them again or create new groups that in-
                           clude the new calculated item, if you want.




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


                                                                                                 3




                                                                                                             CHAPTER
Create Calculated Item In A Pivot Table Or Pivot Chart




Notes

Be careful that the Grand Totals do not include both the data items and the new                       Great Tip!
calculated item(s) in its total (unless preferred so). If so, then remove the Grand
Totals (see Pivot Table options box) and replace with another calculated item for the
Grand Totals.

You cannot change the summary function for fields that contain calculated items.

When a cell in the data area is the intersection of a calculated item and a calculated
field, the formula for the calculated field overrides the calculated item formula.



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


          3
           Create Calculated Field In A Pivot Table Or Pivot Chart

           Steps:
           ⇒ Click a cell in the PivotTable report, or click the PivotChart report
           ⇒ On the PivotTable toolbar, click PivotTable or PivotChart, point to
               Formulas, and then click Calculated Field
           ⇒ In the Name box, type a name for the calculated field
           ⇒ In the Formula box, type the formula for the field
           ⇒ To use the data from a field in the formula, click the field in the
               Fields box, and then click Insert Field
           ⇒ Click Add, and then click OK
           ⇒ If necessary, position calculated field where you want it in the report

           Note: Formulas for calculated fields always operate on all the available
                 data; you cannot narrow their scope by specifying a particular part
                 of the data in the formula.

                  When you create a calculated field in a PivotChart report, the
                  formula and its results are reflected in the associated PivotTable
                  report, and vice versa. If you are working with a PivotChart report
                  and plan to create complex formulas, it is recommended that you
                  create the formulas in the associated PivotTable report, where you
                  can see the individual values that make up your data, and then
                  view the results that are plotted in the PivotChart report.




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


                                                                                                 3
Create Calculated Field In A Pivot Table Or Pivot Chart




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

				
DOCUMENT INFO
Shared By:
Stats:
views:35
posted:7/5/2011
language:English
pages:79