0800 3892304
info@spotlighttraining.co.uk

Whether it’s delivering an Introductory, Intermediate or Advanced Microsoft Excel Training Course or designing your own course that fits your organisation like a glove, Spotlight Training always has the aim of delivering exactly what’s right for you.

Prices from £500 per day

You let Spotlight Training know:

  • The version of MS Excel required for training
  • The level: Introduction, Intermediate or Advanced or…
  • The topics required to create your own tailored course
  • The length of the course

Resources for topics & TNA available below

Spotlight delivers the following:

  • Hands-on practical training
  • Training at location of your choice
  • Training manual for each delegate
  • Training laptops supplied at no extra cost
  • Focused training at tremendous value

MS Excel - Full Day

Description: This course is designed for delegates who have used MS Excel a little bit, maybe created the odd basic formula or SUM function.

Objectives: By the end of the course delegates should feel comfortable with MS Excel and know:

  • How to enter, edit and format data and content
  • How to create formulas
  • How to insert functions such as SUM, MIN and MAX
  • How to sort and filter data
  • How to create Charts
  • How to print out your work from MS Excel

Course Content: The following topics will be covered:

Entering/Editing

  • Content

Formatting

  • Cells
  • Columns & Rows
  • Worksheets

Introduction to Formulas

  • Operator Precedence
  • Cell References
  • NOW Function
  • SUM, AVERAGE, MAX & MIN Functions

Tables

  • Sorting
  • Formatting
  • Filters

Charts

  • Column
  • Pie

Printing

Description: This course assumes you have a good basic grounding in MS Excel and takes you on to the next step.

Objectives: This courses is designed to enable a delegate to be be able to create, edit and modify documents, by the end of the course you should be able to use:

  • Apply formatting to charaters
  • Apply formatting to paragraphs
  • Be able to apply page formatting
  • Be able to edit text
  • Be able to print

The following topics will be covered:

Filling Data

  • Understanding how to fill a Series and growth series
  • How to Create, Modifying and delete a Custom Fill List

Logical Functions

  • Using IF To Display Text or Calculated Values
  • Nesting IF Functions
  • Using IFERROR
  • Using TRUE And FALSE
  • Using AND, OR and NOT

Formula Techniques

  • Developing A Nested Function
  • Creating and Editing Nested Functions
  • Using Concatenation
  • Manual Vs Automatic Recalculations
  • Formatting Dates & Formatting Time

Conditional Formatting

  • Applying & Clearing Conditional Formatting
  • Top Ten Items
  • Working With Data Bars, IconSets and Colour Scales
  • Creating and Editing Sparklines (2010 onwards)

Applying Borders

  • How to Apply and Remove Borders

Page Setup

  • Managing Margins
  • Changing Orientation
  • Setting Up a Print Area
  • Managing Page Breaks
  • Managing advanced Print Settings

Working With A Worksheet

  • Changing Worksheet Views
  • Worksheet Zooming
  • Viewing The Formula Bar, Gridlines and the Ruler
  • Inserting and Deleting Cells
  • Inserting and Deleting Columns and Rows
  • Switching Between Worksheets

Worksheet Techniques

  • Inserting And Deleting Worksheets
  • Copying, Renaming, Moving and Hiding Worksheets
  • Changing Worksheet Tab Colours
  • Grouping Worksheets
  • Hiding and Unhiding Rows And Columns
  • Freezing and Splitting Rows, Columns and Windows

Finding And Replacing

  • Finding Text and Cell References In Formulas
  • Replacing Values
  • Finding Formats
  • Using the Go To Utility

Filtering and Sorting Data in Tables

  • Sorting by A-Z, Dates and Numbers
  • Sorting On More Than One Column
  • Applying And Clearing Filters
  • Applying Compound Filters
  • Using Wildcards

Charting Techniques

  • Adding Chart and Axes Titles
  • Showing Data Labels and Data Tables
  • Modifying The Axes and Plot Area
  • Showing Gridlines
  • Adding A Trendline
  • Adding Error Bars

Description: This course is designed for delegates who are comfortable and experienced users of MS Excel.

Objectives: By the end of the course delegates should have a deep and thorough knowledge of MS Excel and know:

  • How to use Lookup Functions to copy and extract data
  • How to set Excel Options
  • How Link and Consolidate Data
  • How to Create and use a PivotTable and PivotCharts
  • How to use What-If Analysis Utilities
  • How to create and run a Macro

Course Content: The following topics will be covered:

Lookup Functions

  • Using VLOOKUP For Exact Matches
  • Using HLOOKUP
  • Using INDEX & MATCH
  • Using the ROW, ROWS, COLUMN And COLUMNS Functions

Setting Excel Options

  • Personalising Excel
  • Setting Formula Options
  • Understanding Save and File location Options

Chart Object Formatting

  • Selecting and Formatting Chart Elements
  • Using Shape Styles To Format Objects
  • Filling The Chart Area And The Plot Area
  • Filling The Background
  • Using Themes

Labels And Names

  • What is a Named Range?
  • Creating and Using Named Ranges in Formulas
  • Using The Name Manager

Protecting Data

  • Protecting Cells and Worksheets
  • Password Protecting A Workbook
  • Opening A Password Protected Workbook
  • Removing A Password From A Workbook

Summarising And Subtotalling

  • Creating Subtotals and Nested Subtotals
  • How to copy visible cells within Subtotals

Data Linking

  • How to Link Between Worksheets and Workbooks
  • Updating Links Between Workbooks

Data Consolidation

  • Consolidating With Identical Layouts
  • Creating An Outlined Consolidation
  • Consolidating With Different Layouts

Pivot Tables

  • Creating Pivot Tables
  • How to refresh the source data
  • nserting Fields and filtering Fields
  • Clearing Filters
  • Inserting Groups within PivotTables
  • Using Summarise Value by
  • Formatting A PivotTable Report
  • Creating Calculated Fields and Calculated Items
  • Creating Slicers (Version 2010 onwards)
  • How to create PivotCharts

What-If Analysis

  • Using Goal Seek
  • Creating Scenarios with Solver
  • Using Named ranges within Solver

Grouping And Outlining

  • Creating An Automatic Outline
  • Creating A Manual Group
  • Grouping By Columns

Recorded Macros

  • What is a Macro?
  • Recording and Running A Macro
  • Relative Vs Absolute Macros
  • Assigning and Running A Macro from The Toolbar
  • Deleting A Macro

Description: This course is aimed towards delegates who want to be able to create dashboards displaying insight and analysis, trends and comparisons including interactive content within charts and data

Duration: 1, 2 or 3  Day Course depending on level of delegates, depth of knowledge required and requirements

Objectives: By the end of the course delegates will be able to create dashboards and know how to:

  • How to get your fit for purpose
  • Dashboard design principles
  • Dashboard display principles
  • Create interactive controls
  • Analyse the data
  • Automate your dashboard

Course Content: :- The following topics will be covered:

Cleaning up data

  • Use of Tables
  • Text Functions
  • Lookup Functions
  • Macros
  • Design Principles
  • Design and Display principles
  • End Goal

Charts

  • When to use which chart
  • Pie Vs Bar Vs Line Vs Column
  • Secondary Axis
  • Panel Charts
  • Bullet Charts

Controls

  • Form Controls

Analysis  – Ways and Means

  • Creating automated Titles and labels

PivotTables

  • Pivot Tables
  • GETPIVOTTABLE Function
  • Slicers

Assembling – Final steps

The End game – Distribution & Protecting

Description: MS Excel is a great tool for storing data, having either been created by yourself, colleagues or perhaps downloaded as an extract from a database. If the data is in a particular format then the tools and utilities within MS Excel allows you to analyse the data easily drilling down into the data to return relevant totals and percentages leading to uncovering relationships and trends within the data.

Objectives: The Course is divided into three parts and delegates should feel comfortable with the following:

  • Part 1 – Cleaning up the data using Functions, Find & Replace, Text to Column and Tables
  • Part 2 – Analysing the data using Tables and PivotTables
  • Part 3 – Using Macros to make the process take seconds rather than hours

Course Contents: The following topics will be covered:

Cleaning up data

  • Using Functions such as LEFT, RIGHT, MID, TRIM & CLEAN
  • Using the Find & Replace
  • Using the Text to Column
  • Using Tables to standardise data

Tables

  • Custom Sort
  • Filters
  • De-duplicating data

PivotTables

  • Creating
  • Inserting field(s)
  • Filtering
  • Summarising by…
  • Grouping by…
  • Slicers (MS Excel 2010 onwards)
  • PivotCharts
  • Calculating Fields and Items

Using Macros

  • Recorded Vs VBA Macros
  • Where to save?
  • Relative Vs Absolute Macros
  • Assigning to an icon

Description: ‘A picture is worth a thousand words’ so the saying goes and this course is designed to illuminate the many tools that MS Excel offers that makes data easier to understand and analyse visually.

Objectives: By the end of the course delegates should feel comfortable with the following:

  • Advanced Formatting
  • Charts
  • Conditional Formatting
  • Tables
  • PivotTables

Course Contents: The following topics will be covered:

Advanced Formatting

  • Custom Formatting

Charts

  • Formatting
  • Pie, Pie of Pie, Bar of Pie
  • Single Series and Multiple Series Column Charts
  • Secondary Axis Carts

Conditional Formatting

  • Built-in Formats
  • Comparison Formats
  • Using Formulas
  • Using Functions
  • Applying CF to rows

Tables

  • How to create and remove
  • Advantages
  • Sorting – Top to Bottom, Left to Right
  • Filtering – Compound and Multiple Filters
  • Formulas

PivotTables

  • Creating
  • Inserting field(s)
  • Filtering
  • Summarising by…
  • Grouping by…
  • Slicers (MS Excel 2010 onwards)
  • PivotCharts
  • Calculating Fields and Items

Description: The real power of MS Excel is released when you start to get to grips with Functions. When you have a firm grasp of functions, and what you can do with them, you really begin to master MS Excel and this enables you to create you own highly flexible spreadsheets tailored totally to your needs.

Objectives: This course takes you through the different ways functions can be inserted into a MS Excel, how to get help and then goes through the different groups of functions that are available. By the end of the course you should be comfortable using Functions, be able to understand Functions you haven’t seen before and what types of solutions Functions offers.

This is a real-world course with lots of practical hands-on examples and exercises.

Course Contents: The following topics will be covered:

Introduction to Formula

  • Cell References
  • Operator Precedence

Introduction to Functions

  • Function Wizard
  • Formula Complete

Math Functions

  • SUM/MIN/MAX/AVERAGE
  • AutoSUM

Lookup Functions

  • VLOOKUP/HLOOKUP
  • INDEX/MATCH

Logical Functions

  • IF/Nested IF
  • AND/OR/NOT
  • IFERROR
  • SUMIF/SUMIFS

Database Functions

  • DSUM/DCOUNT/DAVEARGE

Text Functions

  • LEFT/MID/RIGHT
  • LEN

Date/Time Functions

  • NOW
  • DAY/MONTH/YEAR/DATE

Array Functions

  • FREQUENCY
  • TRANSPOSE

MS Excel - Half Day

Description: MS Excel is an application for storing, analysing and displaying data. This course is a gentle introduction to MS Excel for people who have not neccessarily used it before.

Objectives: This course is designed as a quick introduction to MS Excel.

Course Contents: The following topics will be covered:

What is Excel?
Entering and Editing Data
Formatting Text & Numbers
Formatting Cells, Columns & Rows
Intro to Formulas
The SUM function
Filters
Printing

Description: This course is a brief introduction to Formulas and Functions, the first part of a series of three courses.

Objectives: This course is designed as an Introduction to formulas and function. How to create, edit, copy and manipulate them.

Course Contents: The following topics will be covered:

Formulas

  • Absolute Vs Relative Cell References
  • Working across worksheets
  • Copying Formulas
  • Introduction to Named Ranges

Mathematical Functions

  • SUM, MAX, MIN, AVERAGE
  • Using the AutoSUM

Lookup Functions

  • VLOOKUP
  • HLOOKUP

Description: This course is a brief introduction to Formulas and Functions, the first part of a series of three courses.

Objectives: This course is designed as an Introduction to formulas and function. How to create, edit, copy and manipulate them.

Course Contents: The following topics will be covered:

Formulas

  • Absolute Vs Relative Cell References
  • Working across worksheets
  • IFERROR Function
  • SUMIF, SUMIFS

Formula Auditing
Date and Time Functions

  • NOW
  • DAY, MONTH, YEAR & DATE

Database Functions

  • DSUM, DCOUNT, DAVERAGE

Description: This course is the final part in the three part series Formulas and Functions and moves on to Text and Information Functions and Array Functions and other ways of using functions.

Objectives: This course focuses on the understanding of how Text, Information and Array Functions work and how to use various functions in popular combinations such as Concatenation, INDEX, MATCH as well as others. The the course will move on to how formulas and Functions are used in other features in Excel such as Conditional Formatting and Data Validation.

Course Contents: The following topics will be covered:

Text Functions

  • UPPER, LOWER, PROPER

Information Functions

  • ISBLANK, ISEVEN, ISODD etc..,
  • TYPE, CELL

Function Combinations

  • VLOOKUP & MATCH
  • INDEX & MATCH
  • COLUMN(s), ROW(s)

Functions in:

  • Conditional Formatting
  • Data Validation

Description: One of best ways to release the potential of MS Excel is to organise data within Excel in a tabular format (also called a Table, List, DataList or block of data). Once the data is setup in this format an excellent array of tools and utilities become available.

Objectives: This course focuses on getting data into a usable format and then through a variety of handson examples and exercises goes through how to cleanup data and then how to Sort and Filter the data. Then the course moves on how to create Sub-totals, Tables, utilise Advanced Filters and then onto PiotTables.

Course Contents: The following topics will be covered:

Cleaning up data

  • TRIM & CLEAN Functions
  • LEFT, MID & RIGHT Functions
  • Text to Columns utility
  • Find and Replace

Sorting

  • Top to bottom
  • Left to right

Sub-totals

  • How to create
  • Sub-sub-totals
  • Remove

Tables

  • Create and remove
  • Filters

Advanced Filters

  • Creating
  • Filters
  • Unique lists

PivotTables

  • Creating
  • Fields
  • Report Filters
  • Filters
  • Slicers (MS Excel 2010 onwards)
  • Sorting
  • Grouping

Description: One of best ways to release the potential of MS Excel is to organise data within Excel in a tabular format and then use PivotTables to slice and dice the data.

Objectives: This course focuses on getting data into a usable format and then to go thorough the many options and tools in detail within PivotTables to analyse the data.

Course Contents: The following topics will be covered:

Cleaning up data

  • TRIM & CLEAN Functions
  • LEFT, MID & RIGHT Functions
  • Text to Columns utility
  • Find and Replace

PivotTables

  • Creating
  • Fields
  • Report Filters
  • Filters
  • Summarise by….
  • Show Values As…
  • Formatting Fields
  • PivotTable Styles
  • Slicers (MS Excel 2010 onwards)
  • Sorting
  • Grouping
  • Calculating Fields
  • Calculating Items
  • Sub-totals
  • Totals
  • Solve Order
  • PivotCharts

Description: ‘A picture is worth a thousand words’ so the saying goes and this course is designed to illuminate the many tools that MS Excel offers that makes data easier to understand and analyse visually.

Objectives: By the end of the course delegates should feel comfortable with the following:

  • Advanced Formatting
  • Charts
  • Conditional Formatting
  • Tables
  • PivotTables

Course Contents: The following topics will be covered:

Advanced Formatting

  • Custom Formatting

Charts

  • Formatting
  • Pie
  • Column Charts

Conditional Formatting

  • Built-in Formats
  • Comparison Formats
  • Using Functions

Tables

  • How to create and remove
  • Formatting
  • Sorting
  • Filtering
  • Formulas

PivotTables

  • Creating
  • Inserting field(s)
  • Filtering
  • Summarising by…
  • Slicers (MS Excel 2010 onwards)
  • PivotCharts

Description: Getting the right answer out of MS Excel is just one part of what makes up a usable and useful speadsheet: formatting your spreadsheet clarifies and draws attention potentially to the different elements of the spreadsheet.

Objectives: This course focuses on the different formatting options and tools available and an understanding of how best to format spreadsheets. By the end of the course delegates will be aware of formatting options available and what constitutes a clearly and clearly formatted spreadsheet.

Course Contents: The following topics will be covered:

Number Formatting

  • General Vs number formatting
  • Date formatting
  • Date formatting issues
  • Keyboard Shortcuts
  • Copying Format

Cell, Row and Column Formatting

  • Alignment
  • Borders
  • Wrap text
  • Hiding Rows/Columns
  • Protecting

Custom Formatting

  • Numbers
  • Dates

Cell Styles

  • How to use

Conditional Formatting

  • Built-in
  • Formatting Rows
  • Formatting using Formaulas
  • Formatting using Functions

Description: MS Excel has improved its Chart option over the years and there are stacks of useful Charts and useful Chart options available, this course will take you into the depths of Charts to make you an expert.

Objectives: This course focuses just on Charts and should answer the following questions:

  • What Charts are available
  • Which Charts to use for what type of data
  • How to find the appropriate elements to format
  • How to make the Chart dynamic
  • How to use PivotCharts

Course Contents: The following topics will be covered:

Overview

  • Working with the Chart Object
  • Charts not to use

Pie Charts

  • Pie Charts
  • Pie Chart Formatting
  • Pie of Pie
  • Bar of Pie

Column, Bar and Line Charts

  • Single Series Charts
  • Multiple Series Charts
  • Dynamic Charts
  • Secondary Axis Charts

Formatting

  • Using F2 to link Cell to Chart Title
  • Formatting the different elements
  • Drawing objects
  • Using Chart Templates

PivotCharts

Chart Gotchas!

Description: Two of the more heavily used sets of Functions within MS Excel are Lookup and Logical Functions. This course will take you into more detail than usual examining when and when not to use them, whether they can be improved and whether they can be used with other Functions and other features within Excel.

Objectives: This course focuses on just Lookup and Logical Functions:

  • When to use them
  • What are there limitations
  • Can they be improved?
  • How to use them with other Functions
  • How to use them with Data Validation and Conditional Formatting

Course Contents: The following topics will be covered:

Absolute & Mixed Cell Referencing

Lookup Functions

VLOOKUP & HLOOKUP Functions:

  • Using False & True arguments
  • How to deal with error messages
  • with TRIM
  • Using Data Validation
  • With IFERROR Function

INDEX & MATCH Functions

  • MATCH Function
  • INDEX Function
  • Used together

Logical Functions

  • IF Function with text, with calculations
  • Nested IF functions
  • AND, OR & NOT
  • Using TRUE & FALSE
  • IFERROR
  • SUMIF, SUMIFS
  • Using CONCATENATE
  • With Conditional Formatting

Description: This course contains an assortment of methods for cleaning up data, extracting unique items and records from data sets and copying data from one worksheet to another or even another workbook, with either a live link or by copying.

Objectives: This course focuses on three aspects of ‘wrangling’ data within MS Excel:

  • Cleaning up data
  • Extracting Unique Items and Records
  • Copying or linking data from worksheet to another worksheet or workbook

Course Contents: The following topics will be covered:

Cleaning up data

  • Using Tables
  • Using Find
  • Text Functions
  • Concatenate

Unique & Duplicate data

  • Unique & Count
  • Tables/duplicate
  • Using Advanced Filters/Unique

Moving data around

  • How to Copy and use Paste Special
  • Using Arrays/Transpose Function
  • Differing ways of using VLOOKUP

Description: This course delves into the world of recorded Macros. Macros are essential a series of commands that you record once and then reuse as many times as you want. Macros can be a real convience and save you a lot of time if you have to repeatedly do the same actions with MS Excel.

Objectives: This main objective of this course is to give people the confidence record and then to use Macros.

Course Contents: The following topics will be covered:

How and Where to Save your Macro

Security

Recording Macros

Relative Vs Absolute Macros

Useful Utilities to use when recording Macros

  • Useful Keyboard Shortcuts
  • Useful Utilities

Assigning Macros

Adding Looping Code to a Macro