Audience

Users who need to enhance their existing knowledge and explore the more advanced features of Excel including complex formulae, charts and forecasting tools.

Course Objectives

At the end of this course, delegates will be aware of many of the “power user” features.

Prerequisites

This course assumes prior attendance on the Excel Intermediate course (or equivalent experience), and a good working knowledge of Windows.

Course contents (1 day)

Using logic

• IF statements and nested IF’s
• Conditions AND/OR/NOT
• SUMIF & COUNTIF

Conditional formatting

• Use of Data bars, Colour scales & Icons
• Value is vs Formula is

Lookup Tables

• Range names review
• Lookups
• Data validation (customising messages, error alerts and use of lists)
• MATCH & INDEX & RANK

String Functions

• Concatenation (&)
• Left, Mid, Right, Len, Find

Creating a Database

• Creating data forms
• Add/edit/delete/find data

Advanced Charting

• Adding non-adjacent data
• Mixing chart types
• Using dual Y axis
• Adding Trendlines
• X-Y charts

Forecasting Tools

• Goal seek
• Scenario Manager
• Data Tables (1 and 2 way)
• Solver

Array formulae

Data Consolidate

Controls

• Creating a form using controls
• Adding buttons – spin, check box, option and lists

Optional Topics (time permitting)

Go to special

Paste Special

Setting preferences

Macros (covered fully on the Excel VBA course)

• What is a macro?
• Record a simple macro
• Assign shortcut keys
• Create a toolbar button and assign the macro to it

Course Materials

Enliten IT will provide each delegate with a workbook and other useful reference materials where applicable.

Maximum number of delegates: 8

Cost:    Members - 1 unit, £150 +VAT

            Non-members - £225 +VAT

To register please email our Training Co-ordinator on registrations@thelearningcollaboration.com