Advanced Microsoft Excel 2010 Training Video

expand all / collapse all

• 01. Getting Started

  • How Advanced Does The Advanced Get?
  • Using The Files Included
  • About The Author
  • New In 2010 - The Sparkline

• 02. The IF Function

  • The Syntax Of IF
  • Nesting The IF Statement
  • Use The AND Operator To Reduce Quantity Of Nested IFs
  • Use The OR Operator To Reduce Quantity Of Nested IFs
  • The NOT Operator Within AND And OR Statements
  • SUMIF For Selective Adding Up
  • COUNTIF For Selective Counting
  • AVERAGEIF For The Mean Of Cells That Meet Our Criteria
  • Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions

• 03. Performing Lookups

  • VLOOKUP Explained
  • Applied Examples For VLOOKUP
  • HLOOKUP Explained
  • HLOOKUP In Action
  • Looking For A Near Match In A Lookup
  • Checking For Missing Data In A Lookup
  • Extending The Size Of A Lookup Table
  • Nested LOOKUPs

• 04. Data Functions

  • 0401 The MATCH Function Explained
  • 0402 The INDEX Function Syntax
  • 0403 How To Stop Nonexistent Row Or Column Lookups In INDEX
  • 0404 The CHOOSE Lookup Function

• 05. Math Functions

  • 0501 Working With TIME In Excel
  • 0502 Rounding To Fractional Values
  • 0503 MOD For Working Out Remainders
  • 0504 Generating A Random Number
  • 0505 Pick A List Item At Random
  • 0506 Calculating Loan Repayments Using PMT
  • 0507 Investment Calculations Using PMT
  • 0508 Working Out Depreciation
  • 0509 Working Out Different Parts Of A Loan Calculation

• 06. Arrays

  • 0601 What Is An Array And An Array Formula
  • 0602 Creating And Using An Array Formula
  • 0603 Conditional Evaluation In An Array Formula
  • 0604 The Very Clever TRANSPOSE Array Function

• 07. Functions For Working With Text

  • 0701 LEN And TRIM Two Very Useful Text Functions
  • 0702 Using LEFT And RIGHT For String Extraction
  • 0703 FIND And MID Working Together To Extract Parts Of Strings
  • 0704 Build Strings From Multiple Cells
  • 0705 Changing The Case Of Text In Cells
  • 0706 REPLACE And SUBSTITUTE In Action
  • 0707 Formatting Numeric Values With A Text String Using TEXT
  • 0708 Extracting The Values From The Text Functions We Have Used

• 08. Other Useful Functions

  • 0801 Welcome To IS Functions
  • 0802 Error Checking With ISERR ISERROR ISNA And IFERROR
  • 0803 The OFFSET Formula Explained
  • 0804 Dynamic Named Ranges Using The OFFSET Function
  • 0805 Use The INDIRECT Function To Build Dynamic Formulas
  • 0806 Dealing With INDIRECT Errors
  • 0807 Use Formulas To Determine An Excel Filename And Or Sheet Name

• 09. Sparklines

  • 0901 Creating A Sparkline
  • 0902 Change The Design Of Sparklines
  • 0903 Dealing with Empty Cells
  • 0904 Comparing One Sparkline To Another by Altering Vertical Scale
  • 0905 Removing Sparklines From A Sheet

• 10. Outlining

  • 1001 Outlining Explained
  • 1002 Creating An Outline Automatically
  • 1003 Creating An Outline Manually
  • 1004 Manually Removing Data From An Outline
  • 1005 Removing The Outlining From A Worksheet
  • 1006 Adjusting A Grouping Created By Automatic Outlining

• 11. Custom Views

  • 1101 Creating A Custom View Of A Worksheet
  • 1102 Changing From One Custom View To Another
  • 1103 Editing A Custom View
  • 1104 How To Delete A Custom View

• 12. Scenario

  • 1201 Setting Up A Scenario And Entering Values
  • 1202 Display The Scenario Values
  • 1203 Editing The Values Of A Scenario
  • 1204 Deleting A Scenario
  • 1205 Merge Scenarios From Different Sheets
  • 1206 Getting A Summary Of All Scenarios

• 13. Auditing And Troubleshooting Formulas

  • 1301 Description Of Tracer Arrows
  • 1302 Tracing Precedents And Dependents
  • 1303 Remove Tracer Arrows
  • 1304 Error Checking Using Auditing Tools
  • 1305 Step By Step Processing Of Formula To Help With Troubleshooting
  • 1306 Utilizing The Watch Window

• 14. Pivot Tables

  • 1401 What Is A Pivot Table
  • 1402 Steps To Create A Pivot Table
  • 1403 Rearranging Fields In A Pivot Table
  • 1404 Changing The Math Of The Data Summary
  • 1405 Number Format Control Of The Summary Area
  • 1406 Creating A Second (Or More) Pivot Table On The Same Data
  • 1407 Moving A Pivot Table
  • 1408 Removing A Pivot Table
  • 1409 Making Use Of The Report Filter Option
  • 1410 Sorting A Pivot Tables Columns
  • 1411 Displaying Values As A Percentage
  • 1412 Refreshing A Pivot Table Manually Or Semi-Automatically
  • 1413 Drilling Down Behind The Pivot Table Summaries
  • 1414 Applying Pivot Table Styles
  • 1415 Creating Your Own Custom Pivot Table Style
  • 1416 Copying A Pivot Table Style Between Workbooks
  • 1417 Using More Than One Field In Row And Column Headings
  • 1418 Disabling And Enabling Grand And Sub Totals
  • 1419 Filtering Columns And Rows Within A Pivot Table
  • 1420 Dealing With Empty (NULL) Cells
  • 1421 Exploring The Additional Pivot Table Options
  • 1422 Introducing The Slicer Tool
  • 1423 Managing Your Slices
  • 1424 Formatting Your Slices
  • 1425 Connecting A Pivot Table To SQL Server
  • 1426 External Connection Refresh Rate And Password Saving

• 15. Pivot Charts

  • 1501 Creating A Pivot Chart
  • 1502 Altering Chart Types Formats And Layouts
  • 1503 Advanced Layout Control Of A Pivot Chart
  • 1504 Filtering A Pivot Chart
  • 1505 Hiding Pivot Chart Elements
  • 1506 Moving A Pivot Chart Between Sheets
  • 1507 Deleting A Pivot Chart (With Care)

• 16. Goal Seek And Solver

  • 1601 Using Goal Seek To Carry Out What If Analysis
  • 1602 Using SOLVER To Carry Out What if Analysis
  • 1603 Activating The SOLVER Add In
  • 1604 Add Constraints Into A SOLVER Problem
  • 1605 Alberts Cafe Solver Solution

• 17. Macros

  • 1701 What Is A Macro
  • 1702 Creating Storing And Running Your First Macro
  • 1703 Using Relative Or Absolute Referencing During Recording
  • 1704 Saving Workbooks With Macros Issues
  • 1705 Opening Files Containing Macros
  • 1706 The PERSONAL Workbook
  • 1707 How To Delete Macros
  • 1708 Use A Macro For Formatting
  • 1709 Trigger A Macro With A Keyboard Shortcut
  • 1710 Using Form Buttons To Trigger Macros
  • 1711 Customizing The Form Buttons
  • 1712 Assigning Macros To Ribbon Icons
  • 1713 Create Your Own Ribbon
  • 1714 Remove Options From Ribbons
  • 1715 View And Edit Macro Code
  • 1716 Add A Confirmation Dialog Box To Macros

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.