Course Overview
Target audiences
- Students who have basic skills with Microsoft Excel 2016 who want to learn intermediate-level skills or students who want to learn the topics covered in this course in the 2016 interface.
Schedule Dates
Curriculum
-
Module 1: Advanced Formulas
This module explains how to work with formulas.
- • Using Named Ranges in Formulas
- • Naming a Single Cell
- • Naming a Range of Cells
- • Naming Multiple Single Cells Quickly
- • Using Formulas That Span Multiple Worksheets
- • Using the IF Function
- • Using AND/OR Functions
- • Using the SUMIF, AVERAGEIF, and COUNTIF Functions
- • 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
- • The UPPER Function
- • The LOWER function
- • The TRIM Function
- • Using the LEFT, RIGHT, and MID Functions
- • The MID Function
- • Using Date Functions
- • Using the NOW and TODAY Functions
- • Creating Scenarios
- • Utilize the Watch Window
- • Consolidate Data
- • Enable Iterative Calculations
- • What-If Analyses
- • Use the Scenario Manager
- • Use Financial Functions
- 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 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 LOOKUP function.
- • Use the VLOOKUP function.
- • Use the HLOOKUP function.
- • Use the CONCATENATE function to join the contents of numerous cells.
- • Use the TRANSPOSE function.
- • 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.
- • 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
- • Grouping and Ungrouping Data in 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
This module explains how to work with illustrations.
- • 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.
- • U 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 work with charts, objects, and text boxes.
- • Creating a Custom Chart Template
- • Inserting Charts
- • Add and Format Objects
- • Insert a Text Box
- • Create a Custom Chart Template
- Lab 1: Visualizing Your Data
- • Inserting Charts
- • 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 module explains how to work with tables.
-
Module 6: Advanced Formatting
This module explains how work with formatting options in Excel.
- • Applying Conditional Formatting
- • Working with Styles
- • Creating and Modifying Templates
- Lab 1: Advanced Formatting
- • Using Conditional 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.
-
Module 7: Microsoft Excel Features that Were New in 2013
This module explains how to use some features that were new in 2013.
- • New Functions in Excel 2013
- • Using New Chart Tools
- • Using the Quick Analysis Tool
- • Using the Chart Recommendation Feature
- Lab 1: Microsoft Excel Features that Were New in 2013
- • 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 the new chart tools in Excel.
- • Use the Quick Analysis tool.
- • Use the Chart Recommendation feature.
-
Module 8: Features New in 2016
This module explains how to use the new charts in 2016.