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.
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