Microsoft Useful Excel Formulas and Functions

Useful Excel Formulas and Functions
One Day - Instructor-led

This in-depth, one-day instructor-led 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 aimed at information workers who need to learn more in-depth 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