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
What is tabular data; The benefits of working with tabular data; Features and benefits of Excel tables; Other tabular data sources
What is the data model? Benefits of using the data model; Key elements of the Excel data model; Building multi-table solutions
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
What Is Power Query? Installing And Activating Power Query; Overview Of Data Sources; Overview Of Power Query Workflows
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
The Power Pivot add-in; Power Pivot overview; Navigating the Power Pivot interface; working with multiple tables; creating relationships
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.
Designing dashboards; Using slicers to filter data; Using timelines to filter data; Specifying report
connections; Creating pivot charts