Excel Level 3

Module 1: Logical Functions

Using the function library

Manually entering a function

Applying Logical Functions

The IF function

The AND Function

The OR Function

Working with Nested Functions

Nested IF Function

Nested IF_AND Function

Nested IF_OR Function

Module 2: Creating Named Ranges and Validation Lists

Working with Names and Ranges

What are named ranges?

Defining Range Names

Create from selection

Using the name box

Using range names in formulas/functions

Use in Formula

Paste Name Dialogue

Editing a Range Name

Deleting a Range Name

Module 3: Data Validation

Validating Your Data

Creating Data Validation Settings

Creating a Validation List

Manually Entering Data

Importing a Pre-created List

Using a Range Name

Module 4: Working with Lookup Functions

Creating a VLOOKUP

Finding an exact match with VLOOKUP

Finding the closest match with VLOOKUP

Module 5: PivotTables and PivotCharts

What is a PivotTable?

Preparing Data to create a PivotTable

Creating a PivotTable

Adding fields to the PivotTable

Rearranging PivotTable data

Hiding and showing Field data

The PivotTable Tools Ribbon

Modifying calculations and data area display

Refreshing a PivotTable

Using Slicers to manipulate PivotTables

Slicers

Insert the Slicer

Creating a PivotChart

 Module 6: Using 'What-If' Analysis Tools

Using Goal Seek

Using scenarios in Excel

Creating a scenario

Module 7: Macros

What is a Macro?

Absolute Macros

Relative Macros

Creating a Macro

The Developer Ribbon

Recording a Macro

Running a Macro

Recording a Relative Reference Macro

Running a Relative Reference Macro

Assigning keystrokes to a Macro

Macro security

Disable all Macros without notification

Disable all Macros with notification

Disable all Macros except digitally signed Macros

Enable all Macros

Quick reference: Excel shortcuts