Advanced Excel Core Level 3

If you use Excel for budgets, timesheets, reports or other data that needs calculating, this one-day course will improve your ability to organise and analyse data. You will take functions further and be able to make them more powerful by nesting them. You will also learn ways to represent your data in a visual and readily understandable chart.

Course Overview

  • Standard Price

    £300*
  • Public Sector Price

    £250*
  • Charity Price

    £200*
  • *Excluding VAT
  • Duration

    1 day
  • Class size

    8 approx.
  • Location

    London
    Map
  • Requirements

    Learners must be confident creating formulas that use Absolute Cell References ($ to fix cell references), Named Ranges and more complex functions like IF. If you’re not confident with those, look at our Excel Core Level 2 course.

Course overview

What You Will Learn

  • Create Partial Absolute Cell References to lock just the Column or just the Row reference in a formula
  • Move around a spreadsheet speedily using range names and use them in formula to improve accuracy and readability
  • Accurate data entry by using Data Validation. e.g. a dropdown list to choose from to avoid errors
  • Use the table tools and calculate within a table
  • Advanced Conditional Formatting – using ‘If Formula Is’ to format cells other than the one that contains the value you are testing
  • Create calculations based on single or multiple conditions. For example, calculate a discount for those over a specific age or ordering a high volume (IF with AND and OR)
  • Use ISERROR and ISNA to tidy up your results
  • Cross reference data using an Hlookup or Vlookup function. For example, look up a National Insurance Number to find other personal details from another sheet or book
  • Use Formula Auditing tools to check for errors and make nested formulas easier to fix
  • Create charts that include a secondary axis and trendlines
  • Create Pivot Charts

Timetable

Session One: Range Names and Nesting Functions

  • Partial Absolute Cell References
  • Range Names
  • Data Validation
  • Nesting Logical Function: IF, AND, OR

Session Two: Excel 2016 functions

  • IFs
  • SWITCH
  • CONCAT
  • TEXTJOIN
  • MINIFS, MAXIFS

Session Three: Lookups

  • VLookup (and if there’s time, HLOOKUP too)
  • Nesting COLUMN and/or ROW function to make your Lookup autofillable
  • IFERROR, IFNA

Session Four: Data visualisation

  • Conditional formatting using a formula, including using IF and conditional formatting for RAG
  • More charts: Combo, Funnel, Waterfall, Box and whisker, Pivot
  • Forecast sheet

Course overview

What You Will Learn

In particular you will be able to:

  • Create Partial Absolute Cell References to lock just the Column or just the Row reference in a formula
  • Move around a spreadsheet speedily using range names and use them in formula to improve accuracy and readability
  • Accurate data entry by using Data Validation. e.g. a dropdown list to choose from to avoid errors
  • Use the table tools and calculate within a table
  • Advanced Conditional Formatting – using ‘If Formula Is’ to format cells other than the one that contains the value you are testing
  • Create calculations based on single or multiple conditions. For example, calculate a discount for those over a specific age or ordering a high volume (IF with AND and OR)
  • Use ISERROR and ISNA to tidy up your results
  • Cross reference data using an Hlookup or Vlookup function. For example, look up a National Insurance Number to find other personal details from another sheet or book
  • Use Formula Auditing tools to check for errors and make nested formulas easier to fix
  • Create charts that include a secondary axis and trendlines
  • Create Pivot Charts
  • Create macros to automate your work and assign it to a toolbar button to make it easier to use

Timetable

Session 1

  • Brief recap on Excel Core Level 2
  • Partial Absolute Cell References
  • Using Insert Function
  • Good Design Principles
  • Range Names
  • Data Validation, Table Tools and Calculating in Tables.

Session 2

  • Conditional Processing – Using the If Function with AND and OR
  • Nesting Ifs with ISERROR and ISNA
  • Using VLookup or HLookup to categorise and find an exact match
  • Auditing and Error Spotting.

Session 3

  • Charts with Trendlines
  • Adding a Secondary Axis
  • Pivot Charts.

Session 4

  • Introduction to Macros – Running and recording macros
  • Assigning a macro to a button on a toolbar.

Course overview

What You Will Learn

In particular you will be able to:

  • Create Partial Absolute Cell References to lock just the Column or just the Row reference in a formula
  • Move around a spreadsheet speedily using range names and use them in formula to improve accuracy and readability
  • Accurate data entry by using Data Validation. e.g. a dropdown list to choose from to avoid errors
  • Use the table tools and calculate within a table
  • Advanced Conditional Formatting – using ‘If Formula Is’ to format cells other than the one that contains the value you are testing
  • Create calculations based on single or multiple conditions. For example, calculate a discount for those over a specific age or ordering a high volume (IF with AND and OR)
  • Use ISERROR and ISNA to tidy up your results
  • Cross reference data using an Hlookup or Vlookup function. For example, look up a National Insurance Number to find other personal details from another sheet or book
  • Use Formula Auditing tools to check for errors and make nested formulas easier to fix
  • Create charts that include a secondary axis and trendlines
  • Create Pivot Charts
  • Create macros to automate your work and assign it to a toolbar button to make it easier to use

Timetable

Session 1

  • Brief recap on Excel Core Level 2
  • Partial Absolute Cell References
  • Using Insert Function
  • Good Design Principles
  • Range Names
  • Data Validation, Table Tools and Calculating in Tables.

Session 2

  • Conditional Processing – Using the If Function with AND and OR
  • Nesting Ifs with ISERROR and ISNA
  • Using VLookup or HLookup to categorise and find an exact match
  • Auditing and Error Spotting.

Session 3

  • Charts with Trendlines
  • Adding a Secondary Axis
  • Pivot Charts.

Session 4

  • Introduction to Macros – Running and recording macros
  • Assigning a macro to a button on a toolbar.

Which Excel course is right for you?

Making sure you’ve booked the right course isn’t always easy.

Luckily we have a handy survey to help you decide.

And if you’re still unsure, call our friendly team on 020 7375 7300 and we’ll help you work out what you need.

Take our Excel survey
5 out of 5 stars

Frist and foremost, the teacher was excellent. Furthermore, I found the entire day to be extremely well thought out and carefully constructed. It turned out to be much better than I was expecting. After all - it was a course on Excel training, yet I wasn't bored at all.

Trusted Customer27 days ago

What is it like to learn with Happy?

Our facilitators make learning interactive and fun. In this video, Maureen delivers a session on Formulas and Functions in Excel at Happy's HQ in London.

Book now

See our available course dates, or contact us for bespoke options

First, select your version of Excel...

The Small Print

The course will run from 10am to 4:45pm (hot buffet lunch 1pm to 1:45pm). Please be careful to arrive by 9:45am for registration and refreshments. The course will start precisely on time and it may not be possible to go back over material missed by late arrivals. If you arrive over 30 minutes late you may be asked to leave and attend another day.

Please ensure you read our Terms and Conditions before booking for our payment terms and cancellation policy.

The happy Guarantee

We want everyone to go away from our courses feeling as though they have learnt useful, practical skills that they can use straight away when they go back to work.

If you don’t feel that your training has been useful or if you are unhappy in any way, please get in touch with our Customer Services team within 30 days of the course. We will be happy to offer you additional support and training free of charge to help you to gain the skills you need, or if you’d prefer, a full refund.

happy learners