Excel

Microsoft Excel Training

On-site and Off-site training *

Training to suit you and your business

Book training with us now.

Level 1

Pre-requisites

It is assumed that the delegate will have a good knowledge of the Windows environment. They may have but do not require some knowledge of Excel.

Course Objectives

This ½ day session is aimed at new and non-users of Excel who would like to enhance their knowledge and find short cuts or better ways of working and would like to move on from their basic knowledge of the system

Course Content

                • Simple formula

                • Basic functions

                • Copying / duplicating formula

                • Using AutoFormat

                • Sorting lists

                • Filtering data

                • Creating subtotal lists

                • Keyboard shortcuts

Level 2

Pre-requisites

It is assumed that the delegate will have a good knowledge of the Windows environment and some knowledge of Microsoft Excel.

Course Objectives

This ½ day session is aimed at existing users of Excel who would like to enhance their knowledge and find short cuts or better ways of working, getting more use from formula to help analyse their data.

Course Content

                • Scrolling screen headings

                • Printing your workbook

                • Headers and footers

                • Working with percentages

                • Absolute and relative references

                • Naming cells and ranges

                • Basic graphs

                • Counting functions

                • Basic analysis of large tables

Level 3

Pre-requisites

It is assumed that the delegate will have a good knowledge of the Windows environment a basic grounding in Excel and some understanding of formula in Microsoft Excel

Course Objectives

This ½ day session is aimed at existing users of Excel who would like to extend their knowledge of formula available to them and begin to work with data tables, summary sheets and graphs.

Course Content

          • Working with IF functions

                • Using VLOOKUP and XLOOKUP to access data from large tables

                • Creating drop down lists

                • Protecting worksheets form accidental data entry

                • Creating charts and graphs

                • Adding trend lines to charts

                • Colour coding with conditional formatting

                • Summarising data tables (subtotals)

Level 4

Pre-requisites

It is assumed that the delegate will have a good knowledge of the Windows environment and some understanding of formula in Microsoft Excel.

Course Objectives

This ½ day session is aimed at existing users of Excel who would like to extend their knowledge of the features available to them working with large amounts of data and multiple worksheets and books.

Course Content

                • Analysing large amounts of data

                • Pivot Tables

                • Creating Scenarios

                • Creating scenario reports

                • Using data forms

                • Linking workbooks

                • Editing multiple worksheets

                • Autofill Options

                • Text Function

Level 5

Pre-requisites

It is assumed that the delegate will have a good knowledge of the Windows environment and a good understanding Microsoft Excel.

Course Objectives

This ½ day session is aimed at advanced users of Excel who would like to extend their knowledge of the features available to them working with large amounts of data possibly from other sources such as Access databases, also working with spreadsheets that are used or populated by others.

Course Content

                • Cell properties

                • Setting data validation

                • Custom Data Validation

                • Cascading (Dependant) drop down lists

                • Creating warning messages for wrong data entry

                • Filter function

                • Working with external data

                • Linking to External Data

                • Text Functions

                • Text editing formula (for splitting names and addresses etc.)

Level 6

Pre-requisites

It is assumed that the delegate will have a good knowledge of the Windows environment and of Microsoft Excel.

Course Objectives

This ½ day session is aimed at existing users of Excel who would like to automate aspects of their day to day work with the use of macros for such tasks as standard formatting of reports and importing data from other sources. This introduction to macros looks at recording and editing macros.

Course Content

                • Recording a macro

                • Where to store macros

                • Using the VBA editor

                • Getting to grips with the code

                • Creating a form

                • Form buttons

                • Assigning macros to buttons

Lookups and Analysis

Pre-requisites

It is assumed that the delegate will have a good knowledge of the Windows environment and some experience of basic functions in Microsoft Excel.

Course Objectives

This ½ day session is aimed at existing users of Excel who work with lists of data and need methods to access and analyse that data.  You will look at nesting functions and defining criteria as ways of pulling relevant data from your lists, comparing lists and producing ‘exceptions’ lists.

Course Content

                • Basic Lookup techniques

                • Nesting Functions

                • Using IF functions

                • Counting and Summing with Criteria

                • Creating Unique references

Pivot and Data Tables

Pre-requisites

It is assumed that the delegate will have a good knowledge of the Windows environment and some experience of basic functions in Microsoft Excel.  

Course Objectives

This ½ day session is aimed at existing users of Excel who work with lists of data and need methods to access and analyse that data.  You will look at ways to analyse data using Pivot Tables and pivot Charts, how to use ‘Slicers’ and build custom fields. How to convert data ranges to ‘data tables’ and the benefits of this. How to sort and filter large data sets with subtotals for analysis.

Course Content

                • Creating a Pivot Tables

                • Using different functions within pivot tables

                • Connecting Pivot Tables via ‘Slicers’

                • Using custom calculations in Pivot Tables

                • Creating and using ‘Data Tables’

                • Sorting and Filtering with Subtotals

                • Preparing data sets for analysis

* May be an additional charge for Off-Site Training

Excel Full Day

General User

Course Content

  • Simple formula
  • Basic Functions
  • Sorting & Filtering data lists
  • Absolute and Relative references
  • Counting functions
  • Printing and managing large spreadsheets

Formula and Functions

Course Content

  • Using Insert Function
  • Lookups and references
  • Multiple worksheets and workbooks
  • Automatic formatting of your workbook
  • Creating graphs

Data Analysis and Pivot Tables

Course Content

  • Working with large tables
  • Lookups
  • Nesting functions
  • Creating pivot tables
  • Formatting pivot tables
  • Using Slicers
  • Adding formula to pivot tables

* May be an additional charge for Off-Site Training

Excel Macros

Level 1
Microsoft Excel
Introduction to Macros

Pre-requisites

Delegates attending this course should be have a good understanding of Excel and be able to open, edit and save files.  They should also be able to use various formula and be able to format spreadsheets.

Course Objectives

By the end of the course users will be able to record and edit basic macros. You will understand where and why macros are stored in specific locations and be able to create a button to activate a macro.

Course Content

Automating Spreadsheets with Macros

  • Recording Macros
  • The Developer Tab
  • Creating Macro Buttons
  • Relative and Absolute modes
  • Editing recorded code
  • Reducing recorded code
  • Documenting your macros
  • The Personal Macro Workbook
  • Modifying the Ribbon Bar

Level 2
Microsoft Excel
Macros and Automation

Pre-requisites

Delegates attending this course should be have a good understanding of Excel and be able to open, edit and save files.  They should also be able to use various formula and be able to format spreadsheets.Continuing Professional Development

Course Objectives

By the end of the course users will be able to record and edit basic macros. You will understand where and why macros are stored in specific locations and be able to create a button to activate a macro.  You will be able to edit code and have an understanding of the syntax used in VBA.

Course Content

  • Recording Macros
  • The Developer Tab
  • Creating Macro Buttons
  • Relative and Absolute modes
  • Editing recorded code
  • Reducing recorded code
  • Documenting your macros
  • The Personal Macro Workbook
  • Modifying the Ribbon Bar
  • Documenting your macros
  • Creating and using variables
  • Creating a User Form
  • Basic Looping
  • Using If statements

Level 3
Microsoft Excel
Macro Master Class

Pre-requisites

An advanced understanding of creating spreadsheets and manipulating data in Excel.

Course Objectives

At the end of the course users will be able to understand and create VBA code. You will be able to create procedures, user-defined functions and forms to help automate Excel spreadsheets for End Users.

Course Content

Day 1 Macro Introduction

  • Recording Macros
  • The Developer Tab
  • Creating Macro Buttons
  • Relative and Absolute modes
  • Editing recorded code
  • Reducing recorded code
  • Documenting your macros
  • The Personal Macro Workbook
  • Modifying the Ribbon Bar
  • Documenting your macros
  • Creating and using variables
  • Creating a User Form
  • Basic Looping
  • Using If statements
  • Homework project

Day 2 Macro Master Class

  • User Form Controls
  • Input Boxes and Message Boxes
  • Controlling program low with IF structures
  • Sorting and Filtering worksheet data
  • Applying worksheet functions within VBA
  • De-Bugging macros
  • Building in bug reporting to code
  • Automatic macros

* May be an additional charge for Off-Site Training