View Basket
Recover Basket
Excel
Functions and Formulas
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 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. The 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