Microsoft Excel Advanced

At the end of this course you will be able to:

Create/save templates. Consolidating the information from several workbooks. Use Goal Seek to change the value in a cell that contains a formula by changing one of the dependent cells. Use shortcuts to show/hide rows/columns temporarily to change the way a spreadsheet looks and using that view to create a report for printing.

The topics listed below indicate the range of features which, depending on the interests of the course attendees, can be covered on the course.

Course Outline:

Templates

• Create and Use a Template

Styles

• Use a Style
• Edit a Style
• Merge a Style

Data Maps

• Create a Data Map
• Data Map Control Dialog Box
• Placing Data on a Map
• Formatting a Data Map

"What If" type Utilities

• Goal Seek
• Graphical Goal Seeking
• Scenario Manager
• Create a Scenario
• Show Scenarios
• Scenario Summary Report
• Solver

Data Tables

• One Variable Data Table
• Two Variable Data Table

Custom Views

• Create a View
• Group/Outline Rows/Columns
• Hide/Show Rows/Columns

Report Manager

• Creating a Report
• Printing a Report

Macros

• Record a Macro
• Assign a Macro to a shortcut key
• Assign a Macro to an Icon

Excel Auditing Tools

• Using tracers to audit a worksheet
• Tracing Precedents
• Tracing Dependents
• Trace Errors in a cell

Use Data Validation

• View Invalid data

Consolidation

• Rename a range of cells
• Consolidate data from several workbooks

VLookup/HLookup

Advanced Filtering

• Criteria for advanced filtering
• Totalling and manipulating filtered data
• Create/remove Subtotals