Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Basic to Super Advance Excel Training Part2 ( 21 Hours)
Rating: 4.8 out of 5(14 ratings)
104 students

Basic to Super Advance Excel Training Part2 ( 21 Hours)

Advance Filter, Conditional formatting. INDIRECT ,Data Validation , CountIFs,SumIfs,Offset,Charts,Form Controls,Arrays
Created byajay parmar
Last updated 3/2023
English

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

Course content

6 sections • 31 lectures • 22h 12m total length

  • Introduction to COUNT and SUM Family51:06
  • data validations1:21:02
  • Introduction to Indirect Name Managers/Ranges and Data Validation1:24:09
  • Indirect function - Project119:28

    this is not just a quick demo but also good for your learning provided you know everything about indirect. Come and join the course and take your skills to the next level.

  • Interesting Project on INDIRECT Function28:15
  • Indirect function - Project215:29

    this is not just a quick demo but also good for your learning provided you know everything about indirect. Come and join the course and take your skills to the next level.

  • Surprise Test - - All complex and advance functions - All in one57:01
  • Surprise Test - - All complex and advance functions - All in one1:33:56
  • Surprise Test - Indirect and Address together11:59

    One more quiz or test or example you can say on indirect function . This time indirect is used with address and this works like a magic

  • One more project - Indirect and Address22:25

    A problem faced by student is sent to me and i found it very interesting and challenging. Join me in this mini project and see once again the magic of INDIRECT and ADDRESS function together.

  • My other Series/Tutorials with links and coupons1:56
  • Create this unique excel Template - Pick OFFERS in random style25:11

Requirements

  • Good Understanding of Excel functions like Vlookup Match Index Text functions is a must.
  • You may check Part1 and Part2 if you like to learn everything mentioned above.

Description

  • 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

Who this course is for:

  • Students who want to make career in analytics field. Require advance excel knowledge
  • If you like to take your excel knowldegE to a new level, this 8 hours tutorial fits for you. See how complex tasks can be solved in a simple way
  • Incredible functions like INDIRECT ,SUMIFS,COUNTIFs and other dynamic features like name managers and data validations is discussed.

Report abuse