Excel Data Analysis

HOW TO USE THIS BOOK

1. GETTING STARTED WITH EXCEL

Excel Data Analysis Options

Excel Data Types

Locate a Value in a Worksheet

Select a Range of Cells

Name a Range

Create Label Ranges

Modify Named Ranges

Copy and Paste a Range of Cells

Create a Custom Number Format

Apply AutoFormat to a Worksheet

Create a Named Style

Create a Custom Template

Protect Worksheets

2. ORGANIZE WORKSHEET DATA

Create a List

Add a Series to a List

Sort a List

Create a Custom Sort

Consolidate Data

Outline Your Data

3. EVALUATE WORKSHEET DATA

Apply Conditional Formatting

Summarize Data with Subtotals

Filter a List

Create a Custom Filter

Create an Advanced Filter

Create Scenarios

Validate Data

4. CREATING FORMULAS

Sum Cells with the AutoSum Button

Add a Function via the Insert Function Dialog Box

Edit Formulas

Evaluate a Formula

Using Solver to Produce Specific Values

Create a Conditional Formula

Solve a Formula with a Data Table

Trace a Formula Error

Lookup a Value in a Specific Row and Column

Determine the Location of a Value

Return a Value at a Specific Location in a Data List

Rank a Value within a Data List

Create an Amortization Table for a Loan

5. WORKING WITH EXTERNAL DATA

Link Data to Other Windows Programs

Import a Delimited Text File

Divide a Column into Multiple Columns

Create a Web Query

Import a Database Table

Using Queries to Screen External Databases

Find the Average of a Database Range

Save for Web

6. CHART DATA

Chart Basics

Modify a Chart Type

Explode Slices of a Pie Chart

Create a Custom Chart Type

Add a Trendline to a Chart

Add or Change New Data to a Chart

Create a Chart with Multiple Chart Types

7. WORKING WITH PIVOTTABLE REPORTS

PivotTable Basics

A View of the PivotTable Report Fields

Create a PivotTable Report from an Excel List

Change the Layout of a PivotTable

Filter a Field

Change the Calculation of a Data Field

Group PivotTable Data Items

Add Another Data Area Calculation

Add a Calculated Field

Add a Calculated Item

Retrieve a Value from a PivotTable Report

8. CREATING PIVOTCHARTS

Create a PivotChart from an External Database

Change the Layout of a PivotChart

Change the Chart Type of a PivotChart

Filter a PivotChart Field

Group Items in a PivotChart Field

Add a Data Table to a PivotChart

9. AUTOMATING ACTIONS WITH MACROS

An Introduction to Macros

Record a Macro

Run a Macro

Activate the Visual Basic Editor

Create a Macro Using the Visual Basic Editor

Update a Recorded Macro

Set Macro Security

Create a Custom Function

Declare a Variable

Execute a Task a Specific Number of Times

Display a Message Box

Request User Input for a Macro

Assign a Macro to a Menu

Run a Macro As a Workbook Opens

Using an Excel Function in a Macro

10. USING FORMS AND DIALOG BOXES

An Introduction to Forms

The Forms Toolbar

VBA Dialog Box Basics

Add a Form Control to a Worksheet

Specify Values for a Form Control

Customize Form Controls with Macros

Create a Custom Dialog Box

Call a Custom Dialog Box from a Macro

Capture Input from a Custom Dialog Box

Validate Input from a Dialog Box

11. USING THE ANALYSIS TOOLPACK ADD-INS

Install Excel Add-ins

Using the Conditional Sum Wizard

Analysis of Variance for Lists of Values (ANOVA)

Find the Correlation between Two Sets of Data

Determine the Covariance of Two Sets of Data

Calculate Descriptive Statistics

Compare the Variances in Two Groups of Data

Calculate a Moving Average

Determine Rank and Percentile

APPENDIX A: EXCEL KEYBOARD SHORTCUTS

Excel Keyboard Shortcuts

APPENDIX B: EXCEL FUNCTION QUICK REFERENCE

Excel Function Quick Reference

APPENDIX C: VBA AND EXCEL EVENTS QUICK REFERENCE

VBA and Excel Events Quick Reference

APPENDIX D: FORMULA BASICS IN EXCEL

Formula Basics in Excel

APPENDIX E: WHAT'S ON THE CD-ROM

What's on the CD-ROM

Using the E-Version of This Book

Wiley Publishing, Inc. End-User License Agreement

INDEX.