Course Overview:

MS Advanced Excel training 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.

Learning Objectives

Upon completion of this program, participants should be
able to :

  • Efficiently and confidently use Microsoft Excel 2016 software at the feature and functionality levels.
  • Use advanced formulas and functions for advanced calculations.
  •  Create advance reports with pivot table, pivot chart and analysis tools.
  • Manipulate excel options to customize their environment to meet varying needs and enhance their productivity.

Duration: 15 hrs
Pre-requisite: knowledge on basic computer operations
Target Audience:  This course is intended for novice information workers who want to learn the advanced level Microsoft Excel 2016 skills.

Outline:

  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
  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.
  3. 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
  4. 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 your excel file using file protection method.
  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
  6. MANAGING BIG DATA IN EXCEL
    • Application of Advanced Filter with Name Manager and logical symbols
    • Data Consolidation from different References
    • Using Database Functions
  7. DATA ANALYSIS TOOLS USING PIVOT FEATURES
    • Describe how to merge database to PIVOT
    • Add available fieldnames 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
  8. INTRODUCTION TO MACRO
    • Introduction to Visual Basic
    • Macro Settings
    • Macro Recording and Assignments
    • Macro Execution
    • Macro with Excel Functions and Features