Excel 2019 Pivot Tables Courseware (0200-500-19-W)

Module 1: Creating and Modifying PivotTables

• PivotTables
  • Guidelines for Data in a PivotTable
• Creating PivotTables
  • Creating a PivotTable
  • Using Recommended PivotTables
  • Using the Quick Analysis Tag
• PivotTable Elements
  • Adding Fields
  • PivotTable Fields Pane
  • PivotTable Tools Tabs
• Adjusting PivotTable Layouts
  • Using the PivotTable Options Dialog
  • Using the Design Tab
  • Setting the Default Layout
• Formatting PivotTables and Fields
  • Using the Design Tab
  • General Formatting Options
  • Value Filed Settings
  • Showing More Than One Value Calculation
• Expanding & Collapsing Fields
• Refreshing PivotTables
  • Refreshing a PivotTable
  • Re-Defining the PivotTable Data Range

Module 2: Filtering PivotTables

• Filtering Data
  • Sorting a PivotTable
  • Basic Sorting
  • Basic PivotTable Filtering
  • Basic Filtering
  • Filtering a PivotTable
  • Filtering in the Report Filter
• Advanced Filtering
  • Label and Value Filters
  • Using the Top or Bottom 10
• Report Filters
  • Using Multiple Report Filters
  • Using the Search Box to Apply a Filter
  • Using Wildcards
• Filtering With Search Box
• Adding to Filters
  • Adding Criteria to an Existing Filter
• Conditional Formatting in a PivotTable
  • Using Conditional Formatting
  • Clearing Conditional Formats
• Grouping Data
  • Grouping Data in a PivotTable
  • Sorting by Fiscal Year
• Using the Slicer
  • Using Slicers
  • Closing or Deleting a Slicer
  • Formatting a Slicer
  • Using the Slicer Settings Dialog
  • Slicer Connections

Module 3: Advanced Features of PivotCharts

• PivotTable and PivotChart Wizard
  • Considerations for Setting Up Your Source Data
• Installing PivotTable and PivotChart Wizard
• Using the Wizard
  • Step 1: What Type of Consolidation do I want?
  • Step 2: Choosing the Data Source
  • Step 3 Where to add PivotTable or PivotChart
• Consolidating Multiple Workbooks Or Worksheets
  • Consolidating Data From Multiple Excel Sources
  • Using The Create A Single Page Field
  • Creating Your Own Page Fields
• Editing A Consolidated PivotTable
  • Modifying a Consolidated PivotTable
  • Using Find&Replace to Rename Page Field Items
• Calculated Fields and Calculated Items
  • Creating a Formula in a PivotTable Report
• Adding A Calculated Item
  • Adding a Calculated Item to a Field
• Adding A Calculated Field
  • Adding a Calculated Field
  • Editing Calculated Items Or Fields
  • Editing Calculated Items
  • Deleting Calculated Items
  • Editing Calculated Fields
  • Deleting Calculated Items
• Solve Order and Displaying Formulas
  • Changing the Order
  • Displaying Formulas

Module 4: Creating and Modifying PivotCharts

• PivotCharts
  • Inserting PivotCharts
  • Creating a PivotChart from the Source data
  • Creating a Chart Based on an Existing PivotTable
• Filtering PivotCharts
  • Using Field Filter Drop-downs
  • Adding Slicers and Timelines
• Formatting PivotCharts
  • Using the Format Shape Dialog Box

Module 5: Creating PivotTables from External Data

• Get Data from a CSV File
  • Connecting to CSV fi les
• Power Query
  • Using Power Query
  • Managing Columns; Split a Column
  • Sorting & Filtering Data
• Editing a Query
  • Accessing the Queries

Appendix A

• Unlocking a Slicer
  • Unlocking a Slicer in a Protected Worksheet
• Custom Slicer Styles
• Creating Custom PivotTable Styles
  • Creating a Custom PivotTable Style
  • Applying a Custom PivotTable Style
  • Modifying a Custom PivotTable Style
• Using Custom PivotTable Styles
  • Using a PivotTable Style in Another Workbook

Appendix B

• Troubleshooting Data
  • Leading and Trailing Spaces
  • The TRIM Function
  • Selecting Blank Rows/Columns for Elimination