Select tickets
Course Code: 0200-500-19-W
Course Format: Instructor-Led Online
Prerequisites:
- Excel Level 1 & 2 and Excel Data Analysis
In this course you will learn how to create and modify PivotTables and PivotCharts, as well as how to filter and modify fields to format and group data. In addition, you will learn to use the advanced features of PivotTables, such as consolidating multiple workbooks as well as creating and using page fields. Finally, you will learn how to generate PivotTables using imported data from external data sources. This information could be in Access, SQL Tables, the Internet, or other database programs. Understanding how to work with PivotTables and PivotCharts to achieve the results you want is one of the most powerful tools in Excel.
Target Student:
Course will benefit those working with large amounts of data in Excel or database programs and want to create reports based on that data.
Topics:
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
- 121 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