HRDF HRD Corp Claimable Excel Advanced Training
100% HRDF / HRC Corp Claimable Excel Advanced Training
4-5 September 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
