course | Advanced Microsoft Excel Course

Data Management & Advanced Formulas
 

Data Consolidation:
 

• Merging data from different files and sheets.
• Consolidation techniques for various data sets.

Advanced Data Validation:
 

• Custom lists, date validations, and dynamic validation techniques.

Text and Cell Management Tools:
 

• Using LEFT, RIGHT, MID, CONCATENATE, and VALUE for efficient cell management.

Advanced Functions:
 

• SUMIF, SUMPRODUCT, COUNTIF, COUNTIFS, SUBTOTAL.
• VLOOKUP and XLOOKUP for advanced data lookups.

Text and Number Manipulation:
 

• Using TRIM, LEN, REPLACE, FIND, and SUBSTITUTE to manage data.
• Slicing dates into day names, weeks, quarters, and other date manipulations.

Advanced Reporting with Pivot Tables and Charts
 

Pivot Table Mastery:
 

• Essential rules for building and using pivot tables.
• Advanced sorting, filtering, and summarizing techniques.
• Grouping and drilling down into data.

Pivot Charts:
 

• Creating dynamic pivot charts and integrating them with reports.
• Linking pivot tables to PowerPoint for presentations.
• Advanced formatting techniques using slicers and GetPivotData.

Conditional Formatting:
 

• Applying formatting rules within pivot tables for visual insights.

Named Ranges, Data Formatting & Automation
 

Named Ranges:
 

• Creating and managing named ranges for simplified formulas.
• Updating and editing name ranges efficiently.

Advanced Data Formatting:
 

• Conditional formatting is based on criteria such as employee performance, grades, and salary levels.
• Setting up alerts and warnings for critical data points (e.g., residency expiry dates).

Macro Automation:
 

• Creating and managing macros to automate repetitive tasks.
• Assigning macros to custom toolbars for quick access.
• Using macros for monthly reports.

Advanced Calculations & Lookup Functions
 

Date & Time Calculations:
 

• Functions for calculating attendance, working days, and years of service.

Advanced Lookup Functions:
 

• VLOOKUP, HLOOKUP, INDEX, and MATCH for data retrieval.
• Combining multiple functions for dynamic data lookups.

Master Data Validation:
 

• Creating master sheets for employee statuses, locations, and grades.
• Custom validation for dates, numbers, and other criteria.

Leave Records & Calculations:
 

• Calculating eligibility for leave and bonuses using IF functions.
• Accrued leave and leave balance management.

End-of-Service Calculations & Advanced Charting

End-of-Service Calculations:
 

• Calculating gratuity and cashable leave for employees.
• Using Excel functions to manage complex HR calculations.

Advanced Charting Techniques:
 

• Creating advanced charts such as speedometers, spinners, and performance gauges.
• Dynamic charting for performance tracking.

Comprehensive Exercises:
 

• Practical exercises on gratuity calculations, salary increment simulations, and performance review scoring.
• Building performance dashboards for managerial reporting.