Microsoft Excel - Expert
Course Description
This is a Microsoft Excel Expert course. The teaching approach contains a mix of lectures, practical and one-to-one work and varies according to the people attending. For the most part, candidates are able to work at a pace which best suits them, and emphasis is placed upon quality of learning, rather than quantity.
Course Duration
1 day
Course Objectives
On completion, candidates should be equipped with the necessary skills to create Pivot Tables, produce complex nested functions, create array functions and record and edit macros.
Course Content
ANALYZING DATA WITH PIVOTTABLES
Building PivotTables
Building a PivotTable from a Table or Range
Working with and Customizing a PivotTable
Working with PivotTable Subtotals
Customizing the Subtotal Calculation
Changing the Data Field Summary Calculation
Using Summary Calculations
Creating a Calculated Field
Using PivotTable Results in a Worksheet Formula
NESTED FUNCTIONS
Nesting IF statements
Nesting AND and OR with IF statements
Nesting IFERROR with VLOOKUP functions
Nesting MATCH with VLOOKUP functions
Nesting INDEX and MATCH functions
ARRAY FUNCTIONS
What are array functions?
Working with standard array functions in Excel
Working with CSE array functions (Ctrl + Shift + Enter)
Returning array solutions from an array
MACROS AND MACRO EDITING
Recording a simple macro (recap only – see pre-requirements above)
Editing a simple macro
Creating an input message box
Displaying a message box
Working with cells
Working with columns and rows
Working with sheets
Working with workbooks
VBA code to increase macro efficiency
Error handling
Where to from here?
Building PivotTables
Building a PivotTable from a Table or Range
Working with and Customizing a PivotTable
Working with PivotTable Subtotals
Customizing the Subtotal Calculation
Changing the Data Field Summary Calculation
Using Summary Calculations
Creating a Calculated Field
Using PivotTable Results in a Worksheet Formula
NESTED FUNCTIONS
Nesting IF statements
Nesting AND and OR with IF statements
Nesting IFERROR with VLOOKUP functions
Nesting MATCH with VLOOKUP functions
Nesting INDEX and MATCH functions
ARRAY FUNCTIONS
What are array functions?
Working with standard array functions in Excel
Working with CSE array functions (Ctrl + Shift + Enter)
Returning array solutions from an array
MACROS AND MACRO EDITING
Recording a simple macro (recap only – see pre-requirements above)
Editing a simple macro
Creating an input message box
Displaying a message box
Working with cells
Working with columns and rows
Working with sheets
Working with workbooks
VBA code to increase macro efficiency
Error handling
Where to from here?
Who should attend?
Aimed at advanced users who have an excellent knowledge of Microsoft Excel, this course provides candidates with practical experience of the topics listed in the course outline.
Pre-requisites
Those attending should have a very good understanding of standard Microsoft Excel functions, be able to record a simple macro and be competent Microsoft Windows users.