Conditional Formatting

• Understanding Conditional Formatting
• Highligting Top or Bottom Values
• Working with Data Bars, Colour scales & Icon sets
• Creating a Schedule using Conditional Formatting

Tables

• Creating a Table
• Filtering a Table
• Creating Charts based on Tables
• Dynamic charts with Tables
• Using structured references with tables
• Calculated columns in Tables

Manage Formats with Styles

• Using Styles
• Creating a Style
• Copying Styles to another Workbook

Formulas to Manage Data

• CHOOSE() function
• NA() function
• SUMIFS()
• LARGE() and SMALL()

Quick Charting Techniques

• Simple chart formatting
• Row/Column control
• Resizing charts
• Adding more data
• Creating combination charts
• User defined charts and templates

Creating Simple Interactive Charts

• Overview of worksheet controls
• Using a checkbox to include/exclude a data series
• Using a combo box to select data to be displayed
• Creating dynamic linked charts using option buttons
• Using a combo box to display different charts
• Using data validation for data selection
• Adding a scrollbar to control the display of a chart

Reporting Progress in Charts

• Actual v Target
• Sparklines
• Creating a dynamic Conditional Chart
• Creating a Waterfall chart to show the rise and fall of data

Creating Dynamic Labels and Legends

• Adding a dynamic message to a chart
• Managing Titles and Labels with formulas
• Managing Legends with formulas
• Using Symbols for Legends
• Using Conditional Formatting.

PivotTable Fundamentals

• What is a PivotTable?
• Why should I use a Pivot Table?
• What are the advantages?
• PivotTable Terminology
• Preparing your data for use in a PivotTable
• Creating a PivotTable
• Selecting the Data Source
• PivotTable Fields List
• Filterering data with Page Fields
• Adding Fields
• Removing Fields
• Understanding the Field icons

Presenting Data in PivotTables

• PivotTable Ribbon
• Hiding and Unhiding Items
• Sorting Data in a PivotTable
• Using AutoSort
• Advanced AutoSort
• Refreshing a PivotTable Report
• Inserting Data into the Data Source
• Formatting Numerical Data
• Selecting Parts of a PivotTable
• PivotTable Formats
• PivotTable Options
• Change the Summary Function
• Adding Multiple Data Fields
• Changing Calculations
• Hiding and Showing Row/Column Details
• Displaying Data Details

Grouping Data

• Grouping Data
• Group by Dates
• Group by Number
• Ungrouping Data

Calculations in PivotTables

• Changing custom calculations
• Creating Calculated Fields
• Calculated Items
• GetPivotData() to extract information
• Using Other Sources of Data
• Selecting data from another Excel workbook
• Connecting to an Database
• Using a Saved Query as the data source
• Using a csv text file as the source of a PivotTable

Using PivotCharts

• PivotChart terms
• Modifying the Chart
• Caution - Loss of Formatting in PivotCharts

Publishing PivotTables to the Web

• Saving a PivotTable as a web page
• Creating Interactive PivotCharts - Web
• Adding Fields to a PivotChart - Browser

Re-using PivotTables

• Requerying data
• Using Saved queries
• Automating PivotTable Updates
• Saving a PivotTable Template

Creating a Data Model

A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook. 

• Create a data model with multiple tables
• Create a relationship between the tables
• Use the data model to create a PivotTable and PivotChart
• Adding data to the data model