Learning Outcomes: Getting to Know Microsoft Excel Creating a New Workbook Working With Workbooks Selecting Ranges Copying Data Filling Data Moving Data Special Pasting Formulas and Functions Formula Referencing Font Formatting Cell Alignment Row and Column Formatting Number Formatting Printing Creating Charts

Topics will be selected from the following list

Creating a New Workbook
Understanding Workbooks
Creating a New Workbook
Typing Text
Typing Numbers
Typing Dates
Typing Formulas
Saving a New Workbook
Easy Formulas
Checking the Spelling
Making Basic Changes
Printing a Worksheet
Safely Closing a Workbook

Working With Workbooks
Opening an Existing Workbook
Navigating a Workbook
Navigating Using the Keyboard
Using Go To
Practice Exercise
Practice Exercise
The Open Dialog Box
Editing in a Workbook
Understanding Data Editing
Overwriting Cell Contents
Editing Longer Cells
Editing Formulas
Clearing Cells
Deleting Data in a Cell
Using Undo and Redo

Copying Data
Understanding Copying in Excel
Using Fill for Quick Copying
Copying From One Cell to Another
Copying From One Cell to a Range
Copying From One Range to Another
Copying Relative Formulas
Copying to a Non-Contiguous Range
Copying to Another Worksheet
Copying to Another Workbook

Filling Data
Understanding Filling
Filling a Series
Filling a Growth Series
Filling a Series Backwards
Filling Using Options
Creating a Custom Fill List
Modifying a Custom Fill List
Deleting a Custom Fill List

Moving Data
Understanding Moving in Excel
Moving Cells and Ranges
Moving Data to Other Worksheets
Moving Data to Other Workbooks

Formulas and Functions
Understanding Formulas
Creating Formulas That Add
Creating Formulas That Subtract
Formulas That Multiply and Divide
Understanding Functions
Using the Sum Function to Add
Summing Non-Contiguous Ranges
Calculating an Average
Finding a Maximum Value
Finding a Minimum Value
More Complex Formulas
What if Formulas
Common Error Messages

Formula Referencing
Absolute Versus Relative Referencing
Relative Formulas
Problems With Relative Formulas
Creating Absolute References
Creating Mixed References

Cell Alignment
Understanding Cell Alignment
Aligning Right
Aligning to the Centre
Aligning Left
Aligning Top
Aligning Bottom
Aligning to the Middle
Rotating Text
Indenting Cells
Wrapping and Merging Text
Merging and Centring
Merging Cells
Unmerging Cells

Row and Column Formatting
Approximating Column Widths
Setting Precise Columns Widths
Setting the Default Column Width
Approximating Row Height
Setting Precise Row Heights
Hiding Rows and Columns
Unhiding Rows and Columns

Printing
Understanding Printing
Previewing Before You Print
Selecting a Printer
Printing a Range
Printing an Entire Workbook
Specifying the Number of Copies
The Print Options

Creating Charts
Understanding the Charting Process
Choosing the Chart Type
Creating a New Chart
Working With an Embedded Chart
Resizing a Chart
Dragging a Chart
Printing an Embedded Chart
Creating a Chart Sheet
Changing the Chart Type
Changing the Chart Layout
Changing the Chart Style
Printing a Chart Sheet
Embedding a Chart Into a Worksheet
Deleting a Chart

Learning Outcomes: understand what Office Online is and how to access it understand and access Office Online to create, save and edit common Microsoft Office documents understand Excel Online and how it can be used work with more advanced features of Excel Online

Topics will be selected from the following list

Introduction to Microsoft Online

• Working in the Cloud
• Understanding Microsoft Online
• Office Online and Microsoft 365
• Accessing Microsoft Online From the Home Page

Starting With Microsoft Online

• Accessing OneDrive
• The OneDrive Screen
• Uploading Files
• Opening Files From OneDrive
• Editing OneDrive Files
• Deleting Files
• Creating a New Document
• The Office Online Screen
• Understanding the Backstage
• Saving a Document
• Reading View vs Editing View
• Opening an Existing Document
• Working Collaboratively
• Accessing Desktop Version in Office Online

Starting With Excel Online

• The Excel Online Screen
• Selecting in Excel Online
• Applying Basic Font Formatting
• Applying Alignment
• Applying Number Formatting
• Understanding Data Editing
• Inserting and Deleting Cells
• Understanding Formulas
• Editing Formulas in a Workbook
• Working With Excel Online
• Inserting Tables
• Inserting Charts
• Working With Chart Labels and Axes
• Creating Surveys
• Sharing Surveys
• Editing Surveys
• Inserting Comments
• Working With Comments

Learning Outcomes: Formulas Copy formulas with AutoFill Functions in Excel SUM Function AutoSum AVERAGE Function MAX Function Absolute cell references Creating formulas that reference cells in other worksheets Linking workbooks

Topics will be selected from the following list

Filling Data and Copy Formulas

• Filling Data and Copy Formulas
• Understanding Filling
• Filling a Series
• Filling a Growth Series
• Filling a Series Backwards
• Filling Using Options
• Creating a Custom Fill List
• Modifying a Custom Fill List
• Deleting a Custom Fill List
• Extracting With Flash Fill
• More Complex Flash Fill Extractions

Formulas and Fucntions

• Formulas and Functions
• Understanding Formulas
• Creating Formulas That Add
• Creating Formulas That Subtract
• Formulas That Multiply and Divide
• Understanding Functions
• Using the SUM Function to Add
• Summing Non-Contiguous Ranges
• Calculating an Average
• Finding a Maximum Value
• Finding a Minimum Value
• Creating More Complex Formulas
• What if Formulas
• Common Error Messages

Formula Referencing

• Formula Referencing
• Absolute Versus Relative Referencing
• Relative Formulas
• Problems With Relative Formulas
• Creating Absolute References
• Creating Mixed References

Learning Outcomes: Protecting data Sharing a workbook via a network Sharing a workbook on OneDrive Using Track Changes to review shared edits

Topics will be selected from the following list

Protecting Data

• Understanding Data Protection
• Providing Total Access to Cells
• Protecting a Worksheet
• Working With a Protected Worksheet
• Disabling Worksheet Protection
• Providing Restricted Access to Cells
• Password Protecting a Workbook
• Opening a Password Protected Workbook
• Removing a Password From a Workbook

Sharing Workbooks

• Sharing Workbooks via the Network
• Sharing Workbooks via OneDrive
• Saving to OneDrive
• Sharing Workbooks
• Opening Shared Workbooks
• Enabling Tracked Changes
• Accepting or Rejecting Changes
• Disabling Tracked Changes
• Adding Worksheet Comments
• Navigating Worksheet Comments
• Editing Worksheet Comments
• Deleting Comments

Track Changes

• Enabling the TrackChanges in Excel Feature
• How do ‘Track Changes’ work in Excel?
• Getting a List of All the Changes
• Accepting/Rejecting Changes
• Disabling Track Changes in Excel
• Difference Between Comments and Track Changes

Learning Outcomes: Formula Referencing Formula Techniques Logical Functions Number Formatting Techniques Conditional Formatting Applying Borders Working With a Worksheet Worksheet Techniques Finding and Replacing Page Setup Sorting Data Filtering Data Charting Techniques Chart Text Formatting

Topics will be selected from the following list

Formula Referencing
Absolute Versus Relative Referencing
Relative Formulas
Problems With Relative Formulas
Creating Absolute References
Creating Mixed References

Formula Techniques
Scoping a Formula
Developing a Nested Function
Creating Nested Functions
Editing Nested Functions
Copying Nested Functions
Using Concatenation
Switching to Manual Recalculation
Forcing a Recalculation
Pasting Values From Formulas

Logical Functions
Understanding Logical Functions
Using IF to Display Text
Using IF to Calculate Values
Nesting IF Functions
Using IFERROR
Using TRUE and FALSE
Using AND
Using OR
Using NOT

Number Formatting Techniques
Using Alternate Currencies
Formatting Dates
Formatting Time
Creating Custom Formats

Conditional Formatting
Understanding Conditional Formatting
Formatting Cells Containing Values
Clearing Conditional Formatting
More Cell Formatting Options
Top Ten Items
More Top and Bottom Formatting Options
Working With Data Bars
Working With Colour Scales
Working With Icon Sets
Understanding Sparklines
Creating Sparklines
Editing Sparklines

Applying Borders
Understanding Borders
Applying a Border to a Cell
Applying a Border to a Range
Applying a Bottom Border
Applying Top and Bottom Borders
Removing Borders
The More Borders Options
Using the More Borders Option

Working With a Worksheet
Understanding Worksheets
Changing Worksheet Views
Worksheet Zooming
Viewing the Formula Bar
Viewing the Gridlines
Viewing the Ruler
Inserting Cells
Deleting Cells
Inserting Columns
Inserting Rows
Deleting Rows and Columns
Switching Between Worksheets

Worksheet Techniques
Inserting and Deleting Worksheets
Copying a Worksheet
Renaming a Worksheet
Moving a Worksheet
Hiding a Worksheet
Unhiding a Worksheet
Copying a Worksheet to Another Workbook
Moving a Worksheet to Another Workbook
Changing Worksheet Tab Colours
Grouping Worksheets
Hiding Rows and Columns
Unhiding Rows and Columns
Freezing Rows and Columns
Splitting Windows

Finding and Replacing
Understanding Find and Replace Operations
Finding Text
Finding Cell References in Formulas
Replacing Values
Using Replace to Change Formulas
Replacing Within a Range
Finding Formats
Finding Constants Using Go to Special
Finding Formulas Using Go to Special
Finding the Current Region
Finding the Last Cell

Page Setup
Understanding Page Layout
Using Built in Margins
Setting Custom Margins
Changing Margins by Dragging
Centring on a Page
Changing Orientation
Specifying the Paper Size
Setting the Print Area
Clearing the Print Area
Inserting Page Breaks
Using Page Break Preview
Removing Page Breaks
Setting a Background
Clearing the Background
Settings Rows as Repeating Print Titles
Clearing Print Titles
Printing Gridlines
Printing Headings
Scaling to a Percentage
Fit to a Specific Number of Pages
Strategies for Printing Larger Worksheets

Sorting Data
Understanding Lists
Performing an Alphabetical Sort
Performing a Numerical Sort
Sorting on More Than One Column
Sorting Numbered Lists
Sorting by Rows

Filtering Data
Understanding Filtering
Applying and Using a Filter
Clearing a Filter
Creating Compound Filters
Multiple Value Filters
Creating Custom Filters
Using Wildcards

Charting Techniques
Understanding Chart Layout Elements
Adding a Chart Title
Adding Axes Titles
Positioning the Legend
Showing Data Labels
Showing a Data Table
Modifying the Axes
Showing Gridlines
Formatting the Plot Area
Adding a Trendline
Adding Error Bars
Adding a Text Box to a Chart
Drawing Shapes in a Chart

Learning Outcomes: Understanding chart types Creating a chart Editing a chart Formatting chart elements Chart Object Formatting Understanding Power Maps

Topics will be selected from the following list

Creating Charts

• Understanding the Charting Process
• Choosing the Right Chart
• Using a Recommended Chart
• Creating a New Chart From Scratch
• Working With an Embedded Chart
• Resizing a Chart
• Repositioning a Chart
• Printing an Embedded Chart
• Creating a Chart Sheet
• Changing the Chart Type
• Changing the Chart Layout
• Changing the Chart Style
• Printing a Chart Sheet
• Embedding a Chart Into a Worksheet
• Deleting a Chart

Power Maps

• Creating a Power Map Tour
• Working With Location Fields
• Working With Mapping Confidence
• Working With Height and Category Fields
• Filtering Data
• Navigating a Data Map
• Changing the Look
• Working With Layers
• Working With Scenes
• Working With Scene Options
• Working With Time Settings
• Viewing and Editing a Tour

Chart Elements

• Understanding Chart Elements
• Adding a Chart Title
• Adding Axes Titles
• Repositioning the Legend
• Showing Data Labels
• Showing Gridlines
• Formatting the Chart Area
• Adding a Trendline
• Adding Error Bars
• Adding a Data Table

Chart Object Formatting

• Understanding Chart Formatting
• Selecting Chart Objects
• Using Shape Styles
• Changing Column Colour Schemes
• Changing the Colour of a Series
• Changing Line Chart Colours
• Using Shape Effects
• Colouring the Chart Background
• Understanding the Format Pane
• Using the Format Pane
• Exploding Pie Slices
• Changing Individual Bar Colours
• Formatting Text
  Formatting With WordArt
• Changing WordArt Fill
• Changing WordArt Effects

Chart Types

• Creating a Column Chart
• Creating a Line Chart
• Creating a Pie Chart
• Creating a Bar Chart
• Creating an Area Chart
• Creating a Scatter Chart
• Understanding Other Chart Types

Learning Outcomes: Introduction to Database Tables Rules for databases Sorting data Sorting by multiple columns Filtering a list Automatic Subtotals Importing data Cleaning up imported data Parsing data into columns Removing duplicates

Topics will be selected from the following list

Worksheet Tables

• Understanding Tables
• Creating a Table From Scratch
• Working With Table Styles
• Inserting Table Columns
• Removing Table Columns
• Converting a Table to a Range
• Creating a Table From Data
• Inserting or Deleting Table Records
• Removing Duplicates
• Sorting Tables
• Filtering Tables
• Renaming a Table
• Splitting a Table
• Deleting a Table

Sorting Data

• Understanding Lists
• Performing an Alphabetical Sort
• Performing a Numerical Sort
• Sorting on More Than One Column
• Sorting Numbered Lists
• Sorting by Rows

Filtering Data

• Understanding Filtering
• Applying and Using a Filter
• Clearing a Filter
• Creating Compound Filters
• Multiple Value Filters
• Creating Custom Filters
• Using Wildcards

Importing and Exporting

• Understanding Data Importing
• Importing From an Earlier Version
• Understanding Text File Formats
• Importing Tab Delimited Text
• Importing Comma Delimited Text
• Importing Space Delimited Text
• Importing Access Data
• Working With Connected Data
• Unlinking Connections

Subtotalling

• Creating Subtotals
• Using a Subtotalled Worksheet
• Creating Nested Subtotals
• Copying Subtotals
• Using Subtotals With AutoFilter
• Creating Relative Names for Subtotals
• Using Relative Names for Subtotals

Learning Outcomes: Introduction to Database Tables Rules for databases Sorting data Sorting by multiple columns Filtering a list Automatic Subtotals Importing data Cleaning up imported data Parsing data into columns Removing duplicates

Topics will be selected from the following list

Logical Functions

Understanding Logical Functions
Using IF With Text
Using IF With Numbers
Nesting IF Functions
Using IFERROR
Using TRUE and FALSE
Using AND
Using OR
Using NOT
NestedIFs

Conditional Formatting

Understanding Conditional Formatting
Formatting Cells Containing Values
Clearing Conditional Formatting
More Cell Formatting Options
Top Ten Items
More Top and Bottom Formatting Options
Working With Data Bars
Working With Colour Scales
Working With Icon Sets
Understanding Sparklines
Creating Sparklines
Editing Sparklines
Creating Custom Rules
The Conditional Formatting Rules Manager
Managing Rules

Learning Outcomes: Setting Excel Options Chart Object Formatting Labels and Names Protecting Data Summarising and Subtotalling Data Linking Data Consolidation Pivot Tables PivotTable Techniques PivotCharts Goal Seeking Grouping and Outlining Solver Recorded Macros Recorder Workshop

Topics will be selected from the following list

Setting Excel Options
Understanding Excel Options
Personalising Excel
Setting the Default Font
Setting Formula Options
Understanding Save Options
Setting Save Options
Setting the Default File Location
Setting Advanced Options

Chart Object Formatting
Understanding Chart Object Formatting
Selecting Chart Elements
Using Shape Styles to Format Objects
Changing Column Colour
Changing Pie Slice Colour
Changing Bar Colours
Changing Chart Line Colours
Using Shape Effects
Filling the Chart Area and the Plot Area
The Format Dialog Box
Using the Format Dialog Box
Using Themes

Labels and Names
Understanding Labels and Names
Creating Names Using Text Labels
Using Names in New Formulas
Applying Names to Existing Formulas
Creating Names Using the Name Box
Using Names to Select Ranges
Pasting Names Into Formulas
Creating Names for Constants
Creating Names From a Selection
Scoping Names to the Worksheet
Using the Name Manager
Documenting Range Names

Protecting Data
Understanding Data Protection
Providing Total Access to Cells
Protecting a Worksheet
Working With a Protected Worksheet
Disabling Worksheet Protection
Providing Restricted Access to Cells
Password Protecting a Workbook
Opening a Password Protected Workbook
Removing a Password From a Workbook

Data Linking
Understanding Data Linking
Linking Between Worksheets
Linking Between Workbooks
Updating Links Between Workbooks

Data Consolidation
Understanding Data Consolidation
Consolidating With Identical Layouts
Creating an Outlined Consolidation
Consolidating With Different Layouts
Consolidating Data Using 3D SUM Functions

Pivot Tables
Understanding Pivot Tables
Creating a PivotTable Shell
Dropping Fields Into a PivotTable
Filtering a PivotTable Report
Clearing a Report Filter
Switching PivotTable Labels
Formatting a PivotTable Report
Understanding Slicers
Creating Slicers
Summarising and Subtotalling
Creating Subtotals
Using a Subtotalled Worksheet
Creating Nested Subtotals
Copying Subtotals
Using Subtotals With AutoFilter
Creating Relative Names for Subtotals
Using Relative Names for Subtotals

PivotTable Techniques
Using Compound Fields
Counting in a PivotTable Report
Formatting PivotTable Report Values
Working With PivotTable Grand Totals
Working With PivotTable Subtotals
Finding the Percentage of Total
Finding the Difference From
Grouping in PivotTable Reports
Creating Running Totals
Creating Calculated Fields
Providing Custom Names
Creating Calculated Items
PivotTable Options
Sorting in a PivotTable

PivotCharts
Creating a PivotChart Shell
Dragging Fields for the PivotChart
Changing the PivotChart Type
Using the PivotChart Filter Field Buttons
Moving PivotCharts to Chart Sheets

Goal Seeking
Understanding Goal Seek Components
Using Goal Seek
Practice Exercise
Practice Exercise
Grouping and Outlining
Understanding Grouping and Outlining
Creating an Automatic Outline
Working With an Outline
Creating a Manual Group
Grouping by Columns

Solver
Understanding How Solver Works
Installing the Solver Add-In
Setting Solver Parameters
Adding Solver Constraints
Performing the Solver Operation
Running Solver Reports

Recorded Macros
Understanding Excel Macros
Setting Macro Security
Saving a Document as Macro Enabled
Recording a Simple Macro
Running a Recorded Macro
Relative Cell References
Running a Macro With Relative References
Viewing a Macro
Editing a Macro
Assigning a Macro to the Toolbar
Running a Macro From the Toolbar
Assigning a Macro to the Ribbon
Assigning a Keyboard Shortcut to a Macro
Deleting a Macro
Copying a Macro

Recorder Workshop
Preparing Data for an Application
Recording a Summation Macro
Recording Consolidations
Recording Divisional Macros
Testing Macros
Creating Objects to Run Macros
Assigning a Macro to an Object

Learning Outcomes Learn how to create and edit VLOOKUP and other Lookup formulas to extract data from Excel lists and tables based on variable conditions and criteria understand and use formula cell referencing to create more complex formulas use a range of lookup and reference functions

Topics will be selected from the following list

Absolute Cell References

• Using Relative instead of Absolute Cell References
• Using Absolute Cell References in Formulas

Range Names

• User Friendly Workbooks
• Managing Range Names
• The Name Manager
• Defining a Name Range
• Rules for Naming Ranges
• Naming Ranges With the Name Box
• Naming Multiple Ranges
• Selecting Named Cells
• Formulas with Range Names
• Deleting Range Names

The LOOKUPs Functions

• Understanding Data Lookup Functions
• Using VLOOKUP
• Using HLOOKUP
• Using INDEX
• Using MATCH

Learning Outcomes Understand Pivot Tables Create a Pivot Table Rearrange field items in a Pivot Table Create a Pivot Chart

Topics will be selected from the following list

PivotTables

• Understanding PivotTables
• Recommended PivotTables
• Creating Your Own PivotTable
• Defining the PivotTable Structure
• Filtering a PivotTable
• Clearing a Report Filter
• Switching PivotTable Fields
• Formatting a PivotTable
• Understanding Slicers
• Creating Slicers
• Inserting a Timeline Filter

PivotTable Features

• Using Compound Fields
• Counting in a PivotTable
• Formatting PivotTable Values
• Working With PivotTable Grand Totals
• Working With PivotTable Subtotals
• Finding the Percentage of Total
• Finding the Difference From
• Grouping in PivotTable Reports
• Creating Running Totals
• Creating Calculated Fields
• Providing Custom Names
• Creating Calculated Items
• PivotTable Options
• Sorting in a PivotTable

PivotCharts

• Inserting a PivotChart
• Defining the PivotChart Structure
• Changing the PivotChart Type
• Using the PivotChart Filter Field Buttons
• Moving PivotCharts to Chart Sheets

Learning Outcomes: Performing simple ‘What if’ modelling Using Scenario Manager Using Goal Seek Using Solver

Topics will be selected from the following list

• Understanding Goal Seeking
• Using Goal Seek

• Understanding Scenarios
• Creating a Default Scenario
• Creating Scenarios
• Using Names in Scenarios
• Displaying Scenarios
• Creating a Scenario Summary Report
• Merging Scenarios

• Understanding How Solver Works
• Installing the Solver Add
• Setting Solver Parameters
• Adding Solver Constraints
• Performing the Solver Operation
• Running Solver Reports

Learning Outcomes: Understand macros Record a simple macro Run a macro Edit a macro in the VBA Editor

Topics will be selected from the following list

Recorded Macros

• Understanding Excel Macros
• Setting Macro Security
• Saving a Document as Macro Enabled
• Recording a Simple Macro
• Running a Recorded Macro
• Relative Cell References
• Running a Macro With
• Relative References
• Viewing a Macro
• Editing a Macro
• Assigning a Macro to the Toolbar
• Running a Macro From the Toolbar
• Assigning a Macro to the Ribbon
• Assigning a Keyboard Shortcut to a Macro
• Deleting a Macro
• Copying a Macro

Recorder Workshop

• Preparing Data for an Application
• Recording a Summation Macro
• Recording Consolidations
• Recording Divisional Macros
• Testing Macros
• Creating Objects to Run Macros
• Assigning a Macro to an Object

Understanding Excel VBA

• Programming in Microsoft Excel
• VBA Terminology
• Understanding Objects
• Viewing the Excel 2010 Object Model
• Using the Immediate Window
• Working With Object Collections
• Setting Property Values
• Working With Worksheets
• Using the Object Browser
• Programming With the Object Browser

Learning Outcomes: Data Linking Data Consolidation Pivot Consolidation

Topics will be selected from the following list

Data Linking

• Understanding Data Linking
• Linking Between Worksheets
• Linking Between Workbooks
• Updating Links Between Workbooks

Data Consolidation

• Understanding Data Consolidation
• Consolidating With Identical Layouts
• Creating a Linked Consolidation
• Consolidating From Different Layouts
• Consolidating Data Using the SUM Function

Pivot Consolidation

• Understanding Pivot Consolidation
• Using Pivot Wizard

Data Modelling

• Starting a dataset in Excel
• Multiple Tables
• Data Modelling

Get & Transform

• Understanding Get & Transform
• Understanding the Navigator Pane
• Creating a New Query From a File
• Creating a New Query From the Web
• Understanding the Query Editor
• Displaying the Query Editor
• Managing Data Columns

Get & Transform (cont’d)

• Reducing Data Row
• Adding a Data Column
• Transforming Data
• Editing Query Steps
• Merging Queries
• Working With Merged Queries
• Saving and Sharing Queries
• The Advanced Editor

Power Pivot

• Understanding Relational Data
• Common Sense Data Modelling
• Enabling Power Pivot
• Connecting to a Data Source
• Working with The Data Model
• Working with Data Model Fields
• Changing A Power Pivot View

Power Pivot (cont’d)

• Creating A Data Model PivotTable
• Using Related Power Pivot Fields
• Creating A Calculated Field
• Creating A Concatenated Field
• Formatting Data Model Fields
• Using Calculated Fields
• Creating A Timeline
• Adding Slicers

Great functions for Analysis

• Understanding Data Lookup Functions
• Using CHOOSE
• Using VLOOKUP
• Using VLOOKUP For Exact Matches
• Using HLOOKUP
• Using INDEX
• Using SUMIF
• Using SUMIFSUsing SUMPRODUCT

Data Validation

• Validation Criteria
• Input Messages & Error Messages
• Drop-Down Lists
• Formulas
• Customised Validation Criteria
• Creating A Number Range Validation

Data Validation (cont’d)

• Testing A Validation
• Creating an Error Message
• Creating a Drop Down List
• Using Formulas as Validation Criteria
• Circling Invalid Data
• Removing Invalid Circles

Using Sparklines to show trends

• What is a Sparkline?
• Types of Sparklines
• Showing Sparklines only
• Specifying a Date Axis
• Hidden Data and Sparklines
• Sparklines and Targets

Using Conditional formatting

• Using Conditional Formatting with a Dashboard
• Top 10 & Custom Formatting
• Data Bars
• Show data bars outside the data cell
• Colour Scales
• Icon Sets
• Creating Rules Based Icon Set
• Removing unnecessary icons
• Using Symbols in Reporting
• Using the Camera Tool

Pivot Tables

• Structure of Pivot Tables
• Using Compound Fields
• Counting in A PivotTable
• Formatting PivotTable Values
• Working with Grand Total & Subtotals
• Finding the Percentage of Total
• Finding the Difference From
• Grouping in PivotTable Reports
• Creating Running Totals

Pivot Tables

• Creating Calculated Fields
• Providing Custom Names
• Creating Calculated Items
• PivotTable Options
• Sorting in a PivotTable
• Top and Bottom Views
• Date Grouping Options
• Hiding or Showing Data Items
• Conditional Formatting and Sparklines in Pivot Tables
• Pivot Caches and File Size

Slicers in Reports

• What are Slicers?
• Creating Slicers
• Using a Slicer on Multiple Pivot Tables
• Renaming Pivot Tables
• Timeline Slicer

Trending Charts

• Why do we use Trending Charts?
• Appropriate Chart Types for Trending
• Vertical or Y-Axis Scales
• Chart Titles linking to a Cell
• Comparative Trending
• Labelling
• Using a Secondary Axis
• Formatting Key Data Points
• How to display Actuals and Forecasts
• Averages and Data Smoothing

Other Report Charts

• Top and Bottom Charts
• How to show Top or Bottom in Data Labels
• Waterfall Charts

Histograms

• Creating Histograms using Formulas
• Creating Histograms using Pivot Tables
• Creating Histograms using Excel’s Statistical Charts

Charting performance against a target

• Performance against Targets
• Creating Thermometer Chart
• Bullet Graph

Defining Dashboards

• Purpose of a Dashboard
• Working out what is needed
• What are the data sources
• Will the audience need further data to drill-down to?
• How often will/can the data refresh?
• Does it need to be maintained?
• How easy will it be to maintain?

Dashboard Design Principles

• Thirteen common mistakes in dashboard design

Making an Interface

• Using Macros with Dashboards
• Recording a Macro
• Navigation using Macros
• Macros to Change Chart types
• Macros and Pivots