Microsoft Excel 2010: Advanced Training
About This Course
Course Description
A Verifiable Certificate of Completion is presented to all students who undertake this Excel course.
This Excel 2010 advanced training course, follows up from the Beginners Excel 2010 course by delving even deeper into the features and functions of this powerful spreadsheet software.
Microsoft Excel 2010 is much more than a quick way to add up numbers. In this online course, you will learn advanced features such as nesting IF statements, how to calculate the Mean of a group of numbers using AVERAGEIF, SUMIF and more. You will learn about performing lookups with HLOOKUP and VLOOKUP. This tutorial also covers Sparklines, and goes indepth with Pivot Tables and Charts. Finally, you will learn how to create and record your own Macros.
This advanced Excel video course is not for beginners. You should have a firm grasp of the basics before taking this Advanced Excel training course. By the conclusion of this advanced computer software tutorial for Microsoft Excel 2010, you will have mastered the advanced features and functions of this software. Working files are included to allow you to follow along with the same Excel files the author trains you with.
What am I going to get from this course?
 Learn Advanced Microsoft Excel 2010 from a professional trainer from your own desk.
 136 lectures (8.5 hours of content) teaching you lookups, advanced IF statements, Macros, Sparklines, and much more!
 Suitable advanced Excel users. ideal for users who learn faster when shown.
 Visual training method, offering users increased retention and accelerated learning.
 Breaks even the most complex applications down into simplistic steps.
Curriculum
Section 1: Getting Started With This Excel Course  

Lecture 1 
Working Files – Download These First

Article  
Lecture 2 
How Advanced Does The Advanced Get?

04:57  
Lecture 3 
Using The Included Excel Files

01:41  
Lecture 4 
New In Excel 2010 – The Sparkline

03:36  
Section 2: The Excel IF Function  
Lecture 5 
The Syntax Of IF

06:02  
Lecture 6 
Nesting The IF Statement In Excel

06:45  
Lecture 7 
Use The AND Operator To Reduce Quantity Of Nested IFs

04:09  
Lecture 8 
Use The OR Operator To Reduce Quantity Of Nested IFs

03:17  
Lecture 9 
The NOT Operator Within AND And OR Statements

02:47  
Lecture 10 
SUMIF For Selective Adding Up

05:04  
Lecture 11 
COUNTIF For Selective Counting

03:52  
Lecture 12 
AVERAGEIF For The Mean Of Cells That Meet Our Criteria

02:34  
Lecture 13 
Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions

07:17  
Section 3: Performing Lookup in Excel  
Lecture 14 
VLOOKUP Explained

03:43  
Lecture 15 
Applied Examples for VLOOKUP

10:03  
Lecture 16 
HLOOKUP Explained

03:01  
Lecture 17 
HLOOKUP In Action

06:37  
Lecture 18 
Looking For A Near Match In A Lookup

04:51  
Lecture 19 
Checking For Missing Data In A Lookup

05:45  
Lecture 20 
Extending The Size Of A Lookup Table

04:14  
Lecture 21 
Nested LOOKUPs In Excel

04:28  
Section 4: Excel Data Functions  
Lecture 22 
The MATCH Function Explained

08:14  
Lecture 23 
The INDEX Function Syntax

05:06  
Lecture 24 
How To Stop Nonexistent Row Or Column Lookups In INDEX

02:41  
Lecture 25 
The CHOOSE Lookup Function

02:37  
Section 5: Excel Math Functions  
Lecture 26 
Working With TIME

05:55  
Lecture 27 
Rounding To Fractional Values

03:35  
Lecture 28 
MOD For Working Out Remainders

02:18  
Lecture 29 
Generating A Random Number

02:33  
Lecture 30 
Pick A List Item At Random

03:05  
Lecture 31 
Calculating Loan Repayments Using PMT

02:55  
Lecture 32 
Excel – Investment Calculations Using PMT

02:59  
Lecture 33 
Working Out Depreciation

04:21  
Lecture 34 
Working Out Different Parts Of A Loan Calculation

04:58  
Section 6: Arrays In Excel  
Lecture 35 
What Is An Array And An Array Formula

03:00  
Lecture 36 
Creating And Using An Array Formula

03:29  
Lecture 37 
Conditional Evaluation In An Array Formula

03:42  
Lecture 38 
The Very Clever TRANSPOSE Array Function

03:32  
Section 7: Excel Functions For Working With Text  
Lecture 39 
LEN And TRIM Two Very Useful Text Functions

02:44  
Lecture 40 
Using LEFT And RIGHT For String Extraction

03:36  
Lecture 41 
FIND And MID Working Together To Extract Parts Of Strings

04:15  
Lecture 42 
Build Strings From Multiple Cells

03:58  
Lecture 43 
Excel – Changing The Case Of Text In Cells

02:56  
Lecture 44 
REPLACE And SUBSTITUTE In Action

05:12  
Lecture 45 
Formatting Numeric Values With A Text String Using TEXT

05:09  
Lecture 46 
Extracting The Values From The Text Functions We Have Used

03:00  
Section 8: Other Useful Excel Functions  
Lecture 47 
Welcome To IS Functions

02:45  
Lecture 48 
Error Checking With ISERR ISERROR ISNA And IFERROR

08:06  
Lecture 49 
The OFFSET Formula Explained

03:17  
Lecture 50 
Dynamic Named Ranges Using The OFFSET Function

05:48  
Lecture 51 
Use The INDIRECT Function To Build Dynamic Formulas

05:59  
Lecture 52 
Dealing With INDIRECT Errors

01:46  
Lecture 53 
Use Formulas To Determine An Excel Filename And Or Sheet Name

05:43  
Section 9: Sparklines  
Lecture 54 
Creating A Sparkline In Excel

03:06  
Lecture 55 
Change The Design Of Sparklines

03:38  
Lecture 56 
Dealing with Empty Cells

01:57  
Lecture 57 
Comparing One Sparkline To Another by Altering Vertical Scale

02:57  
Lecture 58 
Removing Sparklines From A Sheet

01:35  
Section 10: Outlining  
Lecture 59 
Outlining Explained

02:35  
Lecture 60 
Creating An Outline Automatically

02:34  
Lecture 61 
Creating An Outline Manually

02:58  
Lecture 62 
Manually Removing Data From An Outline

03:25  
Lecture 63 
Removing The Outlining From A Worksheet

00:55  
Lecture 64 
Adjusting A Grouping Created By Automatic Outlining

03:54  
Section 11: Custom Views In Excel  
Lecture 65 
Creating A Custom View Of A Worksheet

02:48  
Lecture 66 
Changing From One Custom View To Another

01:34  
Lecture 67 
Editing A Custom View

01:14  
Lecture 68 
How To Delete A Custom View

01:20  
Section 12: Scenario  
Lecture 69 
Setting Up A Scenario And Entering Values

04:17  
Lecture 70 
Display The Scenario Values

02:01  
Lecture 71 
Editing The Values Of A Scenario

01:32  
Lecture 72 
Deleting A Scenario

01:06  
Lecture 73 
Merge Scenarios From Different Sheets

02:13  
Lecture 74 
Getting A Summary Of All Scenarios

04:28  
Section 13: Auditing And Troubleshooting Formulas  
Lecture 75 
Description Of Tracer Arrows

01:00  
Lecture 76 
Tracing Precedents And Dependents

02:56  
Lecture 77 
Remove Tracer Arrows

01:54  
Lecture 78 
Error Checking Using Auditing Tools

04:23  
Lecture 79 
Step By Step Processing Of Formula To Help With Troubleshooting

02:55  
Lecture 80 
Excel – Utilizing The Watch Window

03:48  
Section 14: Pivot Tables In Excel  
Lecture 81 
What Is A Pivot Table

04:19  
Lecture 82 
Steps To Create A Pivot Table In Excel

06:56  
Lecture 83 
Rearranging Fields In A Pivot Table

03:50  
Lecture 84 
Changing The Math Of The Data Summary

03:03  
Lecture 85 
Number Format Control Of The Summary Area

03:17  
Lecture 86 
Creating A Second (Or More) Pivot Table On The Same Data

03:01  
Lecture 87 
Moving A Pivot Table

03:05  
Lecture 88 
Removing A Pivot Table

01:55  
Lecture 89 
Making Use Of The Report Filter Option

05:47  
Lecture 90 
Sorting A Pivot Tables Columns

03:30  
Lecture 91 
Displaying Values As A Percentage

05:00  
Lecture 92 
Refreshing A Pivot Table Manually Or SemiAutomatically

02:56  
Lecture 93 
Drilling Down Behind The Pivot Table Summaries

04:06  
Lecture 94 
Applying Pivot Table Styles

03:14  
Lecture 95 
Creating Your Own Custom Pivot Table Style

03:39  
Lecture 96 
Copying A Pivot Table Style Between Workbooks

02:13  
Lecture 97 
Using More Than One Field In Row And Column Headings

05:01  
Lecture 98 
Disabling And Enabling Grand And Sub Totals

05:53  
Lecture 99 
Filtering Columns And Rows Within A Pivot Table

06:08  
Lecture 100 
Dealing With Empty (NULL) Cells

02:25  
Lecture 101 
Exploring The Additional Pivot Table Options

04:07  
Lecture 102 
Introducing The Slicer Tool

03:54  
Lecture 103 
Managing Your Slices

03:25  
Lecture 104 
Formatting Your Slices

03:07  
Lecture 105 
Connecting A Pivot Table To SQL Server

05:27  
Lecture 106 
External Connection Refresh Rate And Password Saving

02:20  
Section 15: Excel Pivot Charts  
Lecture 107 
Creating A Pivot Chart

04:26  
Lecture 108 
Altering Chart Types Formats And Layouts

03:10  
Lecture 109 
Advanced Layout Control Of A Pivot Chart

04:25  
Lecture 110 
Filtering A Pivot Chart

03:14  
Lecture 111 
Hiding Pivot Chart Elements

02:24  
Lecture 112 
Moving A Pivot Chart Between Sheets

02:57  
Lecture 113 
Deleting A Pivot Chart (With Care)

02:40  
Section 16: Goal Seek And Solver  
Lecture 114 
Using Goal Seek To Carry Out What If Analysis

07:17  
Lecture 115 
Using SOLVER To Carry Out What if Analysis

05:23  
Lecture 116 
Excel – Activating The SOLVER Add In

02:13  
Lecture 117 
Add Constraints Into A SOLVER Problem

05:25  
Lecture 118 
Alberts Cafe Solver Solution

08:13  
Section 17: Macros In Excel  
Lecture 119 
What Is A Macro

02:35  
Lecture 120 
Creating Storing And Running Your First Macro

05:29  
Lecture 121 
Using Relative Or Absolute Referencing During Recording

03:53  
Lecture 122 
Saving Workbooks With Macros Issues

02:49  
Lecture 123 
Opening Files Containing Macros

04:11  
Lecture 124 
The PERSONAL Workbook

01:21  
Lecture 125 
How To Delete Macros

02:46  
Lecture 126 
Use A Macro For Formatting

02:36  
Lecture 127 
Trigger A Macro With A Keyboard Shortcut

03:16  
Lecture 128 
Using Form Buttons To Trigger Macros

02:42  
Lecture 129 
Customizing The Form Buttons In Excel

03:15  
Lecture 130 
Assigning Macros To Ribbon Icons

03:17  
Lecture 131 
Create Your Own Ribbon

03:17  
Lecture 132 
Remove Options From Ribbons

02:00  
Lecture 133 
View And Edit Macro Code

03:54  
Lecture 134 
Add A Confirmation Dialog Box To Macros

04:05  
Lecture 135 
How To Get The Excel Working Files

00:16 
Instructor Biography
Infinite Skills, High Quality Training
Infinite Skills is a Canadian based company who offer a huge and ever growing range of high quality eLearning solutions that teach using studio quality narrated videos backedup with practical handson examples and comprehensive working files. All courses are created by trained educators and experts in video based education.
The emphasis is on teaching real life skills that are essential for progressing in today’s commercial environment.
Course Features
 Lectures 0
 Quizzes 0
 Duration 50 hours
 Skill level All level
 Language English
 Students 71537
 Assessments Self