Advanced Excel Masterclass – SKILLEXO by Stat Modeller

About Course

More than 90% of the professionals use MS Excel in their day to day life. Excel is a powerful tool that has become entrenched in business processes worldwide—whether for analyzing stocks or issuers, budgeting, or organizing client sales lists.

This course is specially designed by keeping the CA,CS,CFA,FRM,CAIA, CIPM in mind BUT its equally useful to others also.

Enroll now to go through a deep dive of the most popular spreadsheet tool on the market, Microsoft Excel. As a Trainer, I will use my 10+ years of training experience to guide you step by step through the beginner to advanced level in this journey.

Starting with the fundamentals will offer you a strong foundation and more understanding as you continue to topics at the intermediate and advanced levels. We have also tried to focus on the small small things while dealing with excel which can make daily tasks more easier.

At completion of this course you will have mastered the most popular Excel tools and come out with confidence to complete any Excel tasks with efficiency and grace. Below are just a few of the topics that you will master:

Introduction to Excel
Basic Mathematical Functions
Absolute and Relative References
Conditional Formatting
Group and Ungroup
Text Functions
Advanced Text Functions
Date Functions
Logical Functions
Data Validation
Lookup Functions
Is Functions
Round Functions
Sorting
Pivot Tables
Different Charts
Dashboards

Master Microsoft Excel from Beginner to Advanced
Learn the most common Excel functions used in the Office
Maintain large sets of Excel data in a list or table
Apply dynamic formulas with IF, VLOOKUP, INDEX, MATCH functions and many more
Create dynamic reports by mastering one of the most popular tools, PivotTables
Access to a Professional Trainer with 10+ years of Excel Training

Course Content

Basic Mathematical Functions
In this session, we are going to learn some basic mathematical functions.

Min, Max, Sum, Average

08:20
Count, CountA, Countblank

06:54
SumIF AverageIF, CountIF, SumProduct

10:51
Large, Small

05:06
Rank

09:09
Absolute

03:39
Aggregate

06:27

Absolute and Relative References
In this session, we will learn most common but important concept of cell referencing.

Conditional Formatting
In this session, we will learn feature which will make boring data table into colorful, stylish and dashing table.

Group and Ungroup
In this session, we will learn how to use grouping and ungrouping of columns as well as rows.

Text Functions
In this session, we will learn various text functions to clean and organize the text data properly in analyzable format.

Advanced Text Functions
Here, we will learn some advanced text functions such as find, search, replace etc...

Date Functions
We will learn some basic and advanced date functions to use date and time dynamically. Also, we will cover calculation of working days by setting up holidays dynamically.

Paste Special
Paste Special is a wonderful tool to increase productivity specially while copying data from some other table or sources.

Logical Functions
We will learn one of the most efficient functions to perform calculations dynamically based on the condition called IF.

Data Validation
We will learn data validation functionality in Excel. It will cover how we can set specific inputs in the cells so that data entry errors can be avoided.

Lookup Functions
In this session, we will learn how to use various lookup functions such as VLOOKUP and HLOOKUP with various examples.

Is Functions
In this session, we will learn IS function which is used to validated/ check current data for specific format/ type.

Round Functions
It is very important to round up values specially in accounting. There are various ways you can round up the values. We will learn that.

Sorting
We will learn various sorting options in this session such as sorting yb text, numeric, date etc...

Pivot Tables
Pivot tables are the magical tools in the Excel to summarize large data in a concise format.

Varieties of Charts and its importance
There are various charts can be prepared in excel but we need to know which chart can be prepared for which type of data.

Interactive Dashboards
Data visualization is one of the key elements of report/MIS presentation nowadays, which can be done through a modern tool like interactive dynamic dashboards.

Basics of Dashboard

09:11
Case Study

04:39
Blueprint of Dashboard

02:55
Prepare Cards using Pivot Table

05:05
Summary and Line Chart

03:33
Bar Chart and Pie Chart

03:13
Geo Map

04:20
Formatting of Chart

10:38
Geo Map Formatting and Summary Table

04:25
Title Logo and Cards

08:23
Final Dashboard

10:03

About Course

What Will You Learn?

Course Content

Basic Mathematical Functions In this session, we are going to learn some basic mathematical functions.

Min, Max, Sum, Average

Count, CountA, Countblank

SumIF AverageIF, CountIF, SumProduct

Large, Small

Rank

Absolute

Aggregate

Absolute and Relative References In this session, we will learn most common but important concept of cell referencing.

Relative Reference

Absolute Reference

Mixed Reference

Conditional Formatting In this session, we will learn feature which will make boring data table into colorful, stylish and dashing table.

Highlight Cell Rules

Top/ Bottom Rules

Data Bars

Color Scale

Icon Set

Group and Ungroup In this session, we will learn how to use grouping and ungrouping of columns as well as rows.

Column Grouping

Row Grouping

Row and Columns Grouping

Text Functions In this session, we will learn various text functions to clean and organize the text data properly in analyzable format.

Text Cleaning

Text Cases

Text Join

Text to Column-Delimited

Text to Column-Fixed width

Advanced Text Functions Here, we will learn some advanced text functions such as find, search, replace etc...

Left, Right, Middle

Len

Find, Search, Replace, Substitute

Date Functions We will learn some basic and advanced date functions to use date and time dynamically. Also, we will cover calculation of working days by setting up holidays dynamically.

Date function Overview

Today and Now Function

Day, Month, Year Function

Adding and Subtracting from Date

Net Working Days

Paste Special Paste Special is a wonderful tool to increase productivity specially while copying data from some other table or sources.

Paste Special

Logical Functions We will learn one of the most efficient functions to perform calculations dynamically based on the condition called IF.

Logical Operators Part 1

Logical Operators Part 2

IF Function

Nested IF Function

IF with AND Function

IF with OR Function

Data Validation We will learn data validation functionality in Excel. It will cover how we can set specific inputs in the cells so that data entry errors can be avoided.

Whole Number

Decimal

List

Date and Time

Text Length

Lookup Functions In this session, we will learn how to use various lookup functions such as VLOOKUP and HLOOKUP with various examples.

VLOOKUP Concept

VLOOKUP – Exact Match Part 1

VLOOKUP – Exact Match Part 2

VLOOKUP – Approximate Match

HLOOKUP – Exact Match

HLOOKUP – Approximate Match

Is Functions In this session, we will learn IS function which is used to validated/ check current data for specific format/ type.

IS Functions

Round Functions It is very important to round up values specially in accounting. There are various ways you can round up the values. We will learn that.

ROUND, ROUNDUP, ROUNDDOWN

CEILING and FLOOR

MROUND

Sorting We will learn various sorting options in this session such as sorting yb text, numeric, date etc...

Sort by Numeric Data

Sort by Text Data

Sort by Date Data

Pivot Tables Pivot tables are the magical tools in the Excel to summarize large data in a concise format.

Importance of Pivot Table

Convert Normal Table into Excel Table

Use of Filter in Pivot Table

Slicer

Varieties of Charts and its importance There are various charts can be prepared in excel but we need to know which chart can be prepared for which type of data.

Importance of Visualization