Course Details close

Course Details

Price:
(inc GST)
$ 385
Application:Excel
Level:Advanced Formulas
Version:All-Versions
Duration:1 day
Location:
ITIC Live
Level 3, 321 Pitt St
Sydney NSW 2000
Participants:Max 10
Length:9:00 to 16:30
Break Times:
(approx)
10:30 (15mins)
12:30 (1hr)
14:45 (15mins)
Course Dates: View
Course Outline: View

Excel Advanced Formulas Course

   (4.69 out of 5)
249 Excel advanced formulas student evaluations
Location: ITIC Live: Level 3
321 Pitt St
Sydney NSW 2060
Break Times:
(approx)
10:30 (15mins)
12:30 (1hr)
14:45 (15mins)
Duration: 1 day Participants: Max 10
Course Length: 9:00 to 16:30 Price:
(inc GST)
$ 385
Sale Price:
(inc GST)
$

Quick links

Excel Advanced Formulas Course

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 This Course

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 North Sydney. 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.

Course Inclusions

FREE Course Re-sit

8 months

When you book a public course at Raisng the Bar we offer you a FREE course re-sit for up to 8 months from the scheduled course date.

FREE Email Support

12 months

Free email support for up to 12 months from the scheduled course date.

Small Class Sizes

10 students

All of our courses are capped at a maximum of 10 students to maximise your learning experience.

Classroom Training

All of our courses are face to face, hands on, classroom based training delievered by .

Course Manual

Every student will receive a printed course manual with step by step instructions to use as a reference guide.

Computer Lab

No need to bring your own laptop. Our training courses are conducted in fully equipped computer labs with free Wi-Fi.

Course Certificate

Every student receives a certificate of completion at the end of course to recognise your new skills.

Webinar Course Dates (via Zoom)

Course Date Price Add Course Qty Status

There are currently no webinar course dates scheduled for this course.

Call us today to discuss - 1300 937 782.

Public Course Dates (Face to Face)

Course Date Price Add Course Qty Status
15-Feb-21 (Mon)$385.00 0
26-Mar-21 (Fri)$385.00 0
16-Apr-21 (Fri)$385.00 0

Course Outline

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

    ARRAY FORMULAE

    Array Formulae

    IMPORTANT AMENDMENT

    An Introduction to Arrays and Array Formulas

    • Create A Multi-Cell Array Formula

    More on Array Formulas

    • Advantages of Array Formulas
    • Array Formula Syntax
    • Rules for Entering and Changing Array Formulas
    • Expanding an Array Formula
    • Disadvantages of Array Formulas
    • Array Constants
    • Creating a Horizontal Constant Array
    • Creating a Vertical Constant Array
    • Creating A 2D Constant Array
    • Using Array Constants within Formulae
    • LARGE Function as a MultiCell Array
    • ROUND Function as a MultiCell Array
    • COUNTIF Function as a MultiCell Array

    More on Array Formulas

    • Naming Array Constants
    • Troubleshooting Array Constants
    • Naming and Applying an Array Constant in a Formula
    • Simplifying the AND Function Using an Array
    • Excluding Zeros from a MIN (Array Formula)
    • SUM IF Multiple Criteria
    • Excluding Zeros from an Average for a Specific Item
    • Create an Array from Existing Values
    • Create Array Constants from Existing Values
    • More Array Examples (with SUM)
    • More Array Examples – (with AVERAGE)
    • More Array Examples - Count the Differences between Two Ranges of Cells
    • Custom {AVERAGE(IF)} function
    • Custom {SUM(IF)} function
    • Custom {MIN(IF)} function

Student Comments

  • Excel Advanced Formulas training course
    great course!
    - Guy
  • Excel Advanced Formulas training course
    Tom was an excellent trainer and knew his content with great explanations and examples.
    - Elizabeth
  • Excel Advanced Formulas training course
    Most of the Advanced Formulas were great and would impact positively on our work environment.
    - Laurence
  • Excel Advanced Formulas training course
    The facilitator Craig was nice friendly and a great teacher, service & quality was perfect. 10/10 from me.
    - Nouha
  • Excel Advanced Formulas training course
    Very happy with what I learned today. Thanks!
    - Sophia
  • Excel Advanced Formulas training course
    Tom was excellent presenter, fast and attention grabbing, i would definately recommend him to others, and would in fact request him.
    - Nelson
  • Excel Advanced Formulas training course
    Awesome, thank you very much for the tips and tricks :)
    - Kylie
  • Excel Advanced Formulas training course
    The trainer was very professional and knew his stuff back the front. Very good and highly recommended.
    - Phil
  • Excel Advanced Formulas training course
    Great course!
    - Aimee
  • Excel Advanced Formulas training course
    It was really interesting to be able to build all these nested formulas and learn all the keyboard shortcuts!
    - Belinda
  • Excel Advanced Formulas training course
    Tom was great in really helping me understand the bits i had not previously got to grips with from the Excel course
    - Matt
  • Excel Advanced Formulas training course
    Craig is very knowledgeable helpful and friendly. Good course. Thank you!!
    - Merrilyn
  • Excel Advanced Formulas training course
    Always good!
    - Lilian
  • Excel Advanced Formulas training course
    Excellent course!
    - Henry
  • Excel Advanced Formulas training course
    Bridging course between Adv xls which just does pivot tables and intro to VBA is great!
    - Nicole
  • Excel Advanced Formulas training course
    Very easy to understand and follow.
    - Evette
  • Excel Advanced Formulas training course
    Excellent! Tom not only taught what was in the guide but expanded providing useful insights, hints and tips.
    - Michael
  • Excel Advanced Formulas training course
    Great course
    - Raluca
  • Excel Advanced Formulas training course
    Very helpful course.
    - Sarah
  • Excel Advanced Formulas training course
    I found the course very helpful and informative. I learnt alot that I can use everyday in my role.
    - Kylie
  • Excel Advanced Formulas training course
    Tom is a great presenter and really talks us through the most difficult formulas.
    - Charles
  • Excel Advanced Formulas training course
    The trainer was great, extremely helpful in keeping content relevant.
    - Kaila
  • Excel Advanced Formulas training course
    Great course. Would recommend.
    - Marc
  • Excel Advanced Formulas training course
    Great course! Learnt a lot of new tricks that will make some of my tasks much faster.
    - Bryce
  • Excel Advanced Formulas training course
    Great facility, great presenter. very friendly
    - Jasmin
  • Excel Advanced Formulas training course
    Great course - thanks Craig
    - Judy
  • Excel Advanced Formulas training course
    Very easy to understand and follow
    - Evette
  • Excel Advanced Formulas training course
    The course is very useful and would recommend to friends
    - Tracey
  • Excel Advanced Formulas training course
    Delivered in easy to understand language, very professional and knowledgable. Has improved my confidence with formulas & functions.
    - Monique
  • Excel Advanced Formulas training course
    Very helpful! I am much more confident with formulas now.
    - Imogen

Need Assistance

  • Send Email
Raising the Bar
Warning Icon Information Icon

OK
1300 937 782 Contact us