Excel 365/2016 Level 3

Excel 365/2016 Level 3

The videos in this suite of Lessons are for those who wish to delve deeper. Specifically through PivotTables you will enhance the presentation and organisation of your data. Investigate also the value of the LOOKUP, INDEX and MATCH functions and learn to provide focus to text and figures through Conditional Formatting.

Subscribe to this Course

Course Content

Expand All | Collapse All

Lessons Status

1

Lesson 1 – Create & Work with a Basic PivotTable

Excel 2016 – 3.1.1 – View and Setup Data

Excel 2016 – 3.1.2 – Create a Basic PivotTable

Excel 2016 – 3.1.3 – Add Currency Style To Pivot Data

Excel 2016 – 3.1.4 – Group a PivotTable By Date

Excel 2016 – 3.1.5 – Filter and Ungroup Pivot Data

Excel 2016 – 3.1.6 – Drill Into Pivot Data

Excel 2016 – 3.1.7 – Drilldown Into Pages

Excel 2016 – 3.1.8 – Create a PivotChart

Excel 2016 – 3.1.9 – Insert and Use Slicers

Excel 2016 – 3.1.10 – Refresh the Data

Excel 2016 – 3.1.11 – Set a PivotTable To Refresh On Open

2

Lesson 2 – Customise PivotTables

Excel 2016 – 3.2.1 – How To Display Blank Cells

Excel 2016 – 3.2.2 – How To Rename Field Headings

Excel 2016 – 3.2.3 – Change The Pivot Layout

Excel 2016 – 3.2.4 – Apply Pivot Styles

3

Lesson 3 – Sorting & Filtering in a PivotTable

Excel 2016 – 3.3.1 – Basic Sorting

Excel 2016 – 3.3.2 – Create a Manual Sort

Excel 2016 – 3.3.3 – Custom List Sort

Excel 2016 – 3.3.4 – Filter By Date

Excel 2016 – 3.3.5 – Show Top 5 Locations

Excel 2016 – 3.3.6 – Using Slicers With Multiple Pivots

4

Lesson 4 – Grouping in a PivotTable

Excel 2016 – 3.4.1 – Group by Numbers

Excel 2016 – 3.4.2 – Group Dates by Week

Excel 2016 – 3.4.3 – Group Text Fields

5

Lesson 5 – Calculating in a PivotTable

Excel 2016 – 3.5.1 – Using Average and Count

Excel 2016 – 3.5.2 – Using % Difference From

Excel 2016 – 3.5.3 – Using % of Grand Total

Excel 2016 – 3.5.4 – Using Rank

Excel 2016 – 3.5.5 – Using % Of

Excel 2016 – 3.5.6 – Using % of Row Total

Excel 2016 – 3.5.7 – Using Calculated Field

Excel 2016 – 3.5.8 – Using Calculated Item

Excel 2016 – 3.5.9 – Pivot Sorting Issue

6

Lesson 6 – Advanced Charts

Excel 2016 – 3.6.1 – Create a Secondary Axis

Excel 2016 – 3.6.2 – Chart Two Datasets

Excel 2016 – 3.6.3 – Create a Trending Chart

Excel 2016 – 3.6.4 – Create a Bar Chart

Excel 2016 – 3.6.5 – Trending Trick 1

Excel 2016 – 3.6.6 – Trending Trick 2

Excel 2016 – 3.6.7 – Chart With Checkbox Control

Excel 2016 – 3.6.8 – Chart With Scroll Bar

7

Lesson 7 – Using the LOOKUP Functions

Excel 2016 – 3.7.1 – About LOOKUPs

Excel 2016 – 3.7.2 – Create a VLOOKUP

Excel 2016 – 3.7.3 – How VLOOKUP Can Go Wrong

Excel 2016 – 3.7.4 – Create a VLOOKUP That Doesn’t Require an Exact Result

Excel 2016 – 3.7.5 – Combine VLOOKUP and Naming

Excel 2016 – 3.7.6 – Create a Drop-down Menu for the Lookup Value

Excel 2016 – 3.7.7 – Create a HLOOKUP

Excel 2016 – 3.7.8 – Create a Vector LOOKUP

Excel 2016 – 3.7.9 – Create an Array LOOKUP

Excel 2016 – 3.7.10 – Create a VLOOKUP to Another Workbook

8

Lesson 8 – Create Left LOOKUPs Using INDEX & MATCH

Excel 2016 – 3.8.1 – Why Do You Need a Left LOOKUP?

Excel 2016 – 3.8.2 – How MATCH Works

Excel 2016 – 3.8.3 – How INDEX Works

Excel 2016 – 3.8.4 – Combine INDEX and MATCH

Excel 2016 – 3.8.5 – Nest 2 MATCH Functions Into INDEX

Excel 2016 – 3.8.6 – Use INDEX and MATCH Across Worksheets

Excel 2016 – 3.8.7 – Left LOOKUP With Naming

9

Lesson 9 – Conditional Formatting with Functions

Excel 2016 – 3.9.1 – Display Icons Using a Formula

Excel 2016 – 3.9.2 – Show Today’s Date Plus 7 Days

Excel 2016 – 3.9.3 – Show Any Range of Dates

Excel 2016 – 3.9.4 – CF With Sales Target

Excel 2016 – 3.9.5 – CF With Text

Excel 2016 – 3.9.6 – CF With VLOOKUP

Excel 2016 – 3.9.7 – CF With Banded Rows

Excel 2016 – 3.9.8 – Create a Risk Register

10

Lesson 10 – SUMPRODUCT

Excel 2016 – 3.10.1 – Introduction to SUMPRODUCT

Excel 2016 – 3.10.2 – Using Three Arrays

Excel 2016 – 3.10.3 – Using Four Arrays

Excel 2016 – 3.10.4 – SUMPRODUCT With Conditions

Excel 2016 – 3.10.5 – How Conditions Work

Excel 2016 – 3.10.6 – Using Two Conditions

Excel 2016 – 3.10.7 – Using Three Conditions

Excel 2016 – 3.10.8 – Calculate a Table of Values

11

Lesson 11 – AGGREGATE

Excel 2016 – 3.11.1 – About the AGGREGATE Function

Excel 2016 – 3.11.2 – Using the Reference Form with SUM

Excel 2016 – 3.11.3 – Using the Array Form with LARGE

Excel 2016 – 3.11.4 – Using the Array Form with SMALL

Excel 2016 – 3.11.5 – Ignore Error Values Using Reference Form

Excel 2016 – 3.11.6 – Ignore Error Values Using Array Form

Excel 2016 – 3.11.7 – AGGREGATE Like SUMPRODUCT

Excel 2016 – 3.11.8 – AGGREGATE as a LOOKUP

12

Lesson 12 – Macros

Excel 2016 – 3.12.1 – What is a Macro?

Excel 2016 – 3.12.2 – Create a Macro

Excel 2016 – 3.12.3 – Save a Macro in a Workbook

Excel 2016 – 3.12.4 – Open a Workbook Containing a Macro

Excel 2016 – 3.12.5 – Create a Button for a Macro

Excel 2016 – 3.12.6 – Move a Macro into the Personal Workbook

13

Lesson 13 – What-if Analysis

Excel 2016 – 3.13.1 – About Data Tables

Excel 2016 – 3.13.2 – Create a One-input Data Table

Excel 2016 – 3.13.3 – Data Table with Multiple Formulas

Excel 2016 – 3.13.4 – Start-up Data Table

Excel 2016 – 3.13.5 – Two-input Data Table

Excel 2016 – 3.13.6 – The Scenario Manager

Excel 2016 – 3.13.7 – Named Cells with Scenarios

Excel 2016 – 3.13.8 – Create a Summary Report

Excel 2016 – 3.13.9 – Goal Seek

Excel 2016 – 3.13.10 – About Solver

Excel 2016 – 3.13.11 – Add Solver to Excel

Excel 2016 – 3.13.12 – Use Solver and Reports