Microsoft Excel 2007 Level 3
Skilled Excel 2007 users will learn advanced workbook automation and productivity techniques including complex formulas and data analysis, Pivot Tables, Solver, macro creation and more.
Quick Facts
Duration: 1 Days
Class Size: 10
Level: Advanced
Times: 9.00am - 5.00pm approx
Other Microsoft Excel 2007 Courses:
About the Course
This course covers aspects of Microsoft Excel beyond building basic workbooks and deals with workbook automation and productivity features. Instructors teach advanced techniques in areas such as workbook and worksheet consolidation, data validation, lookup and reference functions, PivotTables, linking, Solver and macros.
Who should do this course?
This course is intended for experienced Microsoft Excel users who wish to extend their knowledge and skills beyond building and formatting simple workbooks. Upon successful completion of the course, students should be able to use Excel 2007’s advanced data analysis tools and automate workbook operations.
Prerequisites
Students should have completed Excel Level 1 and 2 courses or have equivalent skills.
Course Units
- Unit 1: Setting Excel Options
- - Understanding Excel Options
- - Personalising Excel
- - Setting The Default Font
- - Setting The Formula Options
- - Understanding Save Options
- - Setting Save Options
- - Setting The Default File Location
- - Setting Advanced Options
|
- Unit 2: Looking up 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
|
- Unit 3: Formula Techniques
- - Scoping A Formula
- - Developing A Nested Function
- - Creating Nested Functions
- - Editing Nested Functions
- - Copying Nested Functions
- - Using Concatenation
- - Switching To Manual Recalculation
- - Forcing A Recalculation
- - Pasting values From Formulas
- - Pasting Formulas As Pictures
|
- Unit 4: Goal Seeking
- - Goal Seeking Components
- - Using Goal Seek
|
- Unit 5: Data Linking
- - Understanding Data Linking
- - Linking Between Worksheets
- - Linking Between Workbooks
- - Updating Links Between Workbooks
|
- Unit 6: Data Consolidation
- - Understanding Data Consolidation
- - Consolidating With Identical Layouts
- - Creating An Outlined Consolidation
- - Consolidating With Different Layouts
|
- Unit 7: 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
|
- Unit 8: Summarising And Subtotalling
- - Creating Subtotals
- - Using A Subtotalled Worksheet
- - Creating Nested Subtotals
- - Copying Subtotals
- - Using Subtotals With An AutoFilter
- - Installing The Conditional Sum Wizard
- - Using The Conditional Sum Wizard
- - Creating Relative Names For Subtotals
- - Using Relative Names For Subtotals
|
- Unit 9: Grouping And Outlining
- - Understanding Grouping And Outlining
- - Creating An Automatic Outline
- - Working With An Outline
- - Creating A Manual Group
- - Grouping By Columns
|
- Unit 10: PivotTables
- - Understanding PivotTables
- - Creating A PivotTable Shell
- - Dropping Fields Into A PivotTable
- - Filtering A PivotTable
- - Clearing A Report Filter
- - Switching PivotTable Labels
- - Formatting A PivotTable
|
- Unit 11: PivotTable Techniques
- - Using Compound Fields
- - Counting In A PivotTable
- - Formatting PivotTable Values
- - Working With PivotTable Grand Totals
- - Working With PivotTable Subtotals
- - Finding The Percentage Of Total
- - Finding The Difference From
- - Grouping In PivotTables
- - Creating Running Totals
- - Creating Calculated Fields
- - Providing Custom Names
- - Creating Calculated Names
- - PivotTable Options
- - Sorting In A PivotTable
|
- Unit 12: PivotCharts
- - Creating A PivotChart Shell
- - Dragging Fields For The PivotChart
- - Changing The PivotChart Type
- - Using The PivotChart Filter Pane
- - Moving PivotCharts To Chart Sheets
|
- Unit 13: 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 Keyboard Shortcut To A Macro
- - Deleting A Macro
- - Copying A Macro
- - Tips For Developing Macros
|
- Unit 14: 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
|
- Unit 15: Solver
- - Understanding How Solver Works
- - Setting Solver Parameters
- - Adding Solver Constraints
- - Performing The Solver Operation
- - Running Solver Reports
- - Refining Solver Answers
|
|
Upcoming Courses
|
Sydney
Level 11, 32 Walker Street
North Sydney
View map
|
- Date:
- 08 Feb 12
- 15 Feb 12
- 22 Feb 12
- 02 Mar 12
- 15 Mar 12
- 28 Mar 12
- 12 Apr 12
- 26 Apr 12
- 11 May 12
- 23 May 12
- 06 Jun 12
- 22 Jun 12
|
|
Melbourne
Level 12, 379 Collins Street
Melbourne
View map
|
- Date:
- 10 Feb 12
- 15 Feb 12
- 24 Feb 12
- 01 Mar 12
- 07 Mar 12
- 16 Mar 12
- 22 Mar 12
- 04 Apr 12
- 19 Apr 12
- 04 May 12
- 16 May 12
- 30 May 12
- 15 Jun 12
- 27 Jun 12
|
|
Brisbane
C/- Support Technology, Level 6, 371 Queen Street
Brisbane
View map
|
- Date:
- 17 Feb 12
- 07 Mar 12
- 28 Mar 12
- 18 Apr 12
- 11 May 12
- 31 May 12
- 22 Jun 12
|
|
Canberra
C/O Atlas Business Services, 21 Barry Drive
Turner
View map
|
- Date:
- 22 Feb 12
- 29 Mar 12
- 03 May 12
- 06 Jun 12
|
|
Adelaide
C/- Mindfull Computer & Seminar, Level 4, 74 Pirie Street
Adelaide
View map
|
- Date:
- 15 Feb 12
- 11 Apr 12
|
|
Perth
C/- Lansen Consulting Pty Ltd, Level 1, 484 Albany Highway
Victoria Park
View map
|
- Date:
- 24 Feb 12
- 30 Mar 12
- 26 Apr 12
- 25 May 12
- 20 Jun 12
|
Duration: 1 Days
Class Size: 10
Level: Advanced
Times: 9.00am - 5.00pm approx
