ADVANCED MICROSOFT EXCEL

Level:Advanced

Duration:2 days; 9:00 am – 5:00 pm (16 hours)

Prerequisite: Prior knowledge on Basic and Intermediate Functions of MS Excel

OVERVIEW

This course will provide you with the knowledge on more specialized and advanced capabilities of Microsoft 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. Whilst in this course we'll cover the basics of Excel, including SUM, MIN, MAX and other conditional statements, we'll quickly move onto more advanced-level topics including:

  • IF statements combine to number functions
  • VLOOKUP and HLOOKUP
  • Data Security
  • Pivot Tables and Charts Reporting
  • Macro Operations

LEARNING OBJECTIVE

At the end of this training course, learners will:

  • Gain confidence in efficiently using Excel 2013 software at the feature and functionality levels
  • Appreciate advanced formulas and functions for advanced calculations
  • Learn to create advanced reports using Pivot Table, Pivot Chart, and Analysis Tools
  • Be able to manipulate Excel options to customize their environment to meet varying needs and enhance their productivity.

TARGET AUDIENCE

This course is meant for those desiring to gain advanced skill sets necessary for calculating data using functions and formulas, sorting and filtering data, using PivotTables and Pivot Charts for analyzing data, and customizing workbooks.

  1. Individuals who record, prepare, analyze and interpret reports within an organization such as:
    • Research & Statistics
    • Production
    • IT – Network and Data Analytics
    • Quality Control
    • Finance
    • Sales & Marketing
    • HR and Admin
  2. Business Owners & Entrepreneurs
  3. Project and Product Managers
  4. Faculty Members and Students

LEARNING OUTLINE

TOPIC 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.

TOPIC 2: PERFORMING ADVANCED FORMULAS

  • Simplify Number Functions: SUM,COUNT AVERAGE,MAX MIN with Name Manager
  • Create Solutions in TEXT data using the 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: 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.

TOPIC 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

TOPIC 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.

TOPIC 5: MANAGE BIG DATA WITH CONSOLIDATION TOOLS

  • Basics of Sort and Filter
  • Apply Subtotal
  • Creating Charts and Dashboard
  • Differentiating between different types of charts
  • Describing the circumstances under which certain types of charts are preferable

TOPIC 6: 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

TOPIC 7: MANAGING BIG DATA IN EXCEL

  • Application of Advanced Filter with Name Manager and logical symbols
  • Data Consolidation from different References
  • Using Database Functions

TOPIC 8: 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

TOPIC 9: INTRODUCTION TO MACRO

  • Macro Settings
  • Macro Recording and Assignments
  • Macro Execution
  • Macro with Excel Functions and Features

OUR PARTNER’S PROFILE

  • Microsoft Certified Professional (MCP), Microsoft Office Specialist (MOS), Microsoft Technology Associate (MTA), TESDA NCII Computer Hardware & System Servicing, National TVET Trainer’s Competency, BS Computer Engineer, Rizal Technological University
  • Over 6 Years in IT Professional Training for Government and Corporate Accounts; Certified IT Trainer of BSP, DTI, DEPED, Pag-ibig Fund, Coca-Cola, Jollibee, San Miguel Corporation, SM Supermalls
  • Director for Technical Curriculum Development, Academics Manager, 2014 Academic Excellence Award for Most Outstanding Academic Trainer
  • Proficient in Office Productivity, Database Management, and Computer Programming

UPCOMING PUBLIC TRAINING SCHEDULE

Please view here.

For inquiries on registration, course content, course customization, and on-site training, please click here.