EXCEL ADVANCED FORMULAS

Course Overview

This is a one-day training course tailored to give experienced Excel users the skills required to use Excel’s most commonly used formulas. Lead by dedicated instructors who are experts in the Microsoft Office package, you’ll learn real world tips and tricks for creating and using advanced Excel formulas and functions. We’ll teach you how to enter common formulae using name ranges, sums and dates, utilise helpful functions including database functions, rounding functions, logical functions, financial functions, nesting functions and working up to array formulas and more.

Our courses are taught face-to-face using simple instructions, clear demonstrations and hands on activities. Class sizes are kept small to ensure students receive personalised support and achieve the highest learning outcomes possible. Training with Raising the Bar will provide you with brand new skills and knowledge of Excel, allowing you to create efficient spreadsheets using advanced formulas. We supply all the equipment so all you need to do is show up.

Upon completion of the Excel Advanced Formulas course, you’ll have the proficiency required to use Excel’s common formula functions. You'll also leave with a certificate of completion and a comprehensive course manual to work through at your leisure so you can further develop your skills at your own pace.

Who Should Attend

The Excel Advanced Formulas Training Course is designed especially for students who already have an advanced knowledge of Excel and want to expand their skills to utilise more complex formula functions. This course is perfect for users who want to gain confidence to create, edit and troubleshoot the Excel formulas and functions that they currently utilise in their Excel spreadsheets and workflows.

Most students who attend this course are already comfortable with Excel and know how to use its more complex and technical features to create professional, productive spreadsheets. This course is right for you if you’re an experienced Excel user who wants to learn how to use nesting, name ranges, sum functions, count functions, date and time formulas, various database functions, audit formulas, arrays, concatenation and more.

Our public training courses are delivered throughout the year in the Raising the Bar training centre in the Sydney CBD. Alternatively, private training sessions are available by appointment for organisations in Sydney, Melbourne, Adelaide, Canberra, Brisbane and Perth.

Course Prerequisites

Our course is designed for experienced Microsoft Excel users. Students should have completed our Excel Advanced course or have equivalent skills with the more complex functions of Excel and its associated tools. You will also need general computer skills using the Windows operating system.

Raising the Bar

1300 937 782

www.rtb.com.au

COMMON FORMULAE

Order of Operation (BEMDAS)

How to Enter a Formula Using the Keyboard
Using Point and Click Formulas
Using AutoSum to Create Formulas
Automatically Fill in a Series of Formulas (Autofill)
Another Alternative for Performing the SUM Function
Calculating a percentage
AutoFilling Relative Cell References
Show Formulas in a Spreadsheet
AutoFilling Absolute Cell References
Calculating an AVERAGE
Calculating a MINIMUM and MAXIMUM
LARGE and SMALL Functions
REPT Function (Repeats characters)
MODE function

COUNTING, TESTING AND LOGICAL FORMULAE

COUNT function
COUNTA function
COUNTBLANK function
COUNTIF function
SUMIF function
IF Function to Display a Text Message

Nesting functions

Nesting an IF Function to Display a Calculated result

IS functions

ISBLANK functions
ISERROR functions
IFERROR functions

Other Functions in the Information Category

AND Function / OR Function

AND function
OR function
Nesting IF with AND/OR functions

LOOKUP AND REFERENCE FUNCTIONS

Naming Ranges

Naming a Range Using the Name Box

Using VLOOKUP and Reference Functions in Formulas

VLOOKUP function
Create Name Ranges using Create from Selection
HLOOKUP function
Using the Define Name dialog box

Using LOOKUP

Using the LOOKUP Function in a Formula

Using INDEX and MATCH

INDEX function
MATCH function
Nesting MATCH and INDEX functions
Using Nesting to Replace Error Messages using IFERROR

SUBTOTALS

SUBTOTALS function
Nesting IF and ISERROR and MATCH functions to compare two lists

DATE AND TIME FORMULAE

Common Date and Time Functions

Using Simple Date and Time Functions
TODAY function
NOW function
DAY function
MONTH function
YEAR function
DATE function
NETWORKDAYS function
More Complex Date Calculations
Nesting Date Functions

DATABASE FUNCTIONS

Database Functions

DAVERAGE function
DCOUNT function
DCOUNTA function
DMAX function
DMIN function
DSUM function

ROUNDING FUNCTIONS

The Need for Rounding

ROUND function
ROUNDUP function
ROUNDDOWN function
CEILING function
FLOOR function
INT function

FINANCIAL FUNCTIONS

Financial Functions

RATE function
NPER function
PV function
PMT function

USEFUL TEXT FUNCTIONS

Text Functions

UPPER function
LOWER function
PROPER function
CONCATENATE function
TEXT function

STRUCTURED TABLE REFERENCES

Structured References

Formulas Compared
Examples of Structured Reference formulas
Formatting a Table
Naming the Table
Turning on the Total Row
Adding Calculations using the Total Row
Entering Structured References Using the Mouse or Keyboard