End User Applications Category Banner Image

Microsoft Excel - Expert

Length 1 day
Price NZD 495 exc GST

Course overview

View dates &
book now

Course locations >>

Why study this course

Become an Excel Expert using the Power Platform! This course is intended to help advanced Excel users supercharge their Excel skills and learn multiple ways to boost productivity.

Learn how to work with large data sources, transform and clean data using Power Query, and create an advanced data model in Power Pivot. Take advantage of Microsoft Excel’s cloud automation, Office Scripts, and Power Platform to further automate your spreadsheet both in the cloud and on your desktop. You will also be introduced to Power BI and see how quickly you can turn your data into actionable insights with just a few clicks.

This course builds on what was learned through Microsoft Excel - Beginner, Intermediate, and Advanced courses. After completing this course, you may be interested in one of our Excel Specialist courses.

Note: Excel 2016, Excel 2019, Excel 2021, and Excel 365 are all desktop application versions of Microsoft Excel. Excel 2016, Excel 2019, and Excel 2021 are the perpetual, bought-outright, stand-alone versions of the software; Excel 365 is the subscription-based version. Anyone working with any of these versions will be able to successfully complete this course. Throughout this course you will be using the desktop app; the course does not cover use of the web/online app.

Request Course Information

What you’ll learn

At the completion of this course you should be able to:

Transform and clean data in Power Query
Create data relationships
Create customised calculations in Power Pivot
Use Office Scripts for automation

Microsoft Apps at Lumify Work

Lumify Work has been delivering effective training across all Microsoft products for over 30 years. We are proud to be both Australia's and New Zealand’s first Microsoft Gold Learning Solutions Partner and the winner of the Microsoft MCT Superstars Award for FY24, which formally recognises us as having the highest quality Microsoft Certified Trainers (MCTs) in ANZ. Join more than 5,000 students who attend our quality Microsoft courses every year.

View all Microsoft Apps courses

Who is the course for?

This course is designed for experienced users of Excel who want to learn more about handling data in Power Query and building automations into their models.

Course subjects

Open Power Query

Open Power Query
Configure Power Query
Add Power Pivot
Modify the Quick Access Toolbar
Configure Power Query

Get Data

Import Data from:
- CSV File
- Excel Table, List, Pivoted Data
- Folder
- PDF File
Type in the Data
Load the Data to Excel

Transform Data

Applied Steps
Deleting Applied Steps
Adding Applied Steps
Editing Data using
- Fill Down
- Trim
- Replace Values
Filters
Modify the Filter Manually
Format
Removing Errors
Unpivoting Data

Working with Columns

Deleting Columns
Renaming Columns
Adding Columns
Standard and Custom Date Columns
Renaming Applied Steps
Create a Conditional Column

Working with Queries

Grouping Queries: Creating Query Groups
Appending Queries
View the Loaded Queries
Rename Queries
Reloading the Queries
View the Re-loaded Queries
Stop a Single Data Load
Stop Multiple Data Loads
Extended Exercise: Macro

Power Pivot

Data Model
Add Tables to a Data Model
Arrange the Data Model
Connect the Data Model using Create
Connect the Data Model using Manage
Connect the Data Model using Click and Drag
Pivot Table
Pivot Table from Excel
Pivot Table from Power Pivot
Measure in Power Pivot
Sum in DAX
Pivot Table for Measures
Measure with Filter

Data Types

Standard Data Types
Stock Data Type
Uploading Stock Data Type
Displaying Extra Data
Custom Data Type - Get Data
Custom Data Type - Transform Department Data
Custom Data Type - Transform Countries Data
Custom Data Type - Merge Data
Create Custom Data Type
Create Custom Report
Extend the Custom Report

Office Script

Load file into OneDrive
Office Script
Before You Record a Script
Start Recording
Edit the Script
Rename the Script
Change the Chart Title
Script a Dynamic Range
Script a Loop
Power Automate Online
Open Power Automate
Start Power Automate and Select Trigger
Add an Action
Add a Second Action
Check the Flow
Test the Flow

Power Automate Desktop

The Setup 104
Start Desktop from Online
Working with Excel
Launch Excel Action
Read from Excel Action
Create new List Action
Write to Excel Worksheet Action
Working with the Browser
Get the Converter Page
Looping Through Currencies
Create a For Each Action
Retrieving the Rates
Using the Recorder
Edit the Recording
Copy Actions
Save Value to a List Action
Running the PAD
Run the Actions
Finding the bug

Prerequisites

This course assumes the user is experienced with Excel features and tools. You should be familiar with most of the material covered in Excel - Intermediate and Advanced.

Terms & Conditions

The supply of this course by Lumify Work is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.