DT203 - Excel 2010 level 3

This course aims to provide skills and knowledge which will allow the attendee to 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.

• use a range of lookup and reference functions
• modify Excel options
• customise the formatting of charts in Excel
• create and use labels and names in a workbook
• protect data in worksheets and workbooks
• create summaries in your spreadsheets using subtotals
• use data linking to create more efficient workbooks
• use the Data Consolidation feature to combine data from several workbooks into one
• understand and create simple PivotTable reports
• construct and operate PivotTables using some of the more advanced techniques
• create and edit a PivotChart
• use goal seeking to determine the values required to reach a desired result
• group cells and use outlines to manipulate the worksheet
• use Solver to solve more complex and intricate problems
• create recorded macros in Excel
• use the macro recorder to create a variety of macros

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

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

This course assumes a good understanding of spreadsheets using Microsoft Excel 2010 and how to create, print and chart workbooks. It would also be beneficial to have a general understanding of personal computers and the Windows operating system environment.