Excel Data Analysis For Dummies

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