The Excel Modular Course is delivered in half-day on-site Modules.  Customers can select the modules they wish to include, although later modules build on the learning in earlier modules.

Introduction to Excel Module

This module lays the foundations for a good understanding of Excel and how it works.  Good practice is introduced with respect to entering and editing different types of data.

  • Basic concepts and terminology of Excel
  • Types of data: numbers, text, formulae
  • Selection, navigation and keyboard shortcuts
  • Entering and editing data
  • Managing rows and columns
  • Writing basic calculations, including SUM
  • Information the mouse pointer gives you
  • Opening and saving documents
  • Ways to look at your workbook
  • Data underneath, formatting on top – an introduction to formatting

Data Analysis Module 1

Prerequisites: you must be comfortable with writing formulae.

  • Good practice in workbook design
  • Sorting
  • Filtering, to show only rows that are of interest
  • Finding data – tips and tricks
  • Quick formatting and simple analysis with Tables
  • Removing duplicate data
  • Copy/paste, move, Format Painter and Autofill
  • Dollar signs in formulae – what they are for and how to use them
  • Using range names to make formulae easy
  • Discovering Functions – the powerhouse of Excel
  • Calculating with logic – if this is true, do that, otherwise something else
  • Cleaning text (e.g. capitalising proper nouns, upper and lower case)

 Data Analysis Module 2

Prerequisites: you must have used a variety of functions and be comfortable with the use of multiple values (arguments) inside functions’ brackets.

  • Graphs and charts
  • Calculations across worksheets
  • Conditional formatting – creating visual indicators
  • Working with Dates
  • Combining functions
  • Using helper columns for complex calculations
  • Automatic categorisation with VLOOKUP
  • Avoiding data duplication – referencing other datasets with VLOOKUP
  • How to VLOOKUP when unique values aren’t in the first column
  • Pivot tables and pivot charts – creation, use, advantages and disadvantages

Making Calculations Robust Module

Prerequisites: you must be comfortable with using functions (including nested functions), dollar signs in formulae, and range names.

  • Ensuring valid values with data validation
  • Using cell dropdowns to restrict choices
  • Conditional cell dropdowns – making dropdown lists manageable
  • Using the right data – automatic form-filling, price-selection, etc
  • Using formulae to avoid the disadvantages of pivot tables
  • Ways to check that your calculations are working properly
  • Making offsets robust against insertion of new rows and columns
  • Dealing with new data being added to your data set – self-expanding ranges
  • Structuring your workbook to make it easy to manage future changes

Workbook Management Module

  • Preventing people from ‘breaking’ your workbooks
  • Protecting data
  • Using comments to document your workbook
  • Printing all or part of a sheet
  • Tracking changes
  • Working across workbooks
  • Different ways to import data from other systems
  • Where is the master data?  Ensuring everyone’s using the same data set
  • When to start considering other solutions

Data Analysis Workshop

This half-day does not have specific learning objectives.  Rather, it is an opportunity for the customer’s staff to bring specific problems to the group, in order to discover features and functions of Excel which can be applied to resolve the problem.