End User Applications Category Banner Image

Microsoft Excel - Expert

Length 1 day
Price NZD 495 exc GST

Course overview

View dates &
book now

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 - The Essentials, Level 2, and Level 3 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.

Nexacu Public Schedule

With Lumify Group's acquisition of Nexacu, we're pleased to now offer you the largest public schedule of end user applications training in Australia and New Zealand. As we move to consolidate our end user offering with Nexacu, as an interim measure you can now access the schedule of the most closely aligned courses and book, by clicking on the link below.

Book now!

Request Course Information

What you’ll learn

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

Work effectively with large datasets in Excel
Transform and clean data in Power Query
Create data relationships
Create customised calculations in Power Pivot
Use Office Scripts for automation
Create workflows with Power Automate
Create visualisations of your model in Power BI
How to connect Excel and Power BI data
Understand when to choose Excel or Power BI

Microsoft Apps at Lumify Work

Lumify Work is your best choice for training and certification in any of Microsoft’s leading technologies and services. We’ve been delivering effective training across all Microsoft products for over 30 years, and are proud to be Australia's and New Zealand’s first and largest Microsoft Gold Learning Solutions Partner. 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

Power Query

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

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 - Managing Data and Large Workbooks or Excel - Data Analysis and Reporting.

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.