Microsoft Excel 2010: Analytics

Course Overview

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

If you have a question about this Microsoft training course, call one of our advisors who will be happy to help answers any queries you may have.

Related Courses

• Microsoft Excel 2010: Introduction for Beginners
• Microsoft Excel 2010: Intermediate Level 2
• Microsoft Excel 2010: Advanced
• Microsoft Excel 2007 or 2010: Advanced - Validate & Summarise
• Microsoft Excel 2007 or 2010: Advanced - Formulae & Functions
• Microsoft Excel 2007/2010: Intermediate
• Microsoft Excel 2010: Intermediate
• Microsoft Excel 2010: Visual Basic
• Microsoft Excel 2010: upgrade from Excel 2003 2007
• Microsoft Excel 2010: Expert 2010

Skills Gained

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

Prerequisites

Students who wish to attend this course must have a very good knowledge of Excel 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 Outline

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

• Post your Spreadsheet online

Appendix B - Excel Preferences and Options

Appendix C - Hyperlinks and Saving for the Web