Useful Formulas & Functions - Microsoft® Office Excel® 2007 & 2010
Course Content
Module 1: Making Data Work For You
This module explains how to understand and apply Excel basic formulas and functions.
Lessons
Formula basics
Using cell references
Copy formula without changing cell reference
Transpose formula
Using nested functions
Module 2: Statistical and Logical Functions
This module explains how to use logical functions including CountIf, Sumif, If, IsError.
Lessons
Perform calculation using CountIF
Perform calculation using SumIF
Perform calculation using AverageA
Using IF function to prevent division by zero
Using IsError function to avoid error display
Creating multiple conditions using nested IF
Using logical function OR, And
Module 3: Lookup and Reference Formulas
This module explains how to apply and use lookup formulas including vlookup, hlookup, match and index.
Lessons
Using Vlookup to find specific data
Using Hlookup to find values in rows
Using Match and Index to retrieve data
Module 4: Text Formulas
This module explains how to apply Text formula to help change casing of text, append text and numerical value in excel spreadsheet.
Lessons
Changing case of text
Append text and numerical value
Convert imported text format into numbers
Break imported date field into individual columns
Convert imported text format into numbers
Break imported date field into individual columns
Module 5: Date and Time Formulas
This module explains how to make use of calculate the difference of two given Date fields and to perform calculation with Time fields.
Lessons
Perform addition to Date fields
Calculate difference between two Dates
Perform calculations with Time fields
Module 6: Array and Database Functions
This module explains how to apply and use advance formula including Array, Frequency and Database functions.
Lessons
Using Array Formulas
Calculate the difference between Maximum and Minimum values
Using Frequency function to Count responses
Using Database functions DSum and DCount
Module 7: Efficiency Tips
This module discusses some useful Excel Tips including application of Data Validations and Auditing Tools.
Lessons
Shortening worksheets names
Protecting cells containing formulas
Using Data Validation
Displaying Formula syntax
Using Auditing Tools for errors checking
Tracing precedent and dependent
Adding comments to worksheet