Excel 2019 Advanced
Advanced
4h
By: Edward McCrae
Overview
This course will teach students advanced concepts and formulas in Microsoft Excel 2019. 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 2019 Advanced Introduction1 min
2. Customizing Excel
2.1 Customizing the Ribbon7 min
2.2 Customizing the Quick Access Toolbar5 min
2.3 Customizing the General and Formula Options5 min
2.4 Customizing the AutoCorrect Options3 min
2.5 Customizing the Save Defaults3 min
2.6 Customizing Advanced Excel Options3 min
3. Analyzing Data with Logical Functions
3.1 Working with Common Logical Functions2 min
3.2 Understanding IF Functions3 min
3.3 Evaluating Data with the AND Function5 min
3.4 Evaluating Data with the OR Function3 min
3.5 Creating a Nested IF Function6 min
3.6 Using the IFS Function3 min
3.7 Summarizing Data with SUMIF4 min
3.8 Summarizing Data with AVERAGEIF2 min
3.9 Summarizing Data with COUNTIF2 min
3.10 Summarizing Data with MAXIFS and MINIFS3 min
3.11 Using the IFERROR Function5 min
4. Working with Lookup Functions
4.1 What are Lookup Functions2 min
4.2 Using VLOOKUP7 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 Function3 min
4.7 Using the MATCH Function2 min
4.8 Combining INDEX and MATCH5 min
4.9 Comparing Two Lists with VLOOKUP2 min
4.10 Comparing Two Lists with VLOOKUP and ISNA5 min
5. Using Text Functions
5.1 What are Text Functions2 min
5.2 Using Concat Concatenate and Textjoin6 min
5.3 Using Text to Columns2 min
5.4 Using Left Right and Mid3 min
5.5 Using UPPER, LOWER, and PROPER Functions2 min
5.6 Using the LEN Function3 min
5.7 Using the TRIM Function2 min
5.8 Using the SUBSTITUTE Function3 min
6. Working with Date and Time Functions
6.1 What are Date and Time Functions1 min
6.2 Using TODAY, NOW, and DAY Functions2 min
6.3 Using NETWORKDAYS and YEARFRAC Functions4 min
7. Formula Auditing
7.1 Showing Formulas2 min
7.2 Tracing Precedents and Dependents3 min
7.3 Adding a Watch Window3 min
7.4 Error Checking3 min
8. What-If Analysis Tools
8.1 Using the Scenario Manager5 min
8.2 Using Goal Seek3 min
8.3 Analyzing with Data Tables3 min
9. Worksheet and Workbook Protection
9.1 Understanding Protection3 min
9.2 Encrypting Files with Passwords6 min
9.3 Allowing Specific Worksheet Changes3 min
9.4 Adding Protection to Selected Cells3 min
10. Automating with Macros
10.1 What are Macros3 min
10.2 Displaying the Developer Tab3 min
10.3 Creating a Basic Formatting Macro7 min
10.4 Running a Macro3 min
10.5 Assigning a Macro to a Button3 min
10.6 Creating Complex Macros and Editing the VBA Code9 min
10.7 Adding a Macro to the Quick Access Toolbar2 min
11. Working with Form Controls
11.1 What are Form Controls2 min
11.2 Adding Spin Buttons and Check Boxes5 min
11.3 Adding a Combo Box4 min
12. Ensuring Data Integrity
12.1 What is Data Validation1 min
12.2 Restricting Data Entry to Whole Numbers2 min
12.3 Restricting Data Entry to a List2 min
12.4 Restricting Data Entry to a Date1 min
12.5 Restricting Data Entry to a Specific Text Length1 min
12.6 Composing Input Messages2 min
12.7 Composing Error Alerts2 min
12.8 Finding Invalid Data2 min
12.9 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 Workbook3 min
13.4 Tracking Changes in a Workbook4 min
13.5 Working with Versions1 min
13.6 Sharing Files2 min
14. Importing and Exporting Data to a Text File
14.1 Importing a Text File4 min
14.2 Exporting Data to a Text File1 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