VTEC Training

VTEC Training

Portland,
ME

Class Dates:

5/7/2020

7/6/2020

8/11/2020

Length:

1 Days

Cost:

$295.00

Class Time:

Technology:

Office

Delivery:

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

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

- 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