COMPUTER SKILLS
Computer skills are a valuable addition to any employee’s personal portfolio and can greatly increase your desirability to employers.
Overview
This one-day course is designed to introduce the advanced user to more of Microsoft Excel’s specialised functions.
Objectives
- Paste Special Operations Command
- Using and combining various TEXT functions
- Using DATE functions
- Using Functions such as IF, AND, OR, COUNTIF, SUMIF, COUNTIFS, SUMIFS and SUMPRODUCT
- Using the INDEX and MATCH functions
- Advanced filter using Macros
- Other LOOKUP functions, CEILING and FLOOR functions
- Using Form Controls for Charts and PivotTables
Course Prerequisite
Successfully completed MS Excel (Advanced) Course and be fully competent with all the concepts covered.
Language of Delivery
English
Delivery Methods
Course is facilitated by a competent & experienced subject matter trainer, who utilises the following techniques to ensure that the session is practical and experiential:Â Questions; Discussion; Exercises & Assessments.
Who should attend?
Any delegate wanting to further develop their skills in designing specialised spreadsheet solutions.
Duration
1 Day
Includes:
- Comprehensive Manual
- Lunch & Refreshments (not applicable to on-site training)
- Electronic Certificate (on successful completion of the course)
- Electronic Trainer Feedback Report
- Electronic Delegate Feedback Questionnaire
Course outline
| Module 1 | Excel Add-ins Description of Add-ins Using the Paste Special Understanding the Paste Special Operation Commands |
| Module 2 | The CONCATENATE Function The TEXT Function The CHAR Function The LEFT, RIGHT and MID Functions The EXACT Function Nesting the TEXT, CHAR, LEFT, RIGHT and MID Functions with the CONCATENATE Function |
| Module 3 | Date and Time Functions The DATE Function Using the LEFT, RIGHT and MID Functions with the DATE Function The NETWORKDAYS Function Using the DAY, MONTH and YEAR Functions Using the DATEDIF Function to Calculate Ages The ROUNDDOWN and ROUNDUP Functions |
| Module 4 | Logical Functions Using the AND and OR Functions Nesting the AND and OR function with an IF Function The COUNTIF and SUMIF Functions The COUNTIFS and SUMIFS Functions The SUMPRODUCT Function The IFERROR Function The INDEX and MATCH Functions Troubleshooting the MATCH Function Nesting the INDEX and MATCH Functions The CHOOSE Function Expiring a Worksheet |
| Module 5 | Understanding the Advanced Filter Extract Specific Fields using the Advanced Filter Extract information to the same Sheet Extract Information to a different Sheet Creating a Macro to extract information to a different sheet |
| Module 6 | Using the LOOKUP Functions Using the Vector LOOKUP Option Using the Array LOOKUP Option The CEILING Function The FLOOR Function |
| Module 7 | Using Form Controls in Excel Adding the Developer Tab ActiveX vs Form Controls Creating a Combo Box Creating a List Box Creating a Group Box and Option Buttons Creating a Spin Button Using the INDEX Function with Form Controls Creating and renaming Check Boxes Using a Check Box to toggle a Chart Series on or off |