CLASS DATE(s):

5/7/2020

7/6/2020

8/11/2020

COURSE LENGTH: 1 Day

COURSE COST: $295.00

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

print

register

5/7/2020

7/6/2020

8/11/2020

COURSE LENGTH: 1 Day

COURSE COST: $295.00

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

register

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.

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.

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

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

Defining a Range Name

Working with Range Names

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

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

Understanding Excel's Error Values

Fixing Other Formula Errors

Handling Formula Errors

Using the Formula Error Checker

Auditing a Worksheet

About Excel's Functions

The Structure of a Function

Typing a Function into a Formula

Using the Insert Function Feature

Loading the Analysis ToolPak

Excel's Text Functions

Working with Characters and Codes

Converting Text

Formatting Text

Manipulating Text

Searching for Substrings

Substituting One Substring for Another

Adding Intelligence with Logical Functions

Getting Data with Information Functions

Excel's Lookup Functions

Understanding Lookup Tables

The Choose Function

Looking Up Values in Tables

How Excel Deals with Dates and Times

Using Excel's Date Functions

Using Excel's Time Functions

Excel's Math and Trig Functions

Understanding Excel's Rounding Functions

Summing Values

Generating Random Numbers

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

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

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

Using What-If Analysis

Working with Goal Seek

Working with Scenarios

Choosing a Regression Method

Using Simple Regression on Linear Data

Using Simple Regression on Nonlinear Data

Using Multiple Regression Analysis

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

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

Working with Interest Rates

Calculating the Future Value

Working Toward an Investment Goal

Calculating the Present Value

Discounting Cash Flows

Calculating the Payback Period

Calculating the Internal Rate of Return