Books & Videos

Table of Contents

Chapter: Getting Started

Will I Be Able To Keep Up?

03m 57s

Using The Included Files

01m 17s

New For 2013: Its All In The Cloud

04m 3s

The Very Clever Flash Fill

04m 13s

Chapter: Using Mathematical Functions

The Basic SUM, COUNT, MIN, And MAX

03m 39s


04m 4s

SUMIF For Selective Adding Up

06m 30s

COUNTIF For Selective Counting

03m 18s

AVERAGEIF For The Mean Of Selected Cells

03m 29s

Multiple Criteria Within SUMIF, COUNTIF, And AVERAGEIF

07m 6s

Area And Volume Calculations

04m 9s

Chapter: IF Functionality

IF Syntax And Uses

07m 55s

Nesting The IF Statement

07m 26s

Use Of The AND Operator Within An IF

06m 29s

Use Of The OR Operator Within An IF

04m 3s

The NOT Operator Within AND And OR Statements

04m 28s

Display Cell Formulas In Another Cell

04m 11s

Chapter: Performing Data Lookups

VLOOKUP: Syntax And Usage

04m 51s

VLOOKUP In Live Action

06m 53s

HLOOKUP: Variation On A Theme

04m 42s

Using A Near Match In The Lookup

03m 44s

Dealing With Missing Data In A Lookup

04m 55s

Managing The Lookup Table

05m 3s

Lookups Nested Within Lookups

06m 29s

Chapter: Sparklines

Creating A Sparkline

04m 58s

Altering The Design Of Sparklines

04m 26s

Dealing With Empty Cells

02m 24s

Comparing Sparklines Within A Sparkline Group

04m 18s

Removing Sparklines From A Worksheet

02m 14s

Chapter: Further Mathematical Functions

Working With Time In Excel

10m 20s

Calculations Using Time

03m 58s

Useful Time And Date Functions

04m 45s

Rounding Decimal Places

04m 32s

MOD And INT Functions And Uses

04m 42s

Generate And Use A Random Number

05m 36s

Loan And Investment Calculations

05m 58s

Loan Calculation Elements And Functions

04m 4s

Chapter: Outlining

Create An Outline Automatically

03m 47s

Adding An Outline Manually

03m 22s

Editing And Removing Outlining

06m 39s

Chapter: Scenarios

Setting Up A Set Of Scenarios

06m 59s

Displaying And Editing The Different Scenarios

03m 12s

How To Work Out Which Scenario You Are Displaying

04m 6s

Merging And Deleting Scenarios

03m 41s

Producing A Summary Of Scenarios

03m 52s

Chapter: Custom Views

Custom Views Explained

03m 3s

Use Of Outlining To Help Setup Custom Views

03m 56s

Editing And Deleting Custom Views

04m 25s

Add Quick Access To Custom Views

03m 30s

Chapter: Functions For Manipulating Text

LEFT And RIGHT: Text Manipulation

04m 28s

LEN And TRIM: String Extractions

06m 34s

FIND And MID: Text Functions Working Together

06m 53s

CONCATENATE: Building Strings From Multiple Cells

05m 23s

Changing Case Functions

04m 3s

REPLACE And SUBSTITUTE: Two More String Manipulation Functions

04m 42s

Use Of CHAR Function For More Obscure Characters

06m 12s

Formatting Numeric And Date Values Using TEXT

05m 1s

Keeping The Values Created By String Manipulation

03m 21s

Chapter: Arrays

Arrays And Creating A New Array Formula

05m 27s

Array Formulas With IF Statements

04m 57s

Conditional Evaluation With No IFs

07m 21s

The Array-Only TRANSPOSE Function

06m 16s

Chapter: Useful Data Functions

Using The MATCH Function

06m 58s

How The INDEX Function Works

05m 1s

Handling Out Of Range Index Requests

03m 12s

The CHOOSE Lookup Function

03m 34s

MATCH And INDEX Functions Working Together

05m 4s

Chapter: Some Other Useful Functions

Introducing IS Functions

03m 23s

Error Checking Using ISERR, ISERROR, And IFERROR

06m 28s

OFFSET Function Syntax

03m 55s

OFFSET Function: Creating A Dynamic Named Range

04m 53s

INDIRECT Function To Build Dynamic Formulas

04m 16s

Dealing With INDIRECT Errors

03m 25s

The CELL Function And Determining File Or Sheet Names

07m 39s

Chapter: Auditing And Troubleshooting Formulas

What Are Tracer Arrows

04m 20s

Adding And Removing Tracer Arrows

02m 58s

Auditing Tools: Error Checking And Tracing

06m 26s

Step-By-Step Formula Processing

03m 41s

Using The Watch Window In Troubleshooting

04m 11s

Chapter: PivotTables

What Is A PivotTable?

04m 13s

The New Recommended PivotTable Route

04m 27s

Creating Your Own PivotTables

06m 24s

Changing The Formatting And Formulas In PivotTable Summaries

04m 21s

Creating Multiple PivotTables On The Same Dataset

05m 3s

Moving And Deleting PivotTables

03m 52s

Making Use Of The Report Filter Options

05m 7s

Sorting The PivotTable Columns

05m 16s

Refreshing A PivotTable

02m 51s

Drilling Down Behind The Pivot Numbers

02m 44s

Multiple Fields In Row, Column, Or Data Sections

05m 18s

Controlling Grand Totals And Subtotals

04m 52s

Dealing With Empty Cells And Other Additional Options

05m 8s

PivotTable Styles

05m 4s

Creating Your Own PivotTable Styles

04m 35s

Creating And Using Calculated Fields

03m 50s

Using The New Timeline Filter Option

04m 36s

Adding And Using The Data Slicer

05m 31s

Using Data From An SQL Server In A PivotTable

04m 51s

Managing The External Connection To SQL Server

02m 44s

Chapter: PivotCharts

Creating A PivotChart

03m 37s

Changing The Fields Used In A PivotChart

04m 36s

Formatting The PivotChart

05m 39s

Changing The PivotChart Type

04m 2s

Filtering A PivotChart

04m 2s

Hiding The PivotChart Buttons

02m 10s

Moving And Deleting PivotCharts

04m 40s

Chapter: Goal Seek And Solver

What-If Analysis Using Goal Seek

04m 5s

Activating The Solver Add-In

01m 51s

Using Solver To Complete A What-If

05m 27s

Adding Constraints To Solver

03m 47s

Chapter: Macros

What Is A Macro

01m 41s

Creating And Running Your First Macro

04m 52s

Saving Workbooks With Macros

01m 56s

Macro Security Settings For Workbooks With Macros

03m 51s

The Personal Macro Workbook

04m 27s

Deleting Macros

03m 31s

Use Of Relative Or Absolute Referencing

03m 41s

Trigger A Macro With A Keyboard Shortcut

03m 9s

Formatting With A Macro

04m 26s

Switch Scenarios And Views With Macros

06m 11s

Use Of Worksheet Buttons To Trigger Macros

06m 24s

Customizing Form Buttons And Other Shape Triggers

05m 59s

Assigning Macros To Ribbon Icons

05m 48s

Create Your Own Ribbon

03m 33s

View And Edit Macro Code

07m 10s

Add An Are You Sure Box To Macros

08m 9s

Chapter: Summary And Credits

What Have I Learned

03m 24s

About The Author

01m 14s