Learn to use Excel’s most powerful features on this 2-day advanced Excel training course.

This course is hands-on. It includes plenty of time to experiment with what you are being taught and ask questions.

By the time you finish, you’ll be an expert Excel user.

A complete course that covers all of Excel’s most advanced functions.
Friendly expert trainers, small groups and a comfortable place to learn.
Ongoing support & help with any issues you may have after the course.

The Trainer

Photo Of Dallas - Advanced Excel Trainer

Dallas is one of our regular trainers for our advanced Excel course. He has 12 years of experience as an IT trainer.

He also has a PGCE and worked as a school teacher for 7 years before moving into IT training.

 

What Will I Learn?

By the end of this course you’ll be very comfortable using Excel’s most advanced features. 

  • Working with logical functions in Excel.
  • Using the full range of Excel’s lookup and reference functions.
  • Creating and editing PivotTables.
  • Using the Data Consolidation feature to combine data from several workbooks.
  • Using Solver to solve complex scenario problems.
  • Creating macros to automate repetitive tasks in Excel.

Watch one of our trainers give a taster on what you’ll cover:

Course Details

Download full course details Download full course details

 

Click on the sections below to see the full syllabus that is covered during this advanced Excel course.

The first session of this course reviews the concept of creating and using names for cells which is covered in our Intermediate Excel Course. In doing so it also quickly reviews formulas and selections.

The key topics covered include:

  • Understanding Defined Names
  • Defining Names From Worksheet Labels
  • Using Names in Typed Formulas
  • Applying Names to Existing Formulas
  • Creating Names Using the Name Box
  • Using Names to Select Ranges
  • Pasting Defined Names Into Formulas
  • Defining Names for Constant Values
  • Creating Names From a Selection
  • Scoping Names to a Worksheet
  • Using the Name Manager
  • Documenting Defined Names

This section of the course introduces logical functions. This is a key part of more advanced Excel use. It focuses on the use of IF functions and how they can be combined to produce Nested IF functions and also combined with AND, NOT or OR in more complicated situations.

The key topics covered include:

  • Understanding Logical Functions
  • Using IF With Text
  • Using IF With Numbers
  • Nesting IF Functions
  • Using IFERROR
  • Using TRUE and FALSE
  • Using AND
  • Using OR
  • Using NOT

When working with large amounts of data the ability to validate data using Excel is critical. This is especially true when working in combination with logical formulas which require that all data is in the appropriate format to work. This session covers both data validation, which is checking that the data in the cells meets certain criteria, and also the ways that you can then highlight data that does not meet the criteria.

The key topics covered include:

  • Validating Data
  • Understanding Data Validation
  • Creating a Number Range Validation
  • Testing a Validation
  • Creating an Input Message
  • Creating an Error Message
  • Creating a Drop Down List
  • Using Formulas as Validation Criteria
  • Circling Invalid Data
  • Removing Invalid Circles
  • Copying Validation Settings

Lookup functions are another very powerful tool to master when working with large amounts of data. They allow individual items in a list to be found and then the corresponding value from another column or row in the same data table. For example, if working with a series including dates and values of a variable, they offer the ability to find certain data and have the formula return the value of that variable on the specific date being searched for.

The key topics covered include:

  • Understanding Data Lookup Functions
  • Using CHOOSE
  • Using VLOOKUP
  • Using VLOOKUP for Exact Matches
  • Using HLOOKUP
  • Using INDEX
  • Using Match
  • Understanding Reference Functions
  • Using ROW and ROWS
  • Using COLUMN and COLUMNS
  • Using ADDRESS
  • Using INDIRECT
  • Using OFFSET

Worksheets with a lot of content can become unwieldy and difficult to navigate around. To deal with this, Excel gives you the option to organise your data in groups. These allow you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet.

The key topics covered include:

  • Creating An Automatic Outline
  • Working With An Outline

PivotTables are one of Excel’s most powerful functions. They are extremely versatile and make it very easy to extract information from large tables of data without the use of formulas. They are also very quick to use as by moving, or pivoting, fields of data from one location to another using drag and drop functionality. They allow you to look at the same data in a number of different ways.

The key topics covered include:

  • Understanding Pivot Tables
  • Recommended Pivot Tables
  • Creating Your Own PivotTable
  • Defining the PivotTable Structure
  • Filtering a PivotTable
  • Clearing a Report Filter
  • Switching PivotTable Fields
  • Formatting a PivotTable
  • Understanding Slicers
  • Creating Slicers
  • Inserting a Timeline Filter

The session builds on the previous PivotTable session which introduced the concept. This takes the use of PivotTables one step further and leads delegates through summarising, grouping, sorting and naming the data held in the PivotTable that they have created. This session demonstrates how powerful Excel can be when used correctly. Thousands of data records can be quickly and simply manipulated, analysed and presented using PivotTables in a matter of minutes.

The key topics covered include:

  • Using Compound Fields
  • Counting in a PivotTable
  • Formatting PivotTable Values
  • Working With PivotTable Grand Totals
  • Working With PivotTable Subtotals
  • Finding the Percentage of Total
  • Finding the Difference From
  • Grouping in PivotTable Reports
  • Creating Running Totals
  • Creating Calculated Fields
  • Providing Custom Names
  • Creating Calculated Items
  • PivotTable Options
  • Sorting in a PivotTable

Excel’s Consolidate feature allows you to merge and summarize values from multiple workbooks. It is a great tool for combining data when several users work with different instances of the same file but can also be used when the data to be consolidated is held in different formats.

The key topics covered include:

  • Understanding Data Consolidation
  • Consolidating Data with Identical Layouts
  • Creating a Linked Consolidation
  • Consolidating Data with Different Layouts
  • Consolidating Data Using the SUM Function

The scenario function lets you take a forecast in Excel and quickly see what would happen if you used a different input value. For example, you might create a spreadsheet to forecast the financial results of a business. What if you lowered the average selling price by 10%, or perhaps 15%. Scenarios can be saved, so that you can apply them with a quick click of the mouse.

The key topics covered include:

  • Understanding Scenarios
  • Creating a Default Scenario
  • Creating Scenarios
  • Using Names in Scenarios
  • Displaying Scenarios
  • Creating a Scenario Summary Report
  • Merging Scenarios

Solver takes the scenario analysis tools of Data tables and scenarios one step further. It allows you to set an output cell which you would like to either minimise or maximise. It then lets you set the constraints under which the spreadsheet will operate and the cells which can be varied. Excel will then calculate the maximum or minimum that can be achieved in the objective cell and report the values of the variable cells which achieve this outcome.

The key topics covered include:

  • Understanding How Solver Works
  • Installing The Solver AddIn
  • Setting Solver Parameters
  • Adding Solver Constraints
  • Performing the Solver Operation
  • Running Solver Reports
  • Understanding Data Importing
  • Importing From an Earlier Version
  • Understanding Text File Formats
  • Importing Tab Delimited Text
  • Importing Comma Delimited Text
  • Importing Space Delimited Text
  • Importing Access Data
  • Working With Connected Data
  • Unlinking Connections
  • Exporting to Microsoft Word
  • Exporting Data as Text
  • Inserting a Picture
  • Modifying an Inserted Picture
  • Controls
  • Understanding Types of Controls
  • Understanding How Controls Work
  • Preparing a Worksheet for Controls
  • Adding a Combo Box Control
  • Changing Control Properties
  • Using the Cell Link to Display the Selection
  • Adding a List Box Control
  • Adding a Scroll Bar Control
  • Adding a Spin Button Control
  • Adding Option Button Controls
  • Adding a Group Box Control
  • Adding a Check Box Control
  • Protecting a Worksheet With Controls

Excel macros save time by automating tasks that you perform frequently. A macro is a precise set of actions, performed one after another that carry out the task. Also macros can be written using Excel VBA that is beyond the scope of this course.

The most efficient way to create a macro is to record one in Excel. Excel will store every action that you take one after another while the macro recorder is on and then repeat that series of actions each time that macro is run. However, as Excel records every action that you carry out when recording a macro you need to plan your macro carefully to ensure that each action is applicable every time you run the macro.

The key topics covered include:

  • Understanding Excel Macros
  • Setting Macro Security
  • Saving a Document as Macro Enabled
  • Recording a Simple Macro
  • Running a Recorded Macro
  • Relative Cell References
  • Running a Macro With Relative References
  • Viewing a Macro
  • Editing a Macro
  • Assigning a Macro to the Toolbar
  • Running a Macro From the Toolbar
  • Assigning a Macro to the Ribbon
  • Assigning a Keyboard Shortcut to a Macro
  • Deleting a Macro
  • Copying a Macro

Am I Ready For This Course?

This course is for people who are already reasonably experienced Excel users.

We recommend that you are comfortable with the below before joining this course.

  • Using formulas and basic functions
  • Using absolute cell references
  • Editing and visualising data using formatting and layout techniques including charting
  • Working with database tools like sorting and filtering

Not sure if this is the right level course for you?

Our Excel course level adviser takes less than 60 seconds to complete. Alternatively, give us a call and we’d be delighted to help.

The Training Day

You don’t need to bring anything with you. We provide everything you need for two really productive, fun days of training.

Our courses run from 9.30 am to roughly 4.30 pm with refreshments and biscuits throughout the day and a break for lunch.

We offer a relaxed, supportive learning environment, fully air-conditioned training facilities and some of the nicest instructors on the planet.

Also, you’ll receive:

A full-colour manual which covers everything in the course.
Your course exercises so that you can practice at home.
A Certificate of Attendance.

 

Online Training Requirements

To attend this Excel course online, you will need:

MS Excel on your Windows PC with a camera, speakers & microphone
A stable internet connection capable of running Zoom
To be a confident computer user and able to use Zoom

If you have access to a second screen, we would encourage you to use it as it improves the experience.