Live Seminar: Mastering Microsoft Excel for Advanced Users

Conducted by a Certified Microsoft Trainer, this course will give you a deep understanding of the Advanced Excel formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. We teach through hands-on, contextual examples designed to showcase why these formulas are awesome and how they can be applied in a number of ways. This course also gives you a deep, 100% comprehensive understanding of Excel Pivot Tables and Excel Pivot Charts.

Pre-requisites
-Basic – Intermediate Microsoft Office Excel
-Participants must use their own laptop to apply lessons and work on exercises.

Hardware/Software Requirements
-Microsoft Excel 2016 / 2019 version
-Mac laptop/ Mac book version is not applicable

What you'll learn
At the end of the course, participants will be able to:
-Simplify complex formulas using Name Manager
-Apply different Excel formulas like Text, Date, Logical and Lookup Functions
-Join datasets from multiple sources with Excel’s LOOKUP, INDEX & MATCH functions
-Create Dynamic Reports and Dashboards using Pivot Tables and Charts
-Automate Reports using Macro Recording Tools

TRACK 1: IDENTIFY AND DEFINE THE ESSENTIAL COMPONENTS OF EXCEL FORMULAS
a. Define Names on Cell References
b. Using Create from Selection Method in Name Manager
c. Application of Indirect formula with Name Manager
d. Simplify the ff. Number Functions SUM, COUNT AVERAGE, MAX MIN with Name Manager

TRACK 2: PERFORMING ADVANCED FORMULAS
a. Create Solutions in TEXT data using the ff. text functions Change Case, Trim Case, Combine Case and Find and Replace text methods.
b. Formulate Date and Time functions to streamline aging report
c. Using IF condition and Logical functions like SUMIFS, COUNTIFS, AVERAGEIFS to create dynamic reporting in Excel
d. Use Lookup Functions to simplify IF condition formulas.
e. Application for Lookup functions with Name Manager and Data Validation.

TRACK 3: PERFORM DATA SECURITY AND VALIDATION
a. Create user input validation using data validation method
b. Create a user-friendly drop-down tool for data validation
c. Manage a read-only worksheet using Protect Sheet and Protect Workbook capabilities
d. Add security passwords to your excel file using file protection method.

TRACK 4: MANAGING BIG DATA IN EXCEL
a. Creating Reports using Subtotal
b. Application of Advanced filter

No. of Days:	2
Total Hours:	16