×

Search our courses

Workshop Duration

1 day

Prerequisites

Delegates must have attended an Excel Intro/Inter course, or have an equivalent working knowledge of Excel.

Who will the course benefit?

Users who wish to gain a comprehensive, in-depth knowledge of this application.

Course Content

Overview

  • Short overview of intermediate topics e.g. databases, if statements

Named Ranges

  • Defining and moving to a named range
  • Applying range names in formulae
  • “Sparklines”

Lookup Functions

  • Creating a Lookup table
  • Using the Lookup functions: VLOOKUP, MATCH, INDEX, INDIRECT
  • “Dropping down” versus Exact Matches

Advanced Functions

  • Financial, Date and Time, Text
  • SUMPRODUCT
  • OFFSET

Data Validation

  • To Create Lists
  • Using INDIRECT with Validation

Workspace

  • Saving open files to a workspace

File Linking

  • Linking data between spreadsheets
  • Refreshing, updating and redirecting links

Templates

  • Creating and modifying templates
  • Storing styles in a template and date/time formats

Custom Views

  • Using the “Custom Views” to create different views of your spreadsheet
  • Displaying a view of the Worksheet
  • Using “Report Manager” to print a view

Protection

  • Protecting a Sheet and Workbook
  • Define the levels of access

Number Formats

  • Creating customised, number, currency, and date/time formats

Consolidation

  • Producing a Consolidation spreadsheet
  • Consolidation by position and category

Macros

  • Creating macros in Excel
  • Saving the macro to the Global Macro Sheet
  • Assigning macros to keystrokes, buttons and Toolbar icons

Customising

  • Customising the QAT (Quick Access Toolbar)
  • Customising the Ribbon

Goal Seek

  • Using Solver and Goal Seek to find solutions to problems (Scenarios if required)

Skills gained

On successful completion of the course, delegates will be able to create and apply templates, understand Lookups and Data Tables, solve problems by using Goal Seek, record macros and assign to buttons and link objects between different applications.

If you would like to book onto a course or find out more information please fill out our contact form or call us on 01454 203 355.