Five-day professional programmes in 14 citiesClassroom, online and in-house info@aractech.com +31 85 7444446

Professional training course

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 a... The outline covers Advanced Excel Functions and Features, Advanced Techniques in Creating and Cu...

  • 5 daysClassroom or online
  • 13Upcoming dates
  • 6Cities
  • €2,275Fees from

Choose your format and date

  • 5 days
  • 09:00–14:00
  • Certificate of attendance

Classroom sessions

  1. 7–11 Dec 2026

    Istanbul

    Next date
    €2,825 + VAT
    Register
  2. 11–15 Jan 2027

    Barcelona

    Lowest fee
    €2,275 + VAT
    Register
  3. 8–12 Feb 2027

    Geneva

    €3,315 + VAT
    Register
  4. 8–12 Mar 2027

    Munich

    €2,425 + VAT
    Register
  5. 12–16 Apr 2027

    Amsterdam

    €3,215 + VAT
    Register
  6. 10–14 May 2027

    Istanbul

    €2,825 + VAT
    Register
  7. 14–18 Jun 2027

    Rome

    €2,830 + VAT
    Register
  8. 12–16 Jul 2027

    Istanbul

    €2,825 + VAT
    Register
  9. 9–13 Aug 2027

    Barcelona

    Lowest fee
    €2,275 + VAT
    Register
  10. 13–17 Sep 2027

    Geneva

    €3,315 + VAT
    Register
  11. 11–15 Oct 2027

    Munich

    €2,425 + VAT
    Register
  12. 8–12 Nov 2027

    Amsterdam

    €3,215 + VAT
    Register
  13. 13–17 Dec 2027

    Istanbul

    €2,825 + VAT
    Register

Online / Live sessions

  1. 7–11 Dec 2026

    Live virtual classroom

    Next date
    €1,625 + VAT
    Register
  2. 11–15 Jan 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  3. 8–12 Feb 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  4. 8–12 Mar 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  5. 12–16 Apr 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  6. 10–14 May 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  7. 14–18 Jun 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  8. 12–16 Jul 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  9. 9–13 Aug 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  10. 13–17 Sep 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  11. 11–15 Oct 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  12. 8–12 Nov 2027

    Live virtual classroom

    €1,625 + VAT
    Register
  13. 13–17 Dec 2027

    Live virtual classroom

    €1,625 + VAT
    Register

Introduction

Course overview

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.

View full course outline

What you will achieve

Learning objectives

  • 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

Who should attend

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.

Methodology

Learning approach

  • 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.

Complete course outline

Five focused days of learning and application

The timings provided are estimates and may vary depending on the number of participants and the discussions taking place during the sessions. They are indicative only and do not constitute a binding commitment.

Day 1

Advanced Excel Functions and Features

  1. Table tool
  2. Naming cells and ranges
  3. Data validation using custom formulas
  4. Conditional formatting using formulas
  5. Functions:
  6. Text functions: TextSplit, TextJoin, TextAfter, TextBefore
  7. Coffee break
  8. Text functions: TextSplit, TextJoin, TextAfter, TextBefore (continued)
  9. Trim, Clean, Find/Search, Len
  10. IF, IFS, and using AND & OR
  11. CountIF, SUMIF, and AverageIF
  12. XLookup and Index & Match
  13. Sequence, Unique, Sort, Sort By, and Filter
  14. Formula auditing

Day 2

Advanced Techniques in Creating and Customizing Pivot Tables

  1. Number and cell format
  2. Report layout
  3. Grouping and un-grouping fields
  4. Default and customized sorting and filtering
  5. Sorting using a custom list
  6. Coffee break
  7. Sorting using a custom list (continued)
  8. Creating a calculated field
  9. Pivot charts
  10. Filtering using slicers and timelines
  11. Slicer settings and connections
  12. Customizing reports using the GetPivotData option

Day 3

Power Query: A Must-have Skill

  1. Get and transform: Link your Excel to external other data sources
  2. Excel files
  3. Text files
  4. Web
  5. Folder: all files and latest file
  6. Power Query to transform and clean up data:
  7. Naming, merging, splitting and removing columns
  8. Filtering rows
  9. Coffee break
  10. Fill, Replace, Clean, Trim, and Format
  11. Adding Date columns
  12. Adding columns from examples
  13. Adding custom columns and conditional columns
  14. Using Parameter
  15. UnPivot data
  16. Grouping
  17. Combining queries:
  18. Append
  19. Merge: The different types of joining data

Day 4

Power Pivot and the Data Model

  1. Benefits and drawbacks of PowerPivot
  2. Merging data from multiple tables using relationships
  3. Creating better calculations using the DAX Formulas
  4. Using DAX to create calculated fields
  5. Coffee break
  6. Using DAX to create calculated fields (continued)
  7. Formatting using DAX measures
  8. Date DAX measures
  9. Calculate and Related functions

Day 5

Excel Automation Tools

  1. Data types
  2. Currencies
  3. Geography
  4. Organization
  5. Coffee break
  6. Organization (continued)
  7. Stocks
  8. Automate routine tasks with office scripts (e.g., formatting sheets)
  9. Practical examples of using Macros

Ready to book?

Advanced Excel: Functions, Power Query and Power Pivots

Next session: 7 - 11 December 2026, Istanbul

Course FAQs

Questions about this course

What participants and training managers usually ask before booking Advanced Excel: Functions, Power Query and Power Pivots. Can't find your answer?

WhatsApp
What does Advanced Excel: Functions, Power Query and Power Pivots cover?

This course covers Accounting and Finance through a structured five-day outline focused on practical application, discussion, and implementation planning.

When is the next available session?

The next scheduled session starts on 7 - 11 December 2026. See the course schedules section for the available dates and delivery formats.

Who should attend this course?

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.

Is bilingual course delivery available?

Yes. All course materials and presentations are provided in English, while bilingual explanation and discussion support may be available depending on the selected location and trainer availability. For example, when attending a course in Istanbul, you may request a Turkish-speaking trainer to explain the English course content in Turkish. Arabic explanation support may also be requested at this location. Other languages are available in selected cities. You can choose your preferred language support as an optional preference during registration.

How can I register for a session?

Use any Register button next to the available course dates to open the participant registration page and submit your booking request for the selected session.

Is this course available online as well as classroom-based?

Check the course schedules section for currently published classroom and Online / Live sessions.

Where are classroom sessions delivered?

Current classroom venues include Istanbul, Barcelona, Geneva, Munich, Amsterdam, Rome.

How long is the training programme?

The programme runs for five days from 09:00 to 14:00, which gives 25 contact hours, including a daily 30-minute break (22.5 hours of instruction).