Microsoft® Excel 2016: Data Analysis with PowerPivot

Course Description:

This course involves the creation of advanced Pivot Tables in Microsoft Excel® using the add-in program, Power Pivot. Data analysis in Excel is limited to using data list or table sources constructed as flat-file databases. With Excel being a spreadsheet program and not a relational database, trying to connect to multiple data sources for advanced Pivot Table creation is not theoretically possible. By using the Power Pivot add-in program, Excel power users can now create effective data models that permit the connection to multiple data tables invariably linked by way of a relational join. Pivot Tables, thus, can be much more robust as they can be derived from a variety of data source tables. In addition, there are very powerful calculations that can be created from functions categorized as Data Analysis Expressions, or DAX functions. The Excel power user can also create both calculated fields as well as summary calculations known as Measures. These Measures can also be expressed ironically in the form of KPI’s, or Key Performance Indicators, which essentially are graphics depicted by logical conditions.

Course Objectives:

• Review of Pivot Table basics
• Understanding Relational Databases
• Creating a Data Model in Power Pivot
• Developing Data Relationships
• Creating Pivot Tables from the Power Pivot data model
• Creating calculated fields
• Creating summary calculations (measures)
• Using DAX functions
• Utilizing Key Performance Indicators (KPI’s)