# Useful Formulas and Functions Overview

Get a head start in acquiring the knowledge of **Advanced Formulas and Functions** in your daily work as well as expand your Microsoft Excel knowledge. This one day course will empower you to have answers to Excel questions at your fingertips. There are numerous and varied numbers of new tips you will learn, irrespective of the Excel version used. This course will serve as a refresher as well as a reference. The course is for users at all levels, from beginners through intermediate and advanced.

Upon successful completion of this course, students will be able to:

- Apply formula and functions basics
- Use statistical and logical functions
- Utilize lookup and reference formulas
- Understand how to use text formulas
- Apply date and time formulas
- Utilize array and database functions
- Implement efficiency tips

Prerequisites:

Students are recommended to take the following course or possess equivalent knowledge:

Next Classes:

Related Classes:

See Also:

Prerequisites:

Students are recommended to take the following course or possess equivalent knowledge:

Next Classes:

Related Classes:

See Also:

## Microsoft Excel - Useful Formulas and Functions Training Outline

Lesson 1: Formulas and Functions Basics

• Formula Basics• Use Cell References

• Copy Formula Without Changing Cell References

• Transpose A Formula

• Copy And Paste Values

• Perform A Quick Calculation

• Nesting Functions

Lesson 2: Statistical and Logical Formulas

• Use CountIf to Calculate Survey Results• Use SUMIF to Add Data That Satisfy Condition

• Use AverageA to Include All Cases

• Use IF Function to Prevent Division By Zero

• Use IsError Function to Avoid Error Display

• Create Multiple Conditions Using Nested IF

Lesson 3: Lookup & Reference Formulas

• Use VLookup to Find Specific Data• Use HLookup To Find Values in Rows

• Use Index & Match to Retrieve Data

Lesson 4: Text Formulas

• Change Case of Text• Combine Text and Formatted Numbers

• Convert Imported Text Format Into Numbers

• Break Imported Date Field Into Individual Columns

• Extract A Name Field into Three Columns

Lesson 5: Time & Date Formulas

• Add a Number to Current Date to Get New Dates• Calculate a Period of Time Between Two Dates

• Perform Calculation with Time Fields

Lesson 6: Array and Database Functions

• Use Array Formulas• Calculate the Difference between Maximum and Minimum Values in an Array 61

• Find Value In List

• Use Frequency Function to Count Responses

• Analyze Data in a Database

Lesson 7: Efficiency Tips

• Reduce Formula Recalculation Time• Enter Formulas Quickly By Shortening Sheet Names

• Select & Protect Cells Containing Formulas

• Reduce Data Entry Errors By Using Data Validation

• Display & Print Formula Syntax

• Use Auditing Tools to Check For Errors

• Check Data By Using Trace Precedents

• Use Comments to Annotate Your Worksheet