Power Training

Power Training for Microsoft Excel Users

Microsoft Excel is an essential tool for most modern businesses, providing a dazzling array of features for manipulating and analysing organisational data. The concept of power training for Excel users is not new; and the traditional route to becoming an Excel power user has been to develop a fairly large repertoire of advanced techniques; such as the creation of complex formulas and VBA macros. However; thanks to the significant enhancements made to Excel by Microsoft; it is now possible for all of your Excel users to become power users! And the training they will receive closely resembles Power BI training.

Power Training

Our Excel power training courses show delegates who have an intermediate to advanced knowledge of Excel how they can become power users simply by mastering a series of powerful new features which have been available in Excel for a few years now, but of which most users are still blissfully unaware. G Com Solutions run Excel power training courses every month, at our training centre in Peterborough; and we can also provide on-site training, anywhere in the UK.

Excel’s Hidden Gems

In a few years’ time, it is probable that these features will have become familiar to the majority of Excel users. However, today, they are still hidden gems waiting to be discovered.

Many companies using Excel find that, as they create data, they often end up using Excel as an informal database management system. To analyse this data, tie it all together and create reports from it, requires advanced Excel techniques; and thus, was born the Excel power user; someone who develops a bag of tricks consisting of advanced formulas (usually including VLOOKUP and SUMIFS functions) and VBA macros.

One of the problems with this situation is that the solutions developed by one power user to solve a given problem may be different to those created by another power user to solve the same problem. Thus, many organisations inherit Excel models which only one or two people fully understand. With the release of the Power Pivot and Power Query add-ins and their subsequent incorporation into Excel’s built-in feature list, Microsoft have made available a series of tools which are capable of transforming any competent Excel user into a power user. And the great thing is, the status of power user is bestowed upon them as a result of the tools they use, not as a result of designing their own personal bag of tricks.

Power Pivot and Power Query allow users to work with very large datasets, often derived from disparate sources. Power Pivot allows you to connect to your data and to specify rules for transforming the data as it is being imported in all kinds of useful ways. Most of these transformations can be performed with a series of user-friendly visual tools.

Many of the operations which Power Query allows the user to perform on their raw data could only be done as efficiently and quickly by writing and maintaining complex macros; a skill which relatively few Excel users master. True, anyone can use the macro recorder; but to create scalable, working macros, users have to learn to program; and not many Excel users have the time or inclination to become fully-fledged programmers.

Excel Power User Versions

Excel’s new power features are not available in all versions of Excel; the following table summarizes availability in different versions.

Excel 2016 (Built in)Excel 2016 Standalone. Excel 2016: Office 365 ProPlus, E3, E4 and E5 editions.
Excel 2013 (Built in)Excel 2013 Standalone. Excel 2023: Office 2013 Professional Plus edition.
Excel 2010 (Add-ins)Available as a series of add-ins which can be downloaded and installed free of charge.
Excel 2007 or earlierExcel’s power user features are not available in Excel version 2007 or earlier.

Powerful Tools all your Staff Can Master

The purpose of the Excel power training courses by G Com Solutions is to allow any competent Excel user to become a power user. Delegates are shown how to leverage the most powerful tools in Excel, to bring together disparate related data sources and create insightful reports from them. And the great thing is that these tools are no more difficult to learn than Excel’s mainstream features.

Getting your people trained on Excel’s most sophisticated analysis tools will bring significant rewards in increased productivity and business intelligence. Modern businesses typically accumulate thousands, or millions, of rows of important data every year; and encouraging your staff to use Excel’s power user tools will broaden the range of questions you can answer about your data.

You will find that many of the topics covered are similar to those covered on our Power BI training.

Power Training for Microsoft Excel Users: 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

Power Training for Microsoft Excel Users: Level 2

This one day course is designed for users who are already familiar with Excel’s Power Query and Power Pivot tools. It shows users how to connect to a wide variety of data sources and create data models which include DAX calculated columns and measures. Delegates will also be shown how to create rules for transforming data as it is added to the data model and how to create Power View reports.

Connecting to Data from outside Excel

Getting native Excel data; Getting data from relational databases; Getting data From .CSV And .TXT files; Getting tabular data from a web page

Transforming Columns of Data

Removing unwanted columns; Renaming columns; Reordering columns; Changing the case of columns; Trimming and cleansing columns; Extracting characters from columns; Performing mathematical operations on columns; Splitting columns; Concatenating columns; Converting columns to Boolean values; Transforming date columns

Transforming Rows

Promoting row headers; Removing unwanted rows of data; Removing duplicate rows; Removing rows which contain error values; Replacing error values; Counting rows

Performing other Data Transformations

Using Find and Replace; Filtering out unwanted rows; Changing data types; Unpivotting columns of data; Pivotting rows of data

Creating Calculated Columns

DAX and Excel formulas compared; When to create calculated columns; User-friendly DAX functions; Using IF and SWITCH; Using the RELATED function

Creating Measures

Implicit and explicit measures; Creating measures best practices; Referring to measures in other measures; How measures are stored; Using DAX helper measures

Understanding the DAX Language

Understanding the DAX formula evaluation context; Understanding the Row context; Understanding the filter context; Using SUMX and COUNTX; Using RELATEDTABLE; Using COUNTROWS and DISTINCTCOUNT

Understanding the CALCULATE function

CALCULATE and SUMIFS compared; Why CALCULATE is so important in DAX; Understanding the CALCULATE syntax; How CALCULATE alters the filter context; Using CALCULATE with Boolean filter statements; Using CALCULATE with the ALL, ALLEXCEPT and ALLSELECTED functions; Using CALCULATE with the FILTER function

Creating Power View Reports

Power View basics; Using the filters pane; Using cards for big picture insights; Tables and matrices; Creating line charts; Creating pie charts; Creating scatter charts; Using Power View maps

Power Training for Microsoft Excel Users: Level 3

This course is designed for experienced users of Excel’s Power Pivot and Power Query tools and provides insights into the advanced aspects of these powerful technologies. Delegates will be shown how to write their own functions in the M language which underlies Power Query; and to create complex DAX formulas using advanced time intelligence functions.

Advanced Data Transformation

Grouping and aggregating rows of data; Creating conditional columns; Creating columns by referencing existing columns; Appending data with common column headers; Merging data with common rows; Combining all files in a folder

The Power Query Formula Language

Using the Formula Bar; Using The Advanced Editor; Overview Of The M Language; Exploring M using #shared

Understanding Automatically Generated M Code

Excel.Workbook; File.Contents; Table.PromoteHeaders; Table.TransformColumnTypes; Table. AddColumn; Table.ReplaceValue; Table.Skip; Table.RemoveLastN

Creating custom functions in M

Defining a function; Defining input parameters; The goes-to operator; Defining the function body; Using optional parameters; Calling functions

Iteration Techniques

Benefit of generating lists; Generating lists of numbers; Generating lists of dates; Generating alphanumeric lists; Using the Each function; Applying a function to a list of files

Advanced DAX

Using disconnected parameter tables; Creating parameter measures; Overview of DAX time intelligence; Creating a date table; Using DATEYTD and its variations; Using SAMEPERIODLASTYEAR; Using PARALLELPERIOD; Using FIRSTDATE and LASTDATE; Using DATEADD; Using DATESBETWEEN

Hierarchies

Creating hierarchies; Using RELATED to assemble columns; Adding, removing and reordering columns; Using a hierarchy on a pivot table

KPIs

Overview of KPIs; Creating KPIs; Base value; Target value; Status threshold; Creating measures for KPIs; Displaying a KPI on a pivot table

Creating 3D Map Reports

3D Map data requirements; Understanding scenes and tours; Adding fields to a report; Adding a timeline; Using themes; Creating scenes; Setting scene effect options; Using the Tour Editor; Creating heat maps

Sharing Queries

Getting an Azure Data Catalog subscription; Getting a Power BI subscription; Using Send to Data Catalog; Sharing queries with specific groups; Using Data Catalog Search; Using My Data Catalog


Our Charges for Power Training for Microsoft Excel Users

Our charge for supplying customized training in Aberdeen is £850, per day.
Our price for delivering on-site courses in Accrington/Rossendale is £750, per day.
Our charge for delivering Microsoft Excel power training courses in Barnsley is £750, per day.
Our price for running Microsoft Excel power training courses in Basildon is £695, per day.
Our fee for delivering on-site power training in Basingstoke is £695, per day.
Our charge for supplying customized training in Bedford is £695, per day.
Our price for delivering on-site courses in Belfast is £850, per day.
Our price for delivering on-site training courses in Birkenhead is £695, per day.
Our price for supplying in-house courses in Blackburn is £750, per day.
Our price for supplying on-site power training in Blackpool is £750, per day.
The cost of supplying customized training in Bournemouth is £750, per day.
The charge for running customized training courses in Brighton and Hove is £750, per day.
Our fee for delivering in-house training courses in Bristol is £750, per day.
Our price for running Microsoft Excel power training courses in Burnley is £750, per day.
The cost of supplying in-house training courses in Burton-upon-Trent is £750, per day.
The cost of supplying on-site power training in Cambridge is £695, per day.
The cost of supplying customized courses in Cardiff is £825, per day.
The cost of supplying customized training in Chelmsford is £695, per day.
The fee for running on-site power training in Cheltenham is £750, per day.
Our fee for delivering customized training in Chesterfield is £750, per day.
The cost of supplying in-house courses in Colchester is £695, per day.
Our price for running customized courses in Coventry is £695, per day.
Our price for delivering on-site training courses in Crawley is £695, per day.
The cost of supplying on-site training courses in Derby is £695, per day.
Our charge for supplying on-site training courses in Doncaster is £750, per day.
The fee for running customized training in Dundee is £850, per day.
Our charge for supplying customized courses in Eastbourne is £695, per day.
Our price for running customized training in Edinburgh is £825, per day.
Our fee for delivering customized training in Exeter is £750, per day.
The cost of supplying on-site training courses in Farnborough/Aldershot is £695, per day.
Our price for running customized courses in Glasgow is £825, per day.
Our charge for supplying in-house courses in Gloucester is £750, per day.
Our price for running on-site power training in Greater London is £695, per day.
Our charge for supplying customized training courses in Grimsby is £750, per day.
Our charge for delivering on-site training courses in Hastings is £695, per day.
Our charge for delivering on-site power training in High Wycombe is £695, per day.
Our charge for delivering Microsoft Excel power training courses in Ipswich is £695, per day.
Our charge for delivering on-site training courses in Ireland is £850, per day.
Our charge for supplying on-site courses in Kingston upon Hull is £750, per day.
The cost for running customized training courses in Leicester is £695, per day.
Our price for running customized training courses in Lincoln is £695, per day.
Our price for running customized training in Liverpool is £750, per day.
Our charge for delivering customized training in Luton is £695, per day.
Our price for supplying in-house courses in Maidstone is £695, per day.
Our charge for supplying customized training in Manchester is £750, per day.
The fee for running on-site training courses in Mansfield is £750, per day.
The cost for running customized training in Medway Towns is £695, per day.
Our price for supplying on-site courses in Milton Keynes is £695, per day.
The cost for running customized courses in Newport is £750, per day.
Our price for delivering customized training in Northampton is £695, per day.
The charge for running customized courses in Norwich is £695, per day.
The charge for running customized training in Nottingham is £695, per day.
The cost for running customized training in Oxford is £695, per day.
Our fee for delivering on-site training courses in Peterborough is £695, per day.
Our fee for delivering on-site courses in Plymouth is £750, per day.
Our price for delivering on-site power training in Poole is £750, per day.
The cost of supplying customized training in Preston is £750, per day.
Our price for running Microsoft Excel power training courses in Reading is £695, per day.
Our price for running customized training courses in Sheffield is £695, per day.
Our price for delivering in-house courses in Slough is £695, per day.
The cost for running in-house courses in South Hampshire is £695, per day.
Our charge for delivering customized training courses in Southend-on-Sea is £750, per day.
Our price for running customized courses in Stoke-on-Trent is £695, per day.
Our price for delivering on-site power training in Sunderland is £750, per day.
Our charge for supplying customized training in Swansea is £825, per day.
Our price for supplying on-site courses in Swindon is £695, per day.
Our fee for delivering on-site power training in Teesside is £750, per day.
The cost for running customized training in Telford is £750, per day.
Our price for running Microsoft Excel power training courses in Thanet is £750, per day.
The cost for running customized training courses in The West Midlands is £695, per day.
Our charge for supplying customized training courses in Torquay is £750, per day.
Our price for running on-site training courses in Tyneside is £750, per day.
Our price for supplying in-house training courses in Warrington is £750, per day.
The cost for running on-site courses in West Yorkshire is £695, per day.
Our fee for delivering on-site power training in Wigan is £750, per day.
The charge for running customized training in Worcester is £750, per day.
Our price for running in-house courses in York is £750, per day.