Microsoft Excel 2007 Level 4

Objectives
On completion of this course delegates will be able to:

Use the SumIf, CountIf, Index, and Match functions and calculate with dates
Import and export data from other applications
Use variables to display different results using Data Tables
Carry out "What-Ifs" using Goal Seek, Scenario Manager and Solver
Create a purpose-made form using forms controls
Create standardised worksheets with the use of templates
Automate common tasks in worksheets by recording and editing macros
Assign macros to the Quick Launch Toolbar, keystrokes and buttons

Course Content

Further Functions
INDEX
MATCH
SUMIF
COUNTIF
Using Date Functions

Importing & Exporting Data
Importing Data from Text Files
Exporting Data
Changing External Data Range Properties
Importing Data from Other Applications
Removing the Query Definition
Importing Data from the Web

Using Scenarios & Goal Seek
Using the Scenario Manager
Displaying a Scenario
Creating a Scenario Summary Report
Using Goal Seek

Solving Problems
Using Solver
Restoring the Original Values
Changing a Constraint
Creating a Solver Report

Using Data Tables
Placing Formulas In a Data Table
Creating One & Two-Way Variable Tables

Using Form Controls in a Workbook
Worksheet Controls - Introduction to ActiveX Controls
Creating a Control Box, Check Box, List Box, Options Buttons & Spin Box

Using Templates
Working with Templates
Using, Editing, Deleting Templates

Introduction to Macros
Defining & Running Macros
Recording a Macro
Assigning a Shortcut Key
Adding a Custom Button to the Quick Launch Toolbar

Creating and Editing Macro Buttons

Adding a Macro Button to a Worksheet
Editing a Macro Button
Deleting a Macro Button

Appendix A – Using Shared Workbooks

Appendix B – Excel Preferences and Options

Appendix C – Hyperlinks and Saving for the Web