Advanced Microsoft Excel 2013

Video description

In this advanced training course for Microsoft Excel 2013, expert author Guy Vaccaro takes you beyond the basics of Excel, and teaches you how to use the advanced features and functions in this spreadsheet program from Microsoft.
You will start with basic operations such as SUM, MIN, and MAX, as well as conditional mathematical functions. Guy proceeds to instruct you on using IF statements to control conditions. You will learn how to perform data lookups using VLOOKUP and HLOOKUP, and how to create Sparklines. Other features that are covered in this advanced Excel tutorial are; working with time, outlining, custom views, text manipulation, error checking, pivot tables and pivot charts. Guy also covers some basic Macro operations within Excel for optimizing your workflow.
By the completion of this computer based training video for Microsoft Excel 2013, you will be comfortable with many of the advanced features and functions that this powerful spreadsheet software from Microsoft has to offer. Working files are included, allowing you to follow along with the author throughout the lessons.

Table of contents

  1. Getting Started
    1. Will I Be Able To Keep Up?
    2. Using The Included Files
    3. New For 2013: Its All In The Cloud
    4. The Very Clever Flash Fill
  2. Using Mathematical Functions
    1. The Basic SUM, COUNT, MIN, And MAX
    2. AVERAGE, MODE, MEAN, And MEDIAN
    3. SUMIF For Selective Adding Up
    4. COUNTIF For Selective Counting
    5. AVERAGEIF For The Mean Of Selected Cells
    6. Multiple Criteria Within SUMIF, COUNTIF, And AVERAGEIF
    7. Area And Volume Calculations
  3. IF Functionality
    1. IF Syntax And Uses
    2. Nesting The IF Statement
    3. Use Of The AND Operator Within An IF
    4. Use Of The OR Operator Within An IF
    5. The NOT Operator Within AND And OR Statements
    6. Display Cell Formulas In Another Cell
  4. Performing Data Lookups
    1. VLOOKUP: Syntax And Usage
    2. VLOOKUP In Live Action
    3. HLOOKUP: Variation On A Theme
    4. Using A Near Match In The Lookup
    5. Dealing With Missing Data In A Lookup
    6. Managing The Lookup Table
    7. Lookups Nested Within Lookups
  5. Sparklines
    1. Creating A Sparkline
    2. Altering The Design Of Sparklines
    3. Dealing With Empty Cells
    4. Comparing Sparklines Within A Sparkline Group
    5. Removing Sparklines From A Worksheet
  6. Further Mathematical Functions
    1. Working With Time In Excel
    2. Calculations Using Time
    3. Useful Time And Date Functions
    4. Rounding Decimal Places
    5. MOD And INT Functions And Uses
    6. Generate And Use A Random Number
    7. Loan And Investment Calculations
    8. Loan Calculation Elements And Functions
  7. Outlining
    1. Create An Outline Automatically
    2. Adding An Outline Manually
    3. Editing And Removing Outlining
  8. Scenarios
    1. Setting Up A Set Of Scenarios
    2. Displaying And Editing The Different Scenarios
    3. How To Work Out Which Scenario You Are Displaying
    4. Merging And Deleting Scenarios
    5. Producing A Summary Of Scenarios
  9. Custom Views
    1. Custom Views Explained
    2. Use Of Outlining To Help Setup Custom Views
    3. Editing And Deleting Custom Views
    4. Add Quick Access To Custom Views
  10. Functions For Manipulating Text
    1. LEFT And RIGHT: Text Manipulation
    2. LEN And TRIM: String Extractions
    3. FIND And MID: Text Functions Working Together
    4. CONCATENATE: Building Strings From Multiple Cells
    5. Changing Case Functions
    6. REPLACE And SUBSTITUTE: Two More String Manipulation Functions
    7. Use Of CHAR Function For More Obscure Characters
    8. Formatting Numeric And Date Values Using TEXT
    9. Keeping The Values Created By String Manipulation
  11. Arrays
    1. Arrays And Creating A New Array Formula
    2. Array Formulas With IF Statements
    3. Conditional Evaluation With No IFs
    4. The Array-Only TRANSPOSE Function
  12. Useful Data Functions
    1. Using The MATCH Function
    2. How The INDEX Function Works
    3. Handling Out Of Range Index Requests
    4. The CHOOSE Lookup Function
    5. MATCH And INDEX Functions Working Together
  13. Some Other Useful Functions
    1. Introducing IS Functions
    2. Error Checking Using ISERR, ISERROR, And IFERROR
    3. OFFSET Function Syntax
    4. OFFSET Function: Creating A Dynamic Named Range
    5. INDIRECT Function To Build Dynamic Formulas
    6. Dealing With INDIRECT Errors
    7. The CELL Function And Determining File Or Sheet Names
  14. Auditing And Troubleshooting Formulas
    1. What Are Tracer Arrows
    2. Adding And Removing Tracer Arrows
    3. Auditing Tools: Error Checking And Tracing
    4. Step-By-Step Formula Processing
    5. Using The Watch Window In Troubleshooting
  15. PivotTables
    1. What Is A PivotTable?
    2. The New Recommended PivotTable Route
    3. Creating Your Own PivotTables
    4. Changing The Formatting And Formulas In PivotTable Summaries
    5. Creating Multiple PivotTables On The Same Dataset
    6. Moving And Deleting PivotTables
    7. Making Use Of The Report Filter Options
    8. Sorting The PivotTable Columns
    9. Refreshing A PivotTable
    10. Drilling Down Behind The Pivot Numbers
    11. Multiple Fields In Row, Column, Or Data Sections
    12. Controlling Grand Totals And Subtotals
    13. Dealing With Empty Cells And Other Additional Options
    14. PivotTable Styles
    15. Creating Your Own PivotTable Styles
    16. Creating And Using Calculated Fields
    17. Using The New Timeline Filter Option
    18. Adding And Using The Data Slicer
    19. Using Data From An SQL Server In A PivotTable
    20. Managing The External Connection To SQL Server
  16. PivotCharts
    1. Creating A PivotChart
    2. Changing The Fields Used In A PivotChart
    3. Formatting The PivotChart
    4. Changing The PivotChart Type
    5. Filtering A PivotChart
    6. Hiding The PivotChart Buttons
    7. Moving And Deleting PivotCharts
  17. Goal Seek And Solver
    1. What-If Analysis Using Goal Seek
    2. Activating The Solver Add-In
    3. Using Solver To Complete A What-If
    4. Adding Constraints To Solver
  18. Macros
    1. What Is A Macro
    2. Creating And Running Your First Macro
    3. Saving Workbooks With Macros
    4. Macro Security Settings For Workbooks With Macros
    5. The Personal Macro Workbook
    6. Deleting Macros
    7. Use Of Relative Or Absolute Referencing
    8. Trigger A Macro With A Keyboard Shortcut
    9. Formatting With A Macro
    10. Switch Scenarios And Views With Macros
    11. Use Of Worksheet Buttons To Trigger Macros
    12. Customizing Form Buttons And Other Shape Triggers
    13. Assigning Macros To Ribbon Icons
    14. Create Your Own Ribbon
    15. View And Edit Macro Code
    16. Add An Are You Sure Box To Macros
  19. Summary And Credits
    1. What Have I Learned
    2. About The Author

Product information

  • Title: Advanced Microsoft Excel 2013
  • Author(s):
  • Release date: January 2013
  • Publisher(s): Infinite Skills
  • ISBN: 9781771370646