Advanced Excel 2002 / 2003 - Course Outline
COURSE INFORMATION
Duration: Two Days: 9am - 4:30pm
Price: $780
Book: Enquire or make a course booking
TARGET AUDIENCE & PURPOSE
Advanced Excel Training is for people who can already create moderately complex spreadsheets, and want to add more sophistication and automation.
OBJECTIVES
By the end of this course participants should be able to:
- Use outlines, range names, databases & the data form
- Use AutoFilter & advanced filters
- Work with Data Series
- Use advanced functions
- Modify charts
- Use Scenarios & Goal Seek
- Use templates, data tables & solve problems
- Use automatic formatting & styles
- Work with views, consolidate worksheets
- Create & revise PivotTables
- Use, record & edit macros
PREREQUISITES
Participants should already be using Excel on a regular basis and / or have a good Knowledge of Excel Introduction topics.
COURSE CONTENT
| Using Outline View Applying an Outline, Collapsing / Expanding an Outline, Modifying Outline Settings, Clearing an Outline, Using Auto Outline Using Multiple Workbooks Open & Arrange Multiple Workbooks, Save a Workspace, Create & Maintain Links Using Range Names Jumping to a Named Range, Assigning Names, Using Range Names in Formulas, Creating Range Names from Headings, Using Range Names in 3-D Formulas, Using 3-D Range Names & Labels in Formulas, Using Labels to Define a Range, Using Multiple Stacked Headings Working with Databases Creating & Modifying a Database, Sorting Records by Multiple Fields, Using Data Validation, Creating Subtotals in a List Working with the Data Form Editing Data Form Records, Adding Data Form Records, Defining Criteria, Clearing Data Form Criteria, Deleting Data Form Records Using AutoFilter & Advanced Filters Enabling AutoFilter, Using AutoFilter to Filter a List, Creating a Custom AutoFilter, Disabling AutoFilter, Creating a Criteria Range, Using Comparison Criteria, Using an Advanced 'AND' Condition, Using an Advanced 'OR' Condition, Using Database Functions Using Advanced Functions Using the VLOOKUP & HLOOKUP Function, Using the IF Function& Nested IF Function, Using the ISERROR Function, Using an 'AND' Condition with IF, Using an 'OR' Condition with IF Advanced Charting Adding, Removing & Formatting Gridlines, Formatting an Axis, Changing the Axis Scaling, Formatting the Data Series, Adding Data - Different Worksheets, Using a Secondary Axis, Changing Data Series Chart Types, Adding a Trendline, Creating User-defined Charts Using Templates Saving a Workbook as a Template, Using a Template, Editing a Template, Deleting a Template, Creating Default Templates Using Auditing Tools Displaying the Formula Auditing Toolbar, Displaying/Removing Dependent & Precedent Arrows, Removing All Tracer Arrows, Using the Auditing Tools Buttons |
Using Scenarios & Goal Seeking Using the Scenario Manager, Creating, Displaying & Editing a Scenario, Creating a Scenario Summary Report Solving Problems Using Solver, Saving a Solution as a Scenario, Changing a Constraint, Creating a Solver Report, Viewing Solutions using Scenarios Using Automatic Formatting & Styles Applying an AutoFormat, Changing AutoFormat Options, Extending List Formats & Formulas, Creating a Style by Example, Creating a New Style, Editing an Existing Style, Merging Styles Working with Views Creating, Displaying & Deleting a Custom View Consolidating Worksheets Consolidating Worksheets by Category or by Position Sharing Workbooks Saving a Shared Workbook, Viewing Users Sharing a Workbook, Viewing Shared Workbook Changes, Highlighting Changes, Resolving Conflicting Changes, Adding a History Worksheet, Reviewing Tracked Changes, Merging Shared Workbook Files Creating/Revising PivotTables Creating a PivotTable Report, Adding Fields, Selecting a Page Field Item, Refreshing a PivotTable Report, Changing the Summary Function, Adding New Fields, Moving Fields, Hiding/Unhiding PivotTable Report Items, Deleting PivotTable Report Fields, Creating a Page Field Report, Formatting a PivotTable Report, Creating a PivotChart Report Using, Recording, Editing Macros Opening a Workbook Containing Macros, Running a Macro, Using the Visual Basic Toolbar, Using the Visual Basic Editor Window, Recording a Macro, Using Relative References, Assigning a Macro to a Menu, Removing a Custom Menu Item, Deleting a Macro, Writing a New Macro, Entering Macro Comments, Copying, Editing & Typing Macro Commands, Running a Macro from the Code Window Using Custom Toolbars & Menus Creating a Custom Toolbar, Adding/Removing Toolbar Buttons, Assigning a Macro to a Button, Attaching a Custom Toolbar, Deleting a Custom Toolbar, Creating a Custom Menu Creating a Macro Button Using a Macro Button, Creating a Macro Button, Formatting a Macro Button, Moving/Sizing a Macro Button, Deleting a Macro Button |