HRDF HRD Corp Claimable Microsoft Excel Intermediate – Advanced Training
Microsoft Developer Accidentally Leak all the Excel Tricks
100% HRDF / HRC Corp Claimable Microsoft Excel Intermediate – Advanced Training
13-14 March 2023 – 9am till 5pm
Face-to-Face Classroom
Who should take this course?
This course is for people who have already learned the basics of Excel and want to learn more. For
people who work with a lot of formulas and make reports, this course will help them learn how to
use an electronic spreadsheet.
Pre-requisites
• A basic understanding of Microsoft Excel is a must, along with the following:
• able to move from one task to another
• Simple formatting for making a spreadsheet.
• be able to make a simple chart
• Add headers and footers to a spreadsheet and be able to print it.
• Able to use Auto Filter command
• The Freeze Pane command can be used.
• able to make simple formulas for adding, subtracting, multiplying, and dividing
• AutoSum, Count, Max, Min, and Average functions, as well as AutoSum, Count, and Count.
• Benefits
Excel Intermediate Training Course Outline
HRDF HRD Corp Claimable Microsoft Excel Intermediate Level 2 Days – download pdf
Unit 1: Making Math Problems
Topic A – Reuse Formulas
Related information
Absolute references
Different sources
How to make sense of mixed cell references
Unit 2: Making more complicated formulas
Topic A: How to Use Range Names:
Putting names for ranges in the Name Box
Using the New Name dialog box to add a
range name
Changing the name of a range and getting rid
of a range name
Formulas that use range names
Unit 3: Putting functions to work
Using Statistical Functions (Topic A)
What Does COUNTA Do?
What Does COUNTIFS Do? What Does AVERAGEA
Do?
What Does AVERAGEIFS Do?
Topic B: How to use math functions
What SUMIFS Does
Using a logical function (Topic C)
IF Function Nested IF Function IFERROR Function
Topic D: Using Functions in a Database
Function of DSUM
What Does DCOUNT Do?
The Average Job
What DMAX Does
What DMIN Does
Unit 4: Using Tables to Set Up Worksheet Data
Topic A: Making and changing tables Tables
Table Parts
Make a table in the dialog box
Table tools let you create contextual tab styles
and sets of Quick Styles.
Changing how rows are shown Table change
options
Sort and filter data What’s the difference between
sorting and filtering?
Advanced sorting and filtering
Removing duplicate values
Use the subtotal function and database functions
to figure out how much something costs.
Topic D – Data Validation Validating your data
Data Validation using Lists
Data Validation using Date
Data Validation using Formulas
Unit 5: Making graphs out of data
Topic A: Formatting based on conditions
Point out Cell Rules
Using Data Bars
How to Use Icon Sets
Using Formula to come up with new rules
Topic B: Make Graphs
Charts
Types of Charts
How to put a chart in
Getting bigger and moving Chart
Adding additional data
Changing from rows to columns
Change and set up charts (Topic C)
Formatting Chart elements is different from
making changes to them.
Minimize extraneous chart elements
The tools for charts contextual tabs
Putting together the chart in a style
Putting a title on the chart
Adding Sparklines (Subject D)
How do Sparklines work?
Adding Sparklines
Sparkline Tools Tab
Changing the data in a sparkline Taking out a
sparkline
