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.
Course Content
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
- 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
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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
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
- 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