Course Description
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.
Curriculum
Module 2: Customizing Excel
Lecture 2
Customizing the Ribbon
Lecture 3
Customizing the Quick Access Toolbar
Lecture 4
Customizing the General and Formula Options
Lecture 5
Customizing the Save Defaults
Lecture 6
Customizing Advanced Excel Options
Module 3: Analyzing Data with Logical Functions
Lecture 7
Working with Common Logical Functions
Lecture 8
Understanding IF Functions
Lecture 9
Evaluating Data with the AND Function
Lecture 10
Evaluating Data with the OR Function
Lecture 11
Creating a Nested IF Function
Lecture 12
Using the IFS Function
Lecture 13
Summarizing Data with SUMIF
Lecture 14
Summarizing Data with AVERAGEIF
Lecture 15
Summarizing Data with COUNTIF
Lecture 16
Summarizing Data with MAXIFS and MINIFS
Lecture 17
Using the IFERROR Function
Module 4: Working with Lookup Functions
Lecture 18
What are Lookup Functions?
Lecture 21
Using VLOOKUP with TRUE
Lecture 22
Using HLOOKUP with TRUE
Lecture 23
Using the INDEX Function
Lecture 24
Using the MATCH Function
Lecture 25
Combining INDEX and MATCH
Lecture 26
Comparing Two Lists with VLOOKUP
Lecture 27
Comparing Two Lists with VLOOKUP and ISNA
Module 5: Using Text Function
Lecture 28
What are Text Functions?
Lecture 29
Using CONCAT, CONCATENATE, and TEXTJOIN
Lecture 30
Using Text to Columns
Lecture 31
Using LEFT, RIGHT, and MID
Lecture 32
Using UPPER, LOWER, and PROPER Functions
Lecture 33
Using the LEN Function
Lecture 34
Using the TRIM Function
Lecture 35
Using the SUBSTITUTE Function
Module 6: Working with Date and Time Functions
Lecture 36
What are Date and Time Functions?
Lecture 37
Using TODAY, NOW, and DAY Functions
Lecture 38
Using NETWORKDAYS and YEARFRAC Functions
Module 7: Formula Auditing
Lecture 39
Showing Formulas
Lecture 40
Tracing Precedents and Dependents
Lecture 41
Adding a Watch Window
Lecture 42
Error Checking
Module 8: What-If Analysis Tools
Lecture 43
Using the Scenario Manager
Lecture 44
Using Goal Seek
Lecture 45
Analyzing with Data Tables
Module 9: Worksheet and Workbook Protection
Lecture 46
Understanding Protection
Lecture 47
Encrypting Files with Passwords
Lecture 48
Allowing Specific Worksheet Changes
Lecture 49
Adding Protection to Selected Cells
Module 10: Automating with Macros
Lecture 50
What are Macros?
Lecture 51
Displaying the Developer Tab
Lecture 52
Creating a Basic Formatting Macro
Lecture 53
Running a Macro
Lecture 54
Assigning a Macro to a Button
Lecture 55
Creating Complex Macros and Editing the VBA Code
Lecture 56
Adding a Macro to the Quick Access Toolbar
Module 11: Working with Form Controls
Lecture 57
What are Form Controls?
Lecture 58
Adding Spin Buttons and Check Boxes
Lecture 59
Adding a Combo Box
Module 12: Ensuring Data Integrity
Lecture 60
What is Data Validation?
Lecture 61
Restricting Data Entry to Whole Numbers
Lecture 62
Restricting Data Entry to a List
Lecture 63
Restricting Data Entry to a Date
Lecture 64
Restricting Data Entry to a Specific Text Length
Lecture 65
Composing Input Messages
Lecture 66
Composing Error Alerts
Lecture 67
Finding Invalid Data
Lecture 68
Editing and Deleting Validation Rules
Module 13: Collaborating in Excel
Lecture 69
Working with Comments
Lecture 70
Printing Comments and Errors
Lecture 71
Sharing a Workbook
Lecture 72
Tracking Changes in a Workbook
Lecture 73
Working with Versions
Module 14: Importing and Exporting Data to a Text File
Lecture 75
Importing a Text File
Lecture 76
Exporting Data to a Text File
Module 16: Final Assessment Quiz - Excel 2019 Advanced
Quiz 1
Final Assessment Quiz - Excel 2019 Advanced
