Duration: 1 day(s)
Course Code: XL13L3
Early Bird Courses
London 17th Aug 2017 £191.75 Book
London 29th Sep 2017 £191.75 Book
London 13th Oct 2017 £191.75 Book
London 21st Nov 2017 £191.75 Book
London 12th Dec 2017 £191.75 Book
Click to view further dates
Course Dates Within Next 4 Weeks
London 19th Jul 2017 Book
Normal price: £295.00+VAT

Microsoft Excel 2013 Level 3

Course Aims

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 Prerequisites

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.

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
     

Programme Focus

Using Logical, Lookup & Round Functions
o Using the VLOOKUP/HLOOKUP functions
o Using IF, AND & OR function
o Using the ISERROR function

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

Working with Outlines
o Applying, expanding & collapsing an outline
o Modifying & clearing outline settings
o Using Auto Outline

Consolidating Worksheets
o Consolidating by category & position

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

Creating Subtotals in a List
o Creating subtotals for groups of data
o Multiple subtotals
o Removing subtotals

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
     

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

Working with Comments
o Adding a comment to a cell
o Viewing comments
o The Review tab
o Printing comments

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


Working with Advanced Filters
o Creating a criteria range
o Using the Advanced Filter
o Dfunctions

Appendix– Custom Views (if time)
o Creating, displaying and deleting
 

Click to expand...

Next Learning Step

The next course in this series is:

  • Excel 2013 Level 4