Microsoft Excel Advanced

Work with advanced lookup and reference functions

Create macros to automate common tasks

Use advanced techniques to analyse data

Create PivotTables and PivotCharts

Work with data validation tools

Import and export data

Best Training Ever!

Just finished a bespoke 1-1 training course in Excel Advanced, Macros & VBA. Pedro is an excellent trainer, imparting his skills and knowledge in the best way - appropriately to audience skills, knowledge and ability. Pedro is always approachable, encouraging and supportive, giving delegates the optimum learning environment. I would not hesitate to recommend Pedro as a trainer, whatever your level of ability.

Amanda Morris - Treasury & Systems Accountant at Reall - Real Equity for All

The course was very interesting and engaging and will definitely be put to use. The trainer was very helpful and charismatic.

Marving Lopez - AM Best Europe

Pedro was excellent. Very knowledgeable, clear and great rapport with class.

Darren Barkey - Direct Wines

Great content and learnt a lot. Really enjoyable :)

Kristie-Lee Ryan - Stirling Ackroyd

COUNT, COUNTA, COUNTIF, COUNTBLANK, SUMIF, SUBTOTAL

IF, OR, AND, IFERROR

VLOOKUP to lookup Exact Values and Approximate values

MATCH, INDIRECT, ROW/COLUMN, INDEX, OFFSET

PMT function to calculate a monthly payment of a loan

What If Analysis using Goal Seek

Use scenarios to consider many different variables

Using Solver Add-in to optimize variables

Create a PivotTable to analyse worksheet data

Add or remove fields in a PivotTable

Change the data source for a PivotTable

Working with external data sources

Using different Statistical Functions

Calculate a Running Total, % of Grant Total, Difference From…

Using the Slicer and Timeline to filter the PivotTable

Using Filters in a PivotTable

Group by Date, Auto Group by ranges of values, Custom Group

Create a PivotChart

The GETPIVOTDATA function

Precedents and dependents

Error checking

Watch a formula and its result by using the Watch Window

Evaluate a nested formula one step at a time

Number Validation

Data List Validation

Message Prompts and Alerts

Conditional Data Validation

Data Validation Errors

Consolidate data by position

Consolidate data by category

Use a formula to consolidate data

Create an outline of rows

Create an outline of columns

Copy outlined data

Hide or remove an outline

Using the Power Query Editor to import data

From Text and CSV, From Access, From Web, From another Workbook

Using the Power Query Editor to; Transform your data by Selecting which fields to import, by Splitting Columns, Changing Data Type and more.

Deleting Applied Steps

Refreshing your data

Change Macro Security settings

How to Record a macro

Some Macro Examples

Delete a macro

Edit the macro to view the VBA code

Copy part of a macro to create another macro

Assign a macro to a button