CANCELLED: Excel Pivot Tables in-depth, PowerPivot, and new Data Visualization Features (130221-15)
Description
Excel includes sophisticated features to analyze and present data in
Pivot Table reports. This course covers many Excel features to organize,
calculate and find trends in data and report the results with Pivot
Tables. PowerPivot is a business intelligence tool that will provide you
with additional features to build complex relationships between sets of
data. With PowerPivot you can: analyze data beyond the limits of the
Excel PivotTable; organize tables in a relational way; define complex
calculation expressions; and integrate different data sources into your
analysis. New Excel data visualization including Power Map and Power
View features will also be covered. Participants will learn many
time-saving tips and receive course take-away resources. (Format:
hands-on, instructor-led)
Learning Objectives
After completing this course, participants will be able to: • Use the Pivot Table feature for quick analysis and subtotal reporting • Use new Pivot Table filter features and the slicer •
Learn Pivot Table tips for recalculation and how to secure the Pivot Cache •
Learn how to protect Pivot Table reports • 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 • Add custom calculations to a Pivot Table for averages, counts, percentages, differences •
Understand required design rules for an Excel list • Locate exact match or custom match data with new AutoFilter features • Use "and" or "or" conditions • 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 and data formats • Use data functions, import data and text to columns features • Use Excel's conditional formatting features and icon sets for budgeting and tracking key performance indicators • Become familiar with the Excel PowerPivot application to import and manipulate data • Use DAX functions in PowerPivot and create graphic representations of PowerPivot reports • Use Power Map and Power View features for data visualization
Major Topics
Master 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
Generate automatic Subtotals by list category and show/hide details
Copy and format visible cells only
Use AutoFilter to filter lists in
place with “And” or “Or” conditions
Use the Subtotal Function to
subtotal visible filtered cells
Use Advanced Filter features for more
complex filtering needs with custom criteria
Use the new Table feature
to save time manipulating lists in place
Use Right, Left, Mid, Search,
Exact 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
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
Create PowerPivot PivotTables • Create PivotCharts •
Present PivotTable Data Visually • Manipulate PowerPivot Data Using DAX
Functions • Use Power Map and Power View features for data visualization
Protect Reports • Save Reports in Different File Formats
