- Advantage of Converting data into tables
- Structured formula
- Use of Slicers
- Dynamic Range
- Quiz Module 2
- Home
- Henry Harvin
- Courses
- Certified Advanced Excel Practitioner
Online
24 Hours
₹ 6,750 7,500
Quick facts
particular | details | ||
---|---|---|---|
Medium of instructions
English
|
Mode of learning
Self study, Virtual Classroom
|
Mode of Delivery
Video and Text Based
|
Frequency of Classes
Weekdays, Weekends
|
Course and certificate fees
Fees information
certificate availability
certificate providing authority
The syllabus
Module 1: Working With Tables
Module 2: Introduction to MS Excel
- Advantages of using MS Excel & Various purpose to use Excel
- Understanding of Workbook, Worksheet, columns, rows & Cells
- Ribbon & Tabs
- Quick access bar, Formula bar, Name bar, Status bar
- Keyboard Shortcuts & Navigation while using large data
- Different Use of Mouse
- Insert Comment
- Quiz Module 1
Module 3: Understanding Cell References
- Absolute Reference
- Relative Reference
- Mixed Reference
- 3D Reference
- Quiz Module 3
Module 4: Basics Functions of Excel
- Text to Column
- Flash Fill
- Auto Fill
- Quiz Module 4
Module 5: Sorting Data
- In Ascending & Descending Order
- Sort with Text, Numbers & Date
- Sort with formatting (Cell, Color, Font Color)
- In Multiple Order
- In Custom Order
- Quiz Module 5
Module 6: Filtering Data
- Applying Filters by text, numbers, or date on the Data
- Advanced Filter: Setting up Criteria to Filter the data
- Applying Complex Criteria
- Clearing the existing criteria
- Use Wildcard characters in Filter Criteria
- Quiz Module 6
Module 7: Data Consolidation/ Basics of Formula/ Functions
- From multiple Sheets
- From multiple Files
- Understanding Syntax, Intellisense, Suggestion box & optional argument
- Basic Calculations & BODMAS Rule
- Calculate Square, cube & Raise Number to Power
- Quiz Module 7
Module 8: If Functions and test Functions
- Simple If
- Combination of If with OR, AND & NOT
- Nested If Function
- LEN, TRIM, LEFT, RIGHT, MID, FIND, SEARCH, CONCATENATE, UPPER, LOWER, PROPER
- REPLACE, SUBSTITUTE, SEARCH, FIND
- Quiz Module 8
Module 9: Working With Dates, Countlifs, and Sumlfs
- Date & Time Formula
- COUNTIF, COUNTIES, SUMIF, SUMIFS
- Quiz Module 9
Module 10: Lookup and information Functions
- Lookup Functions - VLOOKUP. HLOOKUP. LOOKUP
- Disadvantage / Limitations of Vlookup
- Reference Functions - MATCH INDEX
- Simple XLookup
- Exact Match
- Approx Match
- Nested XLookup
- Case Sensitive Xlookup
- Multiple Answer column Xlookup
- Lookup Repeating Value (Using Filter)
- Quiz Module 10
Module 11: Using Nameda Ranges
- Defining and Using Range Names
- Using Named Ranges in Functions
- Quiz Module 11
Module 12: Data Validation
- By Numbers, Text Length, Date, List (dropdown)
- Input Message and Error Message
- Dependent Data Validation
- Dynamic Dropdown range with Table
- Quiz Module 12
Module 13: Dynamic Array Formula
- Spilled Ranges & use of #
- Sort
- Sort by
- Unique
- Filter
- Sequence
- Ifs
- Quiz Module 13
Module 14: Using Conditional formatting
- Applying Conditional Formatting
- Applying Multiple Rule Sets
- Creating Custom Rule Sets
- Viewing and Managing Rules
- Clearing Custom Rules
- Quiz Module 14
Module 15: Macros (only Recording, non VBA)
- Displaying the Developer Tab
- Review and Purpose of Macros
- Where to Save Macros
- Absolute and relative record
- Running macros: Assigning to Quick Access Toolbar, shapes
- Pictures and keyboard shortcuts
- Macro recording with charts & sorting Data
- Alter the recorded code
- User-Defined Function (UDF)
- Use prebuild VBA code
- Quiz Module 15
Module 16: Summarizing Data With Pivot Tables
- Creating Pivot Tables
- Rearranging Pivot Table Data
- Modifying Pivot Table calculations
- Drill-Down
- Filtering the Pivot data
- Summarizing Values by Sum. Count. Average. Max. and Product
- Show Values As % of Grand Total, % of Column Total, % of Row Total
- Grouping Fields
- Creating Calculated Column(s) in Pivot
- Creating Calculated Item(s) in Pivot
- Showing and Hiding the Grand Totals
- Refreshing data in pivot table
- Changing the scope of data source
- Quiz Module 16
Module 17: Working With Slicers
- Inserting and Using a Slicer
- Renaming the Slicer
- Changing Slicer Settings
- Formatting a Slicer
- Clearing the Slicer
- Quiz Module 17
Module 18: Power Pivot / Power Query
- Enable PowerPivot
- Understand Import Data, Transform data, Data Model to create a report
- Use Load, Load to & Transform Data
- Use of Power query to record steps for all files from a folder
- Add column, Update Column with Text, Numbers, & date
- Quiz Module 18
Module 19: Charts
- Selecting data
- Formatting your Chart
- Understanding Different Types of Charts
- Combo Chart
- Creating Custom chart types
- Use of Secondary Axis
- Using Trend Line in Charts
- Quiz Module 19
Module 20: Other Topics
- Format Painter
- Freeze Pans
- Removing Duplicates
- Transposing Data
- Paste Special
- Print Settings
- Custom Formatting
- Quiz Module 20
Module 21: Excel Dashboard
- Objective to Create Dashboard
- Create Pivot Table & Charts
- Design Dashboard
- Use Macro to Automatically Refresh all when row data is changed or new data is added
- Quiz Module 21
Module 22: Protecting and Security
- Password Protect workbook (file) for open & modify
- Password Protect Sheet
- Password protect workbook structure (Review => protect Sheet)
- Allow Edit Range with password
- Quiz Module 22
Module 23: Mail Merge
- Understand Mail Merge
- Quiz Module 23
Instructors
Ms CA Sabeena Vasudeva
Instructor
Freelancer
Mr Parvez B Ansari
Corporate Trainer
Freelancer
Ms CS Meenu Arya
Instructor
Freelancer