Title Page
Copyright Page
Table of Contents
Introduction
About This Book
What You Can Safely Ignore
Foolish Assumptions
Icons Used in This Book
Beyond the Book
Where to Go from Here
Part 1 Getting Started with Data Analysis
Chapter 1 Learning Basic Data-Analysis Techniques
What Is Data Analysis, Anyway?
Cooking raw data
Dealing with data
Building data models
Performing what-if analysis
Analyzing Data with Conditional Formatting
Highlighting cells that meet some criteria
Showing pesky duplicate values
Highlighting the top or bottom values in a range
Analyzing cell values with data bars
Analyzing cell values with color scales
Analyzing cell values with icon sets
Creating a custom conditional- formatting rule
Editing a conditional-formatting rule
Removing conditional-formatting rules
Summarizing Data with Subtotals
Grouping Related Data
Consolidating Data from Multiple Worksheets
Consolidating by position
Consolidating by category
Chapter 2 Working with Data-Analysis Tools
Working with Data Tables
Creating a basic data table
Creating a two-input data table
Skipping data tables when calculating workbooks
Analyzing Data with Goal Seek
Analyzing Data with Scenarios
Create a scenario
Apply a scenario
Edit a scenario
Delete a scenario
Optimizing Data with Solver
Understanding Solver
The advantages of Solver
When should you use Solver?
Loading the Solver add-in
Optimizing a result with Solver
Adding constraints to Solver
Save a Solver solution as a scenario
Chapter 3 Introducing Excel Tables
What Is a Table and Why Should I Care?
Building a Table
Getting the data from an external source
Converting a range to a table
Basic table maintenance
Analyzing Table Information
Displaying simple statistics
Adding a column subtotal
Sorting table records
Filtering table records
Clearing a filter
Turning off AutoFilter
Applying a predefined AutoFilter
Applying multiple filters
Applying advanced filters
Chapter 4 Grabbing Data from External Sources
What’s All This about External Data?
Exporting Data from Other Programs
Importing External Data into Excel
Importing data from an Access table
Importing data from a Word table
Introducing text file importing
Importing a delimited text file
Importing a fixed-width text file
Importing data from a web page
Importing an XML file
Querying External Databases
Defining a data source
Querying a data source
Chapter 5 Analyzing Table Data with Functions
The Database Functions: Some General Remarks
Retrieving a Value from a Table
Summing a Column’s Values
Counting a Column’s Values
Averaging a Column’s Values
Determining a Column’s Maximum and Minimum Values
Multiplying a Column’s Values
Deriving a Column’s Standard Deviation
Calculating a Column’s Variance
Part 2 Analyzing Data Using PivotTables and PivotCharts
Chapter 6 Creating and Using PivotTables
Understanding PivotTables
Exploring PivotTable Features
Building a PivotTable from an Excel Range or Table
Creating a PivotTable from External Data
Building a PivotTable from Microsoft Query
Building a PivotTable from a new data connection
Refreshing PivotTable Data
Refreshing PivotTable data manually
Refreshing PivotTable data automatically
Adding Multiple Fields to a PivotTable Area
Pivoting a Field to a Different Area
Grouping PivotTable Values
Grouping numeric values
Grouping date and time values
Grouping text values
Filtering PivotTable Values
Applying a report filter
Filtering row or column items
Filtering PivotTable values
Filtering a PivotTable with a slicer
Chapter 7 Performing PivotTable Calculations
Messing around with PivotTable Summary Calculations
Changing the PivotTable summary calculation
Trying out the difference summary calculation
Applying a percentage summary calculation
Adding a running total summary calculation
Creating an index summary calculation
Working with PivotTable Subtotals
Turning off subtotals for a field
Displaying multiple subtotals for a field
Introducing Custom Calculations
Formulas for custom calculations
Checking out the custom calculation types
Understanding custom calculation limitations
Inserting a Custom Calculated Field
Inserting a Custom Calculated Item
Editing a Custom Calculation
Deleting a Custom Calculation
Chapter 8 Building PivotCharts
Introducing the PivotChart
Understanding PivotChart pros and cons
Taking a PivotChart tour
Understanding PivotChart limitations
Creating a PivotChart
Creating a PivotChart from a PivotTable
Embedding a PivotChart on a PivotTable’s worksheet
Creating a PivotChart from an Excel range or table
Working with PivotCharts
Moving a PivotChart to another sheet
Filtering a PivotChart
Changing the PivotChart type
Adding data labels to your PivotChart
Sorting the PivotChart
Adding PivotChart titles
Moving the PivotChart legend
Displaying a data table with the PivotChart
Part 3 Discovering Advanced Data-Analysis Tools
Chapter 9 Dealing with Data Models
Understanding Excel Data Models
Creating a relationship between tables
Importing related external data tables
Basing a PivotTable on multiple, related tables
Managing a Data Model with Power Pivot
Enabling the Power Pivot add-in
Adding a table to the Data Model
Importing related tables from an external data source
Viewing table relationships
Viewing relationship details
Creating a relationship between tables with Power Pivot
Transforming Data
Creating a PivotTable or PivotChart from Your Data Model
Chapter 10 Tracking Trends and Making Forecasts
Plotting a Best-Fit Trend Line
Calculating Best-Fit Values
Plotting Forecasted Values
Extending a Linear Trend
Extending a linear trend using the fill handle
Extending a linear trend using the Series command
Calculating Forecasted Linear Values
Plotting an Exponential Trend Line
Calculating Exponential Trend Values
Plotting a Logarithmic Trend Line
Plotting a Power Trend Line
Plotting a Polynomial Trend Line
Creating a Forecast Sheet
Chapter 11 Analyzing Data Using Statistics
Counting Things
Counting numbers
Counting nonempty cells
Counting empty cells
Counting cells that match criteria
Counting cells that match multiple criteria
Counting permutations
Counting combinations
Averaging Things
Calculating an average
Calculating a conditional average
Calculating an average based on multiple conditions
Calculating the median
Calculating the mode
Finding the Rank
Determining the Nth Largest or Smallest Value
Calculating the nth highest value
Calculating the nth smallest value
Creating a Grouped Frequency Distribution
Calculating the Variance
Calculating the Standard Deviation
Finding the Correlation
Chapter 12 Analyzing Data Using Descriptive Statistics
Loading the Analysis ToolPak
Generating Descriptive Statistics
Calculating a Moving Average
Determining Rank and Percentile
Generating Random Numbers
Creating a Frequency Distribution
Chapter 13 Analyzing Data Using Inferential Statistics
Sampling Data
Using the t-Test Tools
Performing a z-Test
Determining the Regression
Calculating the Correlation
Calculating the Covariance
Using the Anova Tools
Performing an f-Test
Part 4 The Part of Tens
Chapter 14 Ten Things You Ought to Know about Statistics
Descriptive Statistics Are Straightforward
Averages Aren’t So Simple Sometimes
Standard Deviations Describe Dispersion
An Observation Is an Observation
A Sample Is a Subset of Values
Inferential Statistics Are Cool But Complicated
Probability Distributions Aren’t Always Confusing
Uniform distribution
Normal distribution
Parameters Aren’t So Complicated
Skewness and Kurtosis Describe a Probability Distribution’s Shape
Confidence Intervals Seem Complicated at First But Are Useful
Chapter 15 Ten Ways to Analyze Financial Data
Calculating Future Value
Calculating Present Value
Determining Loan Payments
Calculating a Loan Payment’s Principal and Interest
Calculating Cumulative Loan Principal and Interest
Finding the Required Interest Rate
Determining the Internal Rate of Return
Calculating Straight-Line Depreciation
Returning the Fixed-Declining Balance Depreciation
Determining the Double-Declining Balance Depreciation
Chapter 16 Ten Ways to Raise Your PivotTable Game
Turn the PivotTable Fields Task Pane On and Off
Change the PivotTable Fields Task Pane Layout
Display the Details Behind PivotTable Data
Apply a PivotTable Style
Create a Custom PivotTable Style
Preserve PivotTable Formatting
Rename the PivotTable
Turn Off Grand Totals
Reduce the Size of PivotTable Workbooks
Use a PivotTable Value in a Formula
Appendix Glossary of Data Analysis and Excel Terms
Index
EULA