Excel Pivot Tables, Charts, Reports and Dashboards (2 days - £550, plus VAT)

This course is aimed at experienced Excel users and covers all key aspects of data analysis and reporting in Microsof Excel. The course covers features which will be of interest to anyone using Excel as a Business intelligence tool and looking to communicate key information in the form of dashboards and reports.

Filtering

Removing Duplicates
Removing Duplicates Based on Several Columns
Using AutoFilter
Combining Filters
Clearing Filters
Filtering by Selection
Filtering by Colour or Icon
Filtering Dates, Text, and Numbers
Sorting Filtered Results
Analysing Filtered Results
Copying Filtered Results
Using the Advanced Filter Command

Sub Totalling

Adding Automatic Subtotals
Excel Subtotal Formulas
Creating Multi-level Subtotals
Choosing the Level of Detail
Creating a Summary with Subtotals Only
Copying Only the Subtotal Rows
Formatting Subtotal Rows
Removing Subtotals

Data Analysis

Using Lookup tables
Creating a Single-Input Data Table
Creating a Two-Input Data Table
Using Scenario Manager
Adding Multiple Scenarios
Creating a Scenario Summary Report
Consolidating Data

Charts and Sparklines

Components of a Chart
Creating a Chart
Customizing a Chart
Changing the Location of a Chart
Saving a Chart Style As a Template
Fitting a Chart into the Size of a Cell with Sparklines
How Excel Maps Data to Sparklines
Creating a Group of Sparklines
Customizing Sparklines

Page Layout and Printing

Excel 2010's Improved Headers and Footers
Adding an Automatic Header
Adding a Custom Header
Inserting a Picture in a Header
Using Different Headers and Footers in the Same Document
Scaling Headers and Footers
Adjusting Worksheet Margins
Working with Page Breaks

Pivot Tables

Anatomy of a Pivot Table
Creating the Basic Pivot Table
Customizing your Pivot Table
Renaming Fields
Formatting Numbers
Changing Summary Calculations
Suppressing Subtotals
Removing the Subtotals for a Single Field
Removing Grand Totals
Hiding and Showing Data Items
Sorting a Pivot Table
Creating Groups

Pivot Table Filtering and Slicers

Using the Row and Column Label Filters
Using the new 2010 Search Box
Clearing a Filter
Using the Check Boxes
Using the Label Filter Flyout
Using the Date Filters
Using Value Filters
Filtering Using Report Filter Fields
Arranging the Filters
Selecting Multiple Items
Adding Slicers
Arranging Slicers
Formatting Slicers
Using the Slicer Control

Pivot Charts

Creating a Pivot Chart
Specifying which Rows to Plot
Moving a Pivot Chart
Hiding Field Buttons
Using Slicers with Pivot Charts

PowerPivot

Benefits of PowerPivot
Building a PowerPivot Report
Importing Data
Defining Relationships
Adding Calculated Columns
Creating a Pivot Table
Using Slicers with PowerPivot Pivot Tables
Adding DAX Calculations
Creating New Measures
Using the RELATED() function
Using the CALCULATE() function

Creating Dashboards

How Dashboards Differ from Reports
Placement of Dashboard elements
Making Pivot Tables / Charts More Compact
Using Linked Pictures to Save Space
Using Sparklines with Hidden Data

Creating Interactive Dashboards

Adding and Configuring Form Controls
Using the Button Control
Using the Check Box Control
Using Option Button Controls
Using the Combo Box Control
Using Dynamic Labels
Using Linked Pictures

Back to top