Microsoft Excel 2010: 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 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
