Microsoft Excel 2019

Microsoft Excel is a spreadsheet program. That means it's used to create grids of text, numbers and formulas specifying calculations. That's extremely valuable for many businesses, which use it to record expenditures and income, plan budgets, chart data and succinctly present fiscal results.

It can be programmed to pull in data from external sources such as stock market feeds, automatically running the data through formula such as financial models to update such information in real time. Like Microsoft Word, Excel has become a de facto standard in the business world, with Excel spreadsheets frequently emailed and otherwise shared to exchange data and perform various calculations.
​
Excel also contains fairly powerful programming capabilities for those who wish to use them that can be used to develop relatively sophisticated financial and scientific computation capabilities.

Introductory

Module 1: Getting Started with Excel

OBJECTIVES:
  • Open and close a workbook
  • Navigate through a workbook and worksheet
  • Select cells and ranges
  • Plan and create a workbook
  • Insert, rename, and move worksheets
  • Enter text, dates, and numbers
  • Undo and redo actions
  • Resize columns and rows
  • Enter formulas and the SUM and COUNT functions
  • Copy and paste formulas
  • Move or copy cells and ranges
  • Insert and delete rows, columns, and ranges
  • Create patterned text with Flash Fill
  • Add cell borders and change font size
  • Change worksheet views
  • Prepare a workbook for printing

Module 2: Formatting Workbook Text and Data

OBJECTIVES:
  • Change fonts, font style, and font color
  • Add fill colors and a background image
  • Create formulas to calculate sales data
  • Format numbers as currency and percentages
  • Format dates and times
  • Align, indent, and rotate cell contents
  • Merge a group of cells
  • Use the AVERAGE function
  • Apply cell styles
  • Copy and paste formats with the Format Painter
  • Find and replace text and formatting
  • Change workbook themes
  • Highlight cells with conditional formats
  • Format a worksheet for printing

Module 3: Performing Calculations with Formulas and Functions

OBJECTIVES:
  • Translate an equation into a function
  • Do calculations with dates and times
  • Extend data and formulas with AutoFill
  • Use the Function Library
  • Calculate statistics
  • Using the Quick Analysis toolbar
  • Use absolute and relative cell references
  • Use a logical function
  • Retrieve data with lookup tables
  • Do what-if analysis with Goal Seek

Module 4: Analyzing and Charting Financial Data

OBJECTIVES:
  • Create a pie chart
  • Format chart elements
  • Create a line chart
  • Work with chart legends
  • Create a combination chart
  • Create a scatter chart
  • Edit a chart data source
  • Create a data callout
  • Insert shapes and icons into a worksheet
  • Create and edit a data bar
  • Create and edit a group of sparklines

Intermediate

Module 5: Generating Reports from Multiple Worksheets and Workbooks

OBJECTIVES:
  • Copy worksheets between workbooks
  • View a workbook in multiple windows
  • Organize worksheets in a worksheet group
  • Write a 3-D reference
  • Write an external reference
  • Manage the security features of linked documents
  • Create a hyperlink to a document source
  • Link to an email address
  • Create and apply a named range
  • Work with name scope
  • Create a workbook template

Module 6: Managing Data with Data Tools

OBJECTIVES:
  • Split a workbook window into panes
  • Highlight and remove duplicate values in a data range
  • Sort a data range by one or more fields
  • Add subtotals to a data range
  • Find and select workbook cells
  • Filter data based on one or more fields
  • Create an advanced filter
  • Convert a data range to an Excel table
  • Work with table styles and table elements
  • Create and apply a slicer
  • Calculate summary statistics with the SUBTOTAL function
  • Design and create an interactive dashboard

Module 7: Summarizing Data with PivotTables

OBJECTIVES:
  • Do approximate match lookups
  • Work with logical functions
  • Calculate statistics with summary IF functions
  • Create a PivotTable
  • Change a PivotTable layout
  • Format a PivotTable
  • Create a PivotChart
  • Apply a slicer to multiple PivotTables
  • Create a timeline slicer

Module 8: Performing What-If Analyses

OBJECTIVES:
  • Explore the principles of cost-volume-profit relationships
  • Create a one-variable data table
  • Create a two-variable data table
  • Create and apply different Excel scenarios with the Scenario Manager
  • Generate a scenario summary report
  • Generate a scenario PivotTable report
  • Explore the principles of a product mix
  • Run Solver to calculate optimal solutions
  • Create and apply constraints to a Solver model
  • Save and load a Solver model

Advanced

Module 9: Exploring Financial Tools and Functions

OBJECTIVES:
  • Work with financial functions to analyze loans and investments
  • Create an amortization schedule
  • Calculate interest and principal payments for a loan or investment
  • Perform calculations for an income statement
  • Interpolate and extrapolate a series of values
  • Calculate a depreciation schedule
  • Determine a payback period
  • Calculate a net present value
  • Calculate an internal rate of return
  • Trace a formula error to its source

Module 10: Analyzing Data with Business Intelligence Tools

OBJECTIVES:
  • Retrieve data with the Query Editor
  • Create and edit a query
  • Chart trends and forecast future values
  • Add data to the Excel Data Model
  • Manage table relations in Power Pivot
  • Create PivotTable drawing data from several connected tables
  • Drill through a hierarchy of fields
  • Create maps with the map chart type
  • Create Map presentations with 3D maps

Module 11: Exploring PivotTable Design

OBJECTIVES:
  • Change a PivotTable layout
  • Display and hide PivotTable grand totals and subtotals
  • Sort PivotTable contents
  • Filter PivotTable contents
  • Group items within a PivotTable field
  • Apply calculations to a PivotTable
  • Create PivotTable conditional formats
  • Manage the PivotTable cache
  • Create calculated items and calculated fields
  • Analyze PivotTables based on the Data Model
  • Create a table measure using DAX
  • Retrieve PivotTable data with the GETPIVOTDATA function
  • Explore Excel Database functions

Module 12: Developing an Excel Application

OBJECTIVES:
  • Create a WordArt graphic
  • Plot data with a funnel chart
  • Hide error values with the IFERROR function
  • Validate data entry
  • Hide worksheet rows and columns
  • Hide worksheets
  • Protect worksheets and workbooks from edits
  • Unlock worksheets cells to allow edits
  • Display the Developer tab
  • Record and run a macro
  • Assign a macro to graphic or macro button
  • Edit macro code in an editor