HRDF HRD Corp Claimable Excel Advanced Training

100% HRDF / HRC Corp Claimable Excel Advanced Training

6-7 June 2024 – 9am till 5pm

Face-to-Face Classroom

This program for learning Excel is NOT for beginners. It is for people who already know a lot about Excel and want to learn more functions, formulas, and techniques. This course will help departments and offices that deal with a lot of data with different levels of complexity and need much more advanced skills to manage it. The goal of this 2-day program is to take your Excel skills to the highest level and make sure you know how to use all of Excel’s useful features in the workplace.

Microsoft Excel: Advanced Level
2 Days

Pre-requisites

• A basic understanding of Excel Microsoft is required, along with the following:
• Able to move from one task application to another
• Able to make formulas and functions that range from simple to complex
• Validate the information in a Worksheet
• Use Auto and Advanced Filters to filter data.
• Can clean up duplicate records

Benefits

• When this program is over, people should be able to:
• Using the Subtotal Function, you can make a subtotal.
• Analyze data using Pivot Tables
• What If Analysis
• Use the VLOOKUP function to pull data out.
• Combining the INDEX and MATCH functions
• Combining & Consolidating Data
• Record a Macro and Play It

HRDF HRD Corp Claimable Excel Advanced Training Course Content

Unit 1: Using your data to its fullest

• Topic A – Outlining and Grouping Data Using Automatic Outlining Displaying and Collapsing Levels
• Putting data into groups and making subtotals by hand

Unit 2: Analyzing “What If”

• Topic A – Using Data Analysis Tools
• Using a table with one or two inputs and Goal Seek
• Topic B: Looking at possible situations
• What’s a scenario?
• How to Make a Scenario
• Saving Multiple Scenarios
• Making a Summary Report of a Scenario
• Topic C: Using Solver and Knowing How It Works
• Solver is used to make reports and scenarios.
• Changing Solver values
• Putting constraints on the Solver
• Using Solver to help you reach your goals

Unit 3: Task for Excel Experts

• Work with Array Formulas (Topic A)
• What is a formula for an array?
• What Basic Array Formulas Mean
• How to Use Functions in Array Formulas
• Using the IF function in formulas with arrays
• Array formulas that use IFERROR
• Using the VLOOKUP Function (Topic B)
• Understand VLOOKUP and HLOOKUP
• VLOOKUP is used to find data.
• How to use VLOOKUP to find an exact match
• VLOOKUP lets you find a close match.
• Using VLOOKUP as an Array Formula
• Use of the Advanced Function (Topic C)
• How to Use INDEX Function
• Using the MATCH function Putting the MATCH and INDEX functions together
• Topic D – Linking, Consolidating and Combining Data
• Linking Workbooks
• Putting together workbooks and worksheets

Unit 4: Advanced Charts Topic A: How to Make Advanced Charts with Two Axes

• Making Your Own Templates for Charts

Unit 5: Using the Pivot Table, Slicer, and Pivot Charts to Look at Data

• Topic A: Make a Pivot Table
• The box that says “Create Pivot Table”
• Pivot Table Fields Pane
• Summarize data in a pivot table and show values as a pivot table function Topic B: Filter data by using the slicer
• Place Slicers in the dialogue box
• Topic C: Using a pivot chart to look at data
• Making a Pivot Chart Giving a Pivot Chart a Style

Unit 6: Making the most of workbooks

• Topic A: Making changes to workbooks
• How do Hyperlinks work?
• Inserting Hyperlinks
• Editing Hyperlinks
• Formatting Hyperlinks

Unit 7: Macros and Form Controls Using Hyperlinks in Exce

• Topic A: Macros: Recording Macros, Saving Macros, Closing and Reopening a Macros-Enabled Workbook, and Getting a Security Warning
• How to Play a Macro
• Putting a keystroke in charge of a macro
• Topic B: Controls for the Form
• Putting a control on a worksheet, giving a control a macro, and using form controls

Unit 8: Publishing and Keeping It Safe

• Topic A: Putting the Workbook to PDF or XPS at the End
• Topic B: Keeping the workbook and worksheet safe
• Keeping the current Sheet safe
• Keeping the whole workbook safe
• File protection