Download the Working Excel Files

VLOOKUP Function when the range is whole column

VLOOKUP Function for the specific range

Copy-paste techniques for the VLOOKUP formula

Difference between TRUE and FALSE in VLOOKUP Function

Carefulness for VLOOKUP Function

HLOOKUP function

MATCH function

INDEX function

Combination of INDEX and MATCH function in a single formula

Calling left column value from the source

Dynamic column index number in VLOOKUP function

OFFSET function

LOOKUP function

VLOOKUP function – Bonus Video

IF() Condition excluding ELSE

IF() Condition including ELSE

IFS() Condition excluding ELSE

IFS() Condition including ELSE

Nested IF() formula with AND OR

Setting a single conditional formatting

Setting multi-level conditional formatting in the same range

KPI reporting -1 using data bar (Positive-negative)

KPI reporting -2 using data bar (All Positive)

KPI reporting -3 Highlighting top or bottom values

KPI reporting -4 Using icon set

Customizing the icon set

Highlighting the duplicate values

Conditional formatting using formula

Conditional formatting using Color Scale

Add, Edit or remove conditional formatting

Clearing the Conditional Formatting

Finding out the conditional formatting applied cells

Overview of PivotTable

Creating PivotTable using one row and one column

Creating PivotTable report with multiple groups

Changing layout of PivotTable

Drill down your PivotTable value

Filtering the PivotTable Data

Formatting PivotTable Data

Adding a new calculated column

Creating PivotChart

Use Slicers in PivotTable

Conclusion of PivotTable

Data consolidation from multiple files using PowerQuery

Knowing the chart layouts and styles from the ribbon

Chart formatting from the ribbon

Creating chart taking data from non-continuous ranges

Recognizing Chart Elements

Changing chart type

Adding secondary axis

Column chart and column chart customization

Line chart and line chart customization

Pie chart and pie chart customization

Doughnut chart and doughnut chart customization

Waterfall Chart with customization

KPI presentation using 3 kinds of Sparklines

Thermometer Chart for target vs achievement

SUMIFS function

COUNTIFS function

SUMPRODUCT function

Live Dashboard techniques using SUMIFS, COUNTIFS and Chart

Entry restriction in a specific area of a sheet

Set the password to unprotect the sheet

Unprotect the sheet with correct password

Removing password to unprotect the sheet

What is VBA and Macro?

Why will you use VBA & Macro?

Example of few ready real files created using VBA & Macro

Configuring Excel to start VBA and Macro

Project 1 – Creating first macro

Running the macro clicking on a button

Project 2 – Macro running by shortcut key

Saving process of the Macro enabled file

Understanding how macro writes the scripts

Editing VBA code of a recorded macro

Adding new lines in VBA code

Conclusion of VBA and Macro

Overview of data validation

Setting data validation in a specific area

Changing data validation

Clearing data validation

Searching data validated set area