WSQ Microsoft Excel Intermediate
TGS-2024042612
Course Overview
Do you often work with data at work or frequently extract data to create reports to update your leaders? Microsoft Excel is a powerful spreadsheet program that allows you to make quick and accurate numerical calculations.
You will be more confident when you present formulated spreadsheets to highlight your key data narratives. This course is suitable for you if you are looking to explore more advanced features of Excel to increase your productivity while working with data.
WSQ Microsoft Excel Intermediate course will teach you how to manage data ranges, protections, create tables and charts, insert formulas and functions, trace errors, manage names, and how to sort and filter data. In addition, you will learn how to create VLOOKUP formulas, IF functions and simple Pivot Tables to report or summarise your data for more impactful reporting.
Course Duration
2 Days
Total Training Duration (Hour)
16 Hours
Course Outline
Formulas & Functions
- Identify parts of data sets for categorisation of data
- Cell Referencing (Relative & Absolute)
- Use Excel to select and name data ranges for navigation and formulas
- Trace formula precedents & dependents
- Use Logical functions
- Insert IF to manage single condition
- Insert IFERROR to manage errors in formulas
- Use conditional count, sum & average functions
- Understand function syntax
- Insert functions to perform conditional business aggregate calculations
- Use Database functions
- Create criteria range & use comparison operators
- Insert data functions to calculate aggregate values
- Use Lookup functions
- Approximate and Exact match types
- Use vertical & horizontal functions to search for a value in a data set
Managing Workbooks & Protection
- Split & freeze a window
- View multiple worksheets or workbooks simultaneously
- Protect workbooks
- Protect workbook structure
- Protect worksheets & worksheet elements
- Splitting & Freezing a Window
- Working with Multiple Workbook Windows
- Hiding & Unhiding Worksheets
Working with Data Ranges
- Creating Custom AutoFill List
- Sorting by Colours or Icons
- Sorting by Custom List
- Filtering Data
- Creating Custom AutoFilter
- Using Advanced Filter
Working with Tables
- Create a Table
- Add & remove data
- Display total row & change summary calculation
- Sort & filter a table
- Remove duplicate rows of data
- Add interactive slicer
- Format the table
- Summarize a table with a PivotTable
- Convert table to a range
Introduction to Charts
- Choosing & Selecting the Source Data
- Choosing the Right Chart
- Inserting, Moving, Resizing & Deleting Charts
- Changing Chart Layouts & Styles
- Changing Chart Type
- Working with Chart Labels
Working with Charts
- Filter chart
- Apply with chart labels
- Change the chart gridlines
- Change the scale
- Emphasize data
- Using chart templates
- Printing a Selection, Multiple Worksheets & Workbooks
Certification Obtained and Conferred by
Upon successful completion of the WSQ Microsoft Excel Intermediate assessment, participants who have attended at least 75% of the course will receive a SkillsFuture WSQ Statement of Attainment (SOA) along with a Certificate of Attendance by @ASK Training.
Course Learning Outcome
At the end of the course, you will be able to:
- Use Excel for statistical analysis by organising cell ranges, managing formula errors, applying conditional logic, and employing lookup functions for data search and matching.
- Manage large Excel data sets, including multiple worksheets and workbooks, and application of protection measures for the interpretation of the categorised data.
- Organise data in Excel by sorting, filtering, and separating the contents into multiple sets to effectively interpret the collected data for categorisation of meaningful identified data.
- Use features in Excel to create tables, including managing data sets and removing duplicate records.
- Optimise Excel functionalities for process improvement in business statistics by creating and formatting charts, including displaying data through histograms.
Pre-requisites
To enrol in this course, you should possess the following:
- WPLN Level 4
- Attended our WSQ Microsoft Excel Essentials; OR
- Basic knowledge of Microsoft Excel skills and know how to work with Microsoft Excel function
Course Pre-Requisites
Price
| Course Fee Payable | ||
|---|---|---|
| Original Fee | Before GST | With GST (9%) |
| Course Fee | $450.00 | $490.50 |
| Individual Pricing (Fee payable to Training Provider) | Before GST | With GST (9%) |
| PMET SkillsFuture >= 21 years old | $225.00 | $265.50 |
| PMET Skillsfuture >= 40 years old | $135.00 | $175.50 |
| Corporate Pricing (Fee payable to Training Provider) | ||
| Non-SME | Before GST | With GST (9%) |
| PMET SC and PR | $225.00 | $265.50 |
| Non-SME PMET Funding >= 40 years old | $135.00 | $175.50 |
| SME | Before GST | With GST (9%) |
| PMET funding >= 21 years old | $135.00 | $175.50 |
| PMET funding >= 40 years old | $135.00 | $175.50 |
Full Course Fee: $490.50 (inclusive 9% GST)
Please note that prices are subjected to change.