MICROSOFT EXCEL (ADVANCED LEVEL)

INTRODUCTION FOR MICROSOFT EXCEL (ADVANCED LEVEL) Designed for Excel 365, 2019, 2016, 2013, 2010

Excel, the longstanding powerhouse of spreadsheet software, is one of the most commonly used business applications nowadays. Now, you can get up to speed fast on the most advanced features for improving your efficiency and expanding your capabilities in Excel. The software has unlimited capabilities for making your job easier and less stressful. This training will teach you the most advanced features and functions Excel offers — taking your level of proficiency from basic to advance.

COURSE OBJECTIVE

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 CONTENT

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
• Using AI Functions (New)

Advanced Charting

• Customize Chart Elements
• Create own Chart Themes
• Techniques of adding Trend lines
• Create different Chart Layout:
• 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

 Register 3 Participants From Your Organization And Get The 4th Participant For Free!

The promotion expiry date for each programmes differs. For more information on the exact expiry date of the promotion, feel free to contact us.

OTC Training Centre Sdn Bhd may at any time in its sole and absolute discretion withdraw, amend and/or alter the terms and conditions of the stated promotions without prior notice.