Power Excel – Level 3

Suitable for – An existing Excel user who has to manipulate, analyze and report on massive amounts of data.
Prerequisites – A very good knowledge of Excel, including a working knowledge of PivotTables and an understanding of nested functions.

Syllabus

Module 1 – Getting started with PowerPivot

• Start Excel & launch the PowerPivot window
• Navigating the PowerPivot window
• Exploring the PowerPivot tab and field list in Excel

Module 2 – Adding data to PowerPivot

• Data sources and types supported in PowerPivot workbooks
• Importing data (from pre-prepared file)
• Add Data by using Excel Linked Tables

Module 3 – Preparing data for analysis

• Working with Tables and Columns
• Filtering and Sorting Data
• Creating Relationships Between Tables
• Creating and Working with calculations

Module 4 – Data; formatting and layout

• Creating, deleting a table
• Rename a Table or Column
• Set the Data Type of a Column
• Hide or Freeze Columns
• Undo or Redo an Action
• Sorting and filtering data in a table

Module 5 – PowerPivot and relationships

• Understanding Relationships
• Create a Relationship Between Two Tables
• View and Edit Relationships
• Delete Relationships
• Troubleshoot Relationships

Module 6 – Calculations in PowerPivot

• Overview of Data Analysis Expressions (DAX) language
• Building Formulas for Calculated Columns and Measures
• Understanding the Use of Relationships and Lookups in Formulas
• Understanding Aggregations in Formulas
• Filtering Data in Formulas
• Recalculating Formulas

Module 7 – Creating PivotTables, Charts & Reports

• Create a PivotTable or PivotChart Report
• Create a Reporting Services Report with PowerPivot Data
• Create a Measure in a PivotTable or PivotChart
• Create and Change the Field Layout in a PivotTable or PivotChart Report
• Delete a PivotTable or PivotChart Report
• Filter Data using Slicers
• Work with Relationships in PivotTables
• Change the Display Language

Module 8 – Overview of Key Performance Indicators (KPI’s) in PowerPivot

• Retail sales KPI case study
• Sales per hour, average
• Conversion rates
• Wage to sales ratio

Module 9 – Dashboards basics

• Ideas on sourcing the relevant financial and non-financial information
• Excel tools and functions that are vital for dashboards (create templates)
• Using Pivot table as the dashboard

Module 10 – Key functions for dashboards

• Offset/VLookup/Aggregate
• Linking cells to text box/Linking cells to chart labels
• Comparing time series
• Assessing seasonality in a business
• Mixed metrics and KPI’s
• Comparing year on year results
• Showing actuals versus budgets versus forecasts
• Budget Forecast