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

No credit card required

## Video Description

This course will teach students advanced concepts and formulas in Microsoft Excel 2016. 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.

1. Introduction
1. Introduction 00:01:04
1. Summarizing Data with SUMIF 00:04:27
2. Summarizing Data with AVERAGEIF 00:02:36
3. Summarizing Data with COUNTIF 00:01:53
1. Using VLOOKUP with TRUE 00:03:40
2. Using HLOOKUP with TRUE 00:01:23
3. Using the INDEX Function 00:03:09
4. Using the MATCH Function 00:02:15
5. Combining INDEX and MATCH 00:04:09
6. Comparing Two Lists with VLOOKUP 00:01:43
7. Comparing Two Lists with VLOOKUP and ISNA 00:03:47
4. Using Complex Logical and Text Functions
1. Creating a Nested IF Function 00:02:54
2. Using the IFERROR Function 00:02:16
3. Using the LEN Function 00:02:08
4. Using the TRIM Function 00:01:03
5. Using the SUBSTITUE Function 00:02:23
5. Formula Auditing
1. Showing Formulas 00:01:02
2. Tracing Precedents and Dependents 00:02:33
3. Adding a Watch Window 00:02:16
4. Error Checking 00:01:55
6. What-If Analysis Tools
1. Using the Scenario Manager 00:03:21
2. Using Goal Seek 00:01:44
3. Analyzing with Data Tables 00:02:28
7. Worksheet and Workbook Protection
1. Understanding Protection 00:01:53
2. Encrypting Files with Passwords 00:02:36
3. Allowing Specific Worksheet Changes 00:01:27
4. Adding Protection to Selected Cells 00:02:49
8. Advanced Use of PivotTables and PowerPivot
1. Using the PivotTable and PivotChart Wizard 00:02:02
2. Adding a Calculated Field 00:01:34
3. Adding a Calculated Item 00:01:43
4. Applying Conditional Formatting to a PivotTable 00:02:36
5. Using Filters in a PivotTable 00:01:42
6. Creating Filter Pages for a PivotTable 00:01:17
7. Enabling the PowerPivot Add-In 00:04:00
9. Automating with Macros
1. What are Macros? 00:02:37
2. Displaying the Developer Tab 00:02:42
3. Creating a Basic Formatting Macro 00:02:58
4. Running a Macro 00:01:32
5. Assigning a Macro to a Button 00:02:07
6. Creating Complex Macros 00:02:29
7. Viewing and Editing the VBA Code 00:02:38
10. Working with Form Controls
1. What are Form Controls? 00:01:45
2. Adding Spin Buttons and Check Boxes 00:02:04
3. Adding a Combo Box 00:01:42
11. Ensuring Data Integrity
1. What is Data Validation? 00:01:26
2. Restricting Data Entry to Whole Numbers 00:01:34
3. Restricting Data Entry to a List 00:01:08
4. Restricting Data Entry to Specific Text Lengths 00:01:23
5. Data Validation Restricting Data Entry to a Date 00:01:15
6. Composing Input Messages 00:01:36
8. Finding Invalid Data 00:01:56
9. Editing and Deleting Data Validation Rules 00:00:57
12. Collaborating in Excel