Course Overview

The Excel Advanced course is aimed at those who want to effectively analyse and populate data and produce professional-looking workbooks.

Detailed Course Content

Split Text to Multiple Columns

  • Fixed width data type
  • Delimited data type

Data Cleansing

  • Text case: LOWER, UPPER, PROPER
  • Extract text: LEFT, MID, RIGHT
  • Remove characters: CLEAN, TRIM
  • Replace text: REPLACE, SUBSTITUTE
  • Format: TEXT, VALUE
  • Join text: CONCAT, TEXTJOIN
  • TRANSPOSE

Use What-if Analysis Tools

  • Goal seek
  • Data tables
  • Scenarios
  • Scenario summary reports

Record, Run & Edit Macros

  • Record a macro
  • Run a macro
  • Edit a macro

Data Consolidation

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

Filter Data

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

Advanced Filters

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

Database Functions

  • Use DSUM function
  • Use DAVERAGE function
  • Use DCOUNT & DCOUNTA functions
  • Use DMIN function
  • Use DMAX function

Logical Functions

  • Use IF functions to check data
  • Use IF functions to populate data
  • Use AND & OR functions to to perform multiple tests

Lookup Functions

  • VLOOKUP for vertical lookups
  • HLOOKUP for horizontal lookups
  • XLOOKUP for flexible lookups
  • LOOKUP for approximate matches

Formula Auditing

  • Trace precedents/dependents
  • Show formulas
  • Error checking

Create a PivotTable

  • Insert a PivotTable
  • Select data to add to PivotTable
  • Change PivotTable layout

PivotTable Settings

  • Remove grand totals
  • Print settings

Sort & Group Data in PivotTables

  • Sort data alphanumerically
  • Sort data by custom list
  • Reorder items manually
  • Subtotal data
  • Group data

Use Calculations in PivotTables

  • Create a calculated item
  • Create a calculated field

Filter Data in PivotTables

  • Filter data from PivotTable
  • Filter data from Fields List
  • Insert Slicers and Timelines

Create PivotCharts

  • Create a PivotChart
  • Modify a PivotChart

Sparklines

  • Display mini line chart
  • Display mini column chart
  • Display win/loss chart

Insert Pictures in Excel

  • Insert pictures in headers/footers
  • Insert pictures in worksheets
  • Insert pictures in cells

Checkboxes

  • Insert a checkbox
  • Toggle a checkbox
  • Use checkbox value in COUNTIF function
  • Remove checkbox retaining value

Import Up-to-date Web Data

  • Import stock data
  • Import currency data
  • Import geography data

Printable course information

View Calendar

Pre-requisites

  • Good knowledge of experience in using Microsoft Excel
  • Knowledge of basic functions and linking data
  • Alternatively, completion of Excel Intermediate training course