Certified Advanced Excel Practitioner

Henry Harvin

Online

24 Hours

₹ 6750 7500

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

Module 1: Working With Tables

Advantage of Converting data into tables
Structured formula
Use of Slicers
Dynamic Range
Quiz Module 2

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

Popular Courses

Popular Platforms

Popular Searches

Certified Advanced Excel Practitioner

Online

24 Hours

₹ 6750 7500

Quick Facts

Course and certificate fees

Fees information

certificate availability

certificate providing authority

The syllabus

Module 1: Working With Tables

Module 2: Introduction to MS Excel

Module 3: Understanding Cell References

Module 4: Basics Functions of Excel

Module 5: Sorting Data

Module 6: Filtering Data

Module 7: Data Consolidation/ Basics of Formula/ Functions

Module 8: If Functions and test Functions

Module 9: Working With Dates, Countlifs, and Sumlfs

Module 10: Lookup and information Functions

Module 11: Using Nameda Ranges

Module 12: Data Validation

Module 13: Dynamic Array Formula

Module 14: Using Conditional formatting

Module 15: Macros (only Recording, non VBA)

Module 16: Summarizing Data With Pivot Tables

Module 17: Working With Slicers

Module 18: Power Pivot / Power Query

Module 19: Charts

Module 20: Other Topics

Module 21: Excel Dashboard

Module 22: Protecting and Security

Module 23: Mail Merge

Instructors

Articles

Popular Articles

Latest Articles

Trending Courses

Popular Courses

Popular Platforms

Learn more about the Courses

Thank You!

Download Careers360 App

All this at the convenience of your phone