Established in 2005, Blue Pecan provides in house Excel training courses at your business premises. We offer BeginnersIntermediate and Advanced Excel training as well as Excel Dashboards, Power Query, Power Pivot and Macros/VBA courses. 

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 2019 (Office 365), 2016, 2013, 2010 and 2007.

If you download our resources it would be great if you could "like" this page or link to it. Thanks!!
70+ MUST KNOW EXCEL SHORTCUT KEYS:  Download the pdf
UNDERSTAND & FIX EXCEL ERRORS: Download the pdf
Learn how to fix these errors: #DIV/0!, #N/A!, #NAME?, #NULL!, #NUM!, #REF! & #VALUE!

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 – Day Rates
MS Office Courses inc Excel – £650 plus vat.  
Central London – £695 plus vat
Excel VBA and Macros – £750 plus vat.  
Central London – £795 plus vat
Excel Power Query – £750 plus vat.  
Central London – £795 plus vat
Excel Power Pivot – £750 plus vat.
Central London – £795 plus vat

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-depthPower QueryPowerPivotVisual Basic For Applications (VBA)Excel DashboardsTailored Training

Excel For Complete Beginners: 1 Day Course

Please note this outline can be tailored - nothing is set in stone!  Most of our customers opt for a tailored course outline. 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.  Download the course outline in pdf format.

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

Please note this outline can be tailored - nothing is set in stone!  Most of our customers opt for a tailored course outline. 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. Download the course outline in pdf format.

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

Please note this outline can be tailored - nothing is set in stone!  Most of our customers opt for a tailored course outline. 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. Download the course outline in pdf format.

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

Please note this outline can be tailored - nothing is set in stone!  Most of our customers opt for a tailored course outline. 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. Download the course outline in pdf format.

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

Please note this outline can be tailored - nothing is set in stone!  Most of our customers opt for a tailored course outline. This course allows you to focus on two sets of functions often used in the workplace: logical functions and LOOKUP functions. Download the course outline in pdf format.

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

Please note this outline can be tailored - nothing is set in stone!  Most of our customers opt for a tailored course outline. 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. Download the course outline in pdf format.

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

Introduction to Power Query - 1 Day Course

Please note this outline can be tailored - nothing is set in stone!  Most of our customers opt for a tailored course outline. Power Query allows you to automate data tasks in three stages: extract, transform and load. This course gives a comprehensive introduction to these tools.  By the end of the course delegates will be able to effectively extract data from Excel workbooks, CSV and text files, Access databases and folders.  Delegates will also be able to effectively clean, integrate and enrich data.  Finally, delegates will be able to load data to a connection, an Excel table or a Pivot report. Download the course outline in pdf format.

Navigate the Power Query Editor (PQE)

  • Query Settings task pane
  • Formula bar
  • Query dependencies

Select the Source Data for Your Query

  • Table/Range in current workbook
  • Data in another workbook
  • Text/CSV files
  • Files in folder
  • Access database
  • Blank query
  • Existing connections
  • Referencing existing queries

Close and Load Queries

  • Load to a table
  • Load to PivotTable or Pivot Chart
  • Only create connection
  • Add to data model

Edit a Query

  • Edit a query – launch the PQE
  • Understand steps in the Query Settings task pane
  • Delete steps
  • Modify steps
  • Name/rename a query
  • Delete a query
  • Refresh a query

Transformations

  • Change a field’s data type
  • Remove rows/columms
  • Move to the top or bottom of a worksheet
  • Change a field’s data type
  • Remove rows/columns
  • Split columns
  • Merge columns
  • Sort columns
  • Filter columns
  • Perform calculations on columns
  • Create conditional column
  • Create new columns by example
  • Group columns
  • Fill data
  • Use first row as headers
  • Extract text
  • Trim/clean
  • Change case
  • Create index

Append Data

  • Append all tables in the current workbook
  • Append specific worksheets within the current workbook
  • Append all worksheets in an external workbook
  • Append specific worksheets from an external workbook

Combine Files in a Folder

  • Combine files in a folder where the sheet or table name are the same
  • Combine files in a folder where neither the sheet or table name are the same
  • Import Text/CSV files within a folder

Other Transformations

  • Merge data across tables
    • Match columns
    • Select the correct join kind
  • Pivot data
  • Unpivot data

Share Queries

  • Export a query as a connection file
  • Import a query as a connection file

M Functions and M Language

  • View the M code for your query
  • Write M formula
  • Use M Text functions
  • Use M Date functions
  • Use more M functions
    • CurrentWorkbook
    • Workbook
    • Combine 

Introduction to PowerPivot - 1 Day Course

Please note this outline can be tailored - nothing is set in stone!  Most of our customers opt for a tailored course outline. PowerPivot is designed to provide self-service business intelligence, allowing end users to perform complex data analysis without the intervention of business intelligence technicians.  Delegates will gain a working knowledge of PowerPivot and an understanding of basic database theory.  Download the course outline in pdf format.

This is box title

  • When should I use PowerPivot vs PivotTables
  • Database theory
    • Normalisation
    • Primary & Foreign keys
    • Fact tables vs dimension tables
    • Star scheme vs Snowflake schema

Basic Operations

  • Activate the PowerPivot add-in
  • Open and close the PowerPivot add-in
  • Navigate the PowerPivot add-in
  • Format columns
  • Sort columns
  • Create relationships between tables
  • Create a Pivot report
  • Refresh data

Create Calculated Columns & DAX Formulas

  • Create calculated columns referring to fields in the current table
  • Create calculated columns referring to fields in related tables using the RELATE function

Create Measures Using DAX Functions

  • Theory – calculated columns vs measures
  • Use the CALCULATE function
  • Use the FILTER function
  • Use the ALL function
  • Use the SUMX function
  • Use the AVERAGEX function

Use Time Intelligence Functions

  • Create a data table
  • Use DAX time intelligence functions

More PowerPivot Functionality

  • Create named sets
  • Create hierarchies
  • Create KPIs

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

        Please note this outline can be tailored - nothing is set in stone!  Most of our customers opt for a tailored course outline. 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. Download the course outline in pdf format.

        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

        Excel Dashboards & Advanced Analytics: 2 Day Course

        This course is for advanced users of Excel you need to created automated dashboards based on advanced data analytics.  Topics covered include: dashboard design principles, advanced charting, advanced formulas, interactivity and automation using controls and VBA and dashboard security. Delegates will need to be comfortable with the content of our Fundamentals, Intermediate and Advanced courses to take part in this training.  Download the course outline in pdf format

        Dashboard Design Principles

        • Building a data model
          • The data layer
          • The analysis layer
          • The presentation layer
        • Documentation
        • Keeping it simple
        • Avoiding chart clutter
        • Emphasising / de-emphasising content
        • Positioning components to reflect weighting/importance

        Advanced Charting Techniques

        • Displaying symbols on the category axis
        • Dynamic chart titles based on text formulas
        • Conditional formatting charts to pick out best, worst performing or positive vs negative values
        • Creating dynamic data labels using shapes
        • Using Sparklines
        • Small multiples/panel charts
        • Using the REPT function to compare data
        • Creating rollover buttons using the HYPERLINK function & VBA

        Components that Display Trending Data

        • Line graphs
        • Area charts
        • Bar charts
        • Logarithmic scales
        • Useful techniques for displaying multiple time dimensions as axis labels
        • Comparative trending
        • Displaying forecasts
        • Smoothing data
        • Step charts

        Components that Group Data

        • Histogram charts
        • Pareto charts
        • PivotCharts

        Components that Display Performance vs Target

        • Displaying values against a median or target
        • Creating thermometer charts
        • Displaying a target band
        • Creating bullet charts

        Interactive Controls

        • Check box controls
        • Option button controls
        • Combo box/ list box controls
        • Scroll bar controls
        • Using control outputs to display/hide data in a chart

        Advanced Formula Work for Dashboards

        • Using the INDEX and MATCH functions to determine data for chart series
        • Using the IF function
        • Using the NA function to hide data from a chart
        • Using the CHOOSE function to select data for a chart
        • Using the INDIRECT function with charts
        • Utilising control outputs in formulas

        Macros & VBA for Dashboards

        • Recording macros
        • Editing the VBA for recorded macros
        • Introduction to VBA
          • Writing your own macros
          • Declaring variables
          • Writing IF-Then-Else structures
          • Using control outputs in your VBA
          • Assigning macros to worksheet controls

        Dashboard Security

        • Protect your dashboard at the worksheet level
        • Protect your dashboard at the workbook level
        • Protect your dashboard at the VBA project level

        Avoiding Dashboard Lag

        • Top tips – dos and don’ts

        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.

        Posted by Blue Pecan Computer Training