Module 1: Advanced Formulas

This module explains how to work with advanced formulas in Excel.

Lessons

• Using Named Ranges in Formulas
• Using Formulas That Span Multiple Worksheets
• Using the IF Function
• Using the PMT Function
• Using the LOOKUP Function
• Using the VLOOKUP Function
• Using the HLOOKUP Function
• Using the CONCATENATE Function
• Using the TRANSPOSE Function
• Using the PROPER, UPPER, and LOWER Functions
• Using the LEFT, RIGHT, and MID Functions
• Using Date Functions
• Creating Scenarios

Lab 1: Advanced Formulas

• Using Named Ranges in Formulas
• Entering a Formula Using Data in Multiple Worksheets
• Using the IF Function
• Using the PMT Function
• Using the VLOOKUP Function
• Using the CONCATENATE Function
• Using the PROPER Function
• Using the LEFT and RIGHT Functions
• Using the YEAR, MONTH, and DAY Functions

After completing this module, students will be able to:

• Name and label cells and ranges of cells.
• Use names and labels in formulas.
• Create formulas that span multiple worksheets.
• Use the conditional IF function and its variants in formulas.
• Use the PMT function to calculate payments for loans.
• Use the VLOOKUP function.
• Use the HLOOKUP function.
• Use the CONCATENATE function to join the contents of numerous cells.
• Use the PROPER, UPPER, and LOWER functions to alter the casing of text.
• Use the LEFT, RIGHT, and MID functions to return characters from the start or end of a string, or a specific number of text characters.
• Use various date functions.

Module 2: Working with Lists

This module explains how to work with lists in Microsoft Excel 2013.

Lessons

• Converting a List to a Table
• Removing Duplicates from a List
• Sorting Data in a List
• Filtering Data in a List
• Adding Subtotals to a List

Lab 1: Working with Lists

• Converting a List to a Table
• Removing Duplicates from a List
• Sorting Data in a List
• Filtering Data in a List
• Adding Subtotals to a List

After completing this module, students will be able to:

• Convert data into tables.
• Remove duplicates from tables.
• Sort data in Excel.
• Filter data in Excel.
• Use subtotals to automatically total related data.
• Group and ungroup data.

Module 3: Working with Illustrations

Adding illustrations to Microsoft Excel worksheets is a great way to improve their look and feel, and also to illustrate your points. Fortunately, it’s easy to add pictures (your own or choose from a large library of pictures provided by Microsoft) and shapes.

Lessons

• Working with Clip Art
• Using Shapes
• Working with SmartArt

Lab 1: Working with Illustrations

• Working with Clip Art
• Adding Shapes

After completing this module, students will be able to:

• Add pictures to your worksheets.
• Use Clip Art to illustrate your worksheets.
• Insert shapes into Microsoft Excel worksheets.
• Use SmartArt to illustrate your worksheets.

Module 4: Visualizing Your Data

This module explains how to use techniques for visualizing your data.

Lessons

• Inserting Charts
• Add and Format Objects
• Insert a Text Box
• Create a Custom Chart Template

Lab 1: Visualizing Your Data

• Inserting Charts
• Editing Charts
• Changing the Layout of a Chart
• Changing the Style of a Chart
• Adding a Shape to a Chart
• Adding a Trendline to a Chart
• Adding a Secondary Axis to a Chart
• Adding Additional Data Series to a Chart
• Switch between Rows and Columns in a Chart
• Positioning a Chart
• Modifying Chart and Graph Parameters
• Watching Animation in a Chart
• Showing, Hiding, or Changing the Location of the Legend in a Chart
• Show or Hiding the Title of a Chart
• Changing the Title of a Chart
• Editing Charts

After completing this module, students will be able to:

• Create charts that enable you to visualize your data.
• Choose what data is displayed in your charts.
• Show and hide data labels.
• Show and hide the legend.
• Show and hide the chart title.
• Add a picture or shape to a chart.
• Change the way text displays in a chart.
• Change the fill color of a chart.
• Add and format objects in a worksheet.

Module 5: Working with Tables

This Working with tables in Excel 2013 can help you organize your data. In Excel, you can group data together into a table and then manipulate that table.

Lessons

• Format Data as a Table
• Move between Tables and Ranges
• Modify Tables
• Define Titles

Lab 1: Working with Tables

• Creating and Modifying a Table in Excel

After completing this module, students will be able to:

• Format data in Excel as a table.
• Modify Excel tables.

Module 6: Advanced Formatting

This module explains how to use advanced formatting techniques in Excel 2013.

Lessons

• Applying Conditional Formatting
• Working with Styles
• Creating and Modifying Templates

Lab 1: Advanced Formatting

• Working with Styles

After completing this module, students will be able to:

• Use conditional formatting to display cells differently based on their values.
• Quickly format tables using styles.
• Format cells using styles.

Module 7: Microsoft Excel 2013 New Features

Excel 2013 provides a number of new functions and tools to help make working with worksheets easier and more efficient.

Lessons

• New Functions in Excel 2013
• Using New Chart Tools
• Using the Quick Analysis Tool
• Using the Chart Recommendation Feature

Lab 1: Microsoft Excel 2013 New Features

• Using the New Excel Functions
• Using the New Excel 2013 Chart Tools
• Using the Quick Analysis Tool

After completing this module, students will be able to:

• Use new functions available in Excel 2013.
• Use the new chart tools in Excel 2013.
• Use the Quick Analysis tool.
• Use the Chart Recommendation feature.