Excel 2013 Level 3

Excel 2013 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 and Work with a Basic PivotTable

Excel 2013 – 3.1.1 – View and Setup Data

Excel 2013 – 3.1.2 – Create a Basic PivotTable

Excel 2013 – 3.1.3 – Add Currency Style to Pivot Data

Excel 2013 – 3.1.4 – Group a PivotTable by Date

Excel 2013 – 3.1.5 – Filter and Ungroup Pivot Data

Excel 2013 – 3.1.6 – Drill into Pivot Data

Excel 2013 – 3.1.7 – Drilldown into Pages

Excel 2013 – 3.1.8 – Create a PivotChart

Excel 2013 – 3.1.9 – Insert and Use Slicers

Excel 2013 – 3.1.10 – Refresh the Data

Excel 2013 – 3.1.11 – Set Automatic Refresh on Open

2

Lesson 2 – Customise PivotTables

Excel 2013 – 3.2.1 – How to Display Blank Cells

Excel 2013 – 3.2.2 – How to Rename Field Headings

Excel 2013 – 3.2.3 – Change the Pivot Layout

Excel 2013 – 3.2.4- Apply Pivot Styles

3

Lesson 3 – Sorting & Filtering in a PivotTable

Excel 2013 – 3.3.1 – Basic Sorting in a PivotTable

Excel 2013 – 3.3.2 – Create a Manual Sort

Excel 2013 – 3.3.3 – Create a Custom List Sort

Excel 2013 – 3.3.4 – Filter PivotTable Information by Date Filter by Date

Excel 2013 – 3.3.5 – Filter to Show the Top 5 Locations

Excel 2013 – 3.3.6 – Using Slicers with Multiple Pivots

4

Lesson 4 – Grouping in a PivotTable

Excel 2013 – 3.4.1 – Group by Numbers

Excel 2013 – 3.4.2 – Group Dates by Week

Excel 2013 – 3.4.3 – Group Text Fields

5

Lesson 5 – Calculating in a PivotTable

Excel 2013 – 3.5.1 – Create Average and Count Calculations

Excel 2013 – 3.5.2 – Using % Difference From

Excel 2013 – 3.5.3- Using % of Grand Total

Excel 2013 – 3.5.4 – Using Rank

Excel 2013 – 3.5.5 – Using % Of

Excel 2013 – 3.5.6 – Using % of Row Total

Excel 2013 – 3.5.7- Create a Calculated Field

Excel 2013 – 3.5.8 – Create a Calculated Item

6

Lesson 6 – Using the LOOKUP Functions

Excel 2013 – 3.6.1- About LOOKUPs

Excel 2013 – 3.6.2 – Create a VLOOKUP

Excel 2013 – 3.6.3 – How VLOOKUP can go wrong

Excel 2013 – 3.6.4 – Create a VLOOKUP that Doesn’t Require an Exact Result

Excel 2013 – 3.6.4 – Create a VLOOKUP that Doesn't Require an Exact Result

Excel 2013 – 3.6.5 – Combine VLOOKUP and Naming

Excel 2013 – 3.6.6 – Create a Drop-down Menu for the VLOOKUP Value

Excel 2013 – 3.6.7 – Create a HLOOKUP

Excel 2013 – 3.6.8 – Create a Vector LOOKUP

Excel 2013 – 3.6.9 – Create an Array LOOKUP

Excel 2013 – 3.6.10 – Create a VLOOKUP to Another Workbook

7

Lesson 7 – Create Left LOOKUPs using INDEX and MATCH

Excel 2013 – 3.7.1 – Why Do You Need a Left LOOKUP?

Excel 2013 – 3.7.2 – How the MATCH Function Works

Excel 2013 – 3.7.3 – How the INDEX Function Works

Excel 2013 – 3.7.4 – Combine INDEX and MATCH

Excel 2013 – 3.7.5 – Nest Two MATCH Functions into INDEX

Excel 2013 – 3.7.6 – Use INDEX and MATCH across Worksheets

Excel 2013 – 3.7.7 – Left LOOKUP with Naming

8

Lesson 8 – Conditional Formatting with Functions

Excel 2013 – 3.8.1 – Display Icons using a Formula

Excel 2013 – 3.8.2 – Show Today’s Date plus 7 Days

Excel 2013 – 3.8.2 – Show Today's Date plus 7 Days

Excel 2013 – 3.8.3 – Show any Range of Dates

Excel 2013 – 3.8.4 – Conditional Formatting with Target Sales

Excel 2013 – 3.8.5 – Conditional Formatting with Text

Excel 2013 – 3.8.6 – Conditional Formatting with VLOOKUP

Excel 2013 – 3.8.7 – Conditional Formatting to Create Banded Rows

Excel 2013 – 3.8.8 – Create a Risk Register

9

Lesson 9 – Macros

Excel 2013 – 3.9.1 – What is a Macro?

Excel 2013 – 3.9.2 – Create a Macro

Excel 2013 – 3.9.3 – Saving a Macro in a Workbook

Excel 2013 – 3.9.4 – Open a Workbook Containing a Macro

Excel 2013 – 3.9.5 – Create a Button for a Macro

Excel 2013 – 3.9.6 – Move a Macro into the Personal Workbook

10

Lesson 10 – The SUMPRODUCT Function

Excel 2013 – 3.10.1 – Introduction to SUMPRODUCT

Excel 2013 – 3.10.2 – Using Three Arrays

Excel 2013 – 3.10.3 – Using Four Arrays

Excel 2013 – 3.10.4 – SUMPRODUCT with Conditions

Excel 2013 – 3.10.5 – How Conditions Work

Excel 2013 – 3.10.6 – Using Two Conditions

Excel 2013 – 3.10.7 – Using Three Conditions

Excel 2013 – 3.10.8 – Calculate a Table of Values

11

Lesson 11 – AGGREGATE

Excel 2013 – 3.11.1 – About AGGREGATE

Excel 2013 – 3.11.2 – Ignore Hidden Rows using the Reference Form

Excel 2013 – 3.11.3 – Array Form Large

Excel 2013 – 3.11.4 – Array Form Small

Excel 2013 – 3.11.5 – Ignore Error Values

Excel 2013 – 3.11.6 – Like SUMPRODUCT

Excel 2013 – 3.11.7 – Use as a LOOKUP