Excel 2013 – Level 2

This course effectively builds the knowledge and skills necessary to work with more complex formulas used in business spreadsheets. It covers best practice methods and formulas used for working with data contained in lists and database tables and ways to bring rigour to data quality and security.

Upon completion of this course you will be able to:

• Use LOOKUP, VLOOKUP,
• HLOOKUP, INDEX and other
• Reference functions
• Use Database Tables
• Use Formula Auditing and
• Data Validation tools
• Protect workbooks
• Use Hyperlinks

• LOOKUP functions
• Introduction to VLOOKUP and
• HLOOKUP
• The VLOOKUP functions
• The HLOOKUP function
• The INDEX function
• The CHOOSE function
• The MATCH function
• Understanding Reference functions
• The ROW and ROWS functions
• The COLUMN and COLUMNS functions
• The ADDRESS function
• The INDIRECT function
• The OFFSET function
• Lookup and Reference functions
• Using Excel as a database
• Sorting data
• Automatic Subtotals
• Sorting a subtotalled list
• Preparing a subtotalled list as a report
• Removing subtotals
• Filtering a list using AutoFilter
• Filtering using custom criteria
• Working with a filtered list
• Sort, copy and print preview data from
• a filtered list
• Display the top ten Items in a list
• Creating a table
• Formatting a table
• Adding a record to a table
• Totaling data in a table
• Using Database Tables
• Formula Auditing
• Data Validation
• Using the Formula Auditing toolbar
• Error checking options
• Trace Error
• Data Validation
• Entering data in a validated cell
• Setting up a validation rule
• Creating input and error messages
• Displaying error messages
• Formula Auditing and Data
• Validation
• Worksheet protection
• Unlocking cells
• Allow users to edit ranges
• Setting worksheet protection
• Workbook protection
• File protection
• Protecting workbooks
• Hyperlinks
• Creating a Hyperlink to a file
• Creating a Hyperlink to a named range
• Navigating between Hyperlinks
• Additional links

Successful completion of an Excel Introduction course or equivalent knowledge and skills is required to participate in this course.

This course is for experienced Excel users who either work with existing spreadsheets or create their own spreadsheet models and want to broaden their understanding of more complex formulas. Learners will gain skills to use look up formulas, tools for finding and preventing errors, tools for working with databases and long lists of data and methods for protecting data in workbooks.