Microsoft Excel 2010 Level 4

Microsoft Excel 2010: Level 4

Course Code: XL10L4

Duration: 1 Day

Request availability & pricing

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