Microsoft Excel 2013 Advanced Online Excel Training Course 

Working Files Download These First

Will I Be Able To Keep Up?

Using The Included Files

New For 2013: Its All In The Cloud

The Very Clever Flash Fill

Section 2: Using Mathematical Functions

The Basic SUM, COUNT, MIN, And MAX

AVERAGE, MODE, MEAN, And MEDIAN

SUMIF For Selective Adding Up

COUNTIF For Selective Counting

AVERAGEIF For The Mean Of Selected Cells

Multiple Criteria Within SUMIF, COUNTIF, And AVERAGEIF

Area And Volume Calculations

Section 3: IF Functionality

IF Syntax And Uses

Nesting The IF Statement

Use Of The AND Operator Within An IF

Use Of The OR Operator Within An IF

The NOT Operator Within AND And OR Statements

Display Cell Formulas In Another Cell

Section 4: Performing Data Lookups

VLOOKUP: Syntax And Usage

VLOOKUP In Live Action

HLOOKUP: Variation On A Theme

Using A Near Match In The Lookup

Dealing With Missing Data In A Lookup

Managing The Lookup Table

Lookups Nested Within Lookups

Section 5: Sparklines

Creating A Sparkline

Altering The Design Of Sparklines

Dealing With Empty Cells

Comparing Sparklines Within A Sparkline Group

Removing Sparklines From A Worksheet

Section 6: Further Mathematical Functions

Working With Time In Excel

Calculations Using Time

Useful Time And Date Functions

Rounding Decimal Places

MOD And INT Functions And Uses

Generate And Use A Random Number

Loan And Investment Calculations

Loan Calculation Elements And Functions

Section 7: Outlining

Create An Outline Automatically

Adding An Outline Manually

Editing And Removing Outlining

Section 8: Scenarios: Setting Up A Set Of Scenarios

Displaying And Editing The Different Scenarios

How To Work Out Which Scenario You Are Displaying

Merging And Deleting Scenarios

Producing A Summary Of Scenarios

Section 9: Custom Views

Custom Views Explained

Use Of Outlining To Help Setup Custom Views

Editing And Deleting Custom Views

Add Quick Access To Custom Views

Section 10: Functions For Manipulating Text

LEFT And RIGHT: Text Manipulation

LEN And TRIM: String Extractions

FIND And MID: Text Functions Working Together

CONCATENATE: Building Strings From Multiple Cells

Changing Case Functions

REPLACE And SUBSTITUTE: Two More String Manipulation Functions

Use Of CHAR Function For More Obscure Characters

Formatting Numeric And Date Values Using TEXT

Keeping The Values Created By String Manipulation

Section 11: Arrays

Arrays And Creating A New Array Formula

Array Formulas With IF Statements

Conditional Evaluation With No Ifs

The Array Only TRANSPOSE Function

Section 12: Useful Data Functions

Using The MATCH Function

How The INDEX Function Works

Handling Out Of Range Index Requests

The CHOOSE Lookup Function

MATCH And INDEX Functions Working Together

Section 13: Some Other Useful Functions

Introducing IS Functions

Error Checking Using ISERR, ISERROR, And IFERROR

OFFSET Function Syntax

OFFSET Function: Creating A Dynamic Named Range

INDIRECT Function To Build Dynamic Formulas

Dealing With INDIRECT Errors

The CELL Function And Determining File Or Sheet Names

Section 14: Auditing And Troubleshooting Formulas

What Are Tracer Arrows

Adding And Removing Tracer Arrows

Auditing Tools: Error Checking And Tracing

Step By Step Formula Processing

Using The Watch Window In Troubleshooting

Section 15: PivotTables

What Is A PivotTable?

The New Recommended PivotTable Route

Creating Your Own PivotTables

Changing The Formatting And Formulas In PivotTable Summaries

Creating Multiple PivotTables On The Same Dataset

Moving And Deleting PivotTables

Making Use Of The Report Filter Options

Sorting The PivotTable Columns

Refreshing A PivotTable

Drilling Down Behind The Pivot Numbers

Multiple Fields In Row, Column, Or Data Sections

Controlling Grand Totals And Subtotals

Dealing With Empty Cells And Other Additional Options

PivotTable Styles

Creating Your Own PivotTable Styles

Creating And Using Calculated Fields

Using The New Timeline Filter Option

Adding And Using The Data Slicer

Using Data From An SQL Server In A PivotTable

Managing The External Connection To SQL Server