Key Training Logo

Microsoft Excel 2010: Level 4

Course Code: XL10L4

Duration: 1 Day

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

Key Affiliations

 
Best Company 2019
5 Percent Club
RI Supplier Awards
Youth Employment UK
Investors in people
RI Top 500
Team Service Provider
European Union
ARC
REC Logo
ESFA
Inspiring the future
AELP
Skills for health Quality Mark
Matrix
Ofsted
Apprenticeships