Microsoft Excel 2003 Level 3
Skilled Excel 2003 users will learn advanced workbook automation and productivity techniques including complex formulas and data analysis, Pivot Tables, macro creation and more.
Quick Facts
Duration: 1 Days
Class Size: 10
Level: Advanced
Times: 9.00am - 5.00pm approx
Other Microsoft Excel 2003 Courses:
About the Course
This 1 day course features aspects of Microsoft Excel 2003 beyond building basic workbooks, such as workbook automation and productivity features. Instructors cover advanced techniques in workbook and worksheet consolidation, data validation, Scenario Manager, PivotTables, linking, publishing 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 2003’s advanced data analysis tools and automate workbook operations.
Prerequisites
Students should have completed Excel 2003 Level 1 and 2 courses or have equivalent skills
Course Units
- Unit 1: Nesting Functions Workshop
- - Scoping a Formula
- - Developing a Nested Function
- - Creating a Nested Function
- - Editing a Formula with Nested Functions
- - Copying a Formula with Nested Functions
- - Concatenation
|
- Unit 2: Advanced Filters
- - Advanced Filter Concepts
- - Using an Advanced Filter
- - Extracting Records with Advanced Filter
- - Using Formulas in Criteria
- - Using Database Functions
|
- Unit 3: Validations
- - Validation Techniques
- - Data Validation By Number Range
- - Testing Data Validation
- - Input Messages
- - Creating Error Alerts
- - Creating Drop-Down Lists
- - Using Formulas As Validation Criteria
- - Creating Custom Validation Criteria
- - Number Formats with Built-in Logic
- - Conditional Formatting
- - Copying Data Validation Settings
|
- Unit 4: Goal Seeking
- - Goal Seek Components
- - Using Goal Seeking
|
- Unit 5: Scenarios
- - Creating a Default Scenario
- - Creating Scenarios
- - Using Names in Scenarios
- - Displaying Scenarios
- - Creating a Scenario Summary Report
- - Merging Scenarios
|
- Unit 6: Summarising Data
- - Creating Subtotals
- - Using a Subtotaled Worksheet
- - Creating Nested Subtotals
- - Copying Subtotals
- - Using Subtotals with AutoFilter
- - Installing the Conditional Sum Wizard
- - Using the Conditional Sum Wizard
- - Creating Relative Names for Subtotals
- - Using Relative Names for Subtotals
|
- Unit 7: PivotTables
- - PivotTable Theory
- - Creating a Simple PivotTable
- - Adding a Row Field 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
|
- Unit 8: Linking Workbooks
- - Linking Data in Excel
- - Linking Within a Workbook
- - Linking Between Workbooks
- - Updating Links Between Workbooks
|
- Unit 9: Consolidation
- - Consolidating Data with Identical Layouts
- - Consolidating Data with Different Layouts
- - Consolidating Data Using a PivotTable
|
- Unit 10: Macro Virus Control
- - Opening Workbooks with Macros
- - Setting Macro Security Levels
- - Trusting Publishers
- - Creating a Digital Signature
- - Removing a Trusted Publisher
|
- Unit 11: 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
|
- Appendix 1: Data Tables
- - Using a Simple what-If Model
- - Creating a One-Variable Table
- - Using One-Variable Data Tables
- - Creating a Two-Variable Data Table
|
- Appendix 2: Excel on the Web
- - Previewing Workbooks As Web Pages
- - Publishing a Static Worksheet
- - Adding to an Existing Web Page
- - Publishing an Interactive Web Page
|
- Appendix 3: Recorder Workshop
- - Preparing Data
- - Recording Summation Macros
- - Recording Consolidations
- - Recording Divisional Macros
- - Testing Macros
- - Creating Objects to Run Macros
- - Assigning a Macro to an Object
|
Upcoming Courses
|
Sydney
Level 11, 32 Walker Street
North Sydney
View map
|
- Date:
- 16 Mar 12
- 09 May 12
|
|
Melbourne
Level 12, 379 Collins Street
Melbourne
View map
|
- Date:
- 16 Feb 12
- 23 Mar 12
- 16 May 12
|
|
Brisbane
C/- Support Technology, Level 6, 371 Queen Street
Brisbane
View map
|
Date: upon demand
|
|
Canberra
C/O Atlas Business Services, 21 Barry Drive
Turner
View map
|
Date: upon demand
|
|
Adelaide
C/- Mindfull Computer & Seminar, Level 4, 74 Pirie Street
Adelaide
View map
|
Date: upon demand
|
|
Perth
C/- Lansen Consulting Pty Ltd, Level 1, 484 Albany Highway
Victoria Park
View map
|
Date: upon demand
|
Duration: 1 Days
Class Size: 10
Level: Advanced
Times: 9.00am - 5.00pm approx
