Excel includes sophisticated features to present data in Pivot Table reports. This course covers the many Excel features and functions to test, organize, calculate and find trends in data as well as report the results. The free PowerPivot add-in for Excel takes PivotTable analysis to a new level. PowerPivot is a business intelligence tool that will provide you with additional features to build complex relationships between sets of data. This course applies to Excel 2016/2013/2010. Participants will learn many timesaving tips, work through many examples, and receive take-aways for after class use.
Learning Objectives
Use the Pivot Table feature for quick analysis and subtotal reporting
Use new Pivot Table filter features including the slicers and timeline and Pivot Charts
Learn Pivot Table tips for recalculation and adding custom calculations
Protect Pivot Table reports
Clear the Pivot Cache, copy a Pivot Table, and create separate Pivot Tables and Pivot Charts
Identify Options settings to control formatting and also generating multiple reports automatically
Use the Data Validation feature to control user input
Apply new color scales and icon sets to visually spot trends in data
Sort or filter by background color of cells based on conditional formatting
Generate automatic subtotals by list category and show/hide details
Apply required design rules for an Excel list
Locate exact match or custom match data with new AutoFilter features
Use the Table and Subtotal Function for subtotaling visible filtered cells
Use the Excel data query feature to connect to an external data source such as a Microsoft Access database and retrieve specific data
Use data testing techniques to assure data validity
Use data functions, data import techniques, text to columns, and FlashFill features
Use Excel’s conditional formatting features and icon sets for tracking key performance indicators
Record macros for data cleanup solutions
Discover the benefits of the Excel PowerPivot Data Model
Add data to the PowerPivot Data Model for Pivot Tables and use DAX functions for calculating within the Data Model
Major Topics
Master the creation of complex Pivot Table reports with calculated fields
Understand the pivot cache and how to protect your data
Use Pivot Tables to analyze data by category with subtotals
Learn Pivot Table tips for recalculation and duplicating Pivot Tables
Understand required design rules for Excel data and test data for validity
Use multiple key Sort techniques comparing the Quick Sort tools and the Data Sort dialog box
Generate automatic Subtotals by list category and show/hide details
Copy and format visible cells only
Use AutoFilter to filter lists in place
Use the Subtotal Function to subtotal visible filtered cells
Use the new Table feature to save time manipulating lists in place
Use Right, Left, Mid, Search, and Len functions to extract portions of cell contents
Learn to test data for accuracy and cleanup data using important Excel functions
Use concatenate features to combine information from multiple cells into one cell
Use Text to Columns to import text or .csv files
Create recorded macros for data cleanup and Pivot Table refresh
Apply Conditional Formatting to identify data patterns in color and find duplicates
Learn how to connect to an external data source and run an Excel query
Understand the benefits of and use the PowerPivot Data Model
Import Data from Various Data Sources
Refresh Data from a Data Source
Create Linked Tables
Provider
Business Learning Institute
Course Level
Advanced
Professional Area of Focus
Technology
CPE Field of Study
Information Technology
4.0
Who Should Attend
CPAs and Professionals at all levels of an organization needing to manipulate data within Excel or from data connections to external data sources with the time-saving Pivot Table, PowerPivot and other important Excel data analysis features
