ADVANCED MICROSOFT EXCEL

COURSE DESCRIPTION

This course will provide you with the knowledge on more specialized and advanced capabilities of Excel by automating some common tasks, applying advanced analysis techniques to more complex data sets, collaborating on worksheets with others, and sharing Excel data with other applications.

PREREQUISITES

Basic Microsoft Office Excel

TARGET AUDIENCE

This course is intended for novice information workers who want to learn advanced-level Excel skills.

COURSE OUTLINE

MODULE 1: IDENTIFY AND DEFINE THE ESSENTIAL COMPONENTS OF EXCEL FORMULAS

• Using Relative value for cell reference as primary source of formula

• Using Absolute value for cell reference as primary source of formula

• Managing Names of Cell Reference by Using Name box to calculate data across worksheets

MODULE 2: PERFORMING ADVANCED FORMULAS

• Simplify the ff. Number Functions SUM, COUNT AVERAGE, MAX MIN with Name Manager

• Create Solutions in TEXT data using the ff. text functions Change Case, Trim Case, Combine Case and Find and Replace text methods.

• Formulate Date and Time functions to streamline aging report

• Using IF condition and Logical functions like SUMIF, COUNTIF, AVERAGEIF to create dynamic reporting in Excel

• Use Lookup Functions to simplify IF condition formulas.

• Application for Lookup functions with Name Manager and Data Validation.

MODULE 3: PERFORM DATA SECURITY AND VALIDATION

• Create user input validation using data validation method

• Create a user-friendly drop-down tool for data validation

• Manage a read-only worksheet using Protect Sheet and Protect Workbook capabilities

• Add security passwords to excel file using file protection method.

MODULE 4: IDENTIFY AND UNDERSTAND HOW TO FORMAT FINANCIAL STATEMENTS AND OTHER EXCEL-BASED REPORTS MORE EFFECTIVELY

• Advanced Conditional Formatting and Filtering

• Apply Custom Data Formats

• Apply Custom Styles and Templates

MODULE 5: CREATE EXCEL-BASED CHARTS AND DASHBOARDS TO AID IN COMMUNICATING FINANCIAL DATA INFORMATION

• Application of basics of Sort and Filter

• Creating Charts and Dashboard

• Application of Subtotal per Data Category

MODULE 6: MANAGING BIG DATA IN EXCEL

• Application of Advanced Filter with Name Manager and logical symbols

• Data Consolidation from different References

• Using Database Functions

MODULE 7: DATA ANALYSIS TOOLS USING PIVOT FEATURES

• Describe how to merge database to PIVOT

• Add available field names to different data labels of Pivot Area to generate different report scenario

• Manipulate and re-define PIVOT data resources

• Manage and Derive Formulas in Pivot Area using Value Field settings and Calculated Field feature.

• Re-create PIVOT reports with dashboards using Slicer feature

MODULE 8: INTRODUCTION TO MACRO

• Introduction to Visual Basic

• Macro Settings

• Macro Recording and Assignments

• Macro Execution

• Macro with Excel Functions and Features

Please contact us for schedules and for booking a private class.

Inquire Now