This course is not currently offered by Global Knowledge. Information here is provided for reference only.

This course addresses the use of data series and advanced functions including the LOOKUP functions and nested and multiple criteria IF functions. It also covers PivotTables, scenarios, goal seeking, data tables, and the consolidation of worksheets. Finally, it looks at problem solving, how to work with views, use of auditing tools, and managing macros.

For this course, training is performed on student's equipment. We provide the software and data files for the training.

What You'll Learn

• Work with data series
• Use advanced functions
• Create/revise PivotTables
• Use scenarios and goal seeking
• Use data tables
• Consolidate worksheets
• Solve problems
• Work with views
• Use auditing tools
• Use macros
• Record macros
• Edit macros
• Create macro buttons

Who Needs to Attend

Business users who have taken Excel 2007 - Level 3 (Course 6606) or have equivalent knowledge

Prerequisites

There are no prerequisites for this course.

Follow-On Courses

There are no follow-ons for this course.

Course Outline

1. Working with Data Series

• Using the Series Command
• Creating a Linear Series
• Creating a Date Series
• Using a Stop Value
• Creating a Growth Trend Series

2. Using Advanced Functions

• Using Lookup Functions
• Using the VLOOKUP Function
• Using the HLOOKUP Function
• Using the IF Function
• Using Nested IF Functions
• Using the ISERROR Function
• Using an AND Condition with IF
• Using an OR Condition with IF
• Using the ROUND Function
• Limiting the Precision of Numbers

3. Creating/Revising PivotTables

• Creating a PivotTable Report
• Adding PivotTable Report Fields
• Selecting a Report Filter Field Item
• Refreshing a PivotTable Report
• Changing the Summary Function
• Adding New Fields to a PivotTable Report
• Moving PivotTable Report Fields
• Using Expand and Collapse Buttons
• Hiding/Unhiding PivotTable Report Items
• Deleting PivotTable Report Fields
• Creating Report Filter Pages
• Formatting a PivotTable Report
• Creating a PivotChart Report
• Publishing PivotTable Reports to the Web

4. Using Scenarios and Goal Seeking

• Using the Scenario Manager
• Creating a Scenario
• Displaying a Scenario
• Editing a Scenario
• Creating a Scenario Summary Report
• Using Goal Seek

5. Using Data Tables

• Working with Data Tables
• Placing Formulas in Data Tables
• Creating a One-Variable Table
• Creating a Two-Variable Table

6. Consolidating Worksheets

• Using Consolidated Worksheets
• Consolidating by Category
• Consolidating by Position

7. Solving Problems

• Using Solver
• Saving a Solution as a Scenario
• Restoring the Original Values
• Changing a Constraint
• Creating a Solver Report
• Viewing Solutions using Scenarios

8. Working with Views

• Using Views
• Creating a Normal View
• Creating a Custom View
• Displaying a View
• Deleting a Custom View

9. Using Auditing Tools

• Showing Formulas
• Displaying/Removing Dependent Arrows
• Displaying/Removing Precedent Arrows
• Using the Trace Error Button
• Tracing Cells Causing Errors
• Using the Evaluate Formula Button
• Using the Error Checking Button
• Using the Watch Window

10. Using Macros

• Defining Macros
• Changing Macro Security Settings
• Adding a Folder as a Trusted Location
• Opening a Workbook Containing Macros
• Running a Macro
• Using a Shortcut Key
• Displaying the Developer Tab
• Opening the Visual Basic Editor Window
• Using the Visual Basic Editor Window

11. Recording Macros

• Recording a Macro
• Assigning a Shortcut Key
• Using Relative References
• Deleting a Macro

12. Editing Macros

• Writing a New Macro
• Entering Macro Comments
• Copying Macro Commands
• Editing Macro Commands
• Typing Macro Commands
• Running a Macro from the Code Window

13. Creating Macro Buttons

• Using a Macro Button
• Creating a Macro Button
• Copying a Macro Button
• Formatting a Macro Button
• Moving/Sizing a Macro Button
• Deleting a Macro Button
• Adding a Macro to a Quick Access Toolbar
• Changing a QAT Macro Button Image
• Deleting a Macro Button from the QAT