LIVE Advanced Excel Formulas Masterclass

4,499.00

Description

Course Structure

EXCEL BASICS (for Beginners)

Structure of the Excel working area,
How to navigate in Excel,
Ribbons and tabs,
Quick Access Toolbar (QAT)
Dialog Box & Task Panes,
Customizing ribbons and QAT,
What is an active cell
How to use ALT shortcuts

Once you know are comfortable with the layout of Excel, the next step is to learn how to enter and edit data in Excel.

You can enter three types of data in Excel – numeric, text, and formulas.

To enter data in any cell, just select the cell and start typing. Once done, press enter to move to the cell below. In case you are entering dates, enter it in a format that Excel recognizes as a date (such as 01-01-2020 or 01 Jan 2020 or 01/01/2020).

This video will show you how to enter data in Excel and edit already added data (such as deleting data, modifying existing data and replacing data).

Apart from this, it also covers the auto-fill in Excel as well as number formatting. 

Click here to download the example Excel file

This video covers the basics of data formatting in Excel.

You can format data in Excel to make it look better and to highlight some data points. For example, if you have data-set and you want to highlight the negative numbers, you can simply highlight it in red color to it.

Here are some of the common formatting I use to make my data look better and more readable

Applying borders to all cells
Making headers bold
Giving a background cell color to headers.
Center align the headers

You can find these options in Home tab in the Font category.

Click here to download the example Excel file

In this video of this Free Online Excel Training, you’ll learn how to work with cells and ranges in Excel.

Since a worksheet in Excel is made up for cells, if you want to become proficient in Excel, you need to know how to best work with cells and ranges.

And this video will teach you exactly that.

It covers the following topics:

Cell address nomenclature
Selecting cell/ranges in Excel
Copying and moving cells/ranges in Excel
Using paste special
Selecting special cells
Using find and replace to select cells
Adding comments and
Using quick analysis

Click here to download the example Excel file

In this video of this Free Online Excel Training, you’ll learn how to manage worksheets in Excel.

It covers the following topics:

Navigating through worksheets in Excel
Changing worksheet name and color
Adding/deleting worksheets
Hiding/Un-hiding worksheets
Moving/copying worksheets
Comparing worksheets side by side
Splitting worksheet into panes
Freeze panes
Saving and opening workbooks

EXCEL ESSENTIALS

Click here to download the example Excel file

Excel tables are amazing, especially if you plan to work with a lot of data/dashboards/models. 

Cover the following topics:

Creating an Excel Table
Excel Table features
Structured references
Table slicers

Note that table slicers became available only from Excel 2013 version onward.

Click here to download the example Excel file

In this lesson, I cover the following topics:

Autofill: This allows you to quickly fill data in contiguous cells. For example, you can have ‘Jan’ and ‘Feb’ in two adjacent cells and then you can use Autofill to get the other month names
Custom Lists: Custom lists allows you to create your own lists that you can use in Autofill. For example, if you have 20 names, you can create a custom list and then use Autofill to get all the names by just dragging the mouse
Flash Fill: Flash fill is a new feature in Excel 2013 and allows you to identify a pattern in data and perform data slice and dice based on the pattern

This covers the following topics:

How to access number formatting options
Using number formatting options using a dialog box
Using custom number formatting

Remember that formatting a number in Excel only changes how it’s displayed, but the underlying value remains the same.

EXCEL FORMULAS

Click here to download the example Excel file

This covers the following topics:

Constituents of Excel formula
Operators in formulas
Entering a formula in Excel
Editing a formula
Absolute/relative cell references
Copying/pasting formulas in Excel, and
Formula auditing and debugging.

Click here to download the example Excel file

Basic Functions:
AND function
OR function
NOT function
TRUE function
FALSE function
IF function
IFERROR function
IS function

Extra:
Avoid Nested IF with VLOOKUP
Using IFERROR with VLOOKUP

Click here to download the example Excel file

Learn about the MATH formulas

INT function
MOD function
RAND function
RANDBETWEEN function
ROUND function
SUM function
SUMIF function
SUMIFS function
SUMPRODUCT function

Click here to download the example Excel file

Learn about the following LOOKUP and REFERENCE formulas:

VLOOKUP function
HLOOKUP function
INDEX function
MATCH function
OFFSET function
INDIRECT function
ROW function
ROWS function
COLUMN function
COLUMNS function

Additional Functions:


VLOOKUP Vs INDEX MATCH
VLOOKUP with multiple criteria
Case sensitive VLOOKUP
Use VLookup to Get the Last Number in a List in Excel
Lookup the Second, the Third, or the Nth Value in Excel
Find the Last Occurrence of a Lookup Value a List in Excel

Click here to download the example Excel file

Learn about the following STAT formulas:

AVERAGE function
AVERAGEIF function
AVERAGEIFS function
COUNT function
COUNTA function
COUNTBLANK function
COUNTIF function
COUNTIFS function
LARGE function
SMALL function
RANK function
MAX function
MIN function

Related Function:
Weighted Average in Excel
Standard Deviation in Excel
AVERAGEIFS in Excel

Click here to download the example Excel file

Learn about the following TEXT formulas:

LEFT function
RIGHT function
MID function
LEN function
LOWER function
PROPER function
UPPER function
FIND function
REPLACE function
SUBSTITUTE function
TEXT function

Related Excel Formulas:

Word Count in Excel
Split Cells in Excel
Extract a Substring in Excel
Concatenate Excel Ranges
Find Position of the Last Occurrence of a Character in a String in Excel

Click here to download the example Excel file

Learn about the following DATE and TIME formulas:

DAY function
HOUR function
MINUTE function
DATE function
DATEVALUE function
TODAY function
NOW function
WEEKDAY function
NETWROKDAYS function
NETWORKDAYS.INTL function
WORKDAY function
WORKDAY.INTL function

Related Excel Formulas:

Calculate Number of Days between two dates
Calculate Time in Excel (Time Difference, Hours Worked, Add/ Subtract)
Excel DATEDIF Function
Excel Holiday Calendar Template
Calculate Age in Excel
Insert Date and Time Stamp in Excel
Convert Time to Decimal Number in Excel
How to Remove Time from Date/Timestamp in Excel

Click here to download the example Excel file

Some advanced formula examples that involves using one or more than functions.

I cover the following topics:

Compare Lists, Get Unique List
Get the Closest Match
Get Last Value in the List
Find Last Occurrence of an Item in a List
2 Way and 3 Way Lookup using Index/Match
Count the Number of Words
Extract Username from Email Id
Find the First Monday of the Month
Extract Data using Drop Down List

Related Excel Formulas:

Compare two columns in Excel
Extract Username from Email
INDEX MATCH (Examples)
How to Compare Two Excel Sheets (for differences)
20 Advanced Excel Functions and Formulas (for Excel Pros)

DATA ANALYSIS

Learn about using Named Ranges in Excel.

It covers the following topics:

Creating Named Ranges
Managing Named Ranges
Important Keyboard Shortcuts
Creating Dynamic Named Ranges using OFFSET and INDEX formulas

Related Excel Formulas:

Named Ranges in Excel
Picture Lookup in Excel
Excel OFFSET Function

Learn about using data validation and drop-down lists.

It covers the following topics:

Data Validation Criteria
Input Message
Customizing Error Messages
Circle Invalid Entries
Advanced Data Validation Tricks (such as Create Dependent Validation, Make Sub Headings in Validation, Disguise Numbers as Text, Creating Dynamic Drop Down List)

Related Excel Functions:

Drop Down list in Excel
Dependent Drop Down List in Excel
Extract Data from Drop Down List Selection in Excel.
Select Multiple Items from a Drop Down List in Excel.
Creating a Dynamic Excel Filter Search Box.
Display Main and Subcategory in Drop Down List in Excel.
How to Insert Checkbox in Excel.
Using a Radio Button (Option Button) in Excel

Click here to download the example Excel file

Learn about data sorting and filtering in Excel. It covers the following topics:

Data Sorting
Multi-Levels of Sorting
Data Filtering
Advanced Filtering
Filtering and Sorting in Excel Data Tables

Related Excel Functions:

Data Sorting in Excel
Multiple Level Data Sorting in Excel
Sort Worksheets in Excel (VBA)
Sort Data in Alphabetical Order Automatically
Sort by Color in Excel
Sort Data using VBA
Delete rows based on cell value in Excel

Learn about the amazing conditional formatting.

It covers the following topics:

Introduction to Conditional Formatting
Using Formula in Conditional Formatting
Advanced Examples (Highlight Every Nth Row, Creating Dynamic Search)

Related Excel Formulas:

Conditional Formatting in Excel
Find Duplicates using Conditional Formatting
Search and Highlight Data Using Conditional Formatting
Highlight Every Other Row in Excel
Highlight Blank Cells in Excel
Creating Heat Maps in Excel

INTRODUCTION TO EXCEL CHARTING

Learn about Excel charting and how to make useful charts quickly

Excel has a lot of inbuilt charts that you can use instantly to visualize your data. And there are many combination charts and advanced charts you can create to pack a lot of information in a single chart.

It covers the following topics:

Excel Chart Types
Chart Editing
Formatting Chart Elements
Combination Charts
Handling Gaps in Charts

Related Excel Topics:

Combination Charts in Excel
Add Trendlines in Excel Charts
Add Secondary Axis to Charts in Excel
Creating a Pie Chart in Excel
Excel Area Chart

Click here to download the example Excel file

How to use some tricks to create advanced charts in Excel.

The following advanced charts are covered:

Multi-Category Charts
Gauge Chart
Thermometer Chart
Milestone Chart
Water Fall Chart
Gantt Chart
Chart with Trend Arrows in Data Labels
Actual Vs. Target Values
Spotting Data in a Scatter Chart in Excel
Dynamic Target Line in Excel Bar Charts

Additional Charts in Excel

Sparklines in Excel
Step Chart in Excel
Sales Funnel Chart in Excel
Waffle Chart in Excel
Pareto Chart in Excel
Histogram in Excel

 

Click here to download the example Excel file

How to create dynamic charts in Excel.

Dynamic charts allow you to change the chart based on user selection. For example, you can update the chart as soon as you change the scroll bar or click a button.

Two detailed examples of creating dynamic charts from scratch

Example 1 – Creating a dynamic chart that updates as soon as you use the scroll bar to change the year value.

Example 2 – Creating a dynamic chart that plots the data on a scatter chart based on the column you select. There is a drop-down list that allows you to select which data series should be plotted on the X-axis and which should be plotted on the Y Axis

Related Charts:

Creating Dynamic Chart Range in Excel
Dynamic Chart with Selection Box
Dynamic Target Line in Bar Charts
Creating Dynamic Chart Titles

PIVOT TABLE

Click here to download the example Excel file

How to create and use Pivot Tables in Excel. 

The video covers the following topics:

Introduction to Pivot Tables
Creating a Pivot Table in Excel
Formatting the Pivot Table
Grouping data in an Excel Pivot table
Calculated Field/Items in Pivot Table
Pivot Table Slicers
Pivot Charts

Pivot Tables are amazing as it allows you to quickly summarize a huge amount of data with a few clicks. Even if you’re using Excel for the first time, you can quickly get a hang of it.

Related Excel Topics:

Creating a Pivot Table in Excel – A Step by Step Tutorial
Preparing Source Data For Pivot Table
How to Filter Data in a Pivot Table in Excel
How to Group Dates in Pivot Tables in Excel
How to Group Numbers in Pivot Table in Excel
How to Apply Conditional Formatting in a Pivot Table in Excel
Using Slicers in Excel Pivot Table – A Beginner’s Guide
How to Add and Use an Excel Pivot Table Calculated Field
How to Refresh Pivot Table in Excel
Delete a Pivot Table in Excel
Pivot Cache in Excel – What Is It and How to Best Use It

 

EXCEL EXTRAS FEATURES

How you can lock cells or protect worksheets and workbooks in Excel.

You can protect cells, worksheets, and workbooks in Excel with a password (or without one). Once a worksheet is protected, you’ll need to enter the password to access it (in case a password has been applied).

Additional :

Lock cells in Excel
Lock formulas in Excel

Quick introduction to Excel macros and the VB Editor in Excel.

By recording a macro in Excel, you can easily automate a lot of tasks. And the best part is that you don’t need to know any coding or VBA to do this.

When you record a macro, Excel simply follows whatever you do in the workbook and create a code for it. Now you can run this code and Excel will automatically follow all the steps you showed it.

If you’re a VBA beginner, recording a macro and understanding the code using it can be a great way to learn.

This video lesson covers the following VBA topics:

How to record a macro in Excel
Absolute and Relative Cell References
Introduction to the Visual Basic Editor.

VBA Additional Works:

Working with Cells and Ranges in Excel VBA (Select, Copy, Move, Edit)
Working with Worksheets using Excel VBA
Using Workbook Object in Excel VBA (Open, Close, Save, Set)
Understanding Excel VBA Data Types (Variables and Constants)
Excel VBA Loops – For Next, Do While, Do Until, For Each (with Examples)
If Then Else Statement in Excel VBA (explained with examples)
For Next Loop in Excel VBA – A Beginner’s Guide with Examples
How to Assign a Macro to a Button in Excel

 

Advanced Formula in Excel:

The function is used to look up for a piece of information in a large segment of data and pull that data to your newly formed table. You have to visit the function option. The insert function tab will let you enter ‘VLOOKUP’, or you can find it in the list. Once it is selected, a wizard box will open with a different set of box options.

This function is most popularly used to sum a group of numbers in a particular column. The sum function has a dedicated button for itself in the Home tab, which can be used to sum after selecting the part of the column.

This function is used to pull the maximum and the minimum values from the selected set of data. To get the maximum value, you have to enter ‘MAX’ in the function tab, and to get the minimum value; you have to enter ‘MIN’. The function will automatically draw the values from the primary data table.

The IF function is used to pull truth and false information regarding a parameter set in the variable box. The IF statement is written or broken as:

SUMIF function helps you to look up for a certain set of information that matches your criteria. The criteria are entered in a wizard box which contains the range tab, criteria tab, and the sum range tab.

The only difference between the previous function and the COUNTIF function is that the latter does not add up the matching values from the cells. It just pulls and shows the set to you.

This function is used to set more than one criterion for searching the variables. If the variable matches with the multiple criteria, then the value is returned as True, or else the search shows FALSE. The wizard box has tabs where you can enter a logical set of criteria to find the behavior of the selected set of data. The result comes with another column of TRUE and FALSE on the right side.

The OR function is a little different from the previous AND Function. OR function checks for only one criterion to be TRUE and pulls the value, whereas the AND needs every criterion to match to give a TRUE result. If no criterion matches, then the FALSE value comes out.

The LEFT function helps you to pull the part of the data in a selected column from the left side. You can set the variable or the extent of data you want in your new column via commands.

You can pull apart data from the selected column set from the right side of the information by setting variables in the command box.

This function is the combination of both LEFT and RIGHT Functions in Excel, where a new column of data is prepared by setting the variable to pull a particular section of the data from left and right.

This function is used to round up data with a lot of digits after the decimal point for the convenience of calculation. You do not need to format the cell.

This proper function is used to capitalize or uppercase the letters of a sentence in the cells. It can be done in a customized way. You can selectively change the letters in whatever format you want.

The NOW function is used to insert a column that defines the time when the entry of data is done in that particular cell on the right side of the NOW column. You can change the NOW value to date also.

This advanced excel formula is used to change the case of the cells in aggregate. The letters are changed to the command you give, like from lower to upper or vice versa, and bold them by Proper command.

This advanced excel formula is used to trim or remove extra spaces which appear when a set of data is copied from another source.

This advanced excel formula helps you to extract the customized MAX and Min values from the selected set of cells as per their orders and degrees. You can select the fifth-highest among the large data pool easily by inputting proper commands in the array.

This advanced excel formula is used to remove lengthier IF function statements and to pull the particular set of data you want. It is used when there are more than two outcomes for a given condition.

This code is used to get as many times as a character in a cell.

This advanced excel formula is very handy when you are working on a spreadsheet created by someone else. This command helps you to track down the type of data the cell is having.

This advanced excel formula lets you generate a random number between the values you have set. It helps when you want to simulate some results or behavior in the spreadsheets.

You do not have to seek help from Google all the time to find out the converted value of data in different units. The CONVERT() advanced excel formula will do the rest. The versatile function can also be used to convert currencies and many other things.

The PV function n Excel is a versatile virtual financial expert which can calculate the rate, periods for investment, payment per period, future value, and other arguments based on the input of the variables.

There are a lot of functions that are used to show a piece of information regarding time.
WEEKNUM() is used to get the particular number of the week at a particular date.
WORKDAY() gives the date of the working day when the number of days is specified.
NETWORKDAYS() gives the number of working days between two supplied dates by excluding the weekends and holidays.
YEARFRAC() allows a user to find out the fraction of the year when two dates are supplied.
EDATE() gives the specific date when a the number of days after a particular date is mentioned.

There are a set of commands for various mathematical applications to calculate values in different segments of mathematics. Finding aggregate, difference, total, quotient, LCM, GCD, etc., is a cakewalk with Excel.