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.
What am I going to get from this course?
- Customizing Excel
- Analyzing Data with Logical Functions
- Working with Lookup Functions
- Using Text Functions
- Working with Date and Time Functions
- Formula Auditing
- What-If Analysis
- Worksheet and Workbook Protection
- Automating with Macros
- Working with Form Controls
- Ensuring Data Integrity
- Collaborating in Excel
- Importing and Exporting Data to a Text File
Prerequisites and Target Audience
What will students need to know or do before starting this course?
Excel 2019 Introduction and Intermediate courses or equivalent experience.
Who should take this course? Who should not?
Those wishing to learn advanced concepts and formulas in Microsoft Excel 2019.
Module 2: Customizing Excel
Customizing the Ribbon
Customizing the Quick Access Toolbar
Customizing the General and Formula Options
Customizing the Save Defaults
Customizing Advanced Excel Options
Module 3: Analyzing Data with Logical Functions
Working with Common Logical Functions
Understanding IF Functions
Evaluating Data with the AND Function
Evaluating Data with the OR Function
Creating a Nested IF Function
Using the IFS Function
Summarizing Data with SUMIF
Summarizing Data with AVERAGEIF
Summarizing Data with COUNTIF
Summarizing Data with MAXIFS and MINIFS
Using the IFERROR Function
Module 4: Working with Lookup Functions
What are 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
Module 5: Using Text Function
What are Text Functions?
Using CONCAT, CONCATENATE, and TEXTJOIN
Using Text to Columns
Using LEFT, RIGHT, and MID
Using UPPER, LOWER, and PROPER Functions
Using the LEN Function
Using the TRIM Function
Using the SUBSTITUTE Function
Module 6: Working with Date and Time Functions
What are Date and Time Functions?
Using TODAY, NOW, and DAY Functions
Using NETWORKDAYS and YEARFRAC Functions
Module 7: Formula Auditing
Tracing Precedents and Dependents
Adding a Watch Window
Module 8: What-If Analysis Tools
Using the Scenario Manager
Using Goal Seek
Analyzing with Data Tables
Module 9: Worksheet and Workbook Protection
Encrypting Files with Passwords
Allowing Specific Worksheet Changes
Adding Protection to Selected Cells
Module 10: 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 and Editing the VBA Code
Adding a Macro to the Quick Access Toolbar
Module 11: Working with Form Controls
What are Form Controls?
Adding Spin Buttons and Check Boxes
Adding a Combo Box
Module 12: Ensuring Data Integrity
What is Data Validation?
Restricting Data Entry to Whole Numbers
Restricting Data Entry to a List
Restricting Data Entry to a Date
Restricting Data Entry to a Specific Text Length
Composing Input Messages
Composing Error Alerts
Finding Invalid Data
Editing and Deleting Validation Rules
Module 13: Collaborating in Excel
Working with Comments
Printing Comments and Errors
Sharing a Workbook
Tracking Changes in a Workbook
Working with Versions
Module 14: Importing and Exporting Data to a Text File
Importing a Text File
Exporting Data to a Text File
Module 16: Final Assessment Quiz - Excel 2019 Advanced
Final Assessment Quiz - Excel 2019 Advanced