Course ID: EXCLPT

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

About & Customize

Popular Options

  • In-Person
  • Live Webcast

  • 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

  • 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
Information Technology
Prerequisites

Intermediate Excel experience

Course Producer
Business Learning Institute
CPE Credits
6
Level
Intermediate
Available Formats
In-Person, Live Webcast
Types
Seminars

This course, like all courses in the BLI catalog, is customizable for your organization. Format, length, and content customizations are available.

To get started, contact BLI:
(888) 777-7077
team@blionline.org