View Class

Name	Excel 2010 Extreme Functions

Overview:

Overview: This two-day class gets you up to speed with functions in Microsoft Excel by teaching you the following:

• Mastering Excel Ranges and Formulas
• Harnessing the Power of Functions
• Building Business Models
• Building Financial Formulas
• And more!

Course Duration: 2 day (9 am - 5pm)

Prerequisites: Fundamental knowledge of Microsoft Office and mastery in Excel is helpful.

Course Content

Lesson 1: Getting the Most Our of Ranges

Advanced Range-Selection Techniques

Data Entry in a Range

Filling a Range

Using the Fill Handle

Creating a Series

Advanced Range Copying

Copying Selected Cell Attributes

Clearing a Range

Applying Conditional Formatting to a Range

Lesson 2: Using Range Names

Defining a Range Name

Working With Range Names

Lesson 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

Lesson 4: Creating Advanced Formulas

Working with Arrays

Understanding Array Formulas

Using Array Constants

Using Iteration and Circular References

Applying Data-Validation Rules to Cells

Using Dialog Box Controls on a Worksheet

Lesson 5: Troubleshooting Formulas

Understanding Excel’s Error Values

Fixing Other Formula Errors

Handling Formula Errors with IFERROR

Using the Formula Error Checker

Auditing a Worksheet

Lesson 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

Lesson 7: Working with Text Functions

Excel’s text Functions

Working with Characters and Codes

Converting Text

Formatting Text

Manipulating Text

Removing Unwanted Characters from a String

Extracting a Substring

Searching for Substrings

Substituting One Substring for Another

Lesson 8: Working with Logical and Information Functions

Adding Intelligence with Logical Functions

Case Study: Building an Accounts Receivable Aging Worksheet

Getting Data with Information Functions

Lesson 9: Working with Lookup Functions

Understanding Lookup Functions

The CHOOSE () Function

Looking Up Values in Tables

Lesson 10: Working with Date and Time Functions

How Excel Deals with Dates and Times

Using Excel’s Date Functions

Using Excel’s Time Functions

Lesson 11: Working with Math Functions

Understanding Excel’s Rounding Functions

Case Study: Rounding Billable Time

Summing Values

MOD () Function

Generating Random Numbers

Lesson 12: Working with Statistical Functions

Understanding Descriptive Statistics

Counting Items with Count Function

Calculating Extreme Values

Calculating Measures of Variation

Working with Frequency Distributions

Using the Analysis ToolPack Statistical Tools

Lesson 13: Analyzing Data with Tables

Converting a Range to a Table

Basic Table Operations

Sorting a Table

Filtering Table Data

Referencing Tables in Formulas

Excel’s Table Functions

Lesson 14: Analyzing Data with PivotTables

What Are PivotTables?

Building PivotTables

Working with PivotTable Subtotals

Changing the Data Field Summary Calculation

Creating Custom PivotTable Calculations

Lesson 15: Using Excel’s Business-Modeling Tools

Using What-If Analysis

Working with Goal Seek

Working with Scenarios

Lesson 16: Using Regression to Track Trends & Make Forecasts

Setting Up and Performing a Find

Choosing a Regression Method

Using Simple Regression on Linear Data

Lesson 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 Messages

Lesson 18: Building Loan Formulas

Understanding the Time Value of Money

Calculating the Loan Payment

Building a Loan Amortization Schedule

Calculating the Term of the Loan

Calculating Interest Rate Required for a Loan

Calculating How Much you Can Borrow

Lesson 19: Building Investment Formulas

Working with Interest Rates

Calculating the Future Value

Working Toward an Investment Goal

Lesson 20: Building Discount Formulas

Calculating the Present Value

Discounting Cash flows

Calculating the Payback Period