Get 17 Courses In 5,999/- Rupees & Get 10 Courses In 4,000/-

Log In
Registration

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

Material Includes

  • Files are attached for you to download

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:15
  • IF with Vlookup – Practical
    01:07:32
  • Complex questions on IF function solved
    54:46
  • Error hanlders IFError ISerror
    01:03:51
  • 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:43
  • Count & Sum family functions
    01:06:03
  • Index function- better than Vlookup?
    01:09:25
  • Advance Filter with normal filters
    57:55
  • Indirect with data validation- Super Powerful
    52:22
  • Indirect continues
    29:32
  • 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:01
  • 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
₹1,000.00
₹1,000.00 ₹4,400.00
  • Level
    All Levels
  • Total Enrolled
    30
  • Duration
    35 hours
  • Last Updated
    February 10, 2023

Basic To Super Advance Excel Course – Hindi

₹1,000.00
₹1,000.00 ₹4,400.00
  • Level
    All Levels
  • Total Enrolled
    30
  • Duration
    35 hours
  • Last Updated
    February 10, 2023

Material Includes

  • Files are attached for you to download

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:15
  • IF with Vlookup – Practical
    01:07:32
  • Complex questions on IF function solved
    54:46
  • Error hanlders IFError ISerror
    01:03:51
  • 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:43
  • Count & Sum family functions
    01:06:03
  • Index function- better than Vlookup?
    01:09:25
  • Advance Filter with normal filters
    57:55
  • Indirect with data validation- Super Powerful
    52:22
  • Indirect continues
    29:32
  • 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:01
  • 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

The beautiful thing about learning is that nobody can take it away from you.

Twitter Facebook-f Instagram Telegram-plane Youtube

Quick Links

Imp. Links

Weekly Newsletter

  • Copyright 2022-23 AjayParmarTutor. All Rights Reserved