Passionately developing careers since 1958.
Meirc LMS
Back
Advanced Excel: Functions, Power Query and Power Pivots

Advanced Excel: Functions, Power Query and Power Pivots

Why Attend

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.

Associations
Associations
Quick Enquiry Call Me Back
Quick Enquiry Call Me Back
Overview
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 Meirc 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
Associations
Associations
National Association of State Boards of Accountancy (NASBA)

Meirc Training & Consulting is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit.

Visit Website Check the Courses
Schedule & Fees

2024 Schedule & Fees

Location & Date
22 - 26 Sep, 2024
Dubai
Arabic
Venue to be assigned
Fees
Per participant

USD 5200

Fees + VAT as applicable

(including coffee breaks and a buffet lunch daily)

Discount Plans & Cancellations Policy

Meirc reserves the right to alter dates, content, venue, trainer, and to offer courses in an integrated virtual learning (IVL) format whereby face to face classroom participants and virtual learners participate simultaneously in the same course in an interactive learning experience.

Tax Registration Number: 100239834300003

Virtual Learning
HAVE A QUESTION?
+971 4 556 7213
+971 56 177 0877
+966 56 918 5532
[email protected]
Safa will help you find what you are looking for.