Microsoft Excel Advanced

Please reload

Please reload

Advanced Functions

SUMIF & COUNTIF

Nested IFs

Reference functions;

     VLOOKUP, INDIRECT, MATCH, ROW/COLUMN,
     INDEX, OFFSET

Logical Functions combined with Reference Functions

PMT

GETPIVOTDATA

TEXT Functions

Attaching security to a spreadsheet and workbook

Protect a worksheet
Protect a workbook

Analysing Data

What If Analysis using Goal Seek

Use scenarios to consider many different variables

Use Data Tables to see the effects of one or two variables in a formula

Using Solver addin to optimise variables

Consolidating Data

Linking to External Workbooks

PivotTables

Create a PivotTable to analyse worksheet data

Add or remove fields in a PivotTable

Change the source data for a PivotTable

Working with external data sources

Update (refresh) data in a PivotTable

Working with Statistical Functions

Calculate a percentage for subtotals in a PivotTable

Change the data layout of a PivotTable

Filter data in a PivotTable

Using the Slicer and Timeline to filter the PivotTable

Group or ungroup data in a PivotTable report

Create a PivotChart

Formula auditing

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

Data Validation

Number Validation

Data List Validation

Message Prompts and Alerts

Conditional Data Validation

Data Validation Errors

Consolidation

Consolidate data by position

Consolidate data by category

Use a formula to consolidate data

Outline (group) data in a worksheet

Create an outline of rows

Create an outline of columns

Copy outlined data

Hide or remove an outline

Connect external data to your workbook

Connect data in another workbook to your workbook

From Text or CSV

From Access

From Microsoft Query

Working with Form Controls; Buttons, Option Buttons, Check Boxes Scroll Bars

Form Controls vs Active X Controls

Adding Form Controls to a Worksheet

Understanding How Controls Work

Changing Control Properties

Using the Cell Link to Display the Selection

Protecting a Worksheet With Controls

Macros

Record a macro

Change macro security settings

Edit the macro

Create a macro by using VBA

Copy part of a macro to create another macro

Delete a macro

Assign a macro to a button

Duration - 2 days

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

Excel Advanced

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

Darren Barkey - Direct Wines

Excel Advanced

Great content and learnt a lot. Really enjoyable :)

Kristie-Lee Ryan - Stirling Ackroyd

Excel Advanced

Please reload