Home · Course Details

This course focuses on how to design and create multi-table databases and how to automate operations using macros.

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

NameDateTimeLocationCost
Excel Advanced19 June 201809:00 am - 04:45 pmMelbourne$450
Excel Advanced28 June 201809:00 am - 04:45 pmSydney$450
Excel Advanced14 August 201809:00 am - 04:45 pmMelbourne$450
Excel Advanced21 August 201809:00 am - 04:45 pmSydney$450
Excel Advanced16 October 201809:00 am - 04:45 pmSydney$450
Excel Advanced23 October 201809:00 am - 04:45 pmMelbourne$450
Excel Advanced21 November 201809:00 am - 04:45 pmSydney$450

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.