Books & Videos

Table of Contents

Chapter: Getting Started

How Advanced Does The Advanced Get?

04m 57s

Using The Files Included

01m 41s

About The Author

01m 22s

New In 2010 - The Sparkline

03m 36s

Chapter: The IF Function

The Syntax Of IF

06m 2s

Nesting The IF Statement

06m 45s

Use The AND Operator To Reduce Quantity Of Nested IFs

04m 9s

Use The OR Operator To Reduce Quantity Of Nested IFs

03m 17s

The NOT Operator Within AND And OR Statements

02m 47s

SUMIF For Selective Adding Up

05m 4s

COUNTIF For Selective Counting

03m 52s

AVERAGEIF For The Mean Of Cells That Meet Our Criteria

02m 34s

Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions

07m 17s

Chapter: Performing Lookups

VLOOKUP Explained

03m 43s

Applied Examples For VLOOKUP

10m 3s

HLOOKUP Explained

03m 1s


06m 37s

Looking For A Near Match In A Lookup

04m 51s

Checking For Missing Data In A Lookup

05m 45s

Extending The Size Of A Lookup Table

04m 14s

Nested LOOKUPs

04m 28s

Chapter: Data Functions

The MATCH Function Explained

08m 14s

The INDEX Function Syntax

05m 6s

How To Stop Nonexistent Row Or Column Lookups In INDEX

02m 41s

The CHOOSE Lookup Function

02m 37s

Chapter: Math Functions

Working With TIME In Excel

05m 55s

Rounding To Fractional Values

03m 35s

MOD For Working Out Remainders

02m 18s

Generating A Random Number

02m 33s

Pick A List Item At Random

03m 5s

Calculating Loan Repayments Using PMT

02m 55s

Investment Calculations Using PMT

02m 59s

Working Out Depreciation

04m 21s

Working Out Different Parts Of A Loan Calculation

04m 58s

Chapter: Arrays

What Is An Array And An Array Formula

03m 0s

Creating And Using An Array Formula

03m 29s

Conditional Evaluation In An Array Formula

03m 42s

The Very Clever TRANSPOSE Array Function

03m 32s

Chapter: Functions For Working With Text

LEN And TRIM Two Very Useful Text Functions

02m 44s

Using LEFT And RIGHT For String Extraction

03m 36s

FIND And MID Working Together To Extract Parts Of Strings

04m 15s

Build Strings From Multiple Cells

03m 58s

Changing The Case Of Text In Cells

02m 56s


05m 12s

Formatting Numeric Values With A Text String Using TEXT

05m 9s

Extracting The Values From The Text Functions We Have Used

03m 0s

Chapter: Other Useful Functions

Welcome To IS Functions

02m 45s


08m 6s

The OFFSET Formula Explained

03m 17s

Dynamic Named Ranges Using The OFFSET Function

05m 48s

Use The INDIRECT Function To Build Dynamic Formulas

05m 59s

Dealing With INDIRECT Errors

01m 46s

Use Formulas To Determine An Excel Filename And Or Sheet Name

05m 43s

Chapter: Sparklines

Creating A Sparkline

03m 6s

Change The Design Of Sparklines

03m 38s

Dealing with Empty Cells

01m 57s

Comparing One Sparkline To Another by Altering Vertical Scale

02m 57s

Removing Sparklines From A Sheet

01m 35s

Chapter: Outlining

Outlining Explained

02m 35s

Creating An Outline Automatically

02m 34s

Creating An Outline Manually

02m 58s

Manually Removing Data From An Outline

03m 25s

Removing The Outlining From A Worksheet

00m 55s

Adjusting A Grouping Created By Automatic Outlining

03m 54s

Chapter: Custom Views

Creating A Custom View Of A Worksheet

02m 48s

Changing From One Custom View To Another

01m 34s

Editing A Custom View

01m 14s

How To Delete A Custom View

01m 20s

Chapter: Scenario

Setting Up A Scenario And Entering Values

04m 17s

Display The Scenario Values

02m 1s

Editing The Values Of A Scenario

01m 32s

Deleting A Scenario

01m 6s

Merge Scenarios From Different Sheets

02m 13s

Getting A Summary Of All Scenarios

04m 28s

Chapter: Auditing And Troubleshooting Formulas

Description Of Tracer Arrows

01m 0s

Tracing Precedents And Dependents

02m 56s

Remove Tracer Arrows

01m 54s

Error Checking Using Auditing Tools

04m 23s

Step By Step Processing Of Formula To Help With Troubleshooting

02m 55s

Utilizing The Watch Window

03m 48s

Chapter: Pivot Tables

What Is A Pivot Table

04m 19s

Steps To Create A Pivot Table

05m 35s

Rearranging Fields In A Pivot Table

03m 50s

Changing The Math Of The Data Summary

03m 3s

Number Format Control Of The Summary Area

03m 17s

Creating A Second (Or More) Pivot Table On The Same Data

03m 1s

Moving A Pivot Table

03m 5s

Removing A Pivot Table

01m 55s

Making Use Of The Report Filter Option

05m 47s

Sorting A Pivot Tables Columns

03m 30s

Displaying Values As A Percentage

05m 0s

Refreshing A Pivot Table Manually Or Semi-Automatically

02m 56s

Drilling Down Behind The Pivot Table Summaries

04m 6s

Applying Pivot Table Styles

03m 14s

Creating Your Own Custom Pivot Table Style

03m 39s

Copying A Pivot Table Style Between Workbooks

02m 13s

Using More Than One Field In Row And Column Headings

05m 1s

Disabling And Enabling Grand And Sub Totals

05m 53s

Filtering Columns And Rows Within A Pivot Table

06m 8s

Dealing With Empty (NULL) Cells

02m 25s

Exploring The Additional Pivot Table Options

04m 7s

Introducing The Slicer Tool

03m 54s

Managing Your Slices

03m 25s

Formatting Your Slices

03m 7s

Connecting A Pivot Table To SQL Server

05m 27s

External Connection Refresh Rate And Password Saving

02m 20s

Chapter: Pivot Charts

Creating A Pivot Chart

04m 26s

Altering Chart Types Formats And Layouts

03m 10s

Advanced Layout Control Of A Pivot Chart

04m 25s

Filtering A Pivot Chart

03m 14s

Hiding Pivot Chart Elements

02m 24s

Moving A Pivot Chart Between Sheets

02m 57s

Deleting A Pivot Chart (With Care)

02m 40s

Chapter: Goal Seek And Solver

Using Goal Seek To Carry Out What If Analysis

07m 17s

Using SOLVER To Carry Out What if Analysis

05m 23s

Activating The SOLVER Add In

02m 13s

Add Constraints Into A SOLVER Problem

05m 25s

Alberts Cafe Solver Solution

08m 13s

Chapter: Macros

What Is A Macro

02m 35s

Creating Storing And Running Your First Macro

05m 29s

Using Relative Or Absolute Referencing During Recording

03m 53s

Saving Workbooks With Macros Issues

02m 49s

Opening Files Containing Macros

04m 11s

The PERSONAL Workbook

01m 21s

How To Delete Macros

02m 46s

Use A Macro For Formatting

02m 36s

Trigger A Macro With A Keyboard Shortcut

03m 16s

Using Form Buttons To Trigger Macros

02m 42s

Customizing The Form Buttons

03m 15s

Assigning Macros To Ribbon Icons

03m 17s

Create Your Own Ribbon

03m 17s

Remove Options From Ribbons

02m 0s

View And Edit Macro Code

03m 54s

Add A Confirmation Dialog Box To Macros

04m 5s