Excel Pivot Tables and Dashboards (1 day)

    Scheduled courses in Peterborough or £595, plus VAT, on-site.

    This one day course is designed for experienced Excel users who will be using pivot tables extensively in their work. Delegates will learn how to use this powerful tool to summarise and analyse data and then enhance it with formatting, custom calculations and filtering to produce unique and useful reports.

    Introducing Pivot Tables

    What Is a Pivot Table, Understanding the Benefits of Using Pivot Tables, Preparing to Create a Pivot Table, Exploring the Excel Table Features

    Creating a Pivot Table

    Exploring an Insurance Policy Example, Changing the Pivot Table Layout, Charting the Data in a Pivot Table

    Modifying a Pivot Table

    Changing a Pivot Table, Adding a Report Filter, Updating the Pivot Table, Changing the Summary Function, Applying a PivotTable Style, Deleting a Pivot Table

    Summarizing Data

    Using the Summary Functions, Showing or Hiding Grand Totals, Creating Subtotals, Grouping Numbers and Dates

    Formatting a Pivot Table

    Controlling the Report Layout, Adding Blank Rows in the Layout, Using a Pivot Table Style, Adding Row and Column Shading, Formatting the Row and Column Headers, Removing a Pivot Table Style, Creating a Pivot Table Style, Applying a Custom Pivot Table Style, Modifying a Custom PivotTable Style, Duplicating a Pivot Table Style, Deleting a Custom Pivot Table Style, Using Themes, Applying a Theme, Saving the File

    Sorting and Filtering in a Pivot Table

    Adding Report Filters, Arranging Report Filters, Clearing All Filters, Moving Labels, Sorting Labels, Sorting Values, Sorting Automatically When the Pivot Table Changes, Sorting Labels in a Custom Order, Filtering Row and Column Labels, Viewing Filter and Sort Information, Removing Filters, Filtering Values, Filtering for a Date Range, Applying a Manual Filter, Including New Items in a Manual Filter, Filtering by Selection, Showing Top and Bottom Items

    Using Slicers and Timelines

    Understanding slicers, Creating and formatting standard slicers, Using Timeline slicers, Linking Slicers and Timelines to other objects

    Creating a Pivot Table from External Data

    Creating a Pivot Table from a Text File, Creating a Pivot Table from an Access Query, Creating a Pivot Table from an OLAP Cube

    Updating a Pivot Table

    Updating an Excel Table, Changing an Excel Data Source, Refreshing All Pivot Tables in a Workbook, Retaining Deleted Items, Changing an Access Data Source, Refreshing the Pivot Table Regularly, Saving Source Data with the File

    Creating Calculations in a Pivot Table

    Creating Custom Calculations, Creating Formulas

    Enhancing Pivot Table Formatting

    Applying Conditional Formatting, Setting Format Options

    Pivot Charts and Graphics

    Creating a Default Pivot Chart, Creating a Line Pivot Chart, Formatting and Customizing Charts, Using Pivot Charts to Show Trends

    Creating Dashboards

    Getting Started with Excel Dashboards, Defining Dashboards and Reports, Determining User Requirements, Key Visualization and Design Principles