Advance Excel

Advanced Excel

Advance Excel training course details

Advanced Excel course covers advanced functions and features of Microsoft Excel such such as nested IF statements, VLOOKUP, HLOOKUP, INDEX MATCH, and SUMIFS, data analysis tasks such as data cleaning, filtering, sorting, and pivot table creation, creating and automating tasks using macros, data visualization using interactive dashboards, charts, and graphs, conditional formatting, what-if analysis, and data validation. The course provides a comprehensive understanding of the advanced features and functions of Microsoft Excel and equips individuals with the skills and knowledge required to use Excel for complex data analysis and reporting tasks

Advance Excel training programme modules

  • Basic Understanding Menu and Toolbar
  • Introduction to different category of functions
  • Basics, Mathematical and Statistical
  • Date and Time, Logical
  • Paste Special & Format Painter
  • Lookup and References
  • Text and Information.
  • Conditional Formatting |Number Formatting
  • Date Filtering (Basic | Advance)
  • Shorting | Custom Sorting
  • Data split, Data Validation, Text Function (Data Separation),
  • Rand, Rand-between, Round-up | Round-down| Round, Sum-product
  • User form Function in Excel
     
  • Concatenate
  • Lower, Upper, Proper,
  • Trim, Len, Substitute
  • Find & Search
  • Left, Right, Mid
     
     
  • Formula Intro IF Function with Logic Arguments
  • Nesting With if
  • And + OR Formulas
  • IF Error Implementation
     
     
  • Count, Count A, Count Blank, Count-if, Count-ifs, D-count, D-CountA
  • Sum, Sum-if, Sum-ifs, D-sum
  • Average, Average-if, Average-ifs, D-Average
  • Max & Large
  • Min & Small
  • Abs, Subtotal
     
     
  • Protecting Sheet, Remove Duplicates,
  • Hyperlink Linking | Hyperlink Website | Hyperlink Objectives & File | Hyperlink Sheet Cells
  • Header & Footer
  • Page Setup (Margins, Size, Printing Area)
  • Print Titles | Content Printing Solutions
     
  • Day and Time Formula
  • Today, Now, Date-Value, Time-Value
  • Day, Year, Month
  • Weekday & Dated-IF
  • EO-Month, E-date,
  • Net Workdays & NETWORKDAYS.INTL
  • Workday & WORKDAY.INTL
     
  • V-Lookup,
  • H-Lookup,
  • Match, Match +V-lookup
  • Offset,
  • Indirect,
  • Address,
  • Column, Columns, Row, Rows,
  • Choose, Choose + Array,
  • Arrays Concept in Lookup Formula’s,
  • Past Special, Past link
     
  • Pivot Intro
  • Use of Row Fields, Column Fields & Report Filters, Value Field Setting
  • Grouping, Arrange Date Field & Pivot Range Dynamic & Report Layouts
  • Use Of Percent of Grant Total | ROW | Column Total | Difference Form & Percent Difference Form | Percent Of | Running Total |Ranking In Pivot
  • Slicer in Pivot & Connection with Slicer
  • Use of Legends in Pivot Charts
  • Dashboard Using Slicer & Pivot Charts
  • Dynamic Pivot, Dynamic Chart
  • What-if Analysis
  • Goal Seek, Recording & Assign Macros

1. Introduction, Basic Understanding

  • Basic Understanding Menu and Toolbar
  • Introduction to different category of functions
  • Basics, Mathematical and Statistical
  • Date and Time, Logical
  • Paste Special & Format Painter
  • Lookup and References
  • Text and Information.
  • Conditional Formatting |Number Formatting
  • Date Filtering (Basic | Advance)
  • Shorting | Custom Sorting
  • Data split, Data Validation, Text Function (Data Separation),
  • Rand, Rand-between, Round-up | Round-down| Round, Sum-product
  • User form Function in Excel

2. Text Formulas

  • Concatenate
  • Lower, Upper, Proper,
  • Trim, Len, Substitute
  • Find & Search
  • Left, Right, Mid

3. Logical Formulas

  • Formula Intro IF Function with Logic Arguments
  • Nesting With if
  • And + OR Formulas
  • IF Error Implementation

4. Statistical Functions

  • Count, Count A, Count Blank, Count-if, Count-ifs, D-count, D-CountA
  • Sum, Sum-if, Sum-ifs, D-sum
  • Average, Average-if, Average-ifs, D-Average
  • Max & Large
  • Min & Small
  • Abs, Subtotal

5. Link & Page Setup

  • Protecting Sheet, Remove Duplicates,
  • Hyperlink Linking | Hyperlink Website | Hyperlink Objectives & File | Hyperlink Sheet Cells
  • Header & Footer
  • Page Setup (Margins, Size, Printing Area)
  • Print Titles | Content Printing Solutions

6. Date & Time Formulas

  • Day and Time Formula
  • Today, Now, Date-Value, Time-Value
  • Day, Year, Month
  • Weekday & Dated-IF
  • EO-Month, E-date,
  • Net Workdays & NETWORKDAYS.INTL
  • Workday & WORKDAY.INTL

7. V-lookup, H-lookup, Numeric Calculation, Match Index, Choose, Array

  • V-Lookup,
  • H-Lookup,
  • Index, Index + Match
  • Match, Match +V-lookup
  • Offset,
  • Indirect,
  • Address,
  • Column, Columns, Row, Rows,
  • Choose, Choose + Array,
  • Arrays Concept in Lookup Formula’s,
  • Past Special, Past link

8. Pivot Table - MIS, Data Analysis & Visualization

  • Pivot Intro
  • Use of Row Fields, Column Fields & Report Filters, Value Field Setting
  • Grouping, Arrange Date Field & Pivot Range Dynamic & Report Layouts
  • Use Of Percent of Grant Total | ROW | Column Total | Difference Form & Percent Difference Form | Percent Of | Running Total |Ranking In Pivot
  • Slicer in Pivot & Connection with Slicer
  • Use of Legends in Pivot Charts
  • Dashboard Using Slicer & Pivot Charts
  • Dynamic Pivot, Dynamic Chart
  • What-if Analysis
  • Goal Seek, Recording & Assign Macros
Scroll to Top