Blue Pecan provides in house Excel training at your business premises. We offer Beginners to Advanced training courses including Excel Macros and VBA. Over 10 successful years delivering onsite Excel training to our customers!

Our coverage includes Bedfordshire, Berkshire, Buckinghamshire, Cambridgeshire, Essex, Devon, Dorset, Gloucestershire, Hampshire, Herefordshire, Hertfordshire, Kent, London, Somerset, Suffolk, Surrey, Sussex, Warwickshire, Wiltshire & Worcestershire.

You can ask us to deliver one of our published Excel training outlines or opt for a tailored course. We can also incorporate your working files to make the training as relevant as possible.

Our courses are compatible with any version of Excel including Excel 2016, 2013, 2010 and 2007.

Course Feedback

Excel Mac Training Course in Southampton...

"Great job. Well worth the investment of both time and funds.  Thank you Chester."

January 10, 2013

Pricing and Group Size

Pricing

One to one training £395 plus vat.
Group training starts a £450 plus vat.
You final quote will include modest travel costs.

Group Size

We recommend group sizes no larger than 10, but are happy to accommodate your requirement.

 

Excel Course Outlines

Complete BeginnersFundamentalsIntermediateAdvancedIF & VLOOKUP In-depthDatabase & PivotTables In-depthVisual Basic For Applications (VBA)Tailored Training

Excel For Complete Beginners: 1 Day Course

This course is for people with little or no knowledge of Excel. By the end of the course you will be able to perform basic calculations and work with simple databases. We will also teach you how to format and print your Excel work. Included in the course are useful shortcuts and tips and tricks that will make working with Excel easier and quicker.

Excel Basics

  • Enter & format data
  • Format cells
  • Copy & move data
  • Navigate & select data
  • Navigate between worksheets
  • Show/hide gridlines
  • Insert rows/columns and cells
  • Hide rows and columns
  • Insert & delete worksheets
  • Copy/move a worksheet to another workbook
  • Group worksheets
  • Freeze rows and/or columns
  • Zoom in and out, zoom to selection
  • CTRL shortcuts
  • ALT shortcuts

Formula Basics

  • Mathematical operators - add up, multiply, divide and subtract
  • The golden rule
  • Copy formula
  • Sum rows and columns

Formatting Basics

  • Data formats - date, time, percentage, currency, decimal places, thousand separators
  • Change the number of decimal places displayed
  • Adjust row and column widths - autofit, all columns same width, all rows same height
  • Borders and fills
  • Wrap text within a cell
  • Merge cells
  • Clear formats
  • Use the format painter to copy formatting
  • Apply and create cell styles
  • Format as a table

Selection Basics

  • Select columns and rows
  • Select an entire database
  • Select non-contiguous ranges

Navigation Basics

  • Move to the top/end of a column
  • Move to the beginning/end of a row
  • Move to the top or bottom of a worksheet

Working with Text

  • Change the case of text to upper, lower or proper case
  • Split text across columns
  • Remove duplicates
  • Concatenate text (join text across columns)
  • Use Flash Fill to manipulate text values

Filling Data

  • Fill numeric data - increment or copy
  • Fill alpha numeric data - increment or copy
  • Fill dates - copy or increment, increment workdays, same day each month, same day each year
  • Fill with custom lists including user generated custom lists

Setting up a Basic Database

  • Ranges vs Excel Tables
  • How to create a field
  • How to create a record
  • Autocomplete
  • Copy formula down automatically
  • Add up with a dynamic range

Printing Basics

  • Check your margins, orientation and page size
  • Scale your print
  • Use Page Break Preview to help with print problems
  • Use Page Layout view to help with print problems
  • Set a Print Area
  • Headers and Footers
  • Print titles (column headings) on each page
  • Print settings and preview

Excel Fundamentals: 1 Day Course

The Excel Fundamentals course covers formulas, databases and charts. The course is suitable for delegates who are confident in the basic operations of Excel as covered in the Excel Course for Complete Beginners. In this course delegates will learn essential skills regarding formula work, database work and chart creation.

Formula Basics

  • Order of operations (BODMAS)
  • Evaluate Formula and other formula auditing tools
  • Basic functions - SUM, AVERAGE, MAX, MIN, COUNT, COUNTA, COUNTBLANK
  • Formula error warnings (green triangle)
  • Relative, absolute and mixed cell references
  • Perform calculations using a dynamic range
  • Basic calculations within an Excel table
  • Perform basic calculations across worksheets and workbooks
  • Named range - create, edit and delete
  • Use a named range within a formula

Working with Dates and Times

  • Understand dates and times as numbers
  • Enter dates and times in the correct format - (dates entered as text problem)
  • Understand the Region settings on your PC and how they affect date and time input
  • Basic date and time calculations
  • The TODAY() and NOW() functions

Sorting Basics

  • How to correctly set up a database
  • Avoid problems with sorting - splitting records, sorting headings, blank rows and columns preventing proper sorting
  • Sort on different data types - text, numeric and date
  • Sort by colour
  • Sort on multiple columns
  • Sort by custom order
  • Sort by user generated custom order
  • Case sensitive sort
  • Horizontal sort

Filtering Basics

  • Filter on different data types - text, numeric and date
  • Using wildcards for text field filter
  • Top, Bottom and Average filters on numeric fields
  • Copy filtered results
  • Save filters in Custom Views
  • Filter using Excel Tables

Chart Creation Basics

  • Set up your data for a chart
  • Choose the best chart type for your data
  • Understand categories and series
  • Add/remove chart elements
  • Format axis and gridlines
  • Link chart/axis titles to cell content
  • Create column and bar charts with one series
  • Create column and bar charts with multiple series
  • Create Line and Area charts
  • Create Pie and Doughnut charts
  • Create a Scatter charts with a trend line
  • Introduction to Sparklines

Excel Intermediate: 1 Day Course

Our Excel Intermediate course is designed for delegates who are proficient users of Excel but who need to improve their knowledge in the following areas - IF, COUNTIF, SUMIF, VLOOKUP and HLOOKUP functions, database work, PivotTables and conditional formatting. The course is focused on those skills that are most commonly required within the workplace. It is therefore suitable for delegates who do quite a bit of Excel work but need to become speedier and more proficient at what they do.

Introduction to Logical Functions

  • Comparison operators
  • Create logical test formulas to return TRUE or FALSE
  • Use the IF function to return values or formulas in place of TRUE or FALSE
  • Use COUNTIF and COUNTIFS to count based on a test
  • Use SUMIF and SUMIFS to sum based on a test

Introduction to Lookup Functions

  • Use VLOOKUP for exact match lookups
  • Use VLOOKUP for approximate match lookups
  • Use IFERROR or IFNA to handle NA errors
  • Use the HLOOKUP function
  • Perform a VLOOKUP across sheets
  • Perform a VLOOKUP across workbooks

Date Functions

  • Use WORKDAY and NETWORKDAYS to perform working day calculations

Conditional Formatting

  • Apply formatting based on numeric rules eg greater than, less than, between, equal to etc
  • Apply formatting based on date values - today, date, this week, this month etc
  • Apply formatting based on text rules - contains text or equal to
  • Format duplicate or unique values
  • Format top and/or bottom values
  • Format above/below average values
  • Use Data Bars to visually compare numeric data
  • Use Colour Scales to visually compare numeric data
  • Use Icon Sets to visually represent thresholds/targets
  • Edit /delete conditional formats
  • Get the order of conditions right
  • The 'Stop if true' setting

Working with Excel Tables

  • Use Excel Tables
  • Use the Excel Table Total Row
  • Create formula within an Excel Table
  • Use Slicers to filter within an Excel Table
  • Create a database using Excel Tables and Data Validation

Introduction to PivotTables

  • What is a PivotTable?
  • How to set up your data correctly for a PivotTable
  • Elements of a PivotTable - PivotTable areas
  • Understand the PivotTable cache
  • Create a basic PivotTable
  • Change the calculations used in a PivotTable
  • Show multiple calculations in the same PivotTable
  • Format values in a PivotTable
  • Show/hide subtotals and grand totals
  • Switch between PivotTable layouts
  • Switch between and create your own PivotTable Styles
  • Update and refresh your PivotTable
  • Generate Report Filter Pages
  • Slice a PivotTable
  • Create a PivotChart

Excel Advanced: 1 Day Course

Our Excel Advanced course is designed for delegates who wish to extend their knowledge of the key subject areas covered in the Intermediate Excel course - IF, LOOKUP, conditional formatting and PivotTables. Delegates need to be comfortable with the intermediate content before progressing to this level. The course also covers a basic introduction to recorded macros - a means of automating repetitive tasks.

Logical Functions

  • Use AND and OR with IF to perform multiple tests simultaneously
  • Use the NOT function
  • Create nested IF functions for extended output
  • Use the IFS function for extended output (Office 365 only)

Lookup Functions

  • Lookup to the left using MATCH and INDEX
  • Use MATCH and INDEX to perform a two-way lookup
  • Use the INDIRECT Function to perform a two-way lookup
  • Return a complete record using MATCH and INDEX
  • Use the SWITCH (Office 365 only) or CHOOSE to lookup across multiple tables

Introduction to Array Formulas

  • What are array formulas and why are they useful?
  • Conditional calculations using array formulas
  • Useful lookup techniques using array formulas
  • Using SUMPRODUCT

Conditional Formatting Using Formula

  • Use AND and OR to apply multiple tests simultaneously
  • Format an entire row based on a condition in one or more fields
  • Use functions such as COUNTIF, ISTEXT and ISFORMULA to set up a conditional formatting rule
  • Use text functions to format text values that begin with or end with characters
  • Format duplicates solution (better than the inbuilt solution)
  • Format duplicates across multiple columns

Database Work

  • Use Excel's Advanced Filter to perform complex filters and/or return unique values
  • Use database functions - DSUM, DAVERAGE etc

PivotTables

  • Prepare data for a PivotTable - common data problems
  • Group data - date, numeric, text fields
  • Calculated items and named sets
  • Calculated fields
  • Format slicers and timelines
  • Connect slicers and timelines to multiple reports
  • Apply conditional formatting to a PivotTable

Introduction to Recorded Macros

  • Show the Developer tab
  • Record a basic macro
  • Assign a macro to a Quick Access Toolbar button, a Ribbon button and to a Command button
  • Save a workbook that contains a macro
  • Save a macro to the Personal Macro workbook

IF and VLOOKUP In-depth: 1 Day Course

This course allows you to focus on two sets of functions often used in the workplace: logical functions and LOOKUP functions.

VLOOKUP & HLOOKUP

  • Use VLOOKUP for exact match lookups
  • Use VLOOKUP for approximate match lookups
  • Use the HLOOKUP function
  • Perform a VLOOKUP across sheets
  • Perform a VLOOKUP across workbooks

Error Handling

  • Handle NA errors using IFERROR, IFNA, ISNA and ISERROR

Lookup to the Left

  • Use the LOOKUP function
  • Use MATCH and INDEX

Two Way Lookups

  • Use MATCH and INDEX to perform a two-way lookup
  • Use the INDIRECT Function to perform a two-way lookup
  • Return a complete record using MATCH and INDEX

Lookup Across Multiple Tables

  • Use IF to lookup across multiple tables
  • Use IFERROR to lookup across multiple tables
  • Use INDEX to lookup across multiple tables
  • Use CHOOSE to lookup across multiple tables
  • Use OFFSET to lookup across multiple tables
  • Use SWITCH to lookup across multiple tables (Office 365 only)

Working with Text

  • Use text functions, LEFT, MID and RIGHT to extract a portion of a text string to lookup on
  • Use SEARCH and FIND functions in conjunction with text functions
  • Remove space and non-printing characters to overcome lookup problems
  • Use REPLACE and SUBSTITUTE to replace portions of a text string to help with lookups
  • Use CONCATENATE, CONCAT (Office 365 only) or TEXTJOIN (Office 365 only) to combine text

Comparison Operators

  • Create logical test formula to return TRUE or FALSE

The IF Function

  • Use the IF function to return values or formulas in place of TRUE or FALSE
  • Use AND and OR with IF to perform multiple tests simultaneously
  • Use the NOT function
  • Create nested IF functions for extended output
  • Use the IFS function for extended output (Office 365 only)

Conditional Calculations

  • Use COUNTIF and COUNTIFS to count based on a test
  • Use SUMIF and SUMIFS to sum based on a test
  • Use AVERAGEIF and AVERAGEIFS to average based on a test
  • Use MINIF and MINIFS to return the smallest value based on a test
  • Use MAXIF and MAXIFS to return the largest value based on a test
  • Use wildcards in criteria
  • Case sensitive conditions

Database & PivotTables In-depth: 1 Day Course

This in-depth course allows you to focus on database work and PivotTables. If your work is mainly concerned with data analysis then this is the ideal course for you. The course covers, sorting, autofilter, advanced filter, PivotTables and PivotCharts.

Sorting Basics

  • How to correctly set up a database
  • Avoiding problems with sorting - splitting records, sorting headings, blank rows and columns preventing proper sorting etc
  • Sort on different data types - text, numeric and date
  • Sort by colour
  • Sort on multiple columns
  • Sort by custom order
  • Sort by user generated custom order
  • Case sensitive sort
  • Horizontal sort

Filtering Basics

  • Sort on different data types - text, numeric and date
  • Use wildcards for text field filter
  • Top, Bottom and Average filters on numeric fields
  • Copy filtered results
  • Save filters in Custom Views
  • Filter using Excel Tables

Working with Excel Tables

  • Use Excel Tables
  • Use the Excel Table Total Row
  • Create formula within an Excel Table
  • Use Slicers to filter within an Excel Table
  • Create a database using Excel Tables and Data Validation

Advanced Filter

  • Use Excel's Advanced Filter to perform complex filters and/or return unique values
  • Use database functions - DSUM, DAVERAGE etc

Introduction to PivotTables

  • What is a PivotTable?
  • How to set up your data correctly for a PivotTable
  • Unpivot pivoted data
  • Prepare data for a PivotTable - common data problems
  • Elements of a PivotTable - PivotTable areas
  • Create a basic PivotTable
  • Format values in a PivotTable
  • Switch between PivotTable layouts
  • Switch between and create your own PivotTable Styles
  • Change the calculations used in a PivotTable
  • Update and refresh your PivotTable
  • Show multiple calculations in the same PivotTable
  • Show/hide subtotals and grand totals
  • Apply conditional formatting to a PivotTable
  • Create a PivotChart
  • Understand the PivotTable cache
  • Share/unshare a pivot cache

Analyse and Calculate Within a PivotTable

  • Group data - date, numeric, text fields
  • Generate Report Filter Pages
  • Slice a PivotTable
  • Format slicers and timelines
  • Connecting slicers and timelines to multiple reports
  • % of Column and row calculations
  • Difference from calculations
  • Rank calculations
  • Index calculations
  • Running totals calculations
  • Calculated fields
  • Calculated items and named sets
  • Use the GETPIVOTDATA function

Other Topics

  • Create dashboards with PivotTables, PivotCharts, Timelines and Slicers
  • Use PowerPivot's Data Model to add multiple tables to a PivotTable's data source

Recorded Macros and Introduction to Visual Basic for Applications (VBA): 2 Day Course

Our 2 day Microsoft Excel Macros and Visual Basic for Applications (VBA) course is aimed at those who need gain a good grounding in this programming language.

Recording Macros

  • Recording a Macro
  • Recording a Macro Using Relative References
  • Useful Navigation and Selection Shortcut Keys to Use When Recording Macros
  • Recording Techniques For Macros That Need to Work Regardless of the Number of Columns or Rows
  • Editing a Recorded Macro in the VBA Editor

Running and Storing Macros

  • Saving Macro Enabled Excel files
  • Where to Store Macros
  • Macro Security
  • Customising the Quick Access Toolbar and Ribbon with Macro Buttons
  • Adding Form Buttons to a Sheet

Intro to VBA

  • What is VBA?
  • Understanding Parts of Speech: Objects, Collections, Methods, Parameters and Properties
  • Application Methods and Properties
  • Window/s Methods and Properties
  • Workbook/s Methods and Properties
  • Worksheet/s Methods and Properties
  • Range Methods and Properties
  • Chart/s Methods and Properties

Working With Ranges

  • Referencing Cells/Ranges
  • CurrentRegion
  • The End Property
  • Offset
  • Resize
  • Intersect
  • UsedRange
  • SpecialCells
  • Areas
  • Rows and Columns
  • Copy/Move
  • Formatting Cells and Values

Working With Worksheets

  • Activate Worksheet
  • Create New Worksheet
  • Delete a Worksheet
  • Name a Worksheet
  • Copy/Move Worksheet
  • Group Worksheets
  • Hide Worksheets
  • Protect Worksheets

Working With Workbooks

  • Open/Close Workbook
  • Save Workbook
  • Delete Workbook
  • Protect Workbook
  • Add Named Reference Within a Workbook

Working With Charts

  • Create an Embedded Chart
  • Delete a Chart
  • Create a Chart On a Chart Sheet
  • Apply Chart Style
  • Colour a Chart
  • Position and Size a Chart
  • Add Titles and Other Chart Elements

Variables & Constants

  • Naming Variables
  • Declaring a Variable's Data Type
  • Option Explicit
  • Scoping Variables
    • Local
    • Module-wide
    • Public
  • Static Variables
  • Life of a Variable
  • Assigning Values to Variables and Object Variables
  • Constants

Programming Flow

  • If Then Else, ElseIf
  • Select Case

Creating Loops

  • For Each Next - Looping Through Workbooks, Worksheets & Ranges
  • For Next Loops
  • Do Loops - Do Until, Do While, Exit Do

Events

  • Workbook Events
  • Worksheet Events
  • OnKey Events

Interactivity

  • MsgBox
  • InputBox
  • Application.InputBox
  • Designing Userforms

Function

  • Worksheet Functions
  • VBA Functions
  • User Defined Functions

Debugging and Error Handling

  • Types of Errors
    • Syntax Errors
    • Compile Errors
    • Run-time Errors
  • Stepping Through Code
  • Setting Breakpoints
  • Using the Immediate Window
  • Setting Watches and Using the Watch Window
  • Using the Locals Window
  • Error Trapping and Handling
    • On Error Resume Next
    • On Error Goto
    • Specifying Error Handling for Different Run-time Error Numbers

Tailored Excel Training

Although we offer outlines for Excel training, they are only suggestions for course content. If our outlines don't quite fit with your training requirements we are more than happy to deliver a tailored course for you.

Excel 2016 New Features
We can show you the great new features in Excel 2016, including:

  • New chart types: Treemap, Sunburst, Histogram, Box and Whisker, Waterfall and Funnel
  • Powerful tools such Power Query, PowerPivot and 3D Map are now integrated into the standard Excel interface
  • A new forecasting sheet function has been added
  • New functions including TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS, MINIFS and new FORECAST functions.

Posted by Blue Pecan Computer Training