D I F F I C U L T Y L E V E LBASIC | INTERMEDIATE | ADVANCED | PROFESSIONAL
Basic: Start from zero, learn the essentials and use the tools correctly with confidence.Intermediate: Designed for users who know the basics who want to sharpen their skills for work scenarios.Advanced: Built for power users who want to handle complex data, scenarios, and evolving requirements. Professional: Multiple-skilled training. Designed for users who want to use multiple methods to build business-ready solutions.
Embedded Files
T A R G E T P A R T I C I P A N T SThis course is designed for professionals who regularly work with medium-to-high volumes of data and require more robust, maintainable, and efficient Excel solutions. It is suitable for finance managers, accountants, HR managers, shared services personnel, project managers, revenue and operations analysts, and administrative professionals who have basic to intermediate Excel experience and wish to elevate their skills to a modern, advanced level.
HRDC claimable.
Click here for course outline >
Click here for course outline >
OUTLINE
OUTLINE
1. Introduction
1. Introduction
Excel platforms, data types & formula structures
The Formula Bar and the Insert Function tool
The Name Box, naming and editing cell ranges
Absolute and relative cell references
2. The new Array formula platform
2. The new Array formula platform
Evaluating dynamic arrays, array formulas & normal formulas
Spilled array behavior of formulas
What’s best?
3. Formula Auditing
3. Formula Auditing
Tracing Precedent Cells
Tracing Dependent Cells
Displaying Formulas
Error Checking, cells with missing dependents and tracing errors
Calculation options
4. Logical functions
4. Logical functions
IF, IFS, AND
5. Lookup-related functions
5. Lookup-related functions
VLOOKUP
HLOOKUP
XLOOKUP
6. Sum and Count functions
6. Sum and Count functions
SUMIF, SUMIFS, DSUM
COUNT, COUNTA, COUNTBLANK
SUMPRODUCT
7. Text functions
7. Text functions
TEXTJOIN, TEXTAFTER, TEXTBEFORE
TEXT
TEXTSPLIT
8. Date and Time functions
8. Date and Time functions
DATE, DATEVALUE
EDATE, EOMONTH, DAYS, DAYS360, NETWORKDAYS, NETWORKDAYS.INTL, NOW
9. Transposing & Stacking
9. Transposing & Stacking
TRANSPOSE
VSTACK, HSTACK
10. The Excel Table
10. The Excel Table
Overview of the Excel Table
Creating the Table
Filtering & sorting the Table
The Table Ribbon
Naming & resizing the Table
Table styles
Inserting slicers
Inserting formulas in the Table
Best practices on maintaining good data
11. The Pivot Table
11. The Pivot Table
Creating the Pivot Table
Populating the Pivot Table, formatting values & other Pivot Table options & settings
Slicers & Timelines
Pivot Charts
GETPIVOTDATA
12. Extracting & combining data with Power Query
12. Extracting & combining data with Power Query
Extracting data of various file types and source
Transforming extracted data
Merging and appending data
13. The OLAP Pivot Table
13. The OLAP Pivot Table
What is OLAP?
Introduction to the OLAP Pivot Table
Tables and Data Models
OLAP Pivot Table formulas
Page updated
Report abuse