Course Code: 5527

Excel 2016 Formulas and Functions

Class Dates:
10/17/2019
7/18/2019
8/15/2019
9/19/2019
Length:
1 Days
Cost:
$295.00
Class Time:
Technology:
Office, microsoft
Delivery:

Overview

  • Course Overview
  • This class is designed for students who use Excel regularly and want to explore formulas and functions.

    How You Will Benefit

    After completing this course, you will understand and be able to apply Excel formulas and functions to solve a variety of data analysis problems.

  • Audience

Course Disclaimer

*Course cost listed does not include the cost of courseware. Please contact us at learn@vtec.org or 207-775-0244 for additional pricing information, or if you have any questions. Course is subject to minimum enrollment.

Follow Up

  • Recommended Training:

Prerequisites

  • Before coming to the class, attendees should have an understanding of Excel fundamentals including formatting, navigation, and basic formulas.

  • Recommended Courses:

Course Details

  • What You Will Learn
  • Gain what-if capability with the IF, AND, OR, and CHOOSE functions
  • Cross-reference data with the table-lookup functions VLOOKUP, INDEX, and MATCH
  • Use frequently-overlooked Text functions to clean and manipulate data
  • Discover creative and powerful array formulas and functions
  • Summarize data with the COUNTIF, SUMIF family of functions
  • Instantly display all formulas in a worksheet
  • Highlight all formula cells in a worksheet; highlight all numerical value cells
  • Use entire row and column references in formulas
  • Debug formulas quickly and effectively with a keystroke shortcut
  • Use AutoSum (and its extended capabilities) more efficiently
  • 1. Getting the Most Out of Ranges
  • Advanced Range-Selection Techniques
  • Data Entry in a Range
  • Filling a Range
  • Creating a Series
  • Advanced Range Copying and Pasting
  • Clearing a Range
  • Applying Conditional Formatting to a Range
  • 2. Using Range Names
  • Defining a Range Name
  • Working with Range Names
  • 3. Building Basic Formulas
  • Understanding Formula Basics
  • Understanding Operator Precedence
  • Controlling Worksheet Calculation
  • Copying and Moving Formulas
  • Displaying Worksheet Formulas
  • Converting a Formula to a Value
  • Working with Range Names in Formulas
  • Working with Links in Formulas
  • Formatting Numbers, Dates, and Times
  • 4. Creating Advanced Formulas
  • Working with Arrays
  • Using Intergration and Circular References
  • Consolidating Multi-sheet Data
  • Applying Data-Validation Rules to Cells
  • Using Dialog Box Controls on a Worksheet
  • 5. Troubleshooting Formulas
  • Understanding Excel's Error Values
  • Fixing Other Formula Errors
  • Handling Formula Errors
  • Using the Formula Error Checker
  • Auditing a Worksheet
  • 6. Understanding Functions
  • About Excel's Functions
  • The Structure of a Function
  • Typing a Function into a Formula
  • Using the Insert Function Feature
  • Loading the Analysis ToolPak
  • 7. Working with Text Functions
  • Excel's Text Functions
  • Working with Characters and Codes
  • Converting Text
  • Formatting Text
  • Manipulating Text
  • Searching for Substrings
  • Substituting One Substring for Another
  • 8. Working with Logical and Information Functions
  • Adding Intelligence with Logical Functions
  • Getting Data with Information Functions
  • 9. Working with Lookup Functions
  • Excel's Lookup Functions
  • Understanding Lookup Tables
  • The Choose Function
  • Looking Up Values in Tables
  • 10 Working with Date and Time Functions
  • How Excel Deals with Dates and Times
  • Using Excel's Date Functions
  • Using Excel's Time Functions
  • 11. Working with Math Functions
  • Excel's Math and Trig Functions
  • Understanding Excel's Rounding Functions
  • Summing Values
  • Generating Random Numbers
  • 12. Working with Statistical Functions
  • Excel's Statistical Functions
  • Understanding Descriptive Statistics
  • Counting Items with the Count Function
  • Calculating Averages
  • Calculating Extreme Values
  • Calculating Measures of Variation
  • Working with Frequency Distributions
  • Using the Analysis ToolPak Statistical Tools
  • 13. Analyzing Data with Tables
  • Planning an Excel Table
  • Converting a Range to a Table
  • Basic Table Operations
  • Sorting a Table
  • Filtering Table Data
  • Referencing Tables in Formulas
  • Excel's Table Functions
  • 14. Analyzing Data with Pivot Tables
  • What Are PivotTables?
  • Building PivotTables
  • Working with PivotTable Subtotals
  • Changing the Data Field Summary Calculation
  • Creating Custom PivotTable Calculations
  • Using PivotTable Results in a Worksheet Formula
  • 15. Using Excel's Business Modeling Tools
  • Using What-If Analysis
  • Working with Goal Seek
  • Working with Scenarios
  • 16. Using Regression to Track Trends and Make Forecasts
  • Choosing a Regression Method
  • Using Simple Regression on Linear Data
  • Using Simple Regression on Nonlinear Data
  • Using Multiple Regression Analysis
  • 17. Solving Complex Problems with Solver
  • Some Background on Solver
  • Loading Solver
  • Using Solver
  • Adding Constraints
  • Saving a Solution as a Scenario
  • Setting Other Solver Options
  • Making Sense of Solver's Message
  • Displaying Solver's Reports
  • 18. Building Loan Formulas
  • Understanding the Time Value of Money
  • Calculating a Loan Payment
  • Building a Loan Amortization Schedule
  • Calculating the Term of a Loan
  • Calculating the Interest Rate Required for a Loan
  • Calculating How Much You Can Burrow
  • 19. Building Investment Formulas
  • Working with Interest Rates
  • Calculating the Future Value
  • Working Toward an Investment Goal
  • 20. Building Discount Formulas
  • Calculating the Present Value
  • Discounting Cash Flows
  • Calculating the Payback Period
  • Calculating the Internal Rate of Return