# 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