Excel 2013 Level 3

Course duration: 1 day

Course code: XL13L3

Course Aims:

This course is aimed at people who want expand their knowledge into some of the more advanced calculation and analytical capability of Microsoft Excel.

Course Pre-requisites:

Students who wish to attend this course must have a good knowledge of Excel and be competent at working with basic formulas and functions, multiple sheets and multiple files.

Course Objectives:

On completion of this course delegates will be able to:

  • Use logical, lookup and error functions
  • Use auditing tools
  • Group and outline data
  • Consolidate data on a worksheet by category and position
  • Set restrictions on data entry using data validation
  • Apply custom formats and use conditional formatting
  • Insert and delete comments into a worksheet
  • Protect an Excel workbook, cells and worksheets
  • Use the Subtotal feature
  • Create and edit Pivot Tables and Pivot Charts
  • Use criteria ranges to analyse data using the Advanced Filter and Dbase functions

Course Content:

Introduction and Objectives

Using Logical, Lookup & Round Functions

  • Using the VLOOKUP/HLOOKUP functions
  • Using IF, AND & OR function
  • Using the ISERROR function

Using Auditing Tools

  • Displaying precedents and dependants
  • Removing arrows
  • Error checking
  • Evaluating a formula
  • Using the ‘watch’ window

Working with Outlines

  • Applying, expanding & collapsing an outline
  • Modifying & clearing outline settings
  • Using Auto Outline

Using Data Validation

  • Validating data
  • Creating a custom error message
  • Removing a data validation
  • Using the Go To Special dialog box

Protecting Excel Data

  • Protecting cells on a worksheet
  • Unlocking & locking cells on a worksheet
  • Making exceptions to cell protection
  • Protecting worksheets from being inserted, moved, deleted or hidden/unhidden
  • Adding protection to control how a workbook opens (full protection; read-only)
  • Setting manual & automatic calculation

Creating/Revising Pivot Tables and Pivot Charts

  • Creating a Pivot Table
  • Adding, moving, removing Pivot Table fields
  • Formatting and structuring a Pivot Table
  • Filtering Pivot Table items
  • Adding a Pivot Table slicer
  • Adding a Timeline for filtering date ranges
  • Changing the summary function
  • Summarising data as percentages
  • Refreshing a Pivot Table
  • Changing the data source for a Pivot Table
  • Moving & deleting a Pivot Table
  • Using ‘Recommended Pivot Tables’
  • Using the ‘Quick Analysis button” to create a Pivot Table
  • Creating a Pivot Chart
  • Adding, moving and removing Pivot Chart fields
  • Formatting and structuring a Pivot Chart
  • Filtering Pivot Chart categories and data series
  • Adding a Pivot Chart Slicer
  • Refreshing a Pivot Chart
  • Changing the data for a Pivot Chart
  • Moving and deleting a Pivot Chart
  • Adding a Timeline for filtering date ranges
  • Using the ‘Quick Analysis button” to create a Pivot Chart

Creating Subtotals in a List

  • Creating subtotals for groups of data
  • Multiple subtotals
  • Removing subtotals

Using Conditional & Custom Formats

  • Applying a comparative conditional formatting rule
  • Applying a top/bottom conditional format
  • Managing conditional formats (editing and clearing)
  • Applying built-in data bars, colour scales and icons sets to numeric data
  • Using a formula to conditionally format data
  • Using the ‘Quick Analysis button” to add conditional formatting
  • Creating special codes to format numbers and dates

Working with Advanced Filters

  • Creating a criteria range
  • Using the Advanced Filter
  • Dfunctions

Using Range Names

  • Jumping to a named range
  • Assigning names to ranges
  • Using range names in formulas
  • Editing and deleting named ranges
  • Creating range names from headings
  • Applying range names to existing formulas
  • Creating named ranges across sheets

Appendix– Custom Views (if time)

  • Creating, displaying and deleting

Action planning

Review of Programme

Click Here to view Course Dates

For more information on how to book, please click here.

Many thanks,

WWP & the Euroclear Team