O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Excel 2013 Advanced

Video Description

This course will teach students advanced concepts and formulas in Microsoft Excel 2013. Students will learn functions such as SUMIF, AVERAGEIF, and COUNTIF, advanced lookup functions, and complex logical and text functions. Additionally, students will experiment with auditing formulas and error checking, use the What-If Analysis tools, learn the options for worksheet and workbook protection, review advanced use of PivotTables and PowerPivot add-in, work with Macros, use form controls, and ensure data integrity in their worksheets and workbooks. Students will also learn about Excel's many collaboration features and import and export data to and from their workbooks.

Table of Contents

  1. Introduction
    1. Introduction 00:01:09
  2. Using SUMIF, AVERAGEIF, and COUNTIF
    1. Summarizing Data with SUMIF 00:05:07
    2. Summarizing Data with AVERAGEIF 00:02:11
    3. Summarizing Data with COUNTIF 00:02:03
  3. Using Advanced Lookup Functions
    1. Using VLOOKUP with TRUE to Find an Approximate Match 00:04:11
    2. Using HLOOKUP with TRUE to Find an Approximate Match 00:01:48
    3. Using the INDEX Function 00:02:58
    4. Using the MATCH Function 00:02:06
    5. Creating a Combined INDEX and MATCH Formula 00:02:52
    6. Comparing Two Lists with VLOOKUP 00:02:37
    7. Comparing Two Lists with a Combined VLOOKUP and ISNA 00:04:51
  4. Using Complex Logical and Text Functions
    1. Creating a Nested IF Function 00:06:55
    2. Using the IFERROR Function 00:04:01
    3. Using the LEN Function 00:03:56
    4. Using the TRIM Function 00:01:25
    5. Using the SUBSTITUTE Function 00:02:46
  5. Formula Auditing
    1. Showing Formulas 00:02:00
    2. Tracing Precedents and Dependents 00:02:48
    3. Adding a Watch Window 00:01:46
    4. Error Checking 00:03:20
  6. What-If Analysis Tools
    1. Using the Scenario Manager 00:05:53
    2. Using Goal Seek 00:02:12
    3. Analyzing with Data Tables 00:02:40
  7. Worksheet and Workbook Protection
    1. Protection Overview 00:01:42
    2. Excel File Password Encryption 00:04:36
    3. Allowing Specific Worksheet Changes 00:02:29
    4. Adding Protection to Only Certain Cells in a Worksheet 00:03:52
    5. Additional Protection Features 00:02:42
  8. Advanced Use of PivotTables and PowerPivot
    1. Using the PivotTable and PivotChart Wizard 00:02:09
    2. Adding a Calculated Field 00:02:57
    3. Adding a Calculated Item 00:01:24
    4. Applying Conditional Formatting to a PivotTable 00:02:35
    5. Filters in the PivotTable Fields Pane 00:01:56
    6. Creating Filter Pages for a PivotTable 00:02:23
    7. Enabling the PowerPivot Add-In 00:03:20
  9. Automating with Macros
    1. What are Macros? 00:02:40
    2. Displaying the Developer Tab & Enabling Macros in Excel 00:02:57
    3. Creating a Basic Formatting Macro 00:04:56
    4. Running a Macro 00:01:38
    5. Assigning a Macro to a Button 00:02:18
    6. Creating a More Complex Macro 00:04:06
    7. Viewing and Editing the VBA Code for an Existing Macro 00:06:18
    8. Adding a Macro to the Quick Access Toolbar 00:02:03
  10. Working with Form Controls
    1. What are Form Controls? 00:02:37
    2. Adding Spin Buttons and Check Boxes to a Spreadsheet 00:02:47
    3. Adding a Combo Box to a Spreadsheet 00:01:41
  11. Ensuring Data Integrity
    1. What is Data Validation? 00:01:05
    2. Restricting Data Entry to Whole Numbers 00:02:17
    3. Restricting Data Entry to a List 00:02:35
    4. Restricting Data Entry to Specific Text Lengths 00:01:16
    5. Restricting Data Entry to a Date 00:01:26
    6. Composing Input Messages 00:01:15
    7. Composing Error Alerts 00:01:47
    8. Finding Invalid Data 00:01:28
    9. Editing and Deleting Data Validation Rules 00:01:10
  12. Collaborating in Excel
    1. Working with Comments 00:04:29
    2. Printing Comments and Errors 00:01:57
    3. Sharing a Workbook 00:02:56
    4. Tracking Changes in a Workbook 00:02:30
    5. Working with Versions 00:01:27
    6. Sharing Files via Email 00:01:39
  13. Importing and Exporting Data to a Text File
    1. Importing a Text File into Excel 00:02:58
    2. Exporting Data to a Text File 00:01:23
  14. Conclusion
    1. Course Recap 00:01:30