Microsoft Excel 2007 / 2010 Level 4

Microsoft Excel 2007 Level 4

Course Aims:

This course is aimed at people who want expand their knowledge into the more specialised analytical functionality and built-in automation offered by Microsoft Excel.

Course Pre-requisites:

Students who wish to attend this course must have very good knowledge of Excel 2007/2010 and be competent at working with advanced functions (e.g. Logical & Lookup) and have some basic knowledge of Excel’s analytical capabilities (e.g. Pivot Tables and database tools).

Course Objectives:

On completion of this course delegates will be able to:

• Use the SumIf, Countif, Index, Match and date functions
• Import and export data from other applications
• Carry out “What-Ifs” using Goal Seek, and Scenario Manager
• Create dashboard interfaces using form controls
• Create standardised worksheets with the use of templates
• Automate common tasks in worksheets by recording and editing macros
• Assign macros to the Quick Access Toolbar, keystrokes and button on the worksheet.

Course Content:

Introduction and Objectives

Further Functions

• INDEX
• MATCH
• SUMIF(s)
• COUNTIF(s)
• 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 Goal Seek and Scenario

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

 Using Form Controls in a Workbook

• 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 Access 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

• Post your Spreadsheet online

Appendix B – Excel Preferences and Options

Appendix C – Hyperlinks and Saving for the Web

Action planning

Review of Programme

Related Courses

Excel Level 5 – Intro to VBA

Microsoft® Certified Application Specialist: Using Microsoft® Office Excel 2007/2010