Duration: 1 day(s)
Course Code: XL16L3
Early Bird Courses
London 11th Jun 2019 £198.25 Book
London 12th Jul 2019 £198.25 Book
London 13th Aug 2019 £198.25 Book
London 25th Sep 2019 £198.25 Book
London 17th Oct 2019 £198.25 Book
London 21st Nov 2019 £198.25 Book
London 11th Dec 2019 £198.25 Book
Click to view further dates
For dates or any further enquiries please call 01344 830100
Price: P.O.A

Microsoft Excel 2016 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 you 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

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

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

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

Click to expand...