Microsoft Excel 2013 Level 3

Excel 2013 Level 3 

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, color 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


Related Courses

Access 2013 Foundation

Project 2013 Level 1

Finance for Non-Financial Managers

Presenting with Impact

Excel 2013 Level 4

Dates available on arrangement

WWP Training Limited can also create this course for you on arrangement either at your workplace or at one of their locations.

Book Now Make Enquiry

Write A Review Of Microsoft Excel 2013 Level 3

Book Now Make Enquiry Add To Wishlist
All prices include VAT where applicable