Advanced Microsoft Excel

Module 1: Using Pivot Tables

• 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 : 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.

 

Module 2: Auditing Worksheets

• This module explains how to audit worksheets.

 

Lessons

• Tracing Precedents
• Tracing Dependents
• Showing Formulas
• Lab : 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

 

Module 3: Data Tools

• 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 : 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

 

Module 4: Working with Others

• This module explains how 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 : Working with Others Exercises
• Password Protecting a Workbook
• Passsword Protecting a Worksheet
• Password Protecting Ranges in a Worksheet

 

After completing this module, students will be able to:

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

 

Module 5: Recording and Using Macros

• This module explains how record and use macros.

 

Lessons

• Recording Macros
• Recording a Macro
• Running Macros
• Editing Macros
• Adding Macros to the Quick Access Toolbar
• Adding a Macro to the Quick Access Toolbar
• Lab : 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

 

Module 6: Random Useful Items

• 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 : 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.

 

Module 7: Cloud

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

 

Lessons

• Using the Cloud.
• Lab : Using the Cloud

 

After completing this module, students will be able to:

• Use Excel in the Cloud.

• Create pivot tables and charts.
• Learn to trace precedents and dependents.
• Convert text and validate and consolidate data.
• Collaborate with others by protecting worksheets and workbooks.
• Create, use, edit, and manage macros.
• Import and export data.

 

 

• Experienced Excel users who want to learn the topics covered in this advanced course in the 2019 interface.