Excel Level 3

Excel Level 3 Course Overview

Excel Level 3 Course Prerequisites

• • Before attending this course, we recommend that you have attended our Microsoft Excel Level 1 and 2 courses or have equivalent knowledge.
    

Excel Level 3 Course Objectives

• • Create a PivotTable to manipulate and extract data, then create a chart to display the results.
  • Create a spreadsheet that will display multiple groups of records that can be updated from a single source.
  • Create a formula that will only add up or count records that match specific criteria.
  • Record a Macro that will automate a repetitive task and then assign it to a new button on a new toolbar.

Included with this course

• • Lunch and refreshments when training is delivered at our training venues
  • Comprehensive course materials and exercises
  • Telephone and Email Support

Excel Level 3 Course Content

• Using Absolute and Mixed Referencing

  Difference between Relative and Absolute References
  Absolute Reference and Dollar Signs
  Why use Absolute References
  Applying an Absolute Reference in a formula
  Mixed References

• Tables

  Creating Tables
  Advantages of using tables
  Adding data to tables
  Table Options
  Slicers

• Naming Cells (Ranges)

  Giving text to cells and ranges of cells
  Create Names from Selection
  Create Names using the Name Box
  Names in formulas
  Name Manager

• Complex Functions

  Using Complex Functions
  IF Function
  Nested Ifs
  SUMIF function
  COUNTIF function
  COUNTBLANK function
  Wildcards in functions

• Lookup Functions

  Understanding Lookup Functions
  VLOOKUP
  Approximate and Exact Matches
  Fixing Common Lookup problems
  HLOOKUP

• Data Validation

  Specifying Validation Criteria
  Controlling entry into a cell
  Types of Validation Criteria
  Creating a Drop-down List

• Security

  Protecting a worksheet and a workbook
  

• Pivot Tables

  Creating a pivot table
  Fields to create row headings
  Fields to create column headings
  Data in the summary area
  Dynamically building a pivot table
  Rearranging fields in a pivot table
  Number formatting of the data area
  Moving pivot tables from one sheet to another or around the same sheet
  Deleting a pivot table

• Summarising Pivot Table Data

  Managing subtotals and grand totals
  Changing the data field calculation (Sum, Count, Average etc.)
  Sort and Filter
  Drilling down to the raw data
  Building and using Report Pages

• Grouping Pivot Data

  Grouping Date Fields
  Including Years when Grouping by Months
  Grouping Date Fields by Week
  Multi Grouping

• Pivot Charts

  Building Pivot Charts
  Editing Pivot Chart Layouts
  Editing Pivot Chart Styles
  Using the built in chart filter buttons for presentation

• Macros

  Recording a Macro
  Absolute References and Relative References
  Run a Macro
  Delete a Macro
  Assign a Macro to a Button
  Viewing the Code behind a Macro
  Macro Security