Microsoft Useful Excel Formulas and Functions
Useful Excel Formulas and Functions One Day
Instructorled
This indepth course provides you with the knowledge of using formulas in your daily work as well as the ability to expand your Microsoft Office Excel knowledge. The course will empower you to have answers to Excel questions at your fingertips. There are numerous new tips you will learn irrespective of the Excel version you use; you will be armed with the knowledge to provide a logical solution. This course will serve as a refresher as well as a reference.
Audience: This course is intended for information workers who need to learn more indepth techniques of using functions and formulas
Prerequisites: Before attending this course, students must have:
 Taken the Windows XP Level 1 course or have the basic skills and knowledge of Windows applications

Be an intermediate user of Microsoft Excel
Objectives:
 Enter formulas
 Use the AutoSum button
 Use the AutoSum list
 Enter basic functions
 Insert functions in formulas
 Edit functions
 Use the AutoCalculate feature
 Use Range Borders to modify formulas
 Check for formula errors
 Use financial functions
 Use logical functions
 Use date functions
 Format dates
 Revise formulas
 Use the VLOOKUP function
 Use the HLOOKUP function
 Use the IF function
 Use nested IF functions
 Display the Formula Auditing toolbar
 Display/remove dependent arrows
 Display/remove precedent arrows
 Remove all tracer arrows
 Use the Trace Error button
 Trace cells causing errors
Course Outline:
Lesson 1: Formulas and Functions Basic
 Introduction – Formula Basics
 Use Cell References
 Copy a Formulas Without Changing Cell References
 Transpose a Formulas
 Copy and Paste Values
 Perform a Quick Calculation
 Use Nesting Functions
Lesson 2: Statistical and Logical Functions
 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 and Reference Formulas
 Use VLookup to Find Specific Data
 Use HLookup to Find Values in Rows
 Use Match and Index to Retrieve Data
Lesson 4: Text Formulas
 Change Case of Text
 Join 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: Date and Time Formulas
 Add a Number to Current Date to Get New Dates
 Calculate a Period of Time between Two Dates
 Perform a Calculation with Time Fields
Lesson 6: Array and Database Functions
 Use Array Formulas
 Calculate the Difference between Maximum and Minimum Values in an Array
 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 and Protect Cells Containing Formulas
 Reduce Data Entry Errors by Using Data Validation
 Display and Print Formula Syntax
 Use Auditing Tools to Check for Errors
 Check Data by using Trace Precedents
 Use Comments to Annotate your Worksheets