Microsoft Excel Functions and Formulas (1 day recommended)

Please specify the urgency and number of days

This course is aimed at experienced Excel users and focuses on the creation of formulas using the most useful functions in each of Excel's main function categories. Delegates will also be shown how to use nested functions to build complex formulas, how to audit formulas and handle formula errors.

The course is suitable for those with a good knowledge of Excel basics. Functions are at the core of Microsoft Excel and provide much of its power. However, many users find working with functions and formulas confusing and frustrating.

Course Outline

Building formulas

Entering and editing formulas
Understanding operator precedence
Copying and moving formulas
Understanding relative reference format
Understanding absolute reference format
Copying a formula without adjusting relative references
Displaying Worksheet Formulas
Working with range names in formulas
Pasting a name into a formula
Applying names to formulas
Understanding external references

Working with functions

Inserting functions
Supplying function arguments
Supplying range arguments
Inserting nested functions

Text functions

Converting text
LOWER(), UPPER(), PROPER()
Extracting a substring
LEFT(), RIGHT(), MID()
Concatenation
Creating customer account numbers

Logical functions

Using the IF() function
Working with nested IF()s
Using AND, OR and NOT

Lookup functions

Creating lookup tables
Using VLOOKUP() and HLOOKUP()
Using range lookups
Finding exact matches
Using the CHOOSE() function

Date and time functions

How Excel handles dates and times
Construction dates with the DATE() function
Extracting date elements with DAY(), MONTH(), and YEAR()
Isolating the day, month and year
Determining the day of the week
Calculating the tme between two dates using DATEDIF()
Calculating elapsed time
Creating time sheets

Maths functions

ROUND() and  MROUND()
ROUNDDOWN() and ROUNDUP()
CEILING() and FLOOR()
EVEN() and ODD()
INT() and TRUNC()
Using  RANDBETWEEN() to generate data
Using  RANDBETWEEN() to generate date and time values

Database functions

How database functions work
Defining the Excel database
Defining the criteria area
Using AND and OR with your criteria
The DSUM() function
The DAVERAGE() function
The DCOUNT() function
DMIN() and DMAX()
The DGET() function

Dealing with errors

Understanding Excel error values
#DIV/0!, #NAME?, #REF, #NULL!, #N/A
Using conditionals to anticipate error values
Using IFERROR()
Using the formula error checker
Auditing a worksheet
Tracing cell precedents and dependents
Evaluating formulas
Watching cell values

Back to top