Course ID: EXCLPT

Excel Pivot Tables in-depth, PowerPivot, and Data Analysis Functions and Tips

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

DESIGNED FOR
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

FIELD OF STUDY
Computer Science

PREREQUISITES
Intermediate Excel experience

COURSE PRODUCER
Business Learning Institute

CPE CREDITS
8.0

LEVEL
Advanced

This course is avaliable for your group as:

Group Live
 

Let's Roll!

To learn more or customize this course for your group, complete this form and a BLI team member will get back with you shortly.


Or, contact BLI: 888-481-3500 or learn@blionline.org