Excel 2007 Automating & Forecasting - Advanced Plus
Course Information
This course is designed to enable delegates to use spreadsheets with a high degree of skill by automating much of the daily workflow and using the forecasting facilities available in Excel.
Delegates will be able to automate both simple and complex tasks by creating automated spreadsheets for use by others.
Pre-requisites
Prior attendance of the three previous courses or equivalent knowledge from their own usage of the package is essential.
Course Objectives
By the end of the course delegates will be able to:
- Recognise options and settings within Excel that may be adjusted to suit the user.
- Create templates to speed workbook creation.
- Create on-line forms to control and ease input by users.
- Record macros to automate repetitive tasks.
- Create user-defined functions to automate repetitive calculations.
- Create and manage Scenarios.
- Create What-if data tables.
- Utilise the Goal Seek and Solver tools to produce forecasts.
- Utilise the built-in auditing tools to trace formulae and dependents to assist in troubleshooting.
- Recognise options and settings
What you will learn:
Lesson 1 – Templates
Create a template
Use a template
Edit a template
Insert a template as a worksheet
Lesson 2 – What If
Use goal seek
What-if tables
Scenario Manager
Lesson 3 – Solver
Solver overview
Use the solver feature
Use the help feature to obtain help on solver reports
Lesson 4 – Macros and Custom Controls
Record a macro
Assign a macro to a shortcut key
Run a macro using the tools macro command
Create option buttons
Attach macro to a button
Assign a macro to a button that already exists
Format the text inside a button
Lesson 5 – User Defined Functions
Create and use a simple custom function
Edit the custom function to recalculate the worksheet
Lesson 6 – Auditing Worksheets
The auditing toolbar
Lesson 7 – On-line Forms
The Developer Ribbon
Form Controls
Use buttons linked to formula on a form
Lesson 8 – Importing Data
Importing text into a worksheet
Import data from a database
