Home · Course Details

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, 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.

  • Duration: 1 day
  • Versions: This course is suitable for users of Excel 2010, 2013, 2106
  • 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.

  • 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

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

Name Date Time Location Cost
Excel Advanced 31 January 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 13 February 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 25 February 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 14 March 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 25 March 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 9 April 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 29 April 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 13 May 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 28 May 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 17 June 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 27 June 2019 09:00 am - 04:45 pm Sydney $462
Excel Advanced 30 January 2019 09:00 am - 04:45 pm Melbourne $462
Excel Advanced 19 February 2019 09:00 am - 04:45 pm Melbourne $462
Excel Advanced 20 March 2019 09:00 am - 04:45 pm Melbourne $462
Excel Advanced 15 April 2019 09:00 am - 04:45 pm Melbourne $462
Excel Advanced 23 May 2019 09:00 am - 04:45 pm Melbourne $462
Excel Advanced 24 June 2019 09:00 am - 04:45 pm Melbourne $462

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.