Basic To Super Advance Excel Course – English
Basic To Super Advance Excel Course – English
• Complete Guide to become a SUPER PRO in Super Advance Excel
Created by Ajay Parmar
Course Description
- Know first the basic terms like Cells, rows, columns, address bar, formula and constants, name box , shortcut keys.
- How to use Vlookup and what are rules to be followed while using Vlookup. From its limitations to its advantages – Deep discussion
- Use vlookup within sheets, across sheets and from different workbooks.
- Why locking the cells and unlocking is important to learn . Practical example is given using Vlookup.
- How to sort out issues in Vlookup if Lookup is repeated, What happens if lookup are more than one – Which one to use and Why?
- Vlookup using constants , using helper columns or rows and finally using Match function.
- How to use Match function as a standalone and why it is important to learn Match – Vlookup with Match Magic waiting for you,
- How to use IF Functions including nested IFs. Covering everything about IF – Basic Single IF , IF AND, IF OR and IF inside IF – Nested ones – Super advance level
- How to use IF with Vlookup and match , How to use MATCH with IFs. Practical questions you face in your office.
- New function IFs which is launched in 2021 – see the difference between new IF and old IFs.
- Learn how to use a new function in Office 365 – XLOOKUP – deep discussion.
- Take a deep dive into learning most used and versatile functions in excel like IFERROR, ISERROR, MID LEFT RIGHT INDEX MATCH..
- How and WHY are the two questions – We keep asking ourselves in this course throughout.
- Why INDEX is better than VLOOKUP
- How INDEX helps in achieving the results which VLOOKUP cannot.
- How to select the data in INDEX – Is it full data or we can be selective in data selection.
- Which error handler is better? ISERROR or IFERROR.
- What happens if we leave column or row parameters empty in INDEX
- What are Boolean functions and what is their role in solving the complex data
- How to overcome the confusion of using IF or IFERROR while working with real data.
- How to take help from these error handlers and make your VLOOKUP work like a loop – How about using 3 or 4 or even more than 4 Vlookups .
- Learn the use of TEXT Functions and how to mix them for your data extraction requirement.
- INDEX with MATCH or IFERROR with INDEX or LEFT function with INDEX and IFERROR – There is no limit to mix the functions if you know fundamentals. We are discussing all this one by one by taking practical data points.
- Introduction to INDIRECT Function, Use of indirect in real life. It is considered the most dynamic and powerful function when it comes to linking the data in a structured manner.
- Introduction to ADDRESS function. What happens when indirect and address functions come together. It is mind blowing.
- Using INDIRECT how we can solve complex data problems like data wrong alignments and even in dashboards you can use it.
- What is a NAME MANAGER. How to create name managers. Their use with Indirect function.
- Learn how to make simple drop downs and dynamic powerful drop downs using indirect and name managers
- Learn how to link one drop down with another drop down. Dynamic drop downs and use them in your dashboards.
- Discussing about Count and Sum family Functions – COUNT,COUNTA,COUNTBLANK,COUNTIF,COUNTIFS,SUMIF,SUMIFS,MAXIFS
- Combination of these functions with each other by solving the real excel problems like how to combine Vlookup with SUMIF or COUNTIF – Fully practical scenarios.
- Use of wild characters in Count Sum functions – use of * and ? . Unbelievable magic happens here.
- Taking a deep dive into ADVANCE FILTER from basics to advance.
- First see how a normal filter works with sort features.
- Filter by values, colors, icons.
- Sort by color, column wise, row wise, value wise.
- What is an advanced filter and why it is required in Excel so much.
- What is the difference between Advance Filter and Filters. Are they same or totally different in terms of objectives and functionality?
- Learn How to extract data using criteria in advance filter – Get Fetch unique records
- What is Filter in place and copy in advance filter.
- How to make AND criterias if you have multiple headers you like to filter.
- Create OR Criteria using Advance Filter. Rules to follow while fetching unique records.
- How to use logic in advance filter using wild characters like * and ?
- Using formulas to extract complex data points
- Learning everything about conditional formatting – basic and advance
- How to color cells based on values
- How to color cells using formulas
- How to insert icons using conditional formatting.
- How to highlight duplicate or unique values
- How to highlight values if they are repeating more than 2 times or any nth instance Learn every option given in conditional formatting.
- How to set priorities in conditional formatting.
- Discussing the all Date and Time functions and also how to deal with Complex Date and Time formats.
- How dates are stored in excel and if we need to break down.
- How times are stored in excel and what happens if you want to split the time into seconds or minutes or hours.
- How to split date and time – its a science which you need to understand. What happens behind the scenes.
- Complete walk-through on OFFSET function.
- OFFSET as a normal function – for movements
- OFFSET as an array function – customize your data using it.
- Charts making and important key points to be considered before making them.
- Use and role of OFFSET in charts – Super advance
- Excel Charts for Data Visualization
- Excel Pivots for Dashboards and Reports
- Excel Slicers to bring smoothness in dashboards
- ActiveX and Form Controls
Course Curriculum
Excel Begins
-
Introduction to Cell Referencing – Must know for Beginners
54:53 -
Introduction to Vlookup and Match – So Powerful Part1
01:03:08 -
Introduction to Vlookup and Match – Part2
01:05:59 -
Introduction to IF Function
01:02:10 -
VLOOKUP IF TRIM Match – All in One – Great Project
01:27:25 -
Approximate Vlookup for you
16:25 -
Vlookup Exercise – Student question my solution
11:32 -
XLOOKUP
44:47 -
XLOOKUP – Dynamic range
47:05 -
New Function OFFICE 365- IFS & Vlookup with Different Workbook
25:27 -
Introduction to Error Handlers – IFERROR & ISERROR From fundamentals to real use
01:04:22 -
Powerful Text Functions with Practical’s – LEFT RIGHT MID FIND SEARCH SUBSTITUE
46:34 -
Introduction to Versatile Index and Match functions
55:16 -
Powerful Project on INDEX function for you – Can you solve it?
20:45 -
Excel Test for you INDEX with IF – Very Advance
55:46 -
Introduction to COUNT and SUM Family
51:06 -
Data validation
01:21:03 -
Indirect Name Managers/Ranges and Data Validation
01:24:10 -
Indirect function – Project1
19:29 -
Interesting Project on INDIRECT Function
28:16 -
Indirect function – Project2
15:30 -
Surprise Test – – All complex and advance functions – All in one
57:01 -
Surprise Test – – All complex and advance functions – All in one
01:33:56 -
Introduction to Advance Filter
01:10:13 -
more on Advance Filter
59:19 -
Deep dive into advance Filter
26:57 -
Introduction to Conditional Formatting
53:59 -
Introduction to Conditional Formatting
01:10:18 -
Date Time functions
01:01:54 -
OFFSET
01:05:55 -
How to Create Charts and How to use OFFSET in Charts – Magnificent
01:28:07 -
Line chart, pie chart, doghnut chart and combo chart
33:11 -
Special Speedometer Chart – Explained fully
26:03 -
Special Speedometer Chart – Explained fully part2
40:50 -
Learn Form Controls and ActiveX Controls
01:30:29 -
Introduction to Arrays- fundamentals
41:08 -
Introduction to Arrays- Know your OFFSET Array
01:03:01 -
Introduction to Arrays – project1
23:25 -
Extract the value after back slash- powerful Project2
19:59 -
How to check if table is sorted or not
08:28 -
How to sum odd and even numbers-student question
12:23 -
Extract numbers from a String – Super advance – longest array formula
33:33 -
Sum Unique Values
11:02 -
Find out the latest date Sales – Question for you
13:41 -
Excel Template Challenge – How to create random list
25:12
Basic To Super Advance Excel Course – English
Course Description
- Know first the basic terms like Cells, rows, columns, address bar, formula and constants, name box , shortcut keys.
- How to use Vlookup and what are rules to be followed while using Vlookup. From its limitations to its advantages – Deep discussion
- Use vlookup within sheets, across sheets and from different workbooks.
- Why locking the cells and unlocking is important to learn . Practical example is given using Vlookup.
- How to sort out issues in Vlookup if Lookup is repeated, What happens if lookup are more than one – Which one to use and Why?
- Vlookup using constants , using helper columns or rows and finally using Match function.
- How to use Match function as a standalone and why it is important to learn Match – Vlookup with Match Magic waiting for you,
- How to use IF Functions including nested IFs. Covering everything about IF – Basic Single IF , IF AND, IF OR and IF inside IF – Nested ones – Super advance level
- How to use IF with Vlookup and match , How to use MATCH with IFs. Practical questions you face in your office.
- New function IFs which is launched in 2021 – see the difference between new IF and old IFs.
- Learn how to use a new function in Office 365 – XLOOKUP – deep discussion.
- Take a deep dive into learning most used and versatile functions in excel like IFERROR, ISERROR, MID LEFT RIGHT INDEX MATCH..
- How and WHY are the two questions – We keep asking ourselves in this course throughout.
- Why INDEX is better than VLOOKUP
- How INDEX helps in achieving the results which VLOOKUP cannot.
- How to select the data in INDEX – Is it full data or we can be selective in data selection.
- Which error handler is better? ISERROR or IFERROR.
- What happens if we leave column or row parameters empty in INDEX
- What are Boolean functions and what is their role in solving the complex data
- How to overcome the confusion of using IF or IFERROR while working with real data.
- How to take help from these error handlers and make your VLOOKUP work like a loop – How about using 3 or 4 or even more than 4 Vlookups .
- Learn the use of TEXT Functions and how to mix them for your data extraction requirement.
- INDEX with MATCH or IFERROR with INDEX or LEFT function with INDEX and IFERROR – There is no limit to mix the functions if you know fundamentals. We are discussing all this one by one by taking practical data points.
- Introduction to INDIRECT Function, Use of indirect in real life. It is considered the most dynamic and powerful function when it comes to linking the data in a structured manner.
- Introduction to ADDRESS function. What happens when indirect and address functions come together. It is mind blowing.
- Using INDIRECT how we can solve complex data problems like data wrong alignments and even in dashboards you can use it.
- What is a NAME MANAGER. How to create name managers. Their use with Indirect function.
- Learn how to make simple drop downs and dynamic powerful drop downs using indirect and name managers
- Learn how to link one drop down with another drop down. Dynamic drop downs and use them in your dashboards.
- Discussing about Count and Sum family Functions – COUNT,COUNTA,COUNTBLANK,COUNTIF,COUNTIFS,SUMIF,SUMIFS,MAXIFS
- Combination of these functions with each other by solving the real excel problems like how to combine Vlookup with SUMIF or COUNTIF – Fully practical scenarios.
- Use of wild characters in Count Sum functions – use of * and ? . Unbelievable magic happens here.
- Taking a deep dive into ADVANCE FILTER from basics to advance.
- First see how a normal filter works with sort features.
- Filter by values, colors, icons.
- Sort by color, column wise, row wise, value wise.
- What is an advanced filter and why it is required in Excel so much.
- What is the difference between Advance Filter and Filters. Are they same or totally different in terms of objectives and functionality?
- Learn How to extract data using criteria in advance filter – Get Fetch unique records
- What is Filter in place and copy in advance filter.
- How to make AND criterias if you have multiple headers you like to filter.
- Create OR Criteria using Advance Filter. Rules to follow while fetching unique records.
- How to use logic in advance filter using wild characters like * and ?
- Using formulas to extract complex data points
- Learning everything about conditional formatting – basic and advance
- How to color cells based on values
- How to color cells using formulas
- How to insert icons using conditional formatting.
- How to highlight duplicate or unique values
- How to highlight values if they are repeating more than 2 times or any nth instance Learn every option given in conditional formatting.
- How to set priorities in conditional formatting.
- Discussing the all Date and Time functions and also how to deal with Complex Date and Time formats.
- How dates are stored in excel and if we need to break down.
- How times are stored in excel and what happens if you want to split the time into seconds or minutes or hours.
- How to split date and time – its a science which you need to understand. What happens behind the scenes.
- Complete walk-through on OFFSET function.
- OFFSET as a normal function – for movements
- OFFSET as an array function – customize your data using it.
- Charts making and important key points to be considered before making them.
- Use and role of OFFSET in charts – Super advance
- Excel Charts for Data Visualization
- Excel Pivots for Dashboards and Reports
- Excel Slicers to bring smoothness in dashboards
- ActiveX and Form Controls
Course Curriculum
Excel Begins
-
Introduction to Cell Referencing – Must know for Beginners
54:53 -
Introduction to Vlookup and Match – So Powerful Part1
01:03:08 -
Introduction to Vlookup and Match – Part2
01:05:59 -
Introduction to IF Function
01:02:10 -
VLOOKUP IF TRIM Match – All in One – Great Project
01:27:25 -
Approximate Vlookup for you
16:25 -
Vlookup Exercise – Student question my solution
11:32 -
XLOOKUP
44:47 -
XLOOKUP – Dynamic range
47:05 -
New Function OFFICE 365- IFS & Vlookup with Different Workbook
25:27 -
Introduction to Error Handlers – IFERROR & ISERROR From fundamentals to real use
01:04:22 -
Powerful Text Functions with Practical’s – LEFT RIGHT MID FIND SEARCH SUBSTITUE
46:34 -
Introduction to Versatile Index and Match functions
55:16 -
Powerful Project on INDEX function for you – Can you solve it?
20:45 -
Excel Test for you INDEX with IF – Very Advance
55:46 -
Introduction to COUNT and SUM Family
51:06 -
Data validation
01:21:03 -
Indirect Name Managers/Ranges and Data Validation
01:24:10 -
Indirect function – Project1
19:29 -
Interesting Project on INDIRECT Function
28:16 -
Indirect function – Project2
15:30 -
Surprise Test – – All complex and advance functions – All in one
57:01 -
Surprise Test – – All complex and advance functions – All in one
01:33:56 -
Introduction to Advance Filter
01:10:13 -
more on Advance Filter
59:19 -
Deep dive into advance Filter
26:57 -
Introduction to Conditional Formatting
53:59 -
Introduction to Conditional Formatting
01:10:18 -
Date Time functions
01:01:54 -
OFFSET
01:05:55 -
How to Create Charts and How to use OFFSET in Charts – Magnificent
01:28:07 -
Line chart, pie chart, doghnut chart and combo chart
33:11 -
Special Speedometer Chart – Explained fully
26:03 -
Special Speedometer Chart – Explained fully part2
40:50 -
Learn Form Controls and ActiveX Controls
01:30:29 -
Introduction to Arrays- fundamentals
41:08 -
Introduction to Arrays- Know your OFFSET Array
01:03:01 -
Introduction to Arrays – project1
23:25 -
Extract the value after back slash- powerful Project2
19:59 -
How to check if table is sorted or not
08:28 -
How to sum odd and even numbers-student question
12:23 -
Extract numbers from a String – Super advance – longest array formula
33:33 -
Sum Unique Values
11:02 -
Find out the latest date Sales – Question for you
13:41 -
Excel Template Challenge – How to create random list
25:12