Course Information
Registration period
Year-round Recruitment
Course Level
Study Mode
Duration
22 Hour(s) 12 Minute(s)
Language
English
Taught by
ajay parmar
Rating
Course Overview
Advance Filter, Conditional formatting. INDIRECT ,Data Validation , CountIFs,SumIfs,Offset,Charts,Form Controls,Arrays
This Part-2 you should watch if you are good with V-lookup ,Match and other basic formulas of excel. You may check Part1 before watching this.
This tutorial will take you to new level as we are discussing so many advance excel functions .As always, not just basic functions but their combinations as well - how it works in real excel life. You will then see some mini exercises as well that are going to make you crazy about advance Excel.
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.
How to use basic filters, sort features and super awesome Advance Filters with different criteria
How to use Conditional Formatting in excel with different examples - basic to advance
Assignments are added to support you and help you in monitoring the progress. Write me back if you will have questions. I will love to help you.
Taking a deep dive into ADVANCE FILTER from basics to advance.
First see how normal filter works with sort features.
Filter by values, colors, icons.
Sort by color, column wise, row wise, value wise.
What is advance 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's in advance filter - Get Fetch unique records
What is Filter in place and copy in advance filter.
How to make AND criteria's if you have multiple headers you like to filter.
Create OR Criteria's 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 level in conditional formatting's.
How Dates are stored in Excel. Why we must know the real data type of dates - text or number or what?
Dates can be added or subtracted - how and why it is possible
Date functions can be applied on every date or there is some restrictions. Is there any start or end date in excel for dates
Today function, now function, shorcut keys to insert date and time
How to calculate working days between two dates excluding weekends or any days
Calculate no of days between two dates excluding weekends or holidays
How to use DATEDIF function in excel
How to use Text function in dates and time functions
HOUR, SECOND, MINUTE Functions
How time formatting or values work secretely in excel
Can you add time value in time format. Why you can and why you cannot. Deep study
What are Arrays , why we need to know . How they work - what is the science behind it. Several amazing examples
Course Content
- 6 section(s)
- 31 lecture(s)
- Section 1 Count & Sum Family with Data Validation & Indirect function - Deep Dive
- Section 2 Advance Filter and Filters
- Section 3 Conditional Formatting
- Section 4 Date Time functions with OFFSET and CHARTS
- Section 5 Form & ActiveX controls - wow wow!
- Section 6 Super Smart ARRAYS
What You’ll Learn
- Students will be able to learn dynamic functions like INDIRECT data validation Count Sum family functions with the live exposure of working on excel.
- One of the most advance and complex function atleast appears in first sight is INDIRECT. Let us understand it from its root.
- How to use INDIRECT Function in different ways , Understand the indirect by definition and when it should be used and when can it throw an error.
- Deep dive into Count and Sum Family functions like Count, Sum, Count if, Sum if, CountIFs, SUMIFs,MAXIFs
- Difference between CountIFs, AverageIFs and SUMIFs with practical examples.
- Use of Wild Characters like ? and * - their meaning and use in these functions.
- I have added a project as well which uses INDIRECT with data validation together. It is an automation you can use to add and remove stock items.
- Data Validation rules and customization of error alerts using drop down.
- How to prevent data entry in excel spreadsheet using custom formula in data validation.
- How to use Name managers and mix it with INDIRECT Function. Basic to Advance
- How to create dynamic drop downs using INDIRECT plus data validation plus name managers. All in one.
- Normal Filters, Sort features plus advance Filter, Conditional Formatting - basic to advance
- Active X controls and form Controls
Skills covered in this course
Reviews
-
AAkash Kushwah
with clarity and understandable examples😊
-
MManimaran Nagarajan
Mr.ajay sir is a great teacher ..... he has very good knowledge and his teaching style is awesome .if anyone want to learn Excel then he is the right choice. thank you ajay sir
-
SSharad Gupta
Great learning al all . Never thought that Indirect is that match powerful before. Thanks for giving your guidance.
-
bbrian Cajigas
good but instructor goes too quickly through the most important steps
