Working hard while feeling that you are wasting your time with meaningless, brainless, and tedious tasks is upsetting and frustrating, to say the least.

Spending time with manual work in Excel falls into this description, and unfortunately, that’s routine for many bright professionals out there.

Brilliant people in their professions find themselves having to spend a good chunk of their time repeating the same steps over and over every time they need to produce that same report based on data spit out by another program. Unfortunately, data provided by accounting programs and other business platforms does not usually come in the appropriate layout for analysis and immediate use for easy calculations with Excel.

Accountants, finance professionals, data analysts, business analysts, marketing analysts, HR analysts, sales managers, logistics managers, project managers, and almost any professional using Excel for more than data entry will face this frustration if they are not well-equipped with the appropriate Excel skills.

I see many wonderful professionals in different business areas, eager to do their work well and provide value for their organizations and themselves, but the time they spend with manual work in Excel to deliver their reports on time eats up their time at work and sometimes at home too.

The portion of data importation and data preparation is typically what takes longer in a reporting procedure if done manually. It is also the most boring part because it comprises brainless steps like copy-pasting, removing unwanted rows and columns, splitting columns, repeating formulas down the columns and adjusting cell and range references in formulas, charts and pivot tables, among other things.

Besides being painfully tedious, making these tasks manually makes the process prone to error.

It does not have to be that way. All these steps can be automated with Power Query. This tool is available in Excel, it is easy to learn and implement. By knowing the fundamentals of this tool, you will be able to improve considerably the data importation and data cleansing portion of your reporting procedures. This will not only save time for you and your team but will also help prevent human errors.

Many people have not heard of Power Query before, and others did, but have not found the time, the discipline or the opportunity to learn it well enough to feel confident to apply it to their daily business cases.

What will you learn in this training?

The main topics included in this Training are:

• Data Storage Layout versus Reporting Layout – the advantage of using Excel Tables and organizing data in a tabular layout.
• Introduction to Power Query
  • What Power Query is
  • Scenarios where to use Power Query
  • The data connectors available
  • The Power Query Editor
  • Loading destinations
  • Power Query settings and good practices
• Using Power Query to:
  • Remove unwanted rows and columns
  • Splitting and merging columns
  • When and how to unpivot columns
  • Cleaning data – consistent data formatting and other transformation features
  • Filling values down the column
  • Creating custom and conditional columns

The list of topics above is not exhaustive. We will cover other details not mentioned.

There are many ways to learn. One of the most effective ways is in-person learning, in a dedicated block of time and environment, immersing ourselves in the topic fully. No other distractions. Being together with other like-minded professionals and the instructor to whom we can ask questions while practicing along with the exercise files provides rich learning conditions to help you grasp the concepts quicker.

You can be anywhere in the world and do not need to travel.

Sessions are shorter and spaced out throughout a week to:

• avoid online and screen time fatigue,
• allow more time to let newly learned concepts to settle in and possibly apply them the next day at work,
• accommodate students from multiple time zones.

You need to bring your laptop with one of the following Excel desktop versions for Windows installed:

• Excel for Microsoft 365,
• Excel 2016,
• Excel 2019,
• Excel 2021

Please note:

– The training will be delivered in Excel for Microsoft 365 for Windows, and I cannot currently support students using Excel for Mac.

– Power Query for Excel for Mac is only available for the Microsoft 365 version with limited functionality and some differences in the user interface.

– Other Excel versions not mentioned above, including Excel for the Web, are not supported in this training.

This training is for anyone who:

• Deals with recurring reporting procedures that include:
  • Cleaning data in Excel that was imported from other sources (the most common are Excel files, CSV files, Text files, PDFs, databases and the web.)
  • Combining data from multiple files in a folder
  • Comparing datasets to find common elements or items that only belong to one of them
  • Crossing information between different data sets (typically done with formulas like VLOOKUP, XLOOKUP, and INDEX and MATCH)
• Never used Power Query in Excel or wants to consolidate the fundamentals of this tool to start applying it to their daily work to handle tasks like the ones previously mentioned.