Table of Contents

Chapter 1: Building Formulas for Data Analysis

Introducing Data Analysis

Introducing Formulas

Understanding Formula Types

Build a Formula

Add a Range Name to a Formula

Reference Another Worksheet Range in a Formula

Move or Copy a Formula

Switch to Absolute Cell References

Create an Array Formula

Turn On Iterative Calculations

Chapter 2: Troubleshooting Formulas

Understanding Error Values in Excel

Show Formulas Instead of Results

Use a Watch Window to Monitor a Cell Value

Step Through a Formula

Display Text Instead of Error Values

Check for Formula Errors in a Worksheet

Audit a Formula to Locate Errors

Chapter 3: Enhancing Formulas with Functions

Understanding Excel Functions

Understanding Function Types

Add a Function to a Formula

Add a Row or Column of Numbers

Build an AutoSum Formula

Round a Number

Create a Conditional Formula

Calculate a Conditional Sum

Calculate a Conditional Count

Find the Square Root

Retrieve a Column or Row Number

Look Up a Value

Determine the Location of a Value

Return a Cell Value with INDEX

Perform Date and Time Calculations

Chapter 4: Analyzing Financial Data

Calculate Future Value

Calculate Present Value

Determine the Loan Payments

Calculate the Principal or Interest

Find the Required Interest Rate

Determine the Internal Rate of Return

Calculate Straight-Line Depreciation

Return the Fixed-Declining Balance Depreciation

Determine the Double-Declining Balance Depreciation

Figure the Sum-of-the-Years-Digits Depreciation

Chapter 5: Analyzing Statistical Data

Calculate an Average

Calculate a Conditional Average

Determine the Median or the Mode

Find the Rank

Determine the Nth Largest or Smallest Value

Create a Grouped Frequency Distribution

Calculate the Variance and Standard Deviation

Find the Correlation

Chapter 6: Building Tables for Data Analysis

Understanding Tables

Introducing Table Features

Convert a Range to a Table

Select Table Data

Insert a Table Row

Insert a Table Column

Delete a Table Row

Delete a Table Column

Add a Column Subtotal

Chapter 7: Sorting and Filtering Data

Perform a Simple Sort or Filter

Perform a Multilevel Sort

Create a Custom Sort

Sort by Cell Color, Font Color, or Cell Icon

Using Quick Filters for Complex Sorting

Enter Criteria to Find Records

Create an Advanced Filter

Display Unique Records in the Filter Results

Count Filtered Records

Chapter 8: Learning Data Analysis Techniques

Highlight Cells That Meet Some Criteria

Highlight the Top or Bottom Values in a Range

Show Duplicate Values

Show Cells That Are Above or Below Average

Analyze Cell Values with Data Bars

Analyze Cell Values with Color Scales

Analyze Cell Values with Icon Sets

Create a Custom Conditional Formatting Rule

Highlight Cells Based On a Formula

Modify a Conditional Formatting Rule

Remove Conditional Formatting from a Range

Remove Conditional Formatting from a Worksheet

Set Data Validation Rules

Summarize Data with Subtotals

Group Related Data

Remove Duplicate Values from a Range or Table

Consolidate Data from Multiple Worksheets

Chapter 9: Working with Data Analysis Tools

Create a Data Table

Create a Two-Input Data Table

Skip Data Tables When Calculating Workbooks

Analyze Data with Goal Seek

Analyze Data with Scenarios

Understanding Solver

Load the Solver Add-In

Optimize a Result with Solver

Add Constraints to Solver

Save a Solver Solution as a Scenario

Chapter 10: Tracking Trends and Making Forecasts

Plotting a Best-Fit Trendline

Calculating Best-Fit Values

Plotting Forecasted Values

Extending a Linear Trend

Calculating Forecasted Linear Values

Plotting an Exponential Trendline

Calculating Exponential Trend Values

Plotting a Logarithmic Trendline

Plotting a Power Trendline

Plotting a Polynomial Trendline

Chapter 11: Working with the Analysis ToolPak

Load the Analysis ToolPak

Calculate a Moving Average

Compare Variances

Calculate Correlation

Run a Regression Analysis

Determine Rank and Percentile

Generate Random Numbers

Create a Frequency Distribution

Chapter 12: Analyzing Data with PivotTables

Understanding PivotTables

Explore PivotTable Features

Build a PivotTable from an Excel Range or Table

Create a PivotTable from External Data

Refresh PivotTable Data

Add Multiple Fields to the Row or Column Area

Add Multiple Fields to the Data Area

Move a Field to a Different Area

Group PivotTable Values

Change the PivotTable Summary Calculation

Introducing Custom Calculations

Insert a Custom Calculated Field

Insert a Custom Calculated Item

Chapter 13: Visualizing Data with Charts

Examine Chart Elements

Understanding Chart Types

Create a Chart

Display a Data Table

Change the Chart Layout and Style

Select a Different Chart Type

Add a Sparkline to a Cell

Chapter 14: Importing Data into Excel

Understanding External Data

Import Data from a Data Source

Import Data from an Access Table

Import Data from a Word Table

Import Data from a Text File

Import Data from a Web Page

Import Data from an XML File

Refresh Imported Data

Separate Cell Text into Columns

Chapter 15: Querying Data Sources

Understanding Microsoft Query

Define a Data Source

Start Microsoft Query

Tour the Microsoft Query Window

Add a Table to a Query

Add Fields to a Query

Filter the Records with Query Criteria

Sort Query Records

Return the Query Results

Chapter 16: Learning VBA for Data Analysis

Record a Macro

Open the VBA Editor

Explore the Excel Object Model

Add a Macro to a Module

Run a Macro

Assign a Shortcut Key to a Macro

Assign a Macro to the Quick Access Toolbar

Assign a Macro to the Ribbon

Set the Macro Security Level

Digitally Sign Your Excel Macros

Appendix: Using Excel Keyboard Shortcuts