55270: Advanced Microsoft Excel Training

This module explains how to use pivot tables in Microsoft Excel.

Lessons

• Creating Pivot Tables
• More PivotTable Functionality
• Inserting Slicers
• Multi-Select Option in Slicers
• PivotTable Enhancements
• Working with Pivot Tables
• Inserting Pivot Charts
• More Pivot Table Functionality
• Working with Pivot Tables

Lab 1: Working with Pivot Tables

After completing this module, students will be able to:

• Use pivot tables to analyze data.
• Edit pivot tables.
• Format pivot tables.
• Insert pivot charts.
• Use Slicers.

This module explains how to audit worksheets.

Lessons

• Tracing Precedents
• Tracing Dependents
• Showing Formulas

Lab 1: Auditing Worksheets Exercises

• Tracing Precedents
• Tracing Dependents

After completing this module, students will be able to:

• Audit formulas.
• Trace precedents to determine which cells impact a specific cell.
• Trace dependents to determine which cells are impacted by a specific cell.
• Remove precedent and dependent arrows.
• Show formulas.
• Check for errors frequently found in formulas

This module explains how to work with data tools.

Lessons

• Converting Text to Columns
• Linking to External Data
• Controlling Calculation Options
• Data Validation
• Using Data Validation
• Consolidating Data
• Goal Seek
• Using Goal Seek

Lab 1: Data Tools Exercises

• Converting Text to Columns
• Using Data Validation
• Consolidating Data
• Using Goal Seek

After completing this module, students will be able to:

• Convert text to columns.
• Use Data Validation to restrict the type of data that can be entered into a cell.
• Consolidate data from a number of different ranges into one new range.
• Use Goal Seek to figure out the value to input to obtain a specific result

This module explains how to work with others in Microsoft Excel.

Lessons

• Protecting Worksheets and Workbooks
• Password Protecting a Workbook
• Password Protecting a Worksheet
• Password Protecting Ranges in a Worksheet
• Marking a Workbook as Final

Lab 1: Working with Others Exercises

• Password Protecting a Workbook
• Password Protecting a Worksheet
• Password Protecting Ranges in a Worksheet

After completing this module, students will be able to:

• Password protects a workbook.
• Password protects a worksheet.
• Password protection ranges in a worksheet.
• Track changes.
• Accept and reject changes.
• List all changes on a new sheet

This module explains how to record and use macros.

Lessons

• Recording Macros
• Running Macros
• Editing Macros
• Adding Macros to the Quick Access Toolbar

Lab 1: Recording and Using Macros Exercises

• Recording a Macro
• Adding a Macro to the Quick Access Toolbar

After completing this module, students will be able to:

• Record macros.
• Run macros.
• Edit macros (kind of).
• Add macros to the Quick Access Toolbar

This module explains how to use sparklines and other useful random features.

Lessons

• Sparklines
• Inserting and Customizing Sparklines
• Using Microsoft Translator
• Preparing a Workbook for Internationalization and Accessibility
• Importing and Exporting Files
• Importing Text Files
• Copying Data from Excel to Word
• Copying Charts from Excel to Word

Lab 1: Random Useful Items Exercises

• Inserting and Customizing Sparklines
• Importing Text Files
• Copying Data from Excel to Word
• Copying Charts from Excel to Word

After completing this module, students will be able to:

• Insert sparklines into worksheets.
• Customize sparklines by:
• Change the type of sparkline.
• Highlight key points.
• Change colors.
• Change the axis.
• Prepare a workbook for internationalization, accessibility, and compatibility.
• Import text files into Microsoft Excel.
• Copy cells from a worksheet into Microsoft Word.
• Copy and paste a chart into Microsoft Word.

This module explains how to save, share, and collaborate with others using the cloud.

Lessons

• Using the Cloud.

Lab 1: Using the Cloud

After completing this module, students will be able to:

• Use Excel in the Cloud.