The Content

Excel Workplace

• The Ribbons
• Customising Quick Access Toolbar
• Customising Status Bar
• Office Backstage View
• Using Autocalculate

Data Editing

• Adding/Editing Data/ Undo/Redo
• Cut & Paste & Clipboard
• Using Smarttags
• Using Autofill & Flashfill techniques
• Autocomplete
• Adding Comments
• Saving options, File Formats & Compatibility Mode

Getting Around & Data Selection

• Moving around a worksheet
• Finding & Replace
• Quick tips for selecting areas
• Moving & copying

Formatting your Sheet

• Adding / removing / resizing columns & rows
• Manipulating multiple columns & rows
• Changing Font & Attributes with Live Preview
• Format Painter
• Alignment icons
• Cell orientation
• Indent icons
• Wrapping text and forcing a new line
• Merging cells or centering across selection
• Borders and shading
• Format Painter
• Using Autocorrect
• Quick Formatting using Tables & Styles

The Content

Absolute Rows & Columns

• Partial Absolutes – Row & Column
• The Formulae Auditing Toolbar

Range Names

• The benefits of using range names
• Rules for creating range names
• An alternative to using absolute referencing
• Creating range names from headings
• Using range names to navigate
• Pasting range names into a formulae
• Tips & Tricks

Data Consolidation

• Consolidating data across worksheets
• Consolidating data across workbooks
• Creating links

Sorting

• The pitfalls of sorting – tips & tricks
• Removing empty columns / rows quickly using select blanks feature
• Sorting Data using Custom Lists
• Multi-level sorting

Subtotalling & Outlining

• The Functions available
• Multiple Sub-Totals
• Using the Outline feature

Data Tables

• The Table Design Tools
• Using the total row feature
• Filters in tables
• Calculated Columns

Removing Duplicates

Conditional Formatting

• An introduction to Conditional Formatting
• Highlight cell rules
• Top & Bottom rules
• Data Bars
• Colour Scales
• Icon Sets

The Content

Using Index Match

• INDEX MATCH vs VLOOKUP
• Nesting with IFERROR

Nesting Functions

• Tips and Tricks when nesting functions
• Understanding Nesting in more detail using a variety of functions
• Combining IF’s with VLOOKUP
• Nesting MATCH and INDEX functions
• Nesting text functions LEFT, RIGHT, MID PROPER, FIND, SEARCH
• Nesting INDIRECT
• Nesting workshop to explore other options

Auditing Worksheets

• Evaluating Formulae

Using Array Formulae

• Why use Array Formulae?
• Incorporating other functions

Offset Function

• Creating the OFFSET formula
• Incorporating other functions
• Creating the Combo Box
• Using Concatenate
• Insert a chart & linking the title to the Concatenate formula

Useful Date & Time Functions

• Using variety of date functions including NETWORKDAYS, WORKDAY
• Working with time in formulae
• Using Elapsed Time

The Content

Dashboard Fundamentals

• What is a dashboard
• Dashboard Key Points
• Do’s and Don’ts

Data Preparation

• Specialized Functions
• VLOOKUPS
• INDEX MATCH
• OFFSET
• Nested Ifs
• Combine Functions

Automating Workbook Functionality

• Applying Conditional Formatting
• Working with Form Controls

Visualizing Data with Charts and Graphics

• Creating Charts
• Modifying and formatting Charts
• Using Advanced Chart Features
• Creating Sparklines

Analysing Data with PivotTables, Slicers, and PivotCharts

• Creating a PivotTable
• Analysing PivotTable Data
• Presenting Data with PivotCharts
• Filtering Data by using Slicers

The Content

Introduction

• Creating and recording macros
• The Developer ribbon
• Objects and object models
• Calling a macro
• Using buttons and hotspots
• Security and trusted locations

Getting started with VBA

• The Visual Basic environment
• Adding a module
• Writing a Sub Procedure
• Calling the Macro Sub
• Introducing Variables
• Declaring Variables
• InputBox and MessageBox

Referencing Cells & Objects

• Referring to cells in a worksheet
• Using Range
• Selections and Ranges
• Using Cells
• Cell Properties
• Cells and Ranges within Ranges
• Offset

The Content

Exploiting Excel

• Workbooks and Worksheets
• Collections and accessor methods
• Adding to collections
• Names arguments
• For Each and Count
• Adding charts
• Events
• Timers

Further Visual Basic

• Variable scope
• Variable lifetime
• Module and public variables
• Arrays

Functions and UDF’S

• Functions
• Using a function on a worksheet

Error Handling

User Forms

• Controls and Forms
• Check boxes and spinners
• Linking to cells
• User forms as custom dialogs
• The toolbox
• Designing forms
• Showing forms
• Handling events for a form
• Closing a form
• Using control properties
• Text, list and combo boxes
• Interaction techniques

The Content

Accessing and Running an Existing Macro

• From the Ribbon
• From a Keyboard shortcut
• From a Quick Access Toolbar icon
• From the Developer Tab
• From a Button

Recording a Macro

• How to record a basic Macro
• Macro planning
• The Macro Recorder

Macro Storage locations

• This Workbook
• New Workbook
• Personal Macro Workbook

Understanding Macro limitations

• Understanding the limitations of a basic recorded macro
• How to avoid some basic run-time errors

Define a Trusted Location

• Understanding Trusted Locations and how to they can be created

Getting more from the Macro Recorder

• Using keyboard shortcuts to extend macro flexibility
• Creating a Relative Reference Macro