Microsoft Excel Analysing Data

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 Calendar

Pre-requisites

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