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