Book a course

Duration: 1-day

Location: inhouse, online or at our UK locations

What you will learn

Learn how to utilise nested formulas to extract data base on multiple questions to get results to complex questions. Advanced Pivot Table tools including groupings and calculations. Using what-if analyses to meet targets and generate forecasts. Learn how to quickly automate repetitive tasks by recording macros.

Prerequisite

Attendees are required to be familiar with the Excel Intermediate content before attending this course.

Course outline

Nesting Formulas

  • Principals of nesting formulas together
  • Nest an AND function in an IF
  • Nest an OR function in an IF

Advanced Pivot Tables

  • Group dates, numerical and text items
  • Run percentage analyse
  • Run analyses to compare data
  • Insert Field calculations
  • Create a user-friendly dashboard

Advanced Conditional Formatting

  • Colour table rows based on criteria
  • Apply colour to approaching dates
  • Exploring different rule types

Array Formulas

  • Advantages of writing an array formula
  • Protect formulas with an array

Lookup Functions

  • Going beyond the VLOOKUP
  • Retrieve row data from left or right
  • The versatile INDEX and MATCH
  • Handling columns with duplicates

Calculate by Criteria

  • Calculate by criteria down columns
  • Use SUMIFS to Sum by criteria
  • Use AVERAGEIFS to average by criteria
  • Use COUNTIFS to count criteria values

What-If Analysis

  • Use Goal Seek to meet targets
  • Forecasting with the Scenario Manager

Offset and SumProduct Functions

  • Extract row and column referenced data
  • Use the OFFSET function
  • Use the SUMPRODUCT funtion
  • Sum and multiply ranges of values

Recording Macros

  • Macro security
  • What is a relative referenced macro?
  • Record, run and edit a macro
  • Save file types as macro enabled
  • Save a macro in the personal workbook
  • Manage the personal workbook
  • Introduction to VBA code

Customising the Ribbon

  • Add buttons to the quick access toolbar
  • Add buttons to the ribbon
  • Create a ribbon group