Microsoft Excel Analysing Data

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 Course Calendar

Pre-requisites

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