Introduction
Part I: Mastering Excel Ranges and Formulas
Chapter 1 Getting the Most Out of Ranges
Advanced Range-Selection Techniques
Mouse Range-Selection Tricks
Keyboard Range-Selection Tricks
Working with 3D Ranges
Selecting a Range Using Go To
Using the Go To Special Dialog Box
Data Entry in a Range
Filling a Range
Using the Fill Handle
Flash-Filling a Range
Creating a Series
Advanced Range Copying
Copying Selected Cell Attributes
Combining Two Ranges Arithmetically
Transposing Rows and Columns
Clearing a Range
Applying Conditional Formatting to a Range
Creating Highlight Cells Rules
Creating Top/Bottom Rules
Adding Data Bars
Adding Color Scales
Adding Icon Sets
From Here
Chapter 2 Using Range Names
Defining a Range Name
Working with the Name Box
Using the New Name Dialog Box
Changing the Scope to Define Sheet-Level Names
Using Worksheet Text to Define Names
Naming Constants
Working with Range Names
Referring to a Range Name
Working with Name AutoComplete
Navigating Using Range Names
Pasting a List of Range Names in a Worksheet
Displaying the Name Manager
Filtering Names
Editing a Range Name's Coordinates
Adjusting Range Name Coordinates Automatically
Changing a Range Name
Deleting a Range Name
Using Names with the Intersection Operator
From Here
Chapter 3 Building Basic Formulas
Understanding Formula Basics
Formula Limits in Excel 2013
Entering and Editing Formulas
Using Arithmetic Formulas
Using Comparison Formulas
Using Text Formulas
Using Reference Formulas
Understanding Operator Precedence
The Order of Precedence
Controlling the Order of Precedence
Controlling Worksheet Calculation
Copying and Moving Formulas
Understanding Relative Reference Format
Understanding Absolute Reference Format
Copying a Formula Without Adjusting Relative References
Displaying Worksheet Formulas
Displaying All Worksheet Formulas
Displaying a Cell's Formula Using FORMULATEXT()
Converting a Formula to a Value
Working with Range Names in Formulas
Pasting a Name into a Formula
Applying Names to Formulas
Naming Formulas
Working with Links in Formulas
Understanding External References
Updating Links
Changing the Link Source
Formatting Numbers, Dates, and Times
Numeric Display Formats
Date and Time Display Formats
Deleting Custom Formats
From Here
Chapter 4 Creating Advanced Formulas
Working with Arrays
Using Array Formulas
Using Array Constants
Functions That Use or Return Arrays
Using Iteration and Circular References
Consolidating Multisheet Data
Consolidating by Position
Consolidating by Category
Applying Data-Validation Rules to Cells
Using Dialog Box Controls on a Worksheet
Displaying the Developer Tab
Using the Form Controls
Adding a Control to a Worksheet
Linking a Control to a Cell Value
Understanding the Worksheet Controls
From Here
Chapter 5 Troubleshooting Formulas
Understanding Excel's Error Values
#DIV/0!
#N/A
#NAME?
Case Study: Avoiding #NAME? Errors When Deleting Range Names
#NULL!
#NUM!
#REF!
#VALUE!
Fixing Other Formula Errors
Missing or Mismatched Parentheses
Erroneous Formula Results
Fixing Circular References
Handling Formula Errors with IFERROR()
Using the Formula Error Checker
Choosing an Error Action
Setting Error Checker Options
Auditing a Worksheet
Understanding Auditing
Tracing Cell Precedents
Tracing Cell Dependents
Tracing Cell Errors
Removing Tracer Arrows
Evaluating Formulas
Watching Cell Values
From Here
Part II: Harnessing the Power of Functions
Chapter 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
From Here
Chapter 7 Working with Text Functions
Excel's Text Functions
Working with Characters and Codes
The CHAR() Function
The CODE() Function
Converting Text
The LOWER() Function
The UPPER() Function
The PROPER() Function
The DECIMAL() Function
The NUMBERVALUE() Function
Formatting Text
The DOLLAR() Function
The FIXED() Function
The TEXT() Function
Displaying When a Workbook Was Last Updated
Manipulating Text
Removing Unwanted Characters from a String
The TRIM() Function
The CLEAN() Function
The REPT() Function: Repeating a Character
Padding a Cell
Building Text Charts
Extracting a Substring
The LEFT() Function
The RIGHT() Function
The MID() Function
Converting Text to Sentence Case
A Date-Conversion Formula
Case Study: Generating Account Numbers
Searching for Substrings
The FIND() and SEARCH() Functions
Extracting a First Name or Last Name
Extracting First Name, Last Name, and Middle Initial
Determining the Column Letter
Substituting One Substring for Another
The REPLACE() Function
The SUBSTITUTE() Function
Removing a Character from a String
Removing Two Different Characters from a String
Case Study: Generating Account Numbers, Part II
Removing Line Feeds
From Here
Chapter 8 Working with Logical and Information Functions
Adding Intelligence with Logical Functions
Using the IF() Function
Performing Multiple Logical Tests
Combining Logical Functions with Arrays
Case Study: Building an Accounts Receivable Aging Worksheet
Calculating a Smarter Due Date
Aging Overdue Invoices
Getting Data with Information Functions
The CELL() Function
The ERROR.TYPE() Function
The INFO() Function
The SHEET() and SHEETS() Functions
The IS Functions
From Here
Chapter 9 Working with Lookup Functions
Understanding Lookup Tables
The CHOOSE() Function
Determining the Name of the Day of the Week
Determining the Month of the Fiscal Year
Calculating Weighted Questionnaire Results
Integrating CHOOSE() and Worksheet Option Buttons
Looking Up Values in Tables
The VLOOKUP() Function
The HLOOKUP() Function
Returning a Customer Discount Rate with a Range Lookup
Returning a Tax Rate with a Range Lookup
Finding Exact Matches
Advanced Lookup Operations
From Here
Chapter 10 Working with Date and Time Functions
How Excel Deals with Dates and Times
Entering Dates and Times
Excel and Two-Digit Years
Using Excel's Date Functions
Returning a Date
Returning Parts of a Date
Calculating the Difference Between Two Dates
Using Excel's Time Functions
Returning a Time
Returning Parts of a Time
Calculating the Difference Between Two Times
Case Study: Building an Employee Time Sheet
Entering the Time Sheet Data
Calculating the Daily Hours Worked
Calculating the Weekly Hours Worked
Calculating the Weekly Pay
From Here
Chapter 11 Working with Math Functions
Understanding Excel's Rounding Functions
The ROUND() Function
The MROUND() Function
The ROUNDDOWN() and ROUNDUP() Functions
The CEILING.MATH() and FLOOR.MATH() Functions
Determining the Fiscal Quarter in Which a Date Falls
Calculating Easter Dates
The EVEN() and ODD() Functions
The INT() and TRUNC() Functions
Using Rounding to Prevent Calculation Errors
Setting Price Points
Case Study: Rounding Billable Time
Summing Values
The SUM() Function
Calculating Cumulative Totals
Summing Only the Positive or Negative Values in a Range
The MOD() Function
A Better Formula for Time Differences
Summing Every nth Row
Determining Whether a Year Is a Leap Year
Creating Ledger Shading
Generating Random Numbers
The RAND() Function
The RANDBETWEEN() Function
From Here
Chapter 12 Working with Statistical Functions
Understanding Descriptive Statistics
Counting Items with the COUNT() Function
Calculating Averages
The AVERAGE() Function
The MEDIAN() Function
The MODE() Function
Calculating the Weighted Mean
Calculating Extreme Values
The MAX() and MIN() Functions
The LARGE() and SMALL() Functions
Performing Calculations on the Top k Values
Performing Calculations on the Bottom k Values
Calculating Measures of Variation
Calculating the Range
Calculating the Variance
Calculating the Standard Deviation
Working with Frequency Distributions
The FREQUENCY() Function
Understanding the Normal Distribution and the NORMDIST() Function
The Shape of the Curve I: The SKEW() Function
The Shape of the Curve II: The KURT() Function
Using the Analysis ToolPak Statistical Tools
Using the Descriptive Statistics Tool
Determining the Correlation Between Data
Working with Histograms
Using the Random Number Generation Tool
Working with Rank and Percentile
From Here
Part III: Building Business Models
Chapter 13 Analyzing Data with Tables
Converting a Range to a Table
Basic Table Operations
Sorting a Table
Performing a More Complex Sort
Sorting a Table in Natural Order
Sorting on Part of a Field
Sorting Without Articles
Filtering Table Data
Using Filter Lists to Filter a Table
Using Complex Criteria to Filter a Table
Entering Computed Criteria
Copying Filtered Data to a Different Range
Referencing Tables in Formulas
Using Table Specifiers
Entering Table Formulas
Excel's Table Functions
About Table Functions
Table Functions That Don't Require a Criteria Range
Table Functions That Accept Multiple Criteria
Table Functions That Require a Criteria Range
Case Study: Applying Statistical Table Functions to a Defects Database
From Here
Chapter 14 Analyzing Data with PivotTables
What Are PivotTables?
How PivotTables Work
Some PivotTable Terms
Building PivotTables
Building a PivotTable from a Table or Range
Building a PivotTable from an External Database
Working with and Customizing a PivotTable
Working with PivotTable Subtotals
Hiding PivotTable Grand Totals
Hiding PivotTable Subtotals
Customizing the Subtotal Calculation
Changing the Data Field Summary Calculation
Using a Difference Summary Calculation
Using a Percentage Summary Calculation
Using a Running Total Summary Calculation
Using an Index Summary Calculation
Creating Custom PivotTable Calculations
Creating a Calculated Field
Creating a Calculated Item
Case Study: Using PivotTable Results in a Worksheet Formula
From Here
Chapter 15 Using Excel's Business-Modeling Tools
Using What-If Analysis
Setting Up a One-Input Data Table
Adding More Formulas to the Input Table
Setting Up a Two-Input Table
Editing a Data Table
Working with Goal Seek
How Does Goal Seek Work?
Running Goal Seek
Optimizing Product Margin
A Note About Goal Seek's Approximations
Performing a Break-Even Analysis
Solving Algebraic Equations
Working with Scenarios
Understanding Scenarios
Setting Up Your Worksheet for Scenarios
Adding a Scenario
Displaying a Scenario
Editing a Scenario
Merging Scenarios
Generating a Summary Report
Deleting a Scenario
From Here
Chapter 16 Using Regression to Track Trends and Make Forecasts
Choosing a Regression Method
Using Simple Regression on Linear Data
Analyzing Trends Using Best-Fit Lines
Making Forecasts
Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model
About the Forecast Workbook
Calculating a Normal Trend
Calculating the Forecast Trend
Calculating the Seasonal Trend
Computing the Monthly Seasonal Indexes
Calculating the Deseasoned Monthly Values
Calculating the Deseasoned Trend
Calculating the Reseasoned Trend
Calculating the Seasonal Forecast
Working with Quarterly Data
Using Simple Regression on Nonlinear Data
Working with an Exponential Trend
Working with a Logarithmic Trend
Working with a Power Trend
Using Polynomial Regression Analysis
Using Multiple Regression Analysis
From Here
Chapter 17 Solving Complex Problems with Solver
Some Background on Solver
The Advantages of Solver
When Do You Use Solver?
Loading Solver
Using Solver
Adding Constraints
Saving a Solution as a Scenario
Setting Other Solver Options
Selecting the Method Solver Uses
Controlling How Solver Works
Working with Solver Models
Making Sense of Solver's Messages
Case Study: Solving the Transportation Problem
Displaying Solver's Reports
The Answer Report
The Sensitivity Report
The Limits Report
From Here
Part IV: Building Financial Formulas
Chapter 18 Building Loan Formulas
Understanding the Time Value of Money
Calculating the Loan Payment
Loan Payment Analysis
Working with a Balloon Loan
Calculating Interest Costs, Part I
Calculating the Principal and Interest
Calculating Interest Costs, Part 2
Calculating Cumulative Principal and Interest
Building a Loan Amortization Schedule
Building a Fixed-Rate Amortization Schedule
Building a Dynamic Amortization Schedule
Calculating the Term of the Loan
Calculating the Interest Rate Required for a Loan
Calculating How Much You Can Borrow
Case Study: Working with Mortgages
Building a Variable-Rate Mortgage Amortization Schedule
Allowing for Mortgage Principal Paydowns
From Here
Chapter 19 Building Investment Formulas
Working with Interest Rates
Understanding Compound Interest
Nominal Versus Effective Interest
Converting Between the Nominal Rate and the Effective Rate
Calculating the Future Value
The Future Value of a Lump Sum
The Future Value of a Series of Deposits
The Future Value of a Lump Sum Plus Deposits
Working Toward an Investment Goal
Calculating the Required Interest Rate
Calculating the Required Number of Periods
Calculating the Required Regular Deposit
Calculating the Required Initial Deposit
Calculating the Future Value with Varying Interest Rates
Case Study: Building an Investment Schedule
From Here
Chapter 20 Building Discount Formulas
Calculating the Present Value
Taking Inflation into Account
Calculating Present Value Using PV()
Income Investing Versus Purchasing a Rental Property
Buying Versus Leasing
Discounting Cash Flows
Calculating the Net Present Value
Calculating Net Present Value Using NPV()
Net Present Value with Varying Cash Flows
Net Present Value with Nonperiodic Cash Flows
Calculating the Payback Period
Simple Undiscounted Payback Period
Exact Undiscounted Payback Point
Discounted Payback Period
Calculating the Internal Rate of Return
Using the IRR() Function
Calculating the Internal Rate of Return for Nonperiodic Cash Flows
Calculating Multiple Internal Rates of Return
Case Study: Publishing a Book
Per-Unit Constants
Operating Costs and Sales
Cash Flow
Cash-Flow Analysis
From Here
9780789748676 TOC 1/25/2013
