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