Email Print Bookmark

Microsoft Excel 2007 Level 3

Who should do this course?

This course is intended for current Microsoft Excel users who wish to extend their knowledge and skills beyond building and formatting simple workbooks. Students should of already completed Excel Level 1 and 2 courses or have equivalent skills.

About the course:

This course covers aspects of Microsoft Excel beyond building basic workbooks and deals with workbook automation and productivity features. It includes Advanced analysis tools, especially pivot tables, Solver, outlining and summarising and automating workbook operations using macros.

Learning outcomes:

Upon successful completion of this course, students will be able to:

  • use a range of lookup and reference functions
  • modify Excel options
  • create and use labels and names in a workbook
  • protect data in worksheets and workbooks
  • summarise data using subtotals and relative range naming
  • 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 PivotTables
  • construct and operate PivotTables using some of the more advanced techniques
  • 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

 

Quick Facts

Level: Advanced Price: $385.00 incl GST You will receive:
Duration: 1 Days Discounts*: 2 Students - 10% - Certificate of completion
Size: 10   3 Students - 15% - Training Manual
Times: 9.00am - 5.00pm approx   4 Students - 20% - 12 months FREE email support*
Pre-requisites: Refer to course description Bonus: 2 FREE - FREE class re-sit (if necessary)*
      Movie Tickets Course Brochure:
        Excel_2007_L3.pdf
         
 * Conditions apply.
 

Upcoming Courses

Sydney

Level 11, 32 Walker Street
North Sydney
View map

Date:
24-24 Mar 10
09-09 Apr 10
09-09 Apr 10
21-21 Apr 10
21-21 Apr 10
05-05 May 10
19-19 May 10
04-04 Jun 10
17-17 Jun 10

Melbourne

Level 12, 379 Collins Street
Melbourne
View map

Date:
19-19 Mar 10
19-19 Mar 10
08-08 Apr 10
08-08 Apr 10
23-23 Apr 10
05-05 May 10
21-21 May 10
02-02 Jun 10
17-17 Jun 10

Brisbane

C/- Support Technology, Level 6, 371 Queen St
Brisbane
View map

Date:
19-19 Mar 10
31-31 Mar 10
06-06 Apr 10
16-16 Apr 10
29-29 Apr 10
14-14 May 10
26-26 May 10
11-11 Jun 10
23-23 Jun 10

Canberra

C/- Training Choice, Level 12, 15 London Circuit
Canberra
View map

Date:
20-20 May 10

Adelaide

C/- Mindfull Computer & Seminar, Level 4, 74 Pirie Street
Adelaide
View map

Date: upon demand

For more information on dates or locations, please call 1300 888 724 or enquire online.

Course Units

Unit 1: 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 2: 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 3: 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
Unit 4: 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 Names 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
Unit 5: 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 6: 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 7: Data Linking
- Understanding Data Linking
- Linking Between Worksheets
- Linking Between Workbooks
- Updating Links Between Workbooks
Unit 8: Data Consolidation
- Understanding Data Consolidation
- Consolidating with Identical Layouts
- Creating an Outlined Consolidation
- Consolidating with Different Layout
Unit 9: 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 10: 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 11:
- 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 12: Goal Seeking
- Goal Seeking Components
- Using Goal Seek
Unit 13: Grouping And Outlining
- Understanding Grouping And Outlining
- Creating an Automatic Outline
- Working with an Outline
- Creating a Manual Group
- Grouping By Columns
Unit 14: Solver
- Understanding How Solver Works
- Setting Solver Parameters
- Adding Solver Constraints
- Performing the Solver Operation
- Running Solver Reports
- Refining Solver Answers
Unit 15: 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 16: 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
Man and woman sitting at a computer SEEK logo