Big Sale:17Courses in 4000 Rs, 10courses in 3500 Rs, 7 in 3000 Rs

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

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

Excel Begins

  • Introduction to Cell Referencing – Must know for Beginners
    54:52
  • 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:11
  • 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:23
  • 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:02
  • 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:12
  • 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
899.00
899.00 4,400.00

Basic To Super Advance Excel Course – English

899.00
899.00 4,400.00

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

Excel Begins

  • Introduction to Cell Referencing – Must know for Beginners
    54:52
  • 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:11
  • 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:23
  • 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:02
  • 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:12
  • 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