Electronic Learning and Development Systems Logo


Create Solutions with Complex Formulas and Functions - Microsoft® Office Excel® 2007 & 2010

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.
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.
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.
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.
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.
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.
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.
Custom data validation using functions
Preventing duplicates using data validation
Limiting the total of a list