Microsoft Excel 2016

BEGINNING

Module 1: ​Creating a Worksheet and a Chart

OBJECTIVES:
  • ​Describe the Excel worksheet
  • Enter text and numbers
  • Use the Sum button to sum a range of cells
  • Enter a simple function
  • Copy the contents of a cell to a range of cells using the fill handle
  • Apply cell styles
  • Format cells in a worksheet
  • Create a 3-D pie chart
  • Change a worksheet name and sheet tab color
  • Change document properties
  • Preview and print a worksheet
  • Use the AutoCalculate area to display statistics
  • Correct errors on a worksheet

Module 2: ​Formulas, Functions and Formatting

OBJECTIVES:
  • Use Flash Fill
  • Enter formulas using the keyboard
  • Enter formulas using Point mode
  • Apply the MAX, MIN, and AVERAGE functions
  • Verify a formula using Range Finder
  • Apply a theme to a workbook
  • Apply a date format to a cell or range
  • Add conditional formatting to cells
  • Change column width and row height
  • Check the spelling on a worksheet
  • Change margins and headers in Page Layout view
  • Preview and print versions and sections of a worksheet

Module 3: ​Working with Large Worksheets, Charting and What-If Analysis

OBJECTIVES:
  • Rotate text in a cell
  • Create a series of month names
  • Copy, paste, insert, and delete cells
  • Format numbers using format symbols
  • Enter and format the system date
  • Use absolute and mixed cell references in a formula
  • Use the IF function to perform a logical test
  • Create and format sparkline charts
  • Change sparkline chart types and styles
  • Use the Format Painter button to format cells
  • Create a clustered column chart on a separate chart sheet
  • Use chart filters to display a subset of data in a chart
  • Change the chart style and type
  • Reorder worksheet tabs
  • Change the worksheet view
  • Freeze and unfreeze rows and columns
  • Answer what-if questions
  • Goal seek to answer what-if questions
  • Use the Smart Lookup insight
  • Understand accessibility features

Module 4: ​Financial Functions, Data Tables and Amortization Schedules

OBJECTIVES:
  • Assign a name to a cell and refer to the cell in a formula using the assigned name
  • Determine the monthly payment of a loan using the financial function PMT
  • Use the financial functions PV (present value) and FV (future value)
  • Create a data table to analyze data in a worksheet
  • Create an amortization schedule
  • Control the color and thickness of outlines and borders
  • Add a pointer to a data table
  • Analyze worksheet data by changing values
  • Use names and print sections of a worksheet
  • Set print options
  • Protect and unprotect cells in a worksheet
  • Hide and unhide worksheets and workbooks
  • Use the formula checking features of Excel

INTERMEDIATE

Module 5: Working with Multiple Worksheets and Workbooks

OBJECTIVES:
  • Format a consolidated worksheet
  • Fill using a linear series
  • Use date, time, and rounding functions
  • Apply a custom format code
  • Create a new cell style
  • Copy a worksheet
  • Drill to add data to multiple worksheets at the same time
  • Select and deselect sheet combinations
  • Enter formulas that use 3-D cell references
  • Use the Paste gallery
  • Format a 3-D pie chart with an exploded slice and lead lines
  • Save individual worksheets as separate workbook files
  • View and hide multiple workbooks
  • Consolidate data by linking separate workbooks

Module 6: Creating, Sorting, and Querying a Table

OBJECTIVES:
  • Create and manipulate a table
  • Delete duplicate records
  • Add calculated columns to a table with structured references
  • Use the VLOOKUP function to look up a value in a table
  • Use icon sets with conditional formatting
  • Insert a total row
  • Sort a table on one field or multiple fields
  • Sort, query, and search a table using AutoFilter
  • Remove filters
  • Create criteria and extract ranges
  • Apply database and statistical functions
  • Use the MATCH and INDEX functions to find a value in a table
  • Display automatic subtotals
  • Use outline features to group, hide, and unhide data
  • Create a treemap chart

Module 7: Creating Templates, Importing Data, and Working with SmartArt, Images, and Screenshots

OBJECTIVES:
  • Create and use a template
  • Import data from a text file, an Access database, a webpage, and a Word document
  • Use text functions
  • Paste values and paste text
  • Transpose data while pasting it
  • Convert text to columns
  • Replicate formulas
  • Use the Quick Analysis tool
  • Find and replace data
  • Insert and format a bar chart
  • Insert and modify a SmartArt graphic
  • Add pictures to a SmartArt graphic
  • Apply text effects
  • Include a hyperlinked screenshot

Module 8: Working with Trendlines, PivotTables, PivotCharts, and Slicers

OBJECTIVES:
  • ​Analyze worksheet data using a trendline
  • Create a PivotTable report
  • Format a PivotTable report
  • Apply filters to a PivotTable report
  • Create a PivotChart report
  • Format a PivotChart report
  • Apply filters to a PivotChart report
  • Analyze worksheet data using PivotTable and PivotChart reports
  • Create calculated fields
  • Create slicers to filter PivotTable and PivotChart reports
  • Format slicers
  • Analyze PivotTable and PivotChart reports using slicers

ADVANCED

Module 9: Formula Auditing, Data Validation, and Complex Problem Solving

OBJECTIVES:
  • ​Use formula auditing techniques to analyze a worksheet
  • Trace precedents and dependents
  • Use error checking to identify and correct errors
  • Add data validation rules to cells
  • Enable the Solver add-in
  • Use trial and error to solve a problem on a worksheet
  • Use goal seeking to solve a problem
  • Circle invalid data on a worksheet
  • Use Solver to solve a complex problem
  • Use the Scenario Manager to record and save sets of what-if assumptions
  • Create a Scenario Summary report
  • Create a Scenario PivotTable report

Module 10: Data Analysis with Power Tools and Creating Macros

OBJECTIVES:
  • ​Explain Excel’s power tools
  • Customize the ribbon and enable data analysis
  • Use the Get & Transform data commands
  • Create a query using Query Editor
  • Build a PivotTable using Power Pivot
  • Explain data modeling
  • Create a measure
  • View cube functions
  • Use Power View
  • Create tiles in a Power View report
  • Use 3D Maps
  • Save a tour as an animation
  • Explain Power BI
  • Create hyperlinks
  • Use the macro recorder to create a macro
  • Execute a macro

Module 11: User Interfaces, Visual Basic for Applications (VBA), and Collaboration Features in Excel​

OBJECTIVES:
  • ​Add and configure worksheet form controls such as command buttons, option buttons, and check boxes
  • Record user input to another location on the worksheet
  • Understand Visual Basic Applications (VBA) code and explain event-driven programs
  • Explain sharing and collaboration techniques
  • Use passwords to assign protected and unprotected status a to a worksheet
  • Compare and merge workbooks
  • Review a digital signature on a workbook
  • Insert, edit, delete, and review comments in a workbook
  • Manage tracked changes in a shared workbook
  • Format a worksheet background
  • Enhance charts and sparklines
  • Save a custom view of a worksheet