Advanced Microsoft Excel 2010

Table of contents

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