Excel Intermediate 2 - Data Analysis and more functions and formulae

Course Outline


This one day course is for people who need to analyse large data sets, manipulate data with a range of Excel functions.


At the end of the course delegates will be able to:

  • Use a range of Excel decision and logical functions
  • Use a range of lookup functions
  • Work with Named ranges
  • Data validation to create pick lists and validate data entry
  • Advanced data filters to search for data based on complex criteria
  • Advanced Conditional Formatting using formulas
  • Work with Excel Tables and Pivot Tables and Charts

Course Content


  • IF functions, nested Ifs, ANDs and ORs for decision making. IFERROR
  • LOOKUP functions – VLOOKUP and HLOOKUP

Named Ranges

  • Creating Named ranges
  • INTERSECT function
  • Using Named Ranges

Data Validation

  • Validating Data
  • Creating a Custom Error Message
  • Removing a Data Validation

Advanced Filters and Outlines

  • Working with Advanced Filters and Database Functions
  • Use the Advanced Filter Feature to Display and Extract Data from Tables
  • Database Functions
  • Outlines, applying, expanding and collapsing.

Auditing Tools

  • Displaying the Auditing Toolbar
  • Displaying/Removing Dependent Arrows
  • Displaying/Removing Precedent Arrows
  • Removing Tracer Arrows
  • Tracing Cells Causing Errors

Conditional Formatting

  • Using Conditional & Custom Formats
  • Applying, Changing, Adding & Deleting a Conditional Format

Pivot Tables

  • Pivot Table Functions to summarise and analyse data
  • Grouping Data
  • Slicers and filters
  • Adding, Moving, Deleting PivotTable Report Fields

Show the next months in

Location Dates Time Price exc. VAT   Status
Growtrain Ltd 8C PO20 2BX 19 Jul 2019 09:00h – 16:00h £145.00 Registration Closes
Jul 18, 2019 1:00AM

Growtrain Ltd 8C PO20 2BX

19 Jul 2019

09:00h – 16:00h

£145.00 exc. VAT


Register Now » 8 Places Available!
Register Now » 8 Places Available!
Contact Nina or Graham on 01243 216278 or email courses@growtrain.co.uk to book this course
Share This