Introduction
Elements of this syllabus are subject to change.
This in-depth, one-day instructor-led course provides students 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 or your students use. Be it Excel 2002 or 2003; you will be armed with the knowledge to provide a nifty solution. 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.
Audience
Aimed at information workers who need to learn more in-depth techniques of using functions and formulas.
At Course Completion
After completing this course, students will be able to:
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.
Prerequisites
Before attending this course, students must be:
An intermediate user of Microsoft Office Excel 2002 or 2000 for at least 1 year.
:: Course 50098A ::
Module 1: FORMULAS AND FUNCTIONS BASIC
This module explains how to create basic formulas. The concept of cell referencing is introduced, including relative, absolute, and mixed references. Other tips and tricks on creating formulas fast, such as copying formulas without changing cell references, transposing formulas, pasting formulas as values, and performing quick calculations, is also presented. Finally, students are exposed to techniques for creating nested functions in formulas.
Lessons
Introduction—Formula Basics
Use Cell References
Copy a Formula Without Changing Cell References
Transpose a Formula
Copy and Paste Values
Perform a Quick Calculation
Use Nesting Functions
Lab 1
Review 1
Practice 1
After completing this module, students will be able to:
Use cell references in formulas effectively.
Create formulas and desired results by using quick tips and tricks.
Module 2: STATISTICAL AND LOGICAL FUNCTIONS
This module explains how to use statistical functions like COUNTIF, SUMIF, and AVERAGEA. This module also introduces logical functions such as IF and ISERROR, and how to use logical functions in nested conditions.
Lessons
Use COUNTIF to Calculate Survey Results
Use SUMIF to Add Data That Satisfy Conditions
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
Lab 2
Review 2
Practice 2
After completing this module, students will be able to:
Use statistical functions in getting answers quickly for calculating survey results, add data that satisfy conditions, and create averages that include all cases.
Use logical functions to prevent division by zeros and avoid error display.
Module 3: LOOKUP and REFERENCE FORMULAS
This module explains how to use lookup functions like VLookup and HLookup to find results by searching through a reference table. This module also introduces another technique in referencing a table by combining two other lookup functions, match and index.
Lessons
Use VLookup to Find Specific Data
Use HLookup to Find Values in Rows
Use Match & Index to Retrieve Data
Lab 3
Review 3
Practice 3
After completing this module, students will be able to:
Use lookup functions to find answers by referencing a table.
:: Course 50098A ::
Module 4: TEXT FORMULAS
This module explains how to use text functions to manipulate text in Excel. This module will show how to use functions like UPPER, LOWER, and PROPER to change case of text. Students will learn how to use text operators to join text and formatted numbers. Students will also learn a variety of functions such as LEFT, RIGHT, and MID, and formula techniques to convert text format into numbers, break imported text into columns, and extract words into columns.
Lessons
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
Lab 4
Review 4
Practice 4
After completing this module, students will be able to:
Use text functions to manipulate text in Excel.
Module 5: DATE and TIME FORMULAS
This module explains how to use date and time functions to accomplish a variety of tasks such as adding a number to a date to get new dates, calculate the difference between two dates, and perform time field calculations.
Lessons
Add a Number to Current Date to Get New Dates
Calculate a Period of Time Between Two Dates
Perform Calculations with Time Fields
Lab 5
Review 5
Practice 5
After completing this module, students will be able to:
Get accustomed to a variety of date and time formulas.
Module 6: ARRAY AND DATABASE FUNCTIONS
This module explains how to use array formulas that calculate for a range rather than by single cells. Array formulas provide a variety of advantages, especially in eliminating intermediary formulas.
Lessons
Use Array Formulas
Calculate the Difference Between Maximum and Minimum Values in an Array
Find Values in Lists
Use Frequency Function to Count Responses
Analyze Data in a Database
Lab 6
Review 6
Practice 6
After completing this module, students will be able to:
Use array formulas in calculations.
:: Course 50098A ::
Module 7: EFFICIENCY TIPS
This module explains how to use some tips and tricks in making the formulas you created in the previous modules better. Tips such as reducing calculation time, entering formulas quickly, and checking and reducing errors in formulas are presented.
Lessons
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 Worksheet
Lab 7
Review 7
Practice 7
After completing this module, students will be able to:
Use a variety of tips to help make formulas better
Programme scope, delivery arrangements and suitability are confirmed during enquiry.