COURSE OVERVIEW

Excel, the longstanding powerhouse of spreadsheet software, is one of the most commonly used business applications nowadays. Now, anyone can get up to speed fast on the most advanced features for improving their efficiency and expanding their capabilities in Excel. The software has unlimited capabilities for making their job easier and less stressful.

This training will teach participants the most advanced features and functions Excel offers — taking their level of proficiency from basic to advance.

TARGET AUDIENCE

• Managerial Staff, Supervisor
• For those who are interested to master advanced features of Excel
• For those who are interested to improve reporting skills

LEARNING OUTCOMES

Upon completion of the training, participants should be able to:

• Apply Tables for dynamic reporting
• Manipulate and visualize data efficiently
• Use advanced formulas and functions for dynamic settings
• Create and present chart professionally
• Use Pivot Table and Pivot Chart to create Excel Dashboard
• Use WHAT-IF for planning and budgeting
• Use macro for simple task automation
• Generate reports with least maintenance in the future.

COURSE OUTLINE

• Cell Naming Techniques
  • Easier-to-Understand Formulas with Named Ranges
  • Using Names to Work with Ranges
  • Using Names to Work with Formulas
  • Using Name Manager
  • Worksheet vs. Workbook Scope
  • Assigning name to a constant

• Working with Tables
  • Use Tables
  • Change the Table Name
  • Change the Table Style
  • Create a Total Row
  • Create a Calculated Column
  • Select Parts of a Table

• Advanced Data Manipulation (Advanced Filter, Data Validation, Conditional Formatting)
  • Auto Filter & Advanced Filter
  • Number, Date, Text Filters
  • Subtotal & Outlines
  • Data Validation
  • Type of Data verification & Controls
  • Errors handling with Data Validation
  • Customize Data Validation using Formulas & Functions
  • Conditional Formatting
  • Rules Manager
  • Customize Conditional Formatting using Formulas & Functions

• Essential Functions for Dashboard Design
  • SUMIF, SUMIFS, COUNTIF, COUNTIFS
  • CHOOSE
  • SEARCH, FIND
  • OFFSET
  • ARRAY Functions
  • IF, IF ERRORS
  • AND, OR, NOT
  • INDEX, MATCH, LOOKUP, VLOOKUP
  • INDIRECT

• Advanced Charting
  • Customize Chart Elements
  • Create own Chart Themes
  • Techniques of adding Trend lines
  • Create different Chart Layout:
    • Waterfall Chart
    • Speedometer Chart
    • Gantt Chart
    • Thermometer Chart
    • Etc.
  • Create Dynamic Chart

• Pivot Tables & Pivot Chart (Excel Dashboard)
  • Introduction to Pivot Tables
  • Introduction to Table
  • Drag & Drop Techniques
  • Sorting in Pivot Tables
  • Pivot Fields Grouping
  • Subtotals and Blank Lines Controls
  • Summary Calculation
  • Running Total
  • Calculated Field & Calculated Item
  • Use GETPIVOTDATA Function
  • Limitations of Pivot Table
  • Pivot Cache
  • Using Slicer & Timeline (new)
  • Creating Excel Dashboard using Pivot Tables

• Power Pivot
  • Introduction to Power Pivot & Power Query
  • Relationship Settings
  • Creating Reports using Power Pivot

• What-if Analysis
  • Goal Seek and its application
  • Data Table and its application
  • Solver

• Macro
  • Change Macro Security Settings
  • Open a Workbook Containing Macros
  • Run a Macro
  • Use the Visual Basic Editor Window
  • Record a Macro
  • Use Relative References
  • Copy, Edit and Type Macro Commands
  • Run a Macro from the Code Window
  • Create Macro Button
  • Simple VBA Code