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