Advanced Excel

DURATION:  2 DAYS (16 HOURS)

DIFFICULTY LEVEL: ADVANCED

OUTLINE

1.       Array formulas

Evaluating array formulas vs normal formulas

How to apply legacy CSE formulas

How to apply new platform array formulas

2.       Logical functions

IF

IFS (Microsoft 365, Excel 2021, Excel 2019 only)

3.       Lookup functions

This segment includes basic and advance application of functions.

VLOOKUP

XLOOKUP

4.       SUM & COUNT functions

Learn how to apply each function in different crucial scenarios.

SUMIF, SUMIFS

DSUM

COUNT, COUNTA, COUNTBLANK

5.      Other functions

FILTER

SORT

6.       An introduction to Table

Tables, structured data and maintaining records

Creating the Table, the Filter button and other Table functions

Slicers

7.       The Pivot Table

Creating the Pivot Table from a flat file

Populating the Pivot Table, formatting values & other Pivot Table options & settings

Slicers & Timelines

Pivot Charts

GETPIVOTDATA

8.       The OLAP Pivot Table

What is OLAP?

Introduction to the OLAP Pivot Table

Tables and Data Models

OLAP Pivot Table formulas

9.       Form Controls & Drop-down boxes

ComboBox

Scroll Bar

Data Validation List

10.   Extracting & combining data with Power Query

Extracting data of various file types and source

Transforming extracted data

Merging and appending data

Combining worksheets from a folder

11.   Excel charts

Introducing new graphs and charts in Excel

The primary & secondary axis

Chart styles

Custom chart templates

Sparklines

12.   Conditional Formatting

What is Conditional Formatting?

Conditional Formatting Rules, Data Bars, ColorScales and Icon Sets

Creating and managing formatting rules

Advance formatting using formula