by Guy Vaccaro | Released February 2011 | ISBN: 9781926873367

In this Advanced Excel 2010 training video, expert author Guy Vaccaro follows up his bestselling Beginners Excel 2010 tutorial by delving even deeper into this powerful spreadsheet software.Microsoft Excel 2010 is much more than a quick way to add up numbers. In this video based tutorial, 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 in-depth with Pivot Tables and Charts. Finally, you will learn how to create and record your own Macros.This advanced tutorial video is not for beginners, and only if you have a firm grasp of the basics should you proceed with this video training. 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 files the author trains you with. Show and hide more

1. Getting Started
   • How Advanced Does The Advanced Get? 00:04:57
   • Using The Files Included 00:01:42
   • About The Author 00:01:23
   • New In 2010 - The Sparkline 00:03:37
2. The IF Function
   • The Syntax Of IF 00:06:03
   • Nesting The IF Statement 00:06:46
   • Use The AND Operator To Reduce Quantity Of Nested IFs 00:04:10
   • Use The OR Operator To Reduce Quantity Of Nested IFs 00:03:17
   • The NOT Operator Within AND And OR Statements 00:02:48
   • SUMIF For Selective Adding Up 00:05:05
   • COUNTIF For Selective Counting 00:03:52
   • AVERAGEIF For The Mean Of Cells That Meet Our Criteria 00:02:34
   • Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions 00:07:18
3. Performing Lookups
   • VLOOKUP Explained 00:03:44
   • Applied Examples For VLOOKUP 00:10:03
   • HLOOKUP Explained 00:03:02
   • HLOOKUP In Action 00:06:38
   • Looking For A Near Match In A Lookup 00:04:52
   • Checking For Missing Data In A Lookup 00:05:45
   • Extending The Size Of A Lookup Table 00:04:14
   • Nested LOOKUPs 00:04:28
4. Data Functions
   • The MATCH Function Explained 00:08:14
   • The INDEX Function Syntax 00:05:07
   • How To Stop Nonexistent Row Or Column Lookups In INDEX 00:02:42
   • The CHOOSE Lookup Function 00:02:37
5. Math Functions
   • Working With TIME In Excel 00:05:55
   • Rounding To Fractional Values 00:03:35
   • MOD For Working Out Remainders 00:02:18
   • Generating A Random Number 00:02:34
   • Pick A List Item At Random 00:03:05
   • Calculating Loan Repayments Using PMT 00:02:55
   • Investment Calculations Using PMT 00:03:00
   • Working Out Depreciation 00:04:22
   • Working Out Different Parts Of A Loan Calculation 00:04:58
6. Arrays
   • What Is An Array And An Array Formula 00:03:00
   • Creating And Using An Array Formula 00:03:29
   • Conditional Evaluation In An Array Formula 00:03:42
   • The Very Clever TRANSPOSE Array Function 00:03:32
7. Functions For Working With Text
   • LEN And TRIM Two Very Useful Text Functions 00:02:45
   • Using LEFT And RIGHT For String Extraction 00:03:36
   • FIND And MID Working Together To Extract Parts Of Strings 00:04:15
   • Build Strings From Multiple Cells 00:03:59
   • Changing The Case Of Text In Cells 00:02:56
   • REPLACE And SUBSTITUTE In Action 00:05:12
   • Formatting Numeric Values With A Text String Using TEXT 00:05:10
   • Extracting The Values From The Text Functions We Have Used 00:03:00
8. Other Useful Functions
   • Welcome To IS Functions 00:02:45
   • Error Checking With ISERR ISERROR ISNA And IFERROR 00:08:06
   • The OFFSET Formula Explained 00:03:18
   • Dynamic Named Ranges Using The OFFSET Function 00:05:48
   • Use The INDIRECT Function To Build Dynamic Formulas 00:06:00
   • Dealing With INDIRECT Errors 00:01:47
   • Use Formulas To Determine An Excel Filename And Or Sheet Name 00:05:43
9. Sparklines
   • Creating A Sparkline 00:03:07
   • Change The Design Of Sparklines 00:03:38
   • Dealing with Empty Cells 00:01:57
   • Comparing One Sparkline To Another by Altering Vertical Scale 00:02:57
   • Removing Sparklines From A Sheet 00:01:36
10. Outlining
    • Outlining Explained 00:02:36
    • Creating An Outline Automatically 00:02:35
    • Creating An Outline Manually 00:02:58
    • Manually Removing Data From An Outline 00:03:25
    • Removing The Outlining From A Worksheet 00:00:56
    • Adjusting A Grouping Created By Automatic Outlining 00:03:54
11. Custom Views
    • Creating A Custom View Of A Worksheet 00:02:49
    • Changing From One Custom View To Another 00:01:34
    • Editing A Custom View 00:01:15
    • How To Delete A Custom View 00:01:20
12. Scenario
    • Setting Up A Scenario And Entering Values 00:04:18
    • Display The Scenario Values 00:02:01
    • Editing The Values Of A Scenario 00:01:32
    • Deleting A Scenario 00:01:07
    • Merge Scenarios From Different Sheets 00:02:13
    • Getting A Summary Of All Scenarios 00:04:28
13. Auditing And Troubleshooting Formulas
    • Description Of Tracer Arrows 00:01:00
    • Tracing Precedents And Dependents 00:02:57
    • Remove Tracer Arrows 00:01:55
    • Error Checking Using Auditing Tools 00:04:24
    • Step By Step Processing Of Formula To Help With Troubleshooting 00:02:55
    • Utilizing The Watch Window 00:03:49
14. Pivot Tables
    • What Is A Pivot Table 00:04:20
    • Steps To Create A Pivot Table 00:06:56
    • Rearranging Fields In A Pivot Table 00:03:50
    • Changing The Math Of The Data Summary 00:03:03
    • Number Format Control Of The Summary Area 00:03:18
    • Creating A Second (Or More) Pivot Table On The Same Data 00:03:01
    • Moving A Pivot Table 00:03:06
    • Removing A Pivot Table 00:01:55
    • Making Use Of The Report Filter Option 00:05:47
    • Sorting A Pivot Tables Columns 00:03:30
    • Displaying Values As A Percentage 00:05:01
    • Refreshing A Pivot Table Manually Or Semi-Automatically 00:02:57
    • Drilling Down Behind The Pivot Table Summaries 00:04:06
    • Applying Pivot Table Styles 00:03:15
    • Creating Your Own Custom Pivot Table Style 00:03:39
    • Copying A Pivot Table Style Between Workbooks 00:02:13
    • Using More Than One Field In Row And Column Headings 00:05:02
    • Disabling And Enabling Grand And Sub Totals 00:05:54
    • Filtering Columns And Rows Within A Pivot Table 00:06:08
    • Dealing With Empty (NULL) Cells 00:02:25
    • Exploring The Additional Pivot Table Options 00:04:08
    • Introducing The Slicer Tool 00:03:55
    • Managing Your Slices 00:03:25
    • Formatting Your Slices 00:03:07
    • Connecting A Pivot Table To SQL Server 00:05:27
    • External Connection Refresh Rate And Password Saving 00:02:21
15. Pivot Charts
    • Creating A Pivot Chart 00:04:26
    • Altering Chart Types Formats And Layouts 00:03:10
    • Advanced Layout Control Of A Pivot Chart 00:04:26
    • Filtering A Pivot Chart 00:03:14
    • Hiding Pivot Chart Elements 00:02:24
    • Moving A Pivot Chart Between Sheets 00:02:57
    • Deleting A Pivot Chart (With Care) 00:02:40
16. Goal Seek And Solver
    • Using Goal Seek To Carry Out What If Analysis 00:07:17
    • Using SOLVER To Carry Out What if Analysis 00:05:23
    • Activating The SOLVER Add In 00:02:13
    • Add Constraints Into A SOLVER Problem 00:05:25
    • Alberts Cafe Solver Solution 00:08:14
17. Macros
    • What Is A Macro 00:02:35
    • Creating Storing And Running Your First Macro 00:05:29
    • Using Relative Or Absolute Referencing During Recording 00:03:53
    • Saving Workbooks With Macros Issues 00:02:50
    • Opening Files Containing Macros 00:04:11
    • The PERSONAL Workbook 00:01:22
    • How To Delete Macros 00:02:47
    • Use A Macro For Formatting 00:02:37
    • Trigger A Macro With A Keyboard Shortcut 00:03:16
    • Using Form Buttons To Trigger Macros 00:02:42
    • Customizing The Form Buttons 00:03:16
    • Assigning Macros To Ribbon Icons 00:03:17
    • Create Your Own Ribbon 00:03:17
    • Remove Options From Ribbons 00:02:01
    • View And Edit Macro Code 00:03:55
    • Add A Confirmation Dialog Box To Macros 00:04:06
Show and hide more