Advanced Excel

Who is this course for?

This one day course is suitable for those with a good working knowledge of Excel, or who have completed our Intermediate course and would like a greater understanding of Excel’s formulas and functions and advanced features.

What are the benefits

At the end of this course you will have a better understanding of some of the more advanced Excel functions, use the “What If Analysis” tools to forecast, be able to manipulate data using Pivot Tables and record Macros to eliminate repetitive actions.

Enquire about this course

Course Enquiry

Please type your full name.
Invalid email address.
Invalid email address.
Invalid Input

Course Duration

This is a 2 day course, please contact us to discuss the content if you would like this course delivered as a 1 day course

Course Content

Logical and Lookup Functions

  • Recap of the IF function
  • Creating nested IF functions
  • Working with VLOOKUP and HLOOKUP
  • Finding data using INDEX and MATCH
  • Using the COLUMN function
  • Understanding the error functions ISERROR, ISNA, IFERROR and IFNA (2013)
  • Work with the logical functions, AND, IF, OR
  • Analyse data using SUMIF and COUNTIF

Text Functions

  • Making use of the text functions CONCATENATE, LOWER, UPPER, PROPER, LEFT, RIGHT, MID, TRIM
  • Text to Column

Using Analytical “What If” Tools

  • Using Goal Seek
  • Creating One and Two Input Data Tables
  • Using Scenarios to forecast costs
  • Creating and saving Scenarios
  • Viewing Scenarios

Data Validation

  • Use Data Validation to create drop down lists

Excel as a Database

  • Working with the Database functions DSUM etc
  • Finding and dealing with duplicate entries

Pivot Tables and Pivot Charts

  • Creating and using a Pivot Table
  • Using “recommended” Pivot Tables (2013)
  • Formatting data with the Table feature
  • Filtering and sorting data within a Pivot Table
  • Automatically grouping Pivot data
  • Manually grouping data in a Pivot Table
  • Renaming groups
  • Adding the Slicer functionality (2010 & 2013)
  • Working with the Timeline feature (2013)
  • Creating Pivot Charts

Introduction to Recording Macros

  • Recording a Macro
  • Playing and deleting a Macro
  • Basic editing of a Macro’s Visual Basic Code
  • Inserting copied code into a Macro
  • Running your Macro with a custom button
  • Adding your Macro to the Quick Access Toolbar

Auditing Worksheets

  • Tracking Errors in Excel
  • Understanding Precedents & Dependencies
  • Viewing and hiding tracer arrows
  • Using the Watch Window
  • Evaluating formulas

What our users say

 

  • A very good course, trainer had good knowledge and delivered well
  • I like the fact that you have selected topics/functions on the basis of their interest/possible use - course is much more than pertinent and a better use of time
  • Very friendly and helpful trainer
  • Ray is the only IT Trainer who has ever inspired me with enough confidence to learn effectively
  • It was very good, I really enjoyed it and learnt a lot
  • I will definitely use the Pivot Tables at work in the future! Thank you for such a great training day!
  • Will ensure my team all attend (they all work remotely)
  • Exactly what I was hoping to learn, thanks

  • Contact Us
  • 44 (0) 208 681 1485
  • 44 (0) 7794 0689 6985

E Mail Us