This course is for users who have basic experience with Excel and want to build on their current knowledge to set up their own spreadsheets and manipulate existing ones.

Course Objectives:    

To develop a good working knowledge of Excel to include features such as functions and formulae, sorting and filtering data, working with charts and pivot tables

Course Outline:    

Review Concepts

• Absolute, Relative References Standard calculations

New Features

• One click Forecasting
• Smart Lookup
• Tell me what you want to do… Quick Analysis Tools

Naming Cell Ranges

• Concept And Purpose Naming
• Individual Cells
• Naming Range Of Cells
• Deleting And Amending Named Ranges
• Using Name Ranges in Formulae/Functions
• Named Ranges As Navigation Aid

Linking Sheets and Files

• Changing the default number of workbook sheets 3D Calculations
• Linking sheets in the same file Linking different Excel files
• Using Edit, Links
• Viewing Different Files on One Screen Window Split / Freeze Panes
• Viewing Different Parts of the Sheet On One Screen Custom Views

Logical Functions IF Statements Nested If

• SUMIF / SUMIFS AVERAGEIF / AVERAGEIFS COUNTIF / COUNTIFS ISERROR, IFERROR, IFNA
• Nesting IF WITH AND, OR

Applying and Managing Conditional Formatting

• Data Bars Colour Scales
• Icon Sets
• Top/Bottom
• Creating Formula based Conditional formatting

Lookup Functions

• Vertical Lookup (VLOOKUP)
• Horizontal Lookup (HLOOKUP)

Database \ List Management

• Sorting Data (By Values, By Cell Colour, By Font Colour, By Cell Icons)
• Multi Column Sort
• Filter (By Values, By Cell Colour, By Font Colour, By Cell Icons)
• Multi Column Filter Advanced Filter Adding Subtotals Freeze Panes Group and Outline Data Form
• Format as Table Feature
• Data Validation

Formulae Auditing Formula View

• Tracing Precedents
• Tracing Dependents
• Using Watch Window
• Go To Special…

Charts/Graphs Advanced Techniques

• Using Recommended charts
• Creating Chart Using Shortcut Keys
• Setting Chart as Default
• Area, legends, etc…
• Using Sparklines (Line, Column, Win/Loss)
• Creating a secondary axis
• New Charts in Excel 2016

Pivot Tables

• Creating a Pivot Table
• Updating the Table
• Changing the Table Structure
• Formatting the Pivot Table
• AutoFormats
• Creating Charts from the Data Calculated Items
• Using Get Pivot data

Protection

• Protection – Cells / Sheets / Files Using IRM

Course Summary and Review

Time and Date:

• Wednesday 22nd  Feb 9.30-12.30

Cost:

• €55 for Members of ISME
• €65  for Non Members of ISME