Microsoft Office Training ltd

​

Advanced Functions

COUNT, COUNTA, COUNTIF, COUNTBLANK, SUMIF, SUBTOTAL

IF, OR, AND, IFERROR

VLOOKUP to lookup Exact Values and Approximate values

MATCH, INDIRECT, ROW/COLUMN, INDEX, OFFSET

Analysing Data

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

PivotTables

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

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

Import Data

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

Working with Form Controls; Buttons, Option Buttons and Scroll Bars

Introduction to Macros

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

​

​