Excel 2016 Formulas and Functions
CLASS DATE(s):
9/14/2020
10/8/2020
11/9/2020
12/7/2020

COURSE LENGTH: 1 Day

COURSE COST: $295.00

COURSE TIMES: 9:00am - 4:30pm

Printable version of this course
print
Register for this course
register

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 AND PREREQUISITES

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

  

*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.

COURSE TOPICS:


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