Basic To Super Advance Excel Course 35 hrs– Hindi
Complete Guide to become a SUPER PRO in Super Advance Excel - Hindi language
Office Productivity,Microsoft,Excel VBA
Lectures -36
Duration -35 hours
30-days Money-Back Guarantee
Get your team access to 10000+ top Tutorials Point courses anytime, anywhere.
Course Description
- Know first the basic terms like Cells, rows, columns, address bar, formula and constants, name box, and 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. A 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 the 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 the 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 that 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 the 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 Count and Sum family Functions – COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, SUMIF, SUMIFS, MAXIFS
- Combination of these functions with each other by solving 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.
- Take 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, and 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 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 – it's a science that 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 to dashboards
- ActiveX and Form Controls
Goals
What will you learn in this course:
After finishing this course, you will never look back. You will never need any trainer to learn more. I have created this course for you keeping fundamentals, interview preparation, and most importantly practical real-life scenarios.
Theory can help us in understanding the subject but practical's makes you PRO. This course has a lot to give you in terms of real-life based challenges.
You will be able to implement everything in your office work or personal work after finishing the course. Pls do not skip any lecture as each of the session and sections are in a sequence and they are my live classroom recordings so your doubts too will get answered here automatically.
You shall be able to mix the formulas and create any complex formula and should be able to create dashboards too.
Prerequisites
What are the prerequisites for this course?
You need to install Excel application( Excel 2010 or above) and no prior knowledge is required. It has started from zero level and gradually turning into advance level.
Curriculum
Check out the detailed breakdown of what’s inside the course
Course Begins
36 Lectures
- Know Cell referencing and basic terms in excel 50:55 50:55
- Vlookup with Match – Lookup your data 01:08:42 01:08:42
- More Study on lookup and match 01:11:56 01:11:56
- IF function & doubts on vlookup solved 01:14:56 01:14:56
- In depth knowledge on IF functions 01:10:14 01:10:14
- IF with Vlookup – Practical 01:07:31 01:07:31
- Complex questions on IF function solved 54:46 54:46
- Error hanlders IFError ISerror 01:03:51 01:03:51
- Error Handler Continues 56:21 56:21
- Data Extracting using Mid,Right,Left, Find, Search etc functions 01:04:35 01:04:35
- More on Text Functions use -Real data 33:43 33:43
- Count & Sum family functions 01:06:02 01:06:02
- Index function- better than Vlookup? 01:09:24 01:09:24
- Advance Filter with normal filters 57:54 57:54
- Indirect with data validation- Super Powerful 52:21 52:21
- Indirect continues 29:31 29:31
- Indirect with dynamic Drop down 51:14 51:14
- Amazing Exercise on IF & Find functions 01:11:51 01:11:51
- Offset function – my favourite 01:59:16 01:59:16
- Create Charts and fundamentals 40:05 40:05
- Charts – Part2 55:43 55:43
- Pivots for Data reporting 43:35 43:35
- Pivots – Continues 01:02:51 01:02:51
- Conditional Formatting 53:55 53:55
- Date Time functions 47:41 47:41
- Offset with dashboard adv filter revision 03:13:24 03:13:24
- Surprise Excel Test1 54:33 54:33
- Unique ,Sort and Filter – New functions 01:00:21 01:00:21
- One more test to boost your confidence 12:28 12:28
- XLookup function 56:39 56:39
- Activex Form Controls 52:25 52:25
- ActiveX Controls – More knowledge 40:46 40:46
- Introduction to Arrays- fundamentals 41:07 41:07
- Introduction to Arrays- Know your OFFSET Array 01:03:00 01:03:00
- Introduction to Arrays – project1 23:24 23:24
- Extract before back slash 19:58 19:58
Instructor Details
ajay parmar
eCourse Certificate
Use your certificate to make a career change or to advance in your current career.
Our students work
with the Best
Related Video Courses
View MoreAnnual Membership
Become a valued member of Tutorials Point and enjoy unlimited access to our vast library of top-rated Video Courses
Subscribe nowOnline Certifications
Master prominent technologies at full length and become a valued certified professional.
Explore Now