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.


    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