Code: 
MSEXCEL3
Duration: 
1-5 Day(s)
Price: 
£1694-751

Overview

The skills and knowledge acquired in Microsoft Excel Advanced are sufficient to be able to use and operate the software at an efficient level.

Audience

Microsoft Excel Advanced is designed for users who are keen to extend their understanding and knowledge of the software. Microsoft Excel Advanced assumes the delegate has attended the Intermediate course or has equivalent knowledge.

Skills Gained

At the completion of Microsoft Excel Advanced you should be able to:

  • use names and labels
  • use a wide range of paste special options
  • use a variety of Financial functions
  • create, use and modify data tables
  • use advanced filters to analyse data in a list
  • summarise data using subtotals and relative range naming
  • use data forms to manage data in a list
  • use the statistical functions in Excel
  • use goal seeking to determine the values required to reach a desired result
  • use Solver to resolve complex optimisation problems
  • create and work with scenarios and the Scenario Manager
  • import data into and export data from workbooks
  • create, modify and work with PivotTables
  • create recorded macros in Excel
  • use the macro recorder to create a variety of macros
  • confidently open workbooks that contain macros
  • create and use a range of controls in a worksheet
  • publish workbooks and worksheets for the web

Course Specifics

Course Outline

The course focuses on practical work within the product giving delegates ample opportunity to use their PC skills through a series of graded exercisesTopics covered include:

Labels and Names
  • Labels And Names Explained
  • Accepting Labels In Formulas
  • Using Text Labels
  • Creating Labels
  • Creating Names Using The Name Box
  • Using Names To Select Cells
  • Using Names In Formulas
  • Creating Names Between Workbooks
  • Creating Names For Constants
  • Creating A List Of Names
  • Changing The Range For Names
  • Deleting Names
Paste Special
  • The Paste Special Options
  • Copy Formulas
  • Copy Formats
  • Copy Validation Settings
  • Copy Column Widths
  • Copy Values
  • Copy Comments
  • Add Values
  • Multiply Values
  • Transpose Cells
Financial Functions
  • Financial Functions Listing
  • Regular Payments With PMT
  • Calculating Future Values With FV
  • Net Present Value With NPV
  • Present Value With PV
  • The Interest Rate With RATE
Data Tables
  • Using A Simple What-If Model
  • Creating A One-Variable Table
  • Using One-Variable Data Tables
  • Creating A Two-Variable Data Table
Advanced Filters
  • Advanced Filter Concepts
  • Using An Advanced Filter
  • Extracting Records With Advanced Filter
  • Using Formulas In Criteria
  • Using Database Functions
  • The Database Functions
  • Using The DSUM Function
  • Using The DMIN Function
  • Using The DMAX Function
  • Using The DCOUNT Function
Summarising Data
  • Creating Subtotals
  • Using A Subtotalled Worksheet
  • Creating Nested Subtotals
  • Copying Subtotals
  • Using Subtotals With AutoFilter
  • Using The Conditional Sum Wizard
  • Creating Relative Names For Subtotals
  • Using Relative Names For Subtotals
Data Forms
  • Displaying A Data Form
  • Viewing Records By Criteria
  • Editing Records
  • Adding A New Record
  • Deleting Records
Statistical Functions
  • Statistical Functions Listing
  • Status Bar Statistics
  • Basic Statistical Functions
  • Counting Cells
  • Using MODE and MEDIAN
  • Calculating Standard Deviation
  • Conditional Counting
Goal Seeking
  • Goal Seek Components
  • Using Goal Seek
Solver
  • Installing Solver
  • Solver Theory
  • Using Solver and Solver Reports
  • Restricting Answers Using Solver Options
Scenarios
  • Creating A Default Scenario
  • Creating Scenarios
  • Using Names in Scenarios
  • Displaying Scenarios
  • Creating A Scenario Summary Report
  • Merging Scenarios
Importing and Exporting
  • Importing From Another Spreadsheet Program
  • Text File Formats
  • Importing Tab-Delimited Text
  • Exporting To Microsoft Word
  • Linking Data Into A Word Processing Document
  • Exporting Data As Text
  • Importing Comma-Delimited Text
  • Importing Space-Delimited Text
  • Importing An Object Into A Worksheet
  • Formatting An Imported Object
PivotTables
  • PivotTable Theory
  • Creating A Simple PivotTable
  • Adding Row Fields To A PivotTable
  • Using The Page Field In A PivotTable
  • Filtering Row And Column Values
  • Formatting A PivotTable
  • Counting With PivotTables
  • PivotTable Summary and Display Options
  • Show Data As Percentages In PivotTables
  • Calculated Fields In PivotTables
  • Calculated Items In PivotTables
  • Creating A PivotChart
  • Modifying A PivotChart Via The PivotTable
  • Refreshing A PivotTable
Recorded Macros
  • Setting Macro Security Levels
  • Recording A Simple Macro
  • Running A Recorded Macro
  • Relative Cell References
  • Running A Macro With Relative References
  • Viewing A Macro Module
  • Modifying A Recorded Macro
  • Assigning A Macro To A Toolbar Button
  • Running A Macro From A Custom Button
  • Removing A Custom Button From The Toolbar
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
Macro Virus Control
  • Opening Workbooks With Macros
  • Setting Macro Security Levels
  • Trusting Publishers
  • Creating A Digital Signature
  • Removing A Trusted Publisher
Controls
  • Control Types
  • How Controls Work
  • Preparing A Worksheet For Controls
  • Adding A Combo Box Control
  • Changing Control Properties
  • Using The Cell Link To Display The Selection
  • Adding A List Box Control
  • Adding A Scroll Bar Control
  • Adding A Spinner Control
  • Adding Option Button Controls
  • Adding A Group Box Control
  • Adding A Check Box Control
  • Protecting A Workbook With Controls
Excel On The Web
  • Previewing Workbooks As Web Pages
  • Publishing A Static Worksheet
  • Adding To An Existing Web Page
  • Publishing An Interactive Web Page

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

Upcoming Dates

Course Location Days Cost Date
London
1 383 £383 2018-02-19
Birmingham
2 751 £751 2018-01-29
London
1 449 £449 2018-01-26
Milton Keynes
1 449 £449 2018-01-12
Manchester
5 1694 £1694 2018-01-08
Birmingham
1 449 £449 2018-01-05
Leeds
2 751 £751 2018-01-04
London
2 751 £751 2017-12-20
London
1 449 £449 2017-12-19
Birmingham
2 751 £751 2017-12-18
London
1 383 £383 2017-12-18
Milton Keynes
2 751 £751 2017-12-14
London
2 751 £751 2017-12-14
Leeds
1 449 £449 2017-12-14
Leicester
1 255 £255 2017-12-13
London
1 295 £295 2017-12-12
Edinburgh
1 449 £449 2017-12-11
Bristol
2 751 £751 2017-12-11
Leicester
1 255 £255 2017-12-05
Manchester
2 751 £751 2017-12-04
Leicester
1 255 £255 2017-11-30
Birmingham
2 751 £751 2017-11-27
London
1 295 £295 2017-11-22
London
1 449 £449 2017-11-22
London
1 295 £295 2017-11-21
Leeds
2 751 £751 2017-11-20
Leicester
1 255 £255 2017-11-20
Bristol
1 449 £449 2017-11-20
Leeds
2 751 £751 2017-11-16
Edinburgh
2 751 £751 2017-11-13
London
2 751 £751 2017-11-13
Glasgow
1 449 £449 2017-11-13
London
1 449 £449 2017-11-10
London
5 1694 £1694 2017-11-06
London
2 751 £751 2017-11-06
Manchester
1 449 £449 2017-11-06
Manchester
2 751 £751 2017-11-02
London
1 395 £395 2017-10-27
London
1 449 £449 2017-10-27
Manchester
1 218 £218 2017-10-26
Milton Keynes
1 218 £218 2017-10-26
London
2 751 £751 2017-10-26
Leeds
1 218 £218 2017-10-26
London
1 218 £218 2017-10-26
London
1 395 £395 2017-10-25
London
1 395 £395 2017-10-25
Milton Keynes
1 449 £449 2017-10-23
London
1 449 £449 2017-10-20
London
2 751 £751 2017-10-16
London
1 449 £449 2017-10-13
London
1 295 £295 2017-10-13
London
1 395 £395 2017-10-12
London
2 751 £751 2017-10-09
Leicester
1 255 £255 2017-10-06
Birmingham
1 449 £449 2017-10-06
Leicester
1 255 £255 2017-10-06
London
1 449 £449 2017-09-29
London
1 295 £295 2017-09-29
Leicester
1 255 £255 2017-09-29
Bristol
1 218 £218 2017-09-28
Birmingham
2 751 £751 2017-09-28
Birmingham
1 218 £218 2017-09-28
Coventry
1 218 £218 2017-09-28
London
1 395 £395 2017-09-27
London
1 395 £395 2017-09-27
Bristol
2 751 £751 2017-09-25
London
2 751 £751 2017-09-21
Birmingham
2 751 £751 2017-09-20
Edinburgh
1 449 £449 2017-09-19
Manchester
5 1694 £1694 2017-09-18
Glasgow
2 751 £751 2017-09-18
London
1 449 £449 2017-09-15
Leeds
2 751 £751 2017-09-14
Nottingham
1 218 £218 2017-09-14
Reading
1 218 £218 2017-09-14