HRDF HRD Corp Claimable Excel Intermediate Training
100% HRDF / HRC Corp Claimable Excel Intermediate Training
2-3 December 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.
