Microsoft Excel Power User

Excel Power User

Excel Power User – Course Outline(PDF Printable version)

General Information

Duration: 3 Half Days, 10.5 Hours
Cost: $495 GST‐Exempt, Includes training manual
Prerequisite: Experience with Windows and completed an Excel Intermediate level course.
Objectives: To provide a thorough understanding of the comprehensive concepts and skills that will enable the participant to effectively use Microsoft Excel.
Methodology: Group and individual instruction, hands on practical exercises, visual aids, comprehensive course documentation.
Expected Outcome: By the end of the course the participant should be able to use the following functions and features of Microsoft Excel.

Course Content

Formula Techniques:
• Scoping a Formula
• Developing a Nested Function
• Creating Nested Functions
• Editing Nested Functions
• Copying Nested Functions
• Using Concatenation
• Switching to Manual Recalculation
• Pasting Values From Formulas
• Pasting Formulas as Pictures

Advanced Filters:
• Understanding Advanced Filtering
• Using an Advanced Filter
• Extracting Records with Advanced Filter
• Using Formulas in Criteria
• Using Database Functions
• Using DSUM
• Using the DMIN Function
• Using the DMAX Function
• Using the DCOUNT Function

Validating Data:
• Understanding Data Validation
• Creating a Number Range Validation
• Testing a Validation
• Creating an Input Message
• Creating an Error Message
• Creating a Drop‐Down List
• Using Formulas as Validation Criteria
• Creating Invalid Data
• Removing Invalid Data Circles
• Copying Validation Settings

Goal Seeking:
• Goal Seek Components
• Using Goal Seek

Data Tables:
• Data Tables and What‐If Models
• Using a Simple What‐If Model
• Creating One‐Variable Data Tables
• Using One‐Variable Data Table
• Creating a Two‐Variable Data Table

• Understanding Scenarios
• Creating a Default Scenario
• Creating Scenarios
• Using Names in Scenarios
• Displaying Scenarios
• Creating a Scenario Summary Report
• Merging Scenarios

• Understanding How Solver Works
• Setting Solver Parameters
• Adding Solver Constraints
• Performing the Solver Operation
• Running Solver Reports
• Refining Solver Answers

Pivot Tables:
• Understanding Pivot Tables
• Creating a Simple PivotTable
• Dropping Fields Into a Pivot Table
• Filtering a Pivot Table
• Clearing a Report Filter
• Switching Pivot Table Labels
• Format a Pivot Table
• Using Compound Fields
• Counting in a Pivot Table
• Formatting Pivot Table Values
• Working with Pivot Table Grand Values
• Working With Pivot Table Sub Totals
• Finding the Percentage of a Total
• Finding the Difference From
• Grouping in Pivot Tables
• Creating Running Totals
• Creating Calculated Items
• Pivot Table Options
• Sorting in a Pivot Table

Linking Workbooks:
• Linking Data in Excel
• Linking Within a Workbook
• Linking between Workbooks
• Updating Links between Workbooks

Data Consolidation:
• Understanding Data Consolidation
• Consolidating with Identical Layouts
• Creating an Outlines Consolidation
• Consolidating with Different Layouts

Importing and Exporting:
• Understanding Data Importing
• Importing From An Earlier Version
• Understanding Text File Formats
• Importing Tab Delimited Text
• Importing Access Data
• Working With Connected Data
• Unlinking Connections
• Exporting To Microsoft Word
• Exporting Data as Text

Summarising and Subtotalling:
• Creating Subtotals
• Using a Subtotalled 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

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

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