Highline Excel 2016 Class 03-03 PivotTables

PivotTables

  • What does a PivotTable do?
    • PivotTables create summary reports that contain aggregate calculations with conditions/criteria.
      • The words “Conditions”, “Criteria” and “Filter” are all synonyms for adding criteria to the calculations in a PivotTable.
    • Example: Adding Sales based on the criteria “Quad” (Product Field) and “West” (Region Field).
  • How to create PivotTable:
    • Must have Proper Data Set.
    • Click in one cell in Proper Data Set.
    • Open Create PivotTable dialog box:
      • Insert Ribbon Tab, Tables group, PivotTable button
      • Keyboard: Alt, N, V
  • Add conditions to the PivotTable:
    • Row area or Column area:
      • From the Field List drag fields to the Row area or the Column area.
      • When you drag a filed to the Row area or Column area:
        • A unique list of items from the field is displayed.
        • Each one of the items in the unique list becomes a condition or criterion for each of the calculation in the Values area.
        • Each cell in the Values area has a unique Column Header (criterion) and Row Header (Criterion) that are the criteria for the calculation.
    • Filter or Slicer:
      • From the Field List drag fields to the Filter area.
      • Add a Slicer from the PivotTable Tools Analyze Ribbon Tab, Filter group.
      • Filters and Slicers add conditions/criteria/filters to entire report.
        • All Cells in the Values area use the Condition/Criteria that are selected in the Filter area or Slicer.
    • Slicer:
      • To Select Items not next to each other is a Slicer, use the Ctrl Key.
      • To Clear the selected items in the Slicer, use the “Red X” Clear Button in the Upper Right area of the Slicer.
      • Hide Buttons in Slicer when there is no data:
        • Right-click Slicer and point to “Slicer Settings”, then check the box for: “Hide items with no data.”
      • Connect Multiple PivotTables to a Slicer:
        • Right-click Slicer and point to “Report Connections” and then check the boxes for the desired PivotTables.
  • Grouping Daily Dates into Years, Quarters, Months
    • In Excel 2016, when you drag a Date Field into the Row area of a PivotTable, it is automatically grouping into:
      • Year
      • Quarter
      • Month
    • If you WANT a unique list of Dates (like for a Daily Sales Report) you must:
      • Right-click the date field in the PivotTable
      • Click on Ungroup.
  • Calculations in a PivotTable:
    • From Field List drag field to Values area:
      • The Value area of the PivotTable is where the calculations are made.
      • SUM is the default for Number Values.
      • COUNTA is the default calculation for Text items.
      • The calculation in the Value area is a calculation made based in the conditions in the Row area, Column area or from the Filter/Slicer.
    • To change calculation use:
      • Right-click in PivotTable and point to:
        • Summarise Values by
          • Allows you to change function.
        • Show Values As
          • Allows to create a built-in calculation like:
            • % of Column Total
            • Difference From.
      • Right-click in PivotTable and point to: Value Field Settings to change:
        • Name of calculation at top of PivotTable
        • Aggregate Function
        • Change Calculation (Show Values As tab)
        • Change Number Formatting (button)
        • Change Number Formatting (button)
  • Name PivotTable:
    • Right-click PivotTable, Select PivotTable Options
    • PivotTable Tools Analyze Ribbon Tab, PivotTable group
  • Formatting the PivotTable to show Field Names:
    • Design, Report Layout, Show in Tabular Form
  • Adding Number Formatting to the field, not the cells:
    • Value Field Settings, click on Number Formatting button
    • Right-click in the Values area of PivotTable and click on Number Formatting (Not Format Cells)
  • PivotTable Styles:
    • PivotTable Tools Design Ribbon Tab, Styles, More button, New PivotTable Style, then use dialog box to create your own style.
  • Cross-tabulation
    • Term used when you have dropped a field into the Row area and the Column area.
  • Inside the Pivot:
    • Pivot: drag and drop fields in Field List to “Pivot” the report.
    • Filter from dropdown arrows.
    • Sort from dropdown arrows
  • Create Many PivotTables (One on Each Sheet) with a Single Click:
    • Create PivotTable
    • Drop Field in Filter Area (make sure Filter is showing ALL
    • PivotTable Tools Analyze Ribbon Tab, PivotTable Group, Options drop-down, Click “Show Report Filter Pages”.




Be the first to comment

Leave a Reply