Microsoft Excel Training Courses

Introduction

This course is for existing users of Excel.

Objective

To further enable the user to produce spreadsheets of a more complex nature incorporating many of the functions listed below.

Duration

1-Day Training Course.

Course Outline

• Recap over basics including:
• General formulae
• Basic calculations (add, subtract, multiply and divide)
• Absolute referencing
• Statistical Formulae – average, count, max, min
• Working with sheets and multiple sheets
• 3D calculations
• Conditional Formatting
• Database Techniques
• Group and Outline
• Sorting
• Filtering
• Automatic subtotals
• Flash Fill
• Data Validation
• Pivot tables
• Range Names
• Vlookups and Xlookups
• Useful functions – Freeze Panes, Go To, Find and Replace

Introduction

This course is for experienced users of Excel.

Objective

To enable the user to work with the more advanced tools available within Excel.

Duration

1-Day Training Course.

Course Outline

• Recap over basic/intermediate topics including (if required)
• General formulae, Absolute referencing, 3D referencing, Linking workbooks
• Logical Functions
• If, Nested If, Sum If, Count If , ifand, ifor, iferror
• Date and Time Functions, Network Days
• AutoFill
• Text functions including concatenate, changing case, exact, left, mid, right
• Auditing Tools
• Tracer Arrows, Watch Window
• Lookup Functions
• Vlookup, hlookup, xlookup
• Pivot Tables with Slicers and Timelines
• Dashboards
• Goal Seek
• Basic Macros
• Recording, Assigning to buttons & the toolbar, Running
• Other useful Excel functions
• Index, Match, Offset, Rand, Round and Arrays

Introduction

This course is for people who would like to be proficient with Pivot Tables in Excel.

Objective

By the end of the course you will be able to use all of the Pivot Table features Excel has to offer, using a variety of examples.

Duration

1-Day Training Course.

Course Outline

Session 1 — Creating Pivot Tables

• Create and change Pivot Tables
• Add and remove Pivot Table elements
• Change field and table properties
• Format text and numbers

Session 2 — Updating your Pivot Table and creating calculations within it

• Make Pivot Table data easy to update
• Change the way data is calculated in a pivot table
• Create calculations within a Pivot Table
• Pivot a pivot
• Using the GETPIVOTDATA function

Session 3 — Using Pivot Charts

• Pivot Charts

Session 4 — Creating dashboards

• Arranging tables and charts
• Formatting the dashboard sheet
• Adding interactive tools

Introduction

This two-day, practical Excel Visual Basic Application (VBA / Macros) course, is designed to be an excellent introduction for delegates to enable them to read, write, edit and use Excel VBA Macros in their day to day role. This course will enable delegates with no prior programming experience, to move from recording a basic macro to reading and building practical working macro solutions, which will include up to intermediate level understanding. Delegates will gain the appropriate knowledge and necessary proficiency to harness the power of Excel VBA to automate repetitive tasks such as analysing and manipulating data.

Objective

n completion of this course, delegates will be able to read and create VBA code. They will acquire a good foundation to enable them to custom design their own VBA Macros to simplify complex manual tasks. This course contains the reading and using of Excel VBA code within each module, to support each of the topics being covered. It also includes a number of practical related exercises. Delegates will access, use and retain a significant number of training files during the course. Delegates will additionally receive practical guidance on programming structure and design techniques.

Duration

2-Day Training Course.

Attendees

Delegates must have attended the Excel Advanced training course or have equivalent practical experience prior to attending this training course.

Course Outline

• Module 1 - Introducing Visual Basic for Applications
• Module 2 - VBA Editor & Recording Macros
• Module 3 - Modules and Procedures
• Module 4 - Understanding Objects, Properties, Methods and Events
• Module 5 - Using Expressions and Variables
• Module 6 - Manipulating Data
• Module 7 - Formatting Cells and Working with Strings
• Module 8 - Workbooks and Worksheets
• Module 9 - Controlling Program Execution / Decision Structures
• Module 10 - Using Message Boxes, Input Boxes and Running Macros

Practical Application

A number of practical’s will be undertaken throughout the course and of note at start of day two a practical exercise will be completed by the attendees to reinforce and put into practice what they have learnt on day one.