Excel Training

Advanced Excel

Part 1. Advanced Charting

Session 1 - Create Chart

• Use shortcut to create a chart
• Put chart in a separate worksheet
• Use different chart type

Session 2 - Chart Design Tips

• Apply different chart styles
• Change chart type
• Change data source
• Save as template

Session 3 - Chart Layout Tips

• Understand chart area, plot area, chart titles, legends
• Manager chart axis, gridline
• Apply data labels and customise data labels
• Insert pictures and shapes
• Format Shapes

Session 4 - Create Special Charts

• Waterfall chart
• Speedometer chart
• Thermometer chart
• Funnel chart
• Sparkline 

Session 5 - Dynamic Chart

• Data validation
• How to use data validation to create dynamic chart 

 

Part 2. Pivot Table

Session 1 - Creating PivotTables

• Introducing PivotTables
• Use shortcut to create a PivotTable
• Configuring a PivotTable – row, column, value, filter
• Managing PivotTables

Session 2 - Pivot Table Design

• Applying different PivotTable style
• Different table layout
• Manage grand total and subtotal
• Row & Column header, banded row and column
• Managing subtotals and grand totals

Session 3 - Manage PivotTable

• Change PivotTable data source
• Refresh PivotTable

• Filtering and soring PivotTable
• PivotTable group selection
• Change PivotTable calculation Method
• Add calculated fields 

Session 4 - PivotChart

• Creating a PivotChart
• Apply different chart styles
• Change chart format and layout
• Select Data and change chart type
• Hide all buttons
• Use slicer to create dynamic chart
• Customise slicer into your own style
• Move PivotChart 

Session 5 - PivotTable Troubleshoot

• Can’t create PivotTable
• Calculation not as expected
• Remove Blank
• Remove error message

Dynamic Dashboard

Session 1 - Design Dynamic Dashboard Using VBA Check Box & Radio Button

• Apply check box or radio button to enable and disable data range
• Example: Sale, Budget, LY variance analysis

Session 2 - Design Dynamic Dashboard Using VBA Spin Button

• Use spin button to control different reporting time frame
• Example: sales and trends analysis for more than 10 year

Session 3 - Design Dynamic Dashboard Using VBA Scroll Bar

• Similar to Spin Button
• Scroll at much faster speed and can set up auto scroll

Session 4 - Design Dynamic Dashboard Using VBA Combo Box

• Most Versatile VBA form control
• Example: Validate user input; update multiple filters and picture links etc.

Session 5 - Design Dynamic Dashboard Using VBA ActiveX Control

• Similar to VBA Combo Box but more presentable
• Can customer background, font size, 3D effect & shadow

Session 6 - Design Dynamic Dashboard Using Pivot Table Slicer

• How to customize Slicer to match different style (Hover effect)
• Use one slicer to control multiple tables and charts

Session 7 - Design Dynamic Dashboard Using Named Range and Picture

• Create and control dynamic charts and pictures
• Can use unlimited pictures or objects
• Enable picture, chart & table objects visible and invisible

Session 8 - Design Dynamic Dashboard Using Named Range and Picture Link (or Camera tools)

• Most flexible way to build dynamic dashboard, apply to any circumstance

Advanced VBA

Session 1 - Introductions

• Introducing Visual Basic for Applications
• Enable the Developer Tab in the Ribbon
• Enable macros in Excel workbook and understand macro security
• Saving a Macro-Enabled Workbook
• How to run a Macro in different ways
• Editing a Macro in the Visual Basic code window
• Closing the Visual Basic Editor

Session 2 - Work with Expressions & Variables

• Understanding Expressions and Statements
• Declaring Variables
• Understanding Data Types
• Working with Variable Scope
• Using Message Boxes
• Using Input Boxes
• Declaring and Using Object Variables

Session 3 - Understand Procedures and Functions

• Understanding Modules
• Creating a Standard Module
• Understanding sub Procedures
• Public sub vs. private sub
• Creating a Sub Procedure
• Calling Procedures
• Creating a Function Procedure
• Naming Procedures

Session 4 - Understanding Objects

• Understanding workbook objects
• Extract workbook name, workbook full path
• Workbook method - open, save, close
• Understanding worksheet objects
• Worksheet name, worksheets count, rows & columns count
• Worksheet method – activate, add, delete, copy
• Range object – format, calculate, select, copy, paste
• Working with object property window

Session 5 - Understanding Event

• Workbook Open Event
• Worksheet Activate Event
• Worksheet Selection Change Event
• VBA Controls Event
• Application Event

Session 6 - Program Execution

• Using the If...End If Structures
• Using the Select Case...End Select Structure
• Using the Do...Loop Structure
• Using the For...To...Next Structure
• Using the For Each...Next Structure

Session 7 - Working with Forms and Controls

• Understanding UserForms, Setting Control Properties in the Properties Window
• Working with the Check Box Control
• Working with the Text Box Control
• Working with the Command Button Control
• Working with the Combo Box Control
• Working with Option Button Controls
• Adding Code to Controls and design report

Session 8 - Working with the PivotTable Object

• Understanding PivotTables
• Creating a PivotTable Using Worksheet Data
• Working with the PivotTable Objects
• Working with the PivotFields Collection
• PivotTable Related Event

Session 9 - Working with Images and Charts

• Understanding Image and Charts
• Working with Chart Properties windows
• Pictures copy, paste, delete
• Show image and hide image
• Show Charts and hide charts

Session 10 - VBA Extra Tips:

• Assign Macros to Quick Access Toolbar
• Use shortcuts to run macro, open and close VBA Edit window
• Always activate target worksheet when workbook opens
• Permanently hide worksheets
• Make your macros work anywhere - Enabling Relative References
• How to customise ActiveX component

By the end of this course participants should be able to:

• Work with the VBA editor
• Understand VBA concepts and features
• Enable or disable VBA macros
• Create procedures and sub routines
• Record macros using macro recording and customise the VBA code
• Assign macros to object and shortcut to start macros in different ways
• Work with variables and Excel objects
• Work with different events (workbook, worksheet, controls, application)
• Use named range in VBA coding (highly recommended)
• Create user defined functions (such as user defined lookup functions to replace Vlookup)
• Create custom forms and design report
• Work with time & date, manipulate text string
• Handle errors and resume next