Accelerate your spreadsheet data analysis proficiency. This LIVE,
virtual, hands-on data management course provides the essential
21st century Excel training you need to get ahead. It goes beyond
the fundamentals of PivotTables to explore more advanced topics,
such as calculated fields, calculated items and conditional
formatting. Then it brings you up-to-date and up-to-speed with
Excel for Office 365 Power Tools, including Power Query and Power
Pivot.
With Excel Power Tools, you’ll be equipped to analyze an
unlimited amount of data, combine multiple data sources into a
single report, analyze data across multiple tables and create more
sophisticated calculations.
What You Will Learn
- Build advanced PivotTables
with calculated items and fields
- Combine separate sheets into
one report with the PivotTable PivotChart Wizard
- Automate repetitive importing
and data cleansing tasks instead of writing complicated VBA
macros
- Merge multiple data sets into
a single report using Power Query
- Import data into a Power Pivot
data model
- Create and edit relationships
between two data tables in Power Pivot
Workshop Agenda
Program time: 3 Hours
Session 1: Building Advanced
PivotTables With Calculated Items and Fields
Look at PivotTable fundamentals, then
push the limits of built-in pivots by customizing them with
calculated items, calculated fields and conditional
formatting.
- Review Excel® building
blocks
- Know how to create
calculated items and calculated fields
- Understand how to
conditionally format PivotTable fields
- Describe how to
consolidate multiple ranges into a single report (without
Power Query)
Session 2: Power Query Including the M
language
Learn how Power Query helps you automate
the process of importing, transforming and cleansing your
data. Building an effective Power Query that can be used over
and over on new datasets will save hours of effort and make
you a more effective analyst.
- Discuss the fundamentals
of Power Query (Get & Transform)
- Explain how to use Power
Query to clean up data before you load it
- Extract and organize data
from many sources
- Understand how to merge
queries
Session 3: Introduction to Power Pivot —
Gathering Data
Business analysts use Power Query to
prepare data, then they use Power Pivot to build reports.
Power Pivot is an Excel Add-in used to analyze huge amount of
data from multiple sources. Power Pivot has a different
calculation engine from Excel which makes data analysis
faster.
- Discuss the basics of
Power Pivot
- Import data into your
model
- Link dimension tables and
fact tables
- Build reports