 # Course Content

### Module 1: Explore Advanced Functions

This module investigates how to use some of the more advanced functions in Microsoft Excel and how to use the error functionality in Microsoft Excel. It describes how to create complex formulas and store data in an array.
Lessons
The SUMIFS and COUNTIFS functions
Combining AND, OR and IF functions
Nesting IF functions
Simplifying error checking with ERROR functions

### Module 2: Use the VLOOKUP Function

This module explains how to use the VLOOKUP function to extract data from within a table. It also describes how to use the IF and MATCH functions in conjunction with the VLOOKUP function.
Lessons
The LOOKUP functions in Excel
Extracting data from a table using the VLOOKUP function
Using IF as a nested function with VLOOKUP
Using MATCH as a nested function with VLOOKUP
Worksheet design considerations for using LOOKUP functions

### Module 3: Text, Date and Time functions

This module explains how to use the text, date and time functions in Microsoft Excel to ‘clean up’ (or wash) data and/or convert data to numerical values before performing calculations or text comparisons.
Lessons
Removing spaces from a text string and converting a text string to a value
Combining and replacing values in cells
Determining the length of a value in a cell
Applying number formatting in a text string
Finding and extracting values from a cell

### Module 4: Use Arrays

This module explains how to use array formulas and array constants to simplify worksheet formulas.
Lessons
Understanding arrays and array formulas
Creating single-cell and multi-cell array formulas

### Module 5: Error Check and Audit

This module explains how to check a worksheet for errors. It also describes how to use the Formula Auditing tools in Excel to trace precedence and dependence in a formula.
Lessons
Understanding error checking and Error Alerts
Using automatic formula checking
Interpreting error values
Auditing formulas to locate errors
Evaluating formulas and correcting circular references

### Module 6: Conditional Formatting with Formulas

This module explains how to use the conditional formatting functionality in Excel to highlight anomalies within a dataset. It also describes how to use formulas to test cell conditions using formulas to result in an appropriate format.
Lessons
Conditional formatting using formulas
Using the Rules Manager
Understanding format precedence

### Module 7: Data Validation using Formulas

This module explains how to create automated validation test to prevent invalid data from being entered into a worksheet. It also describes how to use the tools in Excel to protect formulas and prevent errors from compromising the validity of data analysis.
Lessons
Custom data validation using functions
Preventing duplicates using data validation
Limiting the total of a list