Excel Advanced

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

Summary of common shortcut keys

Full list of shortcut keys

Excel file of all keyboard shortcuts

Please note there is no pre-course work for this course.