Course Outline

Module One: Getting Started

• Workshop Objectives

Module Two: Manage Workbook Options and Settings

• Manage Workbooks
• Save a Workbook as a Template
• Reference Data in Another Workbook
• Reference Data by Using Structured References
• Display Hidden Ribbon Tabs

Manage Workbook Review..

• Mark a Workbook as Final
• Protect a Workbook with a Password.
• Protect a Worksheet to Restrict Editing.
• Protect Workbook Structure.
• Manage Workbook Versions.
• Setting Calculation Options.

Module Two: Review Questions.

Module Three: Apply Custom Data Formats and Layouts.

Prepare a Workbook for Internationalization.

• Apply International Currency Formats.
• Apply Locale to Date or Time Formats.

Apply Custom Data Formats and Validation.

• Create Custom Number Formats.
• Populate Cells by Using Advanced Fill Series Options.
• Configure Data Validation.

Apply Advanced Conditional Formatting and Filtering.

• Create Custom Conditional Formatting Rules.
• Create Conditional Formatting Rules that Use Formulas.
• Manage Conditional Formatting Rules.

Create and Modify Custom Workbook Elements.

• Create Custom Color Formats.
• Create Custom Font Sets.
• Create and Modify Cell Styles.
• Create and Modify Custom Themes.
• Manage Multiple Options for Theme Fonts.
• Insert and Configure Form Controls.

Work with Macros.

• Enable Macros.
• Record a Macro.
• Run a Macro.
• Edit a Macro.
• Copy Macros from One Workbook to Another.

Module Three: Review Questions.

Module Four: Create Advanced Formulas.

Define Named Ranges and Objects.

• Name Cells.
• Use Named Cells in a Formula.
• Manage Named Ranges and Objects.

Apply Functions in Formulas.

• Perform the AND Function and the OR Function.
• Perform NOT Function.
• Perform Logical Operations by Using Nested Functions.
• Perform SUMIFS, AVERAGEIFS, and COUNTIFS Functions.

Look Up Data by Using Functions.

• Look Up Data by Using the VLOOKUP Function.
• Look Up Data by Using the HLOOKUP Function.
• Look Up Data by using the MATCH Function.
• Look Up Data by Using the INDEX Function.

Apply Advanced Time and Date Functions.

• Serialize Numbers by Using Date and Time Functions.
• Reference the Date and Time Using the NOW and TODAY functions.

Perform Data Analysis and Business Intelligence.

• Import, Transform, Combine, Display, and Connect to Data.
• Consolidate Data.
• Perform What-If Analysis by Using Goal Seek.
• Perform What-If Analysis by Using Scenario Manager.

Troubleshoot Formulas.

• Trace Precedence and Dependence.
• Monitor Cells and Formulas using the Watch Window..
• Validate Formulas by Using Error Checking Rules.
• Evaluate Formulas.

Module Four: Review Questions.

Module Five: Create Advanced Charts and Tables.

Create Advanced Charts.

• Add Trendlines to Charts.
• Create Dual-Axis Charts.
• Save a Chart as a Template.

Create and Manage PivotTables.

• Create Slicers.
• Create PivotTables.
• Modify Field Selections and Options.
• Group PivotTable Data.
• Reference Data in a PivotTable by Using the GETPIVOTDATA Function.
• Add Calculated Fields.
• Format Data.

Create and Manage PivotCharts.

• Create PivotCharts.
• Drill Down into PivotChart Details.
• Apply Styles to PivotCharts.
• Manipulate Options in Existing PivotCharts.

Module Five: Review Questions.

Module Six: Wrapping Up.

• End of Course Assessments and SUmmary