Excel Power User Level 1

This course is aimed at intermediate and advanced users of Excel who are only familiar with Excel’s “traditional” features. It aims to show delegates how to make the transition from over-reliance on Excel’s VLOOKUP function to working with tabular information from multiple sources using Excel’s supercharged Power Query and Power Pivot tools. Delegates will be introduced to the Excel data model and will learn how to create pivot tables and dashboards which visualize data from multiple related sources.

Introduction

Excel’s new frontiers; Understanding Excel’s new power components; New ways of working in Excel; Limitations of VLOOKUP models; Connections and queries versus automation; Activating Excel’s power tools

Tabular data

What is tabular data; The benefits of working with tabular data; Features and benefits of Excel tables; Other tabular data sources

The Excel Data Model

What is the data model? Benefits of using the data model; Key elements of the Excel data model; Building multi-table solutions

Tables Queries and Connections

Understanding Excel tables; Creating A Query From An Excel Table; Working with linked tables; Adding linked tables to the data model; Reviewing connections; Creating a query from a named range; Creating a query from an ordinary worksheet range

Getting Started With Power Query

What Is Power Query? Installing And Activating Power Query; Overview Of Data Sources; Overview Of Power Query Workflows

Query Basics

Creating A Query; Understanding Power Query Steps; Renaming A Query; Renaming steps; Why no Undo? Changing settings; The Close & Load Command; The Close & Load To Command

Modelling Data

The Power Pivot add-in; Power Pivot overview; Navigating the Power Pivot interface; working with multiple tables; creating relationships

Creating Pivot Table Reports

Creating a Power Pivot pivot table; Using fields from related tables; Formatting pivot table data;
Creating drill-down experiences; Displaying aggregate values; Displaying multiple aggregations;
Displaying percentage breakdowns.

Creating Dashboards

Designing dashboards; Using slicers to filter data; Using timelines to filter data; Specifying report
connections; Creating pivot charts