HRDF HRD Corp Claimable Excel Intermediate Training

100% HRDF / HRC Corp Claimable Excel Intermediate Training

4-5 March 2024 – 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

When this program is over, those who took part should be able to:
Make formulas and functions ranging from simple to complex, such as:
What COUNTA and COUNTIFS Do
What Do AVERAGE A and AVERAGEIFS Do?
How do SUMIF and SUMIFS work?
IF Statement
Nested Functions
What a database does
Validate data in a worksheet
Filter data using Auto & Advanced Filtrers
Formatting for advanced charts
Clean duplicate records
Visualizing data as a graphics

What’s in it HRDF HRD Corp Claimable Excel Intermediate Training

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
E: Changing the Sparklines
Putting data on display or not
Getting a new look
Changing the color of the sparkline and the marker

Unit 6: Managing Views

Topic A: Using Custom View and Page Layout View Express Header and Footer to work with different views
Topic B: Organizing Workbooks in Multiple Windows
Putting two workbooks next to each other and looking at them side by side.