Microsoft Office Excel 2019: Part 3

Microsoft Office Specialist (MOS): Microsoft Excel

ARMY IGNITED CRITERIA

Course Overview

This course will teach students about working with multiple worksheets and workbooks, sharing and protecting workbooks, automating workbook functionality, using Lookup functions and formula auditing, forecasting data, creating sparklines and mapping data, importing and exporting data, internationalizing workbooks, working with Power Pivot, advanced customization options, and working with forms and controls.

Chapter 01 - Working with Multiple Worksheets and Workbooks

• Topic A: Use Links and External References
• Linked Cells, External Link Syntax
• The Edit Links Dialog Box
• External References in Formulas and Functions
• Demo - Creating Links
• Topic B: Use 3-D References
• Grouped Worksheets, 3-D References
• 3-D References in Summary Functions
• Demo - Using 3-D References
• Topic C: Consolidate Data
• Data Consolidation
• Consolidated Dataset with Linked Source Data
• The Consolidate Dialog Box, Demo - Consolidating Data

Chapter 02 - Sharing and Protecting Workbooks

• Topic A: Collaborate on a Workbook
• Comments
• The Compare and Merge Workbooks Command
• The Share Options, Microsoft Accounts and OneDrive
• The PDF and XPS File Formats, Accessibility Options
• The Accessibility Checker
• Demo - Collaborating on a Workbook
• Topic B: Protect Worksheets and Workbooks
• Worksheet and Workbook Element Protection
• The Protect Sheet Command
• The Protect Workbook Command
• The Protect Workbook Options
• Metadata, The Document Inspector
• Demo - Protecting Worksheets and Workbooks

Chapter 03 - Automating Workbook Functionality

• Topic A: Apply Data Validation
• Data Validation, The Data Validation Dialog Box
• Data Validation Criteria
• The Input Message Tab, The Error Alert Tab
• Demo - Applying Data Validation
• Topic B: Search for Invalid Data and Formulas with Errors
• Invalid Data, The Circle Invalid Data Command
• The Error Checking Dialog Box, Error Types
• Demo - Searching for Invalid Data and Formulas with Errors
• Topic C: Work with Macros
• Macros, Macro Security Settings
• VBA, VBE
• The Record Macro Dialog Box
• The Macro Dialog Box
• Macro Names
• The Personal Workbook
• Demo - Creating a Macro
• Demo - Editing a Macro

Chapter 04 - Using Lookup Functions and Formula Auditing

• Topic A: Use Lookup Functions 
• Lookup Functions 
• The VLOOKUP Function, The HLOOKUP Function 
• The MATCH Function, The INDEX Function 
• The TRANSPOSE Function, Demo - Using Lookup Functions 
• Topic B: Trace Cells 
• Precedent and Dependent Cells 
• The Trace Precedents and Trace Dependents Commands, Trace Arrows 
• The Go To Dialog Box and Trace Arrows, The Go To Special Dialog Box 
• The Remove Arrows Options 
• Demo - Tracing Precedent and Dependent Cells 
• Topic C: Watch and Evaluate Formulas 
• The Watch Window 
• Formula Evaluation, The Evaluate Formula Dialog Box 
• Demo - Watching and Evaluating Formulas

Chapter 05 - Forecasting Data

• Topic A: Determine Potential Outcomes Using Data Tables 
• What-If Analysis, Data Tables 
• One-Variable Data Tables, Two-Variable Data Tables 
• The Data Table Dialog Box 
• Demo - Determining Potential Outcomes Using Data Tables 
• Topic B: Determine Potential Outcomes Using Scenarios 
• Scenarios, The Scenario Manager Dialog Box 
• The Add Scenario Dialog Box 
• The Scenario Values Dialog Box, The Scenario Command 
• Demo - Determining Potential Outcomes Using Scenarios 
• Topic C: Use the Goal Seek Feature 
• The Goal Seek Feature, The Goal Seek Dialog Box 
• Iterative Calculations 
• Demo - Using the Goal Seek Feature 
• Topic D: Forecast Data Trends 
• The Create Forecast Worksheet Dialog Box 
• Demo - Forecasting Data with the Forecast Sheet

Chapter 06 - Creating Sparklines and Mapping Data

• Topic A: Create Sparklines
• Sparklines, Sparkline Types
• The Create Sparklines Dialog Box
• The Sparkline Tools Contextual Tab
• Demo - Creating Sparklines
• Topic B: Map Data
• 3D Maps, Data Structuring, 3D Map Layer Pane
• Tours, Demo - Creating a 3D Map
• Chapter 06 Review

Chapter 07 - Importing and Exporting Data 

• Topic A: Import and Export Data 
• Importing Data, Exporting Data 
• Methods of Importing Text Files 
• The Text Import Wizard 
• The New Web Query Dialog Box 
• XML, XML Schemas, XML Maps 
• The XML Source Task Pane 
• The XML Maps Dialog Box 

Chapter 08 - Internationalizing Workbooks 

• Topic A: Internationalize Workbooks 
• Worksheet and Worksheet Tab Direction 
• The Symbol Dialog Box 
• International Number Formatting 

Chapter 09 - Working with Power Pivot

• Topic A: Work with Power Pivot, Power Pivot, The Power Pivot Tab 
• The Data Model 
• The Power Pivot User Interface 
• The Data and Diagram Views 
• The Create Relationship Dialog Box 
• Calculated Columns

Chapter 10 - Advanced Customization Options

• Topic A: Customize Advanced Options
• Formulas Options, Data Options
• Proofing Options
• The AutoCorrect Dialog Box
• Save Options, Language Options, Ease of Access Options
• Advanced Options, Custom Lists
• Add-Ins, The Add-Ins Tab
• The Trust Center

Chapter 11 - Working with Forms and Controls

• Topic A: Work with Forms and Controls
• Forms,
• Form Types
• The Developer Tab
• Form Controls
• Control Properties
• Demo - Adding and Editing Data Using a Data Form
• Demo - Adding Form Controls
• Chapter 11 Review
• Course Closure