Excel Functions and Formulas Training Course (1 day - £295, plus VAT)

Advanced Excel tuition

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. This advanced Excel training course focuses on the creation of formulas using the most useful and essential functions in each of Excel's main function categories. Delegates will also be shown the various types of formulas and the different methods of creating them, the different ways of inserting functions and using nested functions to build complex formulas. This Excel course also covers the auditing of formulas and and the handling of errors.

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