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 & 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 & 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

a Location Cost Days Date  
Birmingham 751229 Jan 18
London 449126 Jan 18
Leicester 255130 Nov 17
Birmingham 751227 Nov 17
London 295122 Nov 17
London 449122 Nov 17
London 295122 Nov 17
London 280122 Nov 17
London 295122 Nov 17
London 395127 Oct 17
London 449127 Oct 17
London 218126 Oct 17
Milton Keynes 218126 Oct 17
Manchester 218126 Oct 17
Leeds 218126 Oct 17
London 751226 Oct 17
London 395125 Oct 17
London 395125 Oct 17
Milton Keynes 449123 Oct 17
London 449129 Sep 17
London 295129 Sep 17
London 295129 Sep 17
London 295129 Sep 17
London 295129 Sep 17
Leicester 255129 Sep 17
Coventry 218128 Sep 17
Birmingham 751228 Sep 17
Birmingham 218128 Sep 17
Bristol 218128 Sep 17
London 395127 Sep 17
London 395127 Sep 17
Bristol 751225 Sep 17
Birmingham 525 Sep 17
London 751221 Sep 17
London 218131 Aug 17
Leeds 218131 Aug 17
Sheffield 218131 Aug 17
Milton Keynes 218131 Aug 17
Reading 449129 Aug 17
Manchester 449125 Aug 17
Cardiff 525 Aug 17
London 395125 Aug 17
London 198125 Aug 17
Milton Keynes 198125 Aug 17
Reading 198125 Aug 17
Slough 198125 Aug 17
London 395123 Aug 17
London 395123 Aug 17
Bristol 449122 Aug 17
Milton Keynes 751231 Jul 17
London 198129 Jul 17
Reading 198129 Jul 17
Slough 198129 Jul 17
Milton Keynes 198129 Jul 17
London 395128 Jul 17
London 449127 Jul 17
London 395126 Jul 17
London 395126 Jul 17
London 751224 Jul 17
London 751224 Jul 17
London 198130 Jun 17
Liverpool 198130 Jun 17
Manchester 198130 Jun 17
Leeds 198130 Jun 17
Milton Keynes 198130 Jun 17
London 395128 Jun 17
London 395128 Jun 17
London 449126 Jun 17
Leeds 751226 Jun 17
Manchester 751226 Jun 17
Leicester 255123 Jun 17
London 395123 Jun 17
Liverpool 218122 Jun 17
London 751222 Jun 17
Manchester 218122 Jun 17