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.70 out of 5)
255 Excel advanced formulas student evaluations
Location: Level 2
80 Mount St
North 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.

Virtual Course Dates (via Zoom)

Course Date Price Add Course Qty Status
11-Oct-21 (Mon)$385.00 0
10-Nov-21 (Wed)$385.00 0

Public Course Dates

Course Date Price Add Course Qty Status
09-Dec-21 (Thu)$385.00 0
13-Jan-22 (Thu)$385.00 0
09-Feb-22 (Wed)$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
    Alot was learnt over the period of the course. Very instructive & very informative, as well as easy to follow. It helps alot, too when you have an instructor that is well versed in the topic. I would commend Sean for his efforts.
    - Ben
  • Excel Advanced Formulas training course
    Always good!
    - Lilian
  • Excel Advanced Formulas training course
    It was good being able to you data from my workplace to use for examples. Also tips about the general way in which you should organise (structure) your data.
    - Rodney
  • Excel Advanced Formulas training course
    Craig is very knowledgeable helpful and friendly. Good course. Thank you!!
    - Merrilyn
  • Excel Advanced Formulas training course
    The course is very well organised and easy to follow. Thanks.
    - Yvonne
  • Excel Advanced Formulas training course
    Great Course, will be letting other work mates to come along!
    - Darren
  • Excel Advanced Formulas training course
    Very happy with what I learned today. Thanks!
    - Sophia
  • Excel Advanced Formulas training course
    The course is very useful and would recommend to friends
    - Tracey
  • 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
    Great course! Learnt a lot of new tricks that will make some of my tasks much faster.
    - Bryce
  • Excel Advanced Formulas training course
    Great course. Would recommend.
    - Marc
  • 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
    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
    Great course - thanks Craig
    - Judy
  • Excel Advanced Formulas training course
    Awesome, thank you very much for the tips and tricks :)
    - Kylie
  • Excel Advanced Formulas training course
    Very easy to understand and follow.
    - Evette
  • Excel Advanced Formulas training course
    Very helpful! I am much more confident with formulas now.
    - Imogen
  • 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
    Very easy to understand and follow
    - Evette
  • Excel Advanced Formulas training course
    Learn`t heaps. Thanks Tom!
    - Dominique
  • Excel Advanced Formulas training course
    Fantastic base to go forward with, worked well to complete earlier training in Excel.
    - Emma
  • Excel Advanced Formulas training course
    The trainer went out of his way to make the course interesting by going into detail on topics we wanted to know more about.
    - Marcus
  • Excel Advanced Formulas training course
    Very happy for what I am learning today. Thanks!
    - Sophia
  • 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
    The trainer was very professional and knew his stuff back the front. Very good and highly recommended.
    - Phil
  • 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
    Excellent course well presented definitely recommend to other employees and friends.
    - Chris
  • Excel Advanced Formulas training course
    Great... had good fun learning :)
    - Valerie
  • Excel Advanced Formulas training course
    Really great course! Learnt so much!
    - Sarah
  • Excel Advanced Formulas training course
    The facilitator Craig was nice friendly and a great teacher, service & quality was perfect. 10/10 from me.
    - Nouha

Need Assistance

  • Send Email
Raising the Bar
Warning Icon Information Icon

OK
1300 937 782 Contact us