Basic To Super Advance Excel Course – Hindi
Basic To Super Advance Excel Course – Hindi
Complete Guide to become a SUPER PRO in Super Advance Excel - Hindi language
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
Course Begins
-
Know Cell referencing and basic terms in excel
50:56 -
Vlookup with Match – Lookup your data
01:08:43 -
More Study on lookup and match
01:11:57 -
IF function & doubts on vlookup solved
01:14:57 -
In depth knowledge on IF functions
01:10:14 -
IF with Vlookup – Practical
01:07:32 -
Complex questions on IF function solved
54:47 -
Error hanlders IFError ISerror
01:03:52 -
Error Handler Continues
56:22 -
Data Extracting using Mid,Right,Left, Find, Search etc functions
01:04:36 -
More on Text Functions use -Real data
33:44 -
Count & Sum family functions
01:06:02 -
Index function- better than Vlookup?
01:09:25 -
Advance Filter with normal filters
57:55 -
Indirect with data validation- Super Powerful
52:21 -
Indirect continues
29:31 -
Indirect with dynamic Drop down
51:15 -
Amazing Exercise on IF & Find functions
51:15 -
offset function – my favourite
01:59:17 -
Create Charts and fundamentals
40:06 -
Charts – Part2
55:44 -
Pivots for Data reporting
43:35 -
Pivots – Continues
01:02:52 -
Conditional Formatting
53:56 -
Date Time functions
01:01:54 -
Offset with dashboard adv filter revision
03:13:25 -
Surprise Excel Test1
57:02 -
One more test to boost your confidence
01:33:56 -
Unique ,Sort and Filter – New functions
01:00:22 -
XLookup function
56:40 -
Activex Form Controls
52:26 -
ActiveX Controls – More knowledge
40:46 -
Introduction to Arrays- fundamentals
41:08 -
Introduction to Arrays- Know your OFFSET Array
01:03:01 -
Introduction to Arrays – project1
23:25 -
Extract before back slash
19:59 -
Pick the sales count from latest date if not zero
14:23
Basic To Super Advance Excel Course – Hindi
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
Course Begins
-
Know Cell referencing and basic terms in excel
50:56 -
Vlookup with Match – Lookup your data
01:08:43 -
More Study on lookup and match
01:11:57 -
IF function & doubts on vlookup solved
01:14:57 -
In depth knowledge on IF functions
01:10:14 -
IF with Vlookup – Practical
01:07:32 -
Complex questions on IF function solved
54:47 -
Error hanlders IFError ISerror
01:03:52 -
Error Handler Continues
56:22 -
Data Extracting using Mid,Right,Left, Find, Search etc functions
01:04:36 -
More on Text Functions use -Real data
33:44 -
Count & Sum family functions
01:06:02 -
Index function- better than Vlookup?
01:09:25 -
Advance Filter with normal filters
57:55 -
Indirect with data validation- Super Powerful
52:21 -
Indirect continues
29:31 -
Indirect with dynamic Drop down
51:15 -
Amazing Exercise on IF & Find functions
51:15 -
offset function – my favourite
01:59:17 -
Create Charts and fundamentals
40:06 -
Charts – Part2
55:44 -
Pivots for Data reporting
43:35 -
Pivots – Continues
01:02:52 -
Conditional Formatting
53:56 -
Date Time functions
01:01:54 -
Offset with dashboard adv filter revision
03:13:25 -
Surprise Excel Test1
57:02 -
One more test to boost your confidence
01:33:56 -
Unique ,Sort and Filter – New functions
01:00:22 -
XLookup function
56:40 -
Activex Form Controls
52:26 -
ActiveX Controls – More knowledge
40:46 -
Introduction to Arrays- fundamentals
41:08 -
Introduction to Arrays- Know your OFFSET Array
01:03:01 -
Introduction to Arrays – project1
23:25 -
Extract before back slash
19:59 -
Pick the sales count from latest date if not zero
14:23