Introduction 1 About This Book 1 What You Can Safely Ignore 2 Foolish Assumptions 2 Icons Used in This Book 3 Beyond the Book 3 Where to Go from Here 4 Part 1: Getting Started with Data Analysis 5 Chapter 1: Learning Basic Data-Analysis Techniques 7 What Is Data Analysis, Anyway? 8 Cooking raw data 8 Dealing with data 8 Building data models 9 Performing what-if analysis 9 Analyzing Data with Conditional Formatting 9 Highlighting cells that meet some criteria 10 Showing pesky duplicate values 11 Highlighting the top or bottom values in a range 13 Analyzing cell values with data bars 14 Analyzing cell values with color scales 15 Analyzing cell values with icon sets 16 Creating a custom conditional-formatting rule 17 Editing a conditional-formatting rule 20 Removing conditional-formatting rules 21 Summarizing Data with Subtotals 22 Grouping Related Data 24 Consolidating Data from Multiple Worksheets 26 Consolidating by position 26 Consolidating by category 28 Chapter 2: Working with Data-Analysis Tools 31 Working with Data Tables 32 Creating a basic data table 32 Creating a two-input data table 34 Skipping data tables when calculating workbooks 37 Analyzing Data with Goal Seek 37 Analyzing Data with Scenarios 39 Create a scenario 40 Apply a scenario 42 Edit a scenario 42 Delete a scenario 43 Optimizing Data with Solver 43 Understanding Solver 43 The advantages of Solver 44 When should you use Solver? 44 Loading the Solver add-in 46 Optimizing a result with Solver 47 Adding constraints to Solver 50 Save a Solver solution as a scenario 52 Chapter 3: Introducing Excel Tables 55 What Is a Table and Why Should I Care? 55 Building a Table 57 Getting the data from an external source 58 Converting a range to a table 58 Basic table maintenance 60 Analyzing Table Information 61 Displaying simple statistics 61 Adding a column subtotal 63 Sorting table records 64 Filtering table records 67 Clearing a filter 68 Turning off AutoFilter 68 Applying a predefined AutoFilter 69 Applying multiple filters 71 Applying advanced filters 72 Chapter 4: Grabbing Data from External Sources 77 What''s All This about External Data? 77 Exporting Data from Other Programs 79 Importing External Data into Excel 80 Importing data from an Access table 80 Importing data from a Word table 81 Introducing text file importing 82 Importing a delimited text file 83 Importing a fixed-width text file 84 Importing data from a web page 86 Importing an XML file 88 Querying External Databases 90 Defining a data source 91 Querying a data source 94 Chapter 5: Analyzing Table Data with Functions 99 The Database Functions: Some General Remarks 100 Retrieving a Value from a Table 101 Summing a Column''s Values 102 Counting a Column''s Values 104 Averaging a Column''s Values 105 Determining a Column''s Maximum and Minimum Values 106 Multiplying a Column''s Values 108 Deriving a Column''s Standard Deviation 109 Calculating a Column''s Variance 110 Part 2: Analyzing Data Using PivotTables and PivotCharts 113 Chapter 6: Creating and Using PivotTables 115 Understanding PivotTables 116 Exploring PivotTable Features 118 Building a PivotTable from an Excel Range or Table 119 Creating a PivotTable from External Data 122 Building a PivotTable from Microsoft Query 122 Building a PivotTable from a new data connection 123 Refreshing PivotTable Data 125 Refreshing PivotTable data manually 125 Refreshing PivotTable data automatically 125 Adding Multiple Fields to a PivotTable Area 126 Pivoting a Field to a Different Area 127 Grouping PivotTable Values 128 Grouping numeric values 128 Grouping date and time values 129 Grouping text values 130 Filtering PivotTable Values 131 Applying a report filter 131 Filtering row or column items 132 Filtering PivotTable values 133 Filtering a PivotTable with a slicer 134 Chapter 7: Performing PivotTable Calculations 137 Messing around with PivotTable Summary Calculations 138 Changing the PivotTable summary calculation 138 Trying out the difference summary calculation 140 Applying a percentage summary calculation 142 Adding a running total summary calculation 144 Creating an index summary calculation 147 Working with PivotTable Subtotals 149 Turning off subtotals for a field 149 Displaying multiple subtotals for a field 149 Introducing Custom Calculations 151 Formulas for custom calculations 151 Checking out the custom calculation types 152 Understanding custom calculation limitations 152 Inserting a Custom Calculated Field 154 Inserting a Custom Calculated Item 155 Editing a Custom Calculation 157 Deleting a Custom Calculation 158 Chapter 8: Building PivotCharts 161 Introducing the PivotChart 161 Understanding PivotChart pros and cons 162 Taking a PivotChart tour 163 Understanding PivotChart limitations 164 Creating a PivotChart 165 Creating a PivotChart from a PivotTable 165 Embedding a PivotChart on a PivotTable''s worksheet 165 Creating a PivotChart from an Excel range or table 166 Working with PivotCharts 169 Moving a PivotChart to another sheet 169 Filtering a PivotChart 170 Changing the PivotChart type 172 Adding data labels to your PivotChart 173 Sorting the PivotChart 174 Adding PivotChart titles 175 Moving the PivotChart legend 177 Displaying a data table with the PivotChart 178 Part 3: Discovering Advanced Data-Analysis Tools 181 Chapter 9: Dealing with Data Models 183 Understanding Excel Data Models 184 Creating a relationship between tables 186 Importing related external data tables 187 Basing a PivotTable on multiple, related tables 189 Managing a Data Model with Power Pivot 192 Enabling the Power Pivot add-in 192 Adding a table to the Data Model 193 Importing related tables from an external data source 193 Viewing table relationships 195 Viewing relationship details 196 Creating a relationship between tables with Power Pivot 197 Transforming Data 199 Creating a PivotTable or PivotChart from Your Data Model 200 Chapter 10: Tracking Trends and Making Forecasts 203 Plotting a Best-Fit Trend Line 204 Calculating Best-Fit Values 206 Plotting Forecasted Values 208 Extending a Linear Trend 210 Extending a linear trend using the fill handle 211 Extending a linear trend using the Series command 211 Calculating Forecasted Linear Values 212 Plotting an Exponential Trend Line 214 Calculating Exponential Trend Values 216 Plotting a Logarithmic Trend Line 218 Plotting a Power Trend Line 220 Plotting a Polynomial Trend Line 222 Creating a Forecast Sheet 223 Chapter 11: Analyzing Data Using Statistics 227 Counting Things 228 Counting numbers 228 Counting nonempty cells 229 Counting empty cells 229 Counting cells that match criteria 229 Counting cells that match multiple criteria 230 Counting permutations 231 Counting combinations 232 Averaging Things 233 Calculating an average 233 Calculating a conditional average 233 Calculating an average based on multiple conditions 234 Calculating the median 235 Calculating the mode 235 Finding the Rank 236 Determining the Nth Largest or Smallest Value 238 Calculating the nth highest value 238 Calculating the nth smallest value 239 Creating a Grouped Frequency Distribution 240 Calculating the Variance 241 Calculating the Standard Deviation 242 Finding the Correlation 243 Chapter 12: Analyzing Data Using Descriptive Statistics 245 Loading the Analysis ToolPak 246 Generating Descriptive Statistics 247 Calculating a Moving Average 250 Determining Rank and Percentile 253 Generating Random Numbers 255 Creating a Frequency Distribution 258 Chapter 13: Analyzing Data Using Inferential Statistics 261 Sampling Data 262 Using the t-Test Tools 264 Performing a z-Test 267 Determining the Regression 269 Calculating the Correlation 271 Calculating the Covariance 273 Using the Anova Tools 274 Performing an f-Test 276 Part 4: The Part of Tens 279 Chapter 14: Ten Things You Ought to Know about Statistics 281 Descriptive Statistics Are Straightforward 282 Averages Aren''t So Simple Sometimes 282 Standard Deviations Describe Dispersion 283 An Observation Is an Observation 285 A Sample Is a Subset of Values 285 Inferential Statistics Are Cool But Complicated 285 Probability Distributions Aren''t Always Confusing 287 Uniform distribution 287 Normal distribution 288