About This Course
Our Microsoft Excel 2021 Expert course is all you need to pass both the Microsoft certification exams MO-200 & MO-201. The comprehensive course structure covers 6 key topics that dive into the most-advanced features of the software.
You’ll learn how to work with multiple worksheets and workbooks, utilizing links, external references, and 3D references. Explore advanced techniques and powerful features to optimize your data analysis, reporting, and automation workflows.
By the end of the Microsoft Excel Expert training course, you’ll be skilled to tackle complex data analysis challenges, and create dynamic, interactive reports.
Skills You’ll Get
- Ability to manipulate and analyze data by consolidation, 3D cell referencing, and data validation
- Track formula dependencies and evaluate expressions to identify errors
- Work with dynamic arrays that automatically adjust in size as data changes
- Retrieve specific data from a range of cells with Lookup functions
- Analyzing large datasets with Power Pivot & perform calculations with array formulas
- Automate repetitive tasks with Macros to create automated data tables
- Find input value for a specific output & analyze different sets of input values
- Expertise with Sparklines and Data Maps for visual representation of data
- Ability to collaborate, secure and share workbooks
Lesson Plan
About This Course
- Course Description
Working with Multiple Worksheets and Workbooks
- Topic A: Use Links and External References
- Topic B: Use 3-D References
- Topic C: Consolidate Data
Sharing and Protecting Workbooks
- Topic A: Collaborate on a Workbook
- Topic B: Protect Worksheets and Workbooks
Automating Workbook Functionality
- Topic A: Apply Data Validation
- Topic B: Search for Invalid Data and Formulas with Errors
- Topic C: Work with Macros
Using Lookup Functions, Dynamic Arrays, and Formula Auditing
- Topic A: Use Lookup Functions
- Topic B: Use Dynamic Arrays and Dynamic Array Functions
- Topic C: Trace Cells
- Topic D: Watch and Evaluate Formulas
Forecasting Data
- Topic A: Determine Potential Outcomes Using Data Tables
- Topic B: Determine Potential Outcomes Using Scenarios
- Topic C: Use the Goal Seek Feature
- Topic D: Forecast Data Trends
Creating Sparklines and Mapping Data
- Topic A: Create Sparklines
- Topic B: Map Data
Appendix A: Microsoft® Excel® 2021 Common Keyboard Shortcuts
Appendix B: Cube Functions
Appendix C: Importing and Exporting Data
- Topic A: Import and Export Data
Appendix D: Internationalizing Workbooks
- Topic A: Internationalize Workbooks
Appendix E: Working with Power Pivot
- Topic A: Work with Power Pivot
Appendix F: Advanced Customization Options
- Topic A: Customize Advanced Options
Appendix G: Working with Forms and Controls
- Topic A: Work with Forms and Controls
Appendix H: Using Array Formulas
- Topic A: Use Array Formulas
Hands0on LAB Activities
Working with Multiple Worksheets and Workbooks
- Creating Links
- Creating External Links
- Creating a 3-D Formula
- Consolidating Data
Sharing and Protecting Workbooks
- Fixing the Missing Alternative Text Error by Using the Accessibility Checker
- Exporting the Workbook in the PDF File Format
- Hiding the Worksheet
- Enabling Worksheet Protection
- Hiding the Formulas
Automating Workbook Functionality
- Enabling and Configuring the Settings for Data Validation
- Checking the Worksheet For Errors In Formulas
- Finding and Fixing Invalid Data Entries
- Saving the Workbook as a Macro-Enabled Workbook
Using Lookup Functions, Dynamic Arrays, and Formula Auditing
- Using the HLOOKUP Function
- Using the INDEX Function
- Using the MATCH Function
- Using the FILTER Function
- Using the SEQUENCE Function
- Using the SORT Function
- Using the LET Function
- Using the XLOOKUP Function
- Using the TRANSPOSE Function
- Using the VLOOKUP Function
- Using the UNIQUE Function
- Using the XMATCH Function
Forecasting Data
- Creating a Two-Variable Data Table
- Creating a Forecast Sheet
Creating Sparklines and Mapping Data
- Creating Sparklines
