Microsoft Excel Training: Level 3

This advanced Microsoft Excel training course provides attendees with the skills to create spreadsheets using the advanced features of Microsoft Excel.

    Interested in attending? Have a suggestion about running this event near you?
    Register your interest now

      Description

      This course aims to provide skills and knowledge which will allow the attendee to workbooks using advanced features of Microsoft Excel.

      Create lookup functions, set Excel working options, enhance charts, protect worksheet data, perform advanced data operations using summarising, PivotTables, data consolidations, goal seeking, and Solver, and create and use macros.

      Prerequisites

      This couse assumes the learner has a good knowledge of working with Excel. The learner should be able to create and edit workbooks, enter formulas, copy, paste, and format data. The learner must also have a general understanding of personal computers and the Windows operating system environment and be able to use File Explorer to locate and copy files.

      Key Topics

      • Lookup Functions
      • Setting Excel Options
      • Chart Object Formatting
      • Labels And Names
      • Protecting Data
      • Summarising And Subtotalling
      • Data Linking
      • Data Consolidation
      • Pivot Tables
      • PivotTable Techniques
      • PivotCharts
      • Goal Seeking
      • Grouping And Outlining
      • Solver
      • Recorded Macros
      • Recorder Workshop

      Learning Objectives

      At the completion of this course you should be able to:

      • modify Excel options
      • protect data in worksheets and workbooks
      • import data into Excel and export data from Excel
      • use data linking to create more efficient workbooks
      • group cells and use outlines to manipulate the worksheet
      • create summaries in your spreadsheets using subtotal
      • use the Data Consolidation feature to combine data from several workbooks onto one
      • create, use and modify data tables
      • create and work with scenarios and the Scenario Manager
      • understand and create simple PivotTables
      • construct and operate PivotTables using some of the more advanced techniques
      • create and edit a PivotChart
      • use advanced filters to analyse data in a list
      • use a variety of data validation techniques
      • create and use a range of controls in a worksheet
      • share workbooks with other users
      • create recorded macros in Excel

      Topic Outline

      Lookup Functions

      • Understanding Data Lookup Functions
      • Using CHOOSE
      • Using VLOOKUP
      • Using VLOOKUP For Exact Matches
      • Using HLOOKUP
      • Using INDEX
      • Using MATCH
      • Understanding Reference Functions
      • Using ROW And ROWS
      • Using COLUMN And COLUMNS
      • Using ADDRESS
      • Using INDIRECT
      • Using OFFSET

      Setting Excel Options

      • Understanding Excel Options
      • Personalising Excel
      • Setting The Default Font
      • Setting Formula Options
      • Understanding Save Options
      • Setting Save Options
      • Setting The Default File Location
      • Setting Advanced Options

      Chart Object Formatting

      • Understanding Chart Object Formatting
      • Selecting Chart Elements
      • Using Shape Styles To Format Objects
      • Changing Column Colour
      • Changing Pie Slice Colour
      • Changing Bar Colours
      • Changing Chart Line Colours
      • Using Shape Effects
      • Filling The Chart Area And The Plot Area
      • Filling The Background
      • The Format Dialog Box
      • Using The Format Dialog Box
      • Using Themes

      Labels And Names

      • Understanding Labels And Names
      • Creating Names Using Text Labels
      • Using Names In New Formulas
      • Applying Names To Existing Formulas
      • Creating Names Using The Name Box
      • Using Names To Select Ranges
      • Pasting Names Into Formulas
      • Creating Names For Constants
      • Creating Names From A Selection
      • Scoping Names To The Worksheet
      • Using The Name Manager
      • Documenting Range Names

      Protecting Data

      • Understanding Data Protection
      • Providing Total Access To Cells
      • Protecting A Worksheet
      • Working With A Protected Worksheet
      • Disabling Worksheet Protection
      • Providing Restricted Access To Cells
      • Password Protecting A Workbook
      • Opening A Password Protected Workbook
      • Removing A Password From A Workbook

      Summarising And Subtotalling

      • Creating Subtotals
      • Using A Subtotalled Worksheet
      • Creating Nested Subtotals
      • Copying Subtotals
      • Using Subtotals With AutoFilter
      • Creating Relative Names For Subtotals
      • Using Relative Names For Subtotals

      Data Linking

      • Understanding Data Linking
      • Linking Between Worksheets
      • Linking Between Workbooks
      • Updating Links Between Workbooks

      Data Consolidation

      • Understanding Data Consolidation
      • Consolidating With Identical Layouts
      • Creating An Outlined Consolidation
      • Consolidating With Different Layouts

      Pivot Tables

      • Understanding Pivot Tables
      • Creating A PivotTable Shell
      • Dropping Fields Into A PivotTable
      • Filtering A PivotTable Report
      • Clearing A Report Filter
      • Switching PivotTable Labels
      • Formatting A PivotTable Report
      • Understanding Slicers
      • Creating Slicers

      PivotTable Techniques

      • Using Compound Fields
      • Counting In A PivotTable Report
      • Formatting PivotTable Report Values
      • Working With PivotTable Grand otals
      • Working With PivotTable Subtotals
      • Finding The Percentage Of Total
      • Finding The Difference From
      • Grouping In PivotTable Reports
      • Creating Running Totals
      • Creating Calculated Fields
      • Providing Custom Names
      • Creating Calculated Items
      • PivotTable Options
      • Sorting In A PivotTable

      PivotCharts

      • Creating A PivotChart Shell
      • Dragging Fields For The PivotChart
      • Changing The PivotChart Type
      • Using The PivotChart Filter Field Buttons
      • Moving PivotCharts To Chart Sheets

      Goal Seeking

      • Understanding Goal Seek Components
      • Using Goal Seek

      Grouping And Outlining

      • Understanding Grouping And Outlining
      • Creating An Automatic Outline
      • Working With An Outline
      • Creating A Manual Group
      • Grouping By Columns

      Solver

      • Understanding How Solver Works
      • Installing The Solver Add-In
      • Setting Solver Parameters
      • Adding Solver Constraints
      • Performing The Solver Operation
      • Running Solver Reports

      Recorded Macros

      • Understanding Excel Macros
      • Setting Macro Security
      • Saving A Document As Macro Enabled
      • Recording A Simple Macro
      • Running A Recorded Macro
      • Relative Cell References
      • Running A Macro With Relative References
      • Viewing A Macro
      • Editing A Macro
      • Assigning A Macro To The Toolbar
      • Running A Macro From The Toolbar
      • Assigning A Macro To The Ribbon
      • Assigning A Keyboard Shortcut To A Macro
      • Deleting A Macro
      • Copying A Macro

      Recorder Workshop

      • Preparing Data For An Application
      • Recording A Summation Macro
      • Recording Consolidations
      • Recording Divisional Macros
      • Testing Macros
      • Creating Objects To Run Macros
      • Assigning A Macro To An Object

      Concluding Remarks

      Target Audience

      This course is designed for users who need to use some of the more advanced features of Microsoft Excel.