Home ·
This course focuses on learning how to use some of the complex Function formulas including IF and VLOOKUP as well as Financial and Text Functions. You will also learn how to use formulas to link and consolidate data across worksheets, Macros to automate routine tasks and Scenarios, and Goal Seek and Solver to model spreadsheet data and make informed decisions.
Not sure which level to attend? Click here to take our quick online quiz.
Not sure which level to attend? Click here to take our quick online quiz.
Snapshot
- Duration: 1 day
- Versions: This course is suitable for users of Excel 2010, 2013, 2016, 2019
- Target Audience: This course will suit you if you are an experienced Excel user who will be creating spreadsheet templates or automated solutions for others in the organisation to use.
- Prerequisites: Successful completion of MCI Excel Intermediate courses or equivalent knowledge and skills are required to participate in this course.
Learning Outcomes
- Use Link formulas
- Consolidate data
- Use Data Validation and Auditing tools
- Protect worksheet data
- Automate routine tasks with Macros
- Use Logical, Lookup, Financial and Text Functions
- Use Goal Seek, Scenario Manager and Solver
Course Outline
Data linking
- Linking between worksheets and workbooks
- Updating links between workbooks
Data consolidation
- Consolidating data
- Creating an outlined consolidation
Data validation
- Creating and testing a validation
- Creating an input and error message
- Creating a drop-down list
- Circling invalid data
Protecting data
- Protecting a worksheet and workbook
- Providing restricted access to cells
Recorded Macros
- Setting Macro security
- Recording and running a simple Macro
- Editing and deleting a Macro
- Assigning a Macro to the ribbon and keyboard shortcut
Logical Functions
- Using IF to display text and calculate values
- Nesting IF Functions
- Using IFERROR, AND, OR and NOT
Lookup Functions
- Using CHOOSE, VLOOKUP, and HLOOKUP
- Using INDEX, and MATCH
- Using ROW, ROWS, COLUMN AND COLUMNS
- Using ADDRESS, INDIRECT and OFFSET
Financial Functions
- Using PMT, FV and NPV
Text Functions
- Using PROPER, UPPER and LOWER
- Using CONCATENATE
- Using LEFT, RIGHT MID and LEN
Advanced analysis tools
- Using Goal Seek
- Creating Scenarios
- Using Solver
Exercise Files| Name | Date | Time | Location | Cost (inc GST) | |
| Excel Advanced | 29 January 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 12 February 2020 | 09:00 am - 04:45 pm | Melbourne | $462.00 | |
| Excel Advanced | 27 February 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 30 March 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 22 April 2020 | 09:00 am - 04:45 pm | Melbourne | $462.00 | |
| Excel Advanced | 28 April 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 26 May 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 29 June 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 28 July 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 29 July 2020 | 09:00 am - 04:45 pm | Melbourne | $462.00 | |
| Excel Advanced | 25 August 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 30 September 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 21 October 2020 | 09:00 am - 04:45 pm | Melbourne | $462.00 | |
| Excel Advanced | 27 October 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 25 November 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Advanced | 22 December 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 |
Not quite the content you’re looking for?
Need a course you can’t find content for on our website? We have quite possibly the largest catalogue of course content in Australia! Let us know what you’re looking for and we will customise something for you.