Advanced Excel – Pivot Tables

This is not about programming but just my reference for some advanced spreadsheet skills for Excel 2007. To be updated for later Excel versions.

Steps to create pivot tables are provided here. This is just an elaboration on my part, with input from other websites like chandoo.org.

  1. Preselect the data range. If the data to be used in the pivot table is in one of the worksheets, clicking on any one of the cells inside that range of data will automatically make the wizard select that data range. Make sure that data range does not have blank rows.
  2. Open the Create Pivot Table Wizard to Configure the pivot table. Navigate to the ribbon and follow this menu selection: Insert > Pivot Table > Pivot Table. When the wizard comes up, there are two choices you have to make:
    • Where to get the data? The data source and range, which can either be:
      1. From the same worksheet. If the default data range is incorrect, manually edit the values in this input field with the following syntax: [workbookname]sheetname!range
      2. From an external data source. If you choose this, a list of existing connections will pop up and you can create a new connection as well. image

        To create a new connection, click on the  the “Browse for more” button and the Select Data Source dialog box will pop up.image

        If the data source is MS SQL Server choose “NewSQLServerConnection”, otherwise, choose “Connect to New Data Source” and click Open. image

        The first two options will open a new connection dialog box with SQL Server while the fourth will connect to an Oracle database. image

        Choosing “ODBC DSN” opens up another dialog box: image

        Choosing “Other/Advanced” will allow you to fine tune the connection for other types of data sources:

        imageimageimageimage

    • Where to place the pivot table. You can place it in a new worksheet or in an existing worksheet. To make it easier placing it in an existing worksheet, you can collapse the dialog box, chose the first cell, and expand the dialog box afterwards. image
  3. Choose which fields are placed where on the pivot table. Upon clicking the OK button in the Create PivotTable dialog box, an empty pivot table will be created and a PivotTable Field list will be displayed. Configure the pivot table by either dragging and dropping them on the empty table or on the PivotTable Field.

    image

    The four places to place them are:

    • Column Labels = Column Fields;
    • Row Labels = Row Fields,
    • Values  = Data Items ;
    • Report Filter = Page Fields.  
  4. Some of the things that are configurable in the pivot table are:
    • The row fields and the column fields can be sorted ascending, descending or custom.image
    • In the Field Table Field List, you can configure the field settings (move, remove, rename). The Values field settings data can be summarized in different ways as well:  imageimageimageimageimage

      Some explanation for the values in the Summarize by tab and explanation for Show value as tab.The p in VARP and STDVARP means “of population”, those without are “of a sample”. Some explanations here and here, here. VAR and VARP have been renamed in later Excel as VAR.S, VAR.P, respectively. Old function names may be phased out so its better to use the new names. Those with “A” in the statistical function will not ignore text and logical values, as explained here. Use the STDDEV to show the more intuitive distribution of data. Click here for a discussion why VAR and STDDEV are not the best way to measure dispersion since it amplifies larger deviations. It is better to use MAD mean (or AVEDEV in Excel) as distribution measure plus IQR. AVE and STDDEV are non-robust measures of dispersion.

    • Its possible to use the same underlying data but presented differently, say as another column but with different name and different summary or display value format. Or transpose the fields between rows and columns.
    • You can also modify how it is displayed by right clicking on the pivot table by formatting, sorting and other pivot table options . image
    • The context menu on the row and column fields are different, with Filter, Expand/Collapse, Group (by months, quarters, years, etc.) or Ungroup. Some other tips on grouping. imageimage
    • You can choose to display the summary or details of each higher level field if there is a lower level row/column fields by clicking on the icon to the left of the field names.image
    • You can choose to filter data display by unticking on which row values to not display.  image
    • Even the data values can be filtered using the “Value Filters” image
    • You can also filter report by like which “Regions” to display in this specific report.image
    • There are some pre-defined table formats that can be used, just click on any cell within the pivot table and click on  Home > Format as Table and choose any format. Conditional formatting can be done as well using HomeConditional Formatting or styles on the cell using Home > Cell Styles. Some example of zebra style formatting and increase/decrease icons and creating/editing formats.
    • There is also on the ribbon the Pivot Table Tools you can field header filter icons and other options. From this you can also add Calculated fields (Formula > Calculated Field), change data source and generate additional reports using show report filter pages. For Show Report Filter Pages to work, there must be a field in the Report Filter. image
  5. Other things you can do:
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s