As an alternative to the standard Introductory, Intermediate and Advanced courses, why not consider our additional courses offering a fast-paced, packed programme in one day covering multiple levels as detailed below:-
- Excel the Basics (covering topics over Introduction and Intermediate levels)
- Excel Beyond the Basics (covering topics over Intermediate and Advanced levels)
- Excel Above and Beyond (covering topics over Advanced and further Advanced)
- Excel Automating Reporting (similar to Excel Above and Beyond but offering more features on Macros and introducing Forms)
- New course coming soon …. Excel – Power User Data Analysis – a great new course for Power Excel users! – ask our team for more information
Microsoft Power BI (Business Intelligence) Courses
- Learn how Power BI can transform your company data into rich visuals so you can focus on what matters to you
- Create stunning interactive reports to communicate your message effectively
- Live dashboards provide a 360 degree view of your business
- Important metrics in one place
- Use the Power BI Desktop to combine data from databases, files and web services
For all the Power BI courses available, see the Standard Courses + Power BI section of this website
Visual Basic for Applications (VBA) Courses for Excel
- Learn how to exploit the power of Excel and its programming capabilities to automate tasks and increase productivity
All versions of Microsoft Excel are supported – if you require a Course Outline for specific version, please contact our Sales Team – see our Contact Page for more information
- Excel 2003 – Excel 2007 – Excel 2010 – Excel 2013 – Excel 2016 – Excel 2019 – Office 365
Excel The Basics
Target Audience
Aimed at new users of Excel or users who want to ‘fill in the gaps’ in their knowledge, this will give delegates the rudiments with topics at Introduction to Intermediate levels.
Delegates will learn how to create, layout and manage spreadsheets incorporating the use of formulae, absolute cell references and links across sheets and workbooks.
Delegate Pre-Requisites
No prior knowledge of Excel is required
Course Duration
This is a One Day Course
Course Details
Courses commence at 9:30 a.m. through to 4:30 p.m.
A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated
All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate
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
Multiple Worksheets
- Selecting / Rearranging Worksheets
- Renaming a Worksheet
- Deleting & Copying Worksheets
- Hiding Worksheets
- Hiding Columns and Rows
- Grouping Worksheets
Using Formulae
- Adding Operator Icons to Quick Access Toolbar
- Building formulae—tips & tricks & BODMAS
- Workshop session with lots of formulae
building exercises - AutoSum / Average / Maximum / Minimum
- Working with percentages
- Working with dates
- Linking formulae across worksheet & workbooks
- Editing links
- Using absolute cell references
- Formulae Auditing
- Moving & copying options with formulae
- Converting a formula to a value
Numeric Formatting
- Using the icons
- The menu options
- Working with dates
- Creating a custom format
Sorting & Filtering
- Introduction to Filters
- Single level sorting
Preparing to Print
- Page Layout View
- Page Break Preview
- Controlling Page Breaks
- Headers & Footers
- Repeating Headings
Delegate Pre-Requisites
All delegates should have a sound knowledge of building formulae using operators and functions together with the use of range names.
Delegates should be able to use absolute and relative referencing and be familiar with autofill techniques together with linking formulae across worksheets and workbooks
Course Duration
This is a One Day Course
Course Details
Courses commence at 9:30 a.m. through to 4:30 p.m.
A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated
All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate
Visual Basic
If you want to learn more about creating and editing macros, we offer several Visual Basic courses – see our Excel course outlines page from our website for more information and links to the Visual Basic courses available!
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
Pivot Tables
- The Pivot Table Design Tools
- Changing the Structure / Pivoting
- Using SUM, AVERAGE, MAX, MIN, COUNT
- Subtotals & Grand Total
- Grouping data by Date
- Grouping data by Value
- Grouping data by Text
- Running Totals
- Percentages
- Pivot Table Styles for quick formatting
- Report Layouts – Compact, Outline & Tabular
- Filtering Pivot Tables using Slicers
- Seeing the Details using Drill Down
- Calculated Fields
- Using Show Report Filter Pages to offset
reports to separate sheets
Creating a Pivot Chart
- The benefits of Pivot Charts
- The Pivot Chart Design Tools
Lookups
- VLOOKUP and HLOOKUP
- Error trapping using IFERROR
- Nesting IFERROR with VLOOKUP and HLOOKUP
Index Match
- An alternative to VLOOKUP
- Nesting IFERROR with INDEX MATCH
Data Validation
- What is data validation?
- Date validation options
- List validation options
- Using Tables & Range Names to create a dynamic list where additional items added to the end of a list are automatically included
Logical Functions
- The IF Function
- Nesting multiple IF’s
- Nesting IF with AND, OR Functions
- SUMIF
- COUNTIF
Excel Above and Beyond
Target Audience
Aimed at high level users of Excel, this will look at more advanced features including techniques for nesting formulae using a variety of functions, with a workshop feel to the session, as well as working with array formulae and exploring more advanced features in Conditional Formatting. It includes an informative introduction into using PowerPivots, part of the Power BI tools within Excel, as well as an introduction into recording Macros to automate reporting.
Delegate Pre-Requisites
Aimed at delegates with a high level of experience using Excel, preferably having attended the Excel Beyond the Basics course.
All delegates should have a sound knowledge of building formulae using various functions but who require to further their skills by using more complex formulae building.
Course Duration
This is a One Day Course
Course Details
Courses commence at 9:30 a.m. through to 4:30 p.m.
A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated
All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate
Visual Basic
If you want to learn more about creating and editing macros, we offer several Visual Basic courses – see our Excel course outlines page from our website for more information and links to the Visual Basic courses available!
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
Advanced Filters
Dependent Data Validation
Conditional Formatting – More Features
- Custom conditional formatting rules
- Utilising formulae within conditional formatting
- Applying formatting to entire row
- Creating a dynamic record highlight
- Comparing values
- Creating banded columns
- Comparing data on different sheets
- Formatting values using LARGE, SMALL
- Using dates in conditional formatting
- Separating dates within a list
- Using stop if true
- Conditional formatting workshop
Introduction to PowerPivot
- What is PowerPivot?
- How to access data from multiple sources
- How to generate a PowerPivot Table & Chart
- Demonstration of features
Introduction to Macros
- How to record a Macro
- How to access recorded Macro
- Tips & Tricks on Macro Recorder
Excel Creating Dashboards
Target Audience
Excel Dashboards provide insight, analysis and even alerts. They are interactive and dynamic, and have a wide range of applications, providing help with project management, customer service, KPI management and forecasting displaying key trends, comparisons and data in small graphs or tables. They are also often interactive, which allows the user to filter data and switch views easily.
This course will help you to create advanced workbooks and worksheets and graphical features that you then use to create dashboards giving delegates the fundamentals to analyse data, extract actionable intelligence from it and present that information to decision makers is in a clear visual and dynamic manner.
Delegate Pre-Requisites
Attendees should be regular users of excel, with a good knowledge of working with excel functions and formulas. Previous use of Excel Charts and Pivot Tables would be advantageous.
Course Duration
This is a One Day Course
Course Details
Courses commence at 9:30 a.m. through to 4:30 p.m.
A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated
All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate
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
Delegate Pre-Requisites
Aimed at delegates with a good working knowledge of Excel preferably having attended the Excel Beyond the Macro Recorder course
Course Duration
This is a One Day Course
Course Details
Courses commence at 9:30 a.m. through to 4:30 p.m.
A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated
All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate
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
Conditionals
- IF, ELSE, ELSEIF
- Select Case
Loops
- FOR Loop
- DO Loop
Excel VBA The Next Level
Target Audience
This course is a follow-on from out Excel VBA The Basics course. It takes interaction with the Excel environment further, allowing the dynamic creation and population of worksheets and charts, and moves onto a comprehensive coverage of application-designed forms and dialogs. It also takes a closer look at the VBA language which is essential for this interaction.
Delegate Pre-Requisites
Aimed at delegates with experience of using Excel VBA preferably having attended the Excel Beyond the Macro Recorder and VBA The Basics course
Course Duration
This is a One Day Course
Course Details
Courses commence at 9:30 a.m. through to 4:30 p.m.
A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated
All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate
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
Excel & Other Applications
You’ve been there before. you are tearing your hair out trying to finish the presentation due for a monthly performance meeting. The model is refreshed, and now it just takes a LOT of copying, pasting, and positioning to get the PowerPoint ready. Finally, the slides are finished…, until you read a new message from your boss requesting a minor change. But of course the changes means you have to start all over with the copy and pastes…
There is always a better way! Excel VBA makes it easy to automate the entire process. So when a simple change is requested, the presentation is automatically generated with the click of a button. No more wasting time!
Learn how to interact with other applications using Excel Macros
- Excel to PowerPoint
- Excel to Word
- Excel to Outlook (Demonstration on trainer laptop only)
Excel VBA Advanced
Target Audience
This course is the last in our series of courses exploiting Visual Basic programming within Excel. It covers the major subject of interacting with databases, including Access and others, using many practical examples. It also covers how your work can be used as a distributable and reusable solution, including making it an Excel Add-In.
Delegate Pre-Requisites
Aimed at delegates with experience of using Excel VBA preferably having attended the Excel VBA The Next Level course
Course Duration
This is a One Day Course
Course Details
Courses commence at 9:30 a.m. through to 4:30 p.m.
A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated
All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate
The Content
Databases and ADO
- What is a database, and how can it interact with Excel?
- Microsoft’s database technologies
- Active Data Objects – ADO
- Adding references
- Connecting to a database – using connection strings
- Returning data using Recordsets
- Reading column values
- Adding data
- Building and executing SQL
- Taking it further
Creating an Add-In
- Project properties
- Protection and security
- Using macros from shortcut menus
- Creating an Add-In
- Saving Add-Ins
- Using Add-Ins
- Auto_Open and Auto_Close
- Distributing Add-Ins
Excel Beyond the Macro Recorder
Target Audience
This course is a great step into using macros where delegates are shown how to record the majority of the macro using keystrokes and then guided into understanding the basics of the VBA coding behind a macro and have a go at basic macro editing with lots of tips and tricks to avoid run-time errors!
Delegate Pre-Requisites
Aimed at delegates with a good working knowledge of Excel. No previous knowledge of Macros or VBA is required.
Course Duration
This is a One Day Course
Course Details
Courses commence at 9:30 a.m. through to 4:30 p.m.
A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated
All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate
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
Take a closer look at the macro
- Understand basic recorded macro syntax
- Basic macro editing
- Basic debug tools
Running Multiple Macros together
- How to create a set of macros to complete a task
- How to allow one macro to run another