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.

Make Enquiry

Write A Review Of Microsoft Excel 2013 Level 3

Face-to-face
£336.00
Make Enquiry Add To Wishlist
All prices include VAT where applicable