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