Microsoft Excel: Super User (Expert)

Course details

This one-day course is designed to introduce the advanced user to more of Microsoft Excel's specialised functions.

Course Outline

Module 1

• Add-ins
• Description of the Add-in's
• Paste Special
• The Operation Commands

Module 2

Text Functions:

• The CONCATENATE Function
• The CHAR Function
• The EXACT Function
• Extracting Characters from a Text String
• The LEFT Function
• The RIGHT Function
• The MID Function

Module 3

Date & Time Options:

• DATEDIF Function
• Calculating Working Days between Two Dates
• The DAY Function
• The MONTH Function
• Calculate the Number of Months between Two Dates
• The YEAR Function
• Calculating Ages
• The LEFT Function
• The ROUNDDOWN Function
• The ROUNDUP Function

Module 4

Logical Functions:

• The AND Function
• The OR Function
• Nesting the OR/AND Function With an IF Function
• The SUMIF Function
• The COUNTIF Function
• Nesting the SUM & IF Functions
• The SUMIFS Function
• The COUNTIFS Function
• The IFERROR Function
• The SUMPRODUCT
• INDEX and MATCH Functions
• MATCH Function
• Nesting the INDEX & MATCH Functions
• Troubleshoot the MATCH Formula
• The CHOOSE Function

Module 5

Using the Advanced Filter:

• Filtering List "In-Place"
• Copy Filter Results to a New Location
• Extracting a Range to another Workbook
• Creating a Macro to Extract Information]

Module 6

The Lookup Function:

• Using the CEILING Function
• Using the FLOOR Function

Module 7

Using Form Controls:

• Using Forms Controls in Excel
• Activex vs Forms Controls?
• Forcing User to Choose From a List
• Creating a Combo Box
• Controlling Multiple Charts with List Box
• Create a List Box
• Group Box and Option Buttons
• Spin Button
• Checkbox
• Rename the Checkbox Button
• Creating a Macro to Filter the Chart by Region
• Assigning Macros to Buttons