Introduction
- Introduction
Using Advanced IF Statements
- Summarizing Data with SUMIF
- Summarizing Data with AVERAGEIF
- Summarizing Data with COUNTIF
Using Advanced Lookup Functions
- Using VLOOKUP with TRUE
- Using HLOOKUP with TRUE
- Using the INDEX Function
- Using the MATCH Function
- Combining INDEX and MATCH
- Comparing Two Lists with VLOOKUP
- Comparing Two Lists with VLOOKUP and ISNA
Using Complex Logical and Text Functions
- Creating a Nested IF Function
- Using the IFERROR Function
- Using the LEN Function
- Using the TRIM Function
- Using the SUBSTITUTE Function
Formula Auditing
- Showing Formulas
- Tracing Precedents and Dependents
- Adding a Watch Window
- Error Checking
What-If Analysis Tools
- Using the Scenario Manager
- Using Goal Seek
- Analyzing with Data Tables
Worksheet and Workbook Protection
- Understanding Protection
- Encrypting Files with Passwords
- Allowing Specific Worksheet Changes
- Adding Protection to Selected Cells
- Additional Protection Features
Advanced Use of PivotTables and PowerPivot
- Using the PivotTable and PivotChart Wizard
- Adding a Calculated Field
- Adding a Calculated Item
- Applying Conditional Formatting to a PivotTable
- Using Filters in a PivotTable
- Creating Filter Pages for a PivotTable
- Enabling the PowerPivot Add-In
Automating with Macros
- What are Macros?
- Displaying the Developer Tab
- Creating a Basic Formatting Macro
- Running a Macro
- Assigning a Macro to a Button
- Creating Complex Macros
- Viewing and Editing the VBA Code
- Adding a Macro to the Quick Access Toolbar
Working with Form Controls
- What are Form Controls?
- Adding Spin Buttons and Check Boxes
- Adding a Combo Box
Ensuring Data Integrity
- What is Data Validation?
- Restricting Data Entry to Whole Numbers
- Restricting Data Entry to a List
- Restricting Data Entry to Specific Text Lengths
- Restricting Data Entry to a Date
- Composing Input Messages
- Composing Error Alerts
- Finding Invalid Data
- Editing and Deleting Data Validation Rules
Collaborating in Excel
- Working with Comments
- Printing Comments and Errors
- Sharing a Workbook
- Tracking Changes in a Workbook
- Working with Versions
- Sharing Files via Email
Importing and Exporting Data to a Text File
- Importing a Text File
- Exporting Data to a Text File
Conclusion
- Course Recap
Excel 2016 Advanced Assessment
- Assessment Test
