Advanced Excel: Functions, Power Query and Power Pivots
The "Advanced Excel: Functions, Power Query, and Power Pivots" training course delves into advanced, and most recently added, Excel functions like XLOOKUP, TEXTJOIN, TEXT…
The "Advanced Excel: Functions, Power Query, and Power Pivots" training course delves into advanced, and most recently added, Excel functions like XLOOKUP, TEXTJOIN, TEXTSPLIT, UNIQUE, SORT, and FILTER, enabling you to streamline your data tasks with ease. The course will cover Power Query to import and transform data from various sources, such as Excel files, folders, PDFs, and websites, preparing it for further analysis and reporting. Additionally, the course introduces Power Pivots, which teaches you how to create sophisticated data models, establish relationships between tables, and craft decisive DAX measures for complex calculations. This course is designed to elevate your Excel skills, enhance your productivity, and provide you with the tools needed for advanced data analysis and reporting.
Course Methodology
This is a hands-on course with practical applications using Microsoft Excel 2021 or Office 365 throughout the five days. All examples, exercises, and cases are practiced in Excel.
Course Objectives
By the end of the course, participants will be able to:
Apply key Excel functions to prepare data for analysis and reporting
Utilize pivot tables functions and calculations to generate a set of management and business analysis reports
Utilize Power Query to clean up and prepare data for reporting
Report and analyze big data using Power Pivots
Utilize Excel automation solutions to speed up work
Target Audience
Accountants, senior and junior accountants, business analysts, accounting and finance professionals, business analysts, research professionals, and staff from any function who need to master and upgrade their skills in Excel functions, pivot tables, Power Query, and Power Pivots and work with big data analysis.
Target Competencies
Excel functions
Power Query
Practicing pivot tables
Utilizing Power Pivot
Reporting
Analyzing business data
Note
This is a hands-on training course using laptops, which Arab will provide for the duration of the course.
Course Outline
Advanced Excel Functions and Features
Table tool
Naming cells and ranges
Data validation using custom formulas
Conditional formatting using formulas
Functions:
Text functions: TextSplit, TextJoin, TextAfter, TextBefore
Trim, Clean, Find/Search, Len
IF, IFS, and using AND & OR
CountIF, SUMIF, and AverageIF
XLookup and Index & Match
Sequence, Unique, Sort, Sort By, and Filter
Formula auditing
Advanced Techniques in Creating and Customizing Pivot Tables
Number and cell format
Report layout
Grouping and un-grouping fields
Default and customized sorting and filtering
Sorting using a custom list
Creating a calculated field
Pivot charts
Filtering using slicers and timelines
Slicer settings and connections
Customizing reports using the GetPivotData option
Power Query: A Must-have Skill
Get and transform: Link your Excel to external other data sources
Excel files
Text files
Web
Folder: all files and latest file
Power Query to transform and clean up data:
Naming, merging, splitting and removing columns
Filtering rows
Fill, Replace, Clean, Trim, and Format
Adding Date columns
Adding columns from examples
Adding custom columns and conditional columns
Using Parameter
UnPivot data
Grouping
Combining queries:
Append
Merge: The different types of joining data
Power Pivot and the Data Model
Benefits and drawbacks of PowerPivot
Merging data from multiple tables using relationships
Creating better calculations using the DAX Formulas
Using DAX to create calculated fields
Formatting using DAX measures
Date DAX measures
Calculate and Related functions
Excel Automation Tools
Data types
Currencies
Geography
Organization
Stocks
Automate routine tasks with office scripts (e.g., formatting sheets)
Practical examples of using Macros
Schedule & Fees
2026 Schedule & Fees
There are no public sessions remaining for the course this year. Please contact us if you wish to have a customized in-house session, or to find out how an additional public session can be scheduled.