↑ Return to Microsoft Excel

PowerPivot for Excel

PowerPivot for Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the application users already know and love— Microsoft Excel.

You can transform enormous quantities of data with incredible speed into meaningful information to get the answers you need in seconds. You can effortlessly share your findings with others.

By using PowerPivot for Excel 2010 in your daily work you will be able to get answers you need fast and gain deeper insight into any of your complex datasets.

If you already use Pivot Tables, Pivot Charts and Slicers you will seamlessly be able to leverage your knowledge with these features and hit the ground running.

PowerPivot is designed for fast calculations and analysis. You can process millions of rows in about the same time as thousands, and make the most of multi-core processors and gigabytes of memory for fastest processing of calculations. You can also retrieve data seamlessly from any source

By using DAX expressions you will be able to build new data relationships to perform powerful manipulations. Follow relationships between tables as in a database, define calculated columns and measures, and aggregate over billions of rows.

 

What We Will Cover:

 

INTRODUCTION TO POWERPIVOT

  • Review of Excel concepts and facilities
  • Excel limits
  • Classic Excel PivotTables
  • PivotTables in PowerPivot
  • Using the PivotTable to produce reports
  • Handling irrelevant columns
  • Calculated columns and measures
  • Adding additional tables
  • Dates
  • Refreshing data
  • Slicers

BUILDING POWERPIVOT MODELS

  • What is a data model?
  • Overview of external source types, e.g., relational databases, text files, Analysis Services cubes, etc.
  • Creating and managing connections
  • Using Access
  • Using SQL Server connection
  • Using MS Query
  • Using Analysis Services
  • Using a Reporting Services report
  • Alternate methods of loading data, e.g., linked Excel tables, Clipboard, etc.
  • PowerPivot Query Designers

MANAGING POWERPIVOT MODELS

  • Modifying connections
  • Changing table properties
  • Defining Date Tables
  • Filtering data
  • Data types
  • Creating relationships
  • Managing awkward relationships (e.g., many-to-many, multiple key fields, etc.)
  • Diagram View vs. Data View
  • Defining hierarchies

INTRODUCTION TO DATA ANALYSIS EXPRESSION LANGUAGE (DAX)

  • Calculation in DAX
  • Evaluation context
  • Calculated columns and measures
  • Error handling in DAX expressions
  • Common DAX functions
  • Using dates in DAX

GETTING MORE OUT OF PIVOTTABLES

  • PivotTable types
  • Custom sorts
  • Ratios and percentages
  • Data aggregation without using SUM
  • Year-to-date calculations
  • Conditional formatting and Sparklines
  • PowerPivot Slicers vs. Excel Slicers
  • Key Performance Indicators (KPIs)
  • Creating dashboards