Excel 365 Advanced
Advanced
4h
By: Edward McCrae
Overview
This course will teach students advanced concepts and formulas in Microsoft Excel 365. 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.
Modules
1. Introduction
1.1 Excel 365 Advanced Introduction3 min
2. Customizing the Excel Environment
2.1 Customizing the Ribbon6 min
2.2 Customizing the Quick Access Toolbar3 min
2.3 Customizing the General and Formula Options3 min
2.4 Customizing the AutoCorrect Options3 min
2.5 Customizing the Save Defaults2 min
2.6 Customizing Advanced Excel Options2 min
3. Analyzing Data with Logical Functions
3.1 Working with the Most Common Logical Functions2 min
3.2 Understanding IF Functions4 min
3.3 Evaluating Data with the AND Function3 min
3.4 Evaluating Data with the OR Function3 min
3.5 Creating a Nested IF Function7 min
3.6 Using the IFS Function4 min
3.7 Nesting AND and OR Inside an IF Function5 min
3.8 Summarizing Data with SUMIF5 min
3.9 Summarizing Data with AVERAGEIF2 min
3.10 Summarizing Data with COUNTIF2 min
3.11 Summarizing Data with MAXIFS and MINIFS3 min
3.12 Using the IFERROR Function4 min
4. Working with Lookup Functions
4.1 What are Lookup Functions3 min
4.2 Using VLOOKUP3 min
4.3 Using HLOOKUP2 min
4.4 Using VLOOKUP with TRUE4 min
4.5 Using HLOOKUP with TRUE1 min
4.6 Using the Index Function5 min
4.7 Using the MATCH Function3 min
4.8 Combining INDEX and MATCH6 min
4.9 Comparing Two Lists with VLOOKUP3 min
4.10 Comparing Two Lists with VLOOKUP and ISNA3 min
5. Using Text Functions
5.1 What are Text Functions1 min
5.2 Using CONCAT, CONCATENATE, AND TEXTJOIN7 min
5.3 Using Text to Columns3 min
5.4 Using LEFT, RIGHT, and MID Functions3 min
5.5 Using UPPER, LOWER, and PROPER Functions2 min
5.6 Using the Len Function2 min
5.7 Using the Trim Function2 min
5.8 Using the SUBSTITUTE Function2 min
6. Working with Date and Time Functions
6.1 What are Date and Time Functions1 min
6.2 Using TODAY, NOW, and DAY Functions3 min
6.3 Using NETWORKDAYS and YEARFRAC Functions2 min
6.4 Using the DATEVALUE Function to Convert Text Dates1 min
7. Formula Auditing
7.1 Showing Formulas1 min
7.2 Tracing Precedents and Dependents5 min
7.3 Adding a Watch Window4 min
7.4 Error Checking5 min
8. What-If Analysis Tools
8.1 Using the Scenario Manager9 min
8.2 Using Goal Seek4 min
8.3 Analyzing with Data Tables3 min
9. Worksheet and Workbook Protection
9.1 Understanding Protection1 min
9.2 Encrypting Files with Passwords2 min
9.3 Allowing Specific Worksheet Changes4 min
9.4 Adding Protection to Selected Cells3 min
9.5 Additional Protection Features3 min
9.6 Protecting the Structure of an Entire Workbook2 min
10. Automating with Macros
10.1 What are Macros3 min
10.2 Displaying the Developer Tab5 min
10.3 Creating a Basic Formatting Macro10 min
10.4 Running a Macro6 min
10.5 Assigning a Macro to a Button5 min
10.6 Creating Complex Macros5 min
10.7 Viewing and Editing the VBA Code8 min
11. Enhancing a Worksheet with Form Controls
11.1 What are Form Controls3 min
11.2 Adding a Spin Button and Check Boxes5 min
11.3 Adding a Combo Box4 min
12. Validating Data
12.1 What is Data Validation3 min
12.2 Restricting Data Entry to Whole Numbers3 min
12.3 Restricting Data Entry to a List3 min
12.4 Restricting Data Entry to a Date2 min
12.5 Restricting Data Entry to Specific Text Lengths1 min
12.6 Finding Invalid Data2 min
12.7 Editing and Deleting Validation Rules2 min
13. Collaborating in Excel
13.1 Working with Comments3 min
13.2 Printing Comments and Errors2 min
13.3 Sharing a Workbook4 min
13.4 Tracking Changes in a Workbook4 min
13.5 Working with Versions2 min
13.6 Sharing files Via Email2 min
14. Importing and Exporting Data
14.1 Importing a Text File2 min
14.2 Exporting Data to a Text File2 min
15. Conclusion
15.1 Excel 365 Advanced Recap1 min
Ready to Elevate Your Team's Learning?
Talk with our sales team to see how StormWind can transform the way your organization trains and grows.
CONTACT SALES