Course Overview

The Excel Advanced course is aimed at those who want to effectively analyse their data and create professional reports in Excel.

Detailed Course Content

Sorting

  • Sort alphabetically
  • Sort numerically
  • Sort by date
  • Sort multiple columns
  • Sort by colour
  • Sort by custom list

Filtering

  • Filter items
  • Use the filter search tool
  • Text filters
  • Number filters
  • Date filters
  • Custom filters

Advanced Filtering

  • Set up filter criteria
  • Apply a filter in-place
  • Clear a filter
  • Extract data
  • Database Functions

    • Use DSUM function
    • Use DAVERAGE function
    • Use DCOUNT function
    • Use DMIN function
    • Use DMAX function

    Goal Seek

    • Use Goal Seek to set desired outcome
    • Clear Goal Seek results

    Data Tables

    • Use a one-way data table
    • Use a two-way data table

    Scenarios

    • Set up a scenario
    • Display a scenario
    • Produce a scenario summary report

    Macros

    • Record a macro for a scenario summary
    • Run the macro

    Consolidation

    • Consolidate identical source layout
    • Consolidate with links to source data
    • Consolidate non-identical source data

Subtotals

  • Subtotal a single column
  • Create a nested subtotal
  • Remove subtotals

Group data

  • Manually group rows and columns
  • Automatically group data
  • Clear grouping

Create a Pie Chart

  • Insert a 2D pie chart
  • Select / Change pie chart data
  • Add percentages to pie chart
  • Change pie slice colour

Create a Column Chart

  • Insert a quick column chart
  • Add a trendline

Customise Charts

  • Change chart type
  • Select chart style and colours
  • Add chart elements
  • Move a chart
  • Resize a chart

Create a Pivot Table

  • Insert a pivot table
  • Select data to add to pivot table
  • Change pivot table layout
  • Pivot table options
  • Update pivot table source data

Analyse data in Pivot Tables

  • Filter data
  • Subtotal data
  • Group data

Calculations in Pivot Tables

  • Create a calculated item
  • Create a calculated field

Printable course information

View Availability

Pre-requisites

  • Good experience in using Excel
  • Knowledge of basic functions and linking data
  • Alternatively, completion of Excel Effective Workbooks training course