Microsoft Excel 2013 Advanced. Online Excel Training Course

Course Curriculum

Chapter 1: Getting Started

Lecture 1: Working Files – Download These First

Lecture 2: 0101 Will I Be Able To Keep Up?

Lecture 3: 0102 Using The Included Files

Lecture 4: 0103 New For 2013: Its All In The Cloud

Lecture 5: 0104 The Very Clever Flash Fill

Chapter 2: Using Mathematical Functions

Lecture 1: 0201 The Basic SUM, COUNT, MIN, And MAX

Lecture 2: 0202 AVERAGE, MODE, MEAN, And MEDIAN

Lecture 3: 0203 SUMIF For Selective Adding Up

Lecture 4: 0204 COUNTIF For Selective Counting

Lecture 5: 0205 AVERAGEIF For The Mean Of Selected Cells

Lecture 6: 0206 Multiple Criteria Within SUMIF, COUNTIF, And AVERAGEIF

Lecture 7: 0207 Area And Volume Calculations

Chapter 3: IF Functionality

Lecture 1: 0301 IF Syntax And Uses

Lecture 2: 0302 Nesting The IF Statement

Lecture 3: 0303 Use Of The AND Operator Within An IF

Lecture 4: 0304 Use Of The OR Operator Within An IF

Lecture 5: 0305 The NOT Operator Within AND And OR Statements

Lecture 6: 0306 Display Cell Formulas In Another Cell

Chapter 4: Performing Data Lookups

Lecture 1: 0401 VLOOKUP: Syntax And Usage

Lecture 2: 0402 VLOOKUP In Live Action

Lecture 3: 0403 HLOOKUP: Variation On A Theme

Lecture 4: 0404 Using A Near Match In The Lookup

Lecture 5: 0405 Dealing With Missing Data In A Lookup

Lecture 6: 0406 Managing The Lookup Table

Lecture 7: 0407 Lookups Nested Within Lookups

Chapter 5: Sparklines

Lecture 1: 0501 Creating A Sparkline

Lecture 2: 0502 Altering The Design Of Sparklines

Lecture 3: 0503 Dealing With Empty Cells

Lecture 4: 0504 Comparing Sparklines Within A Sparkline Group

Lecture 5: 0505 Removing Sparklines From A Worksheet

Chapter 6: Further Mathematical Functions

Lecture 1: 0601 Working With Time In Excel

Lecture 2: 0602 Calculations Using Time

Lecture 3: 0603 Useful Time And Date Functions

Lecture 4: 0604 Rounding Decimal Places

Lecture 5: 0605 MOD And INT Functions And Uses

Lecture 6: 0606 Generate And Use A Random Number

Lecture 7: 0607 Loan And Investment Calculations

Lecture 8: 0608 Loan Calculation Elements And Functions

Chapter 7: Outlining

Lecture 1: 0701 Create An Outline Automatically

Lecture 2: 0702 Adding An Outline Manually

Lecture 3: 0703 Editing And Removing Outlining

Chapter 8: Scenarios

Lecture 1: 0801 Setting Up A Set Of Scenarios

Lecture 2: 0802 Displaying And Editing The Different Scenarios

Lecture 3: 0803 How To Work Out Which Scenario You Are Displaying

Lecture 4: 0804 Merging And Deleting Scenarios

Lecture 5: 0805 Producing A Summary Of Scenarios

Chapter 9: Custom Views

Lecture 1: 0901 Custom Views Explained

Lecture 2: 0902 Use Of Outlining To Help Setup Custom Views

Lecture 3: 0903 Editing And Deleting Custom Views

Lecture 4: 0904 Add Quick Access To Custom Views

Chapter 10: Functions For Manipulating Text

Lecture 1: 1001 LEFT And RIGHT: Text Manipulation

Lecture 2: 1002 LEN And TRIM: String Extractions

Lecture 3: 1003 FIND And MID: Text Functions Working Together

Lecture 4: 1004 CONCATENATE: Building Strings From Multiple Cells

Lecture 5: 1005 Changing Case Functions

Lecture 6: 1006 REPLACE And SUBSTITUTE: Two More String Manipulation Functions

Lecture 7: 1007 Use Of CHAR Function For More Obscure Characters

Lecture 8: 1008 Formatting Numeric And Date Values Using TEXT

Lecture 9: 1009 Keeping The Values Created By String Manipulation

Chapter 11: Arrays

Lecture 1: 1101 Arrays And Creating A New Array Formula

Lecture 2: 1102 Array Formulas With IF Statements

Lecture 3: 1103 Conditional Evaluation With No IFs

Lecture 4: 1104 The Array-Only TRANSPOSE Function

Chapter 12: Useful Data Functions

Lecture 1: 1201 Using The MATCH Function

Lecture 2: 1202 How The INDEX Function Works

Lecture 3: 1203 Handling Out Of Range Index Requests

Lecture 4: 1204 The CHOOSE Lookup Function

Lecture 5: 1205 MATCH And INDEX Functions Working Together

Chapter 13: Some Other Useful Functions

Lecture 1: 1301 Introducing IS Functions

Lecture 2: 1302 Error Checking Using ISERR, ISERROR, And IFERROR

Lecture 3: 1303 OFFSET Function Syntax

Lecture 4: 1304 OFFSET Function: Creating A Dynamic Named Range

Lecture 5: 1305 INDIRECT Function To Build Dynamic Formulas

Lecture 6: 1306 Dealing With INDIRECT Errors

Lecture 7: 1307 The CELL Function And Determining File Or Sheet Names

Chapter 14: Auditing And Troubleshooting Formulas

Lecture 1: 1401 What Are Tracer Arrows

Lecture 2: 1402 Adding And Removing Tracer Arrows

Lecture 3: 1403 Auditing Tools: Error Checking And Tracing

Lecture 4: 1404 Step-By-Step Formula Processing

Lecture 5: 1405 Using The Watch Window In Troubleshooting

Chapter 15: PivotTables

Lecture 1: 1501 What Is A PivotTable?

Lecture 2: 1502 The New Recommended PivotTable Route

Lecture 3: 1503 Creating Your Own PivotTables

Lecture 4: 1504 Changing The Formatting And Formulas In PivotTable Summaries

Lecture 5: 1505 Creating Multiple PivotTables On The Same Dataset