Excel Data Analysis For Dummies

Paul McFedries is the president of Logophilia Limited, a technical writing company, and has worked with computers large and small since 1975. Although now primarily a writer, Paul has worked as a programmer, consultant, database developer, and website developer. Paul has written more than 90 books that have sold over four million copies worldwide.

Introduction 1

About This Book 1

What You Can Safely Ignore 2

Foolish Assumptions 3

Icons Used in This Book 3

Beyond the Book 4

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 12

Highlighting the top or bottom values in a range 13

Analyzing cell values with data bars 14

Analyzing cell values with color scales 16

Analyzing cell values with icon sets 16

Creating a custom conditional formatting rule 17

Editing a conditional formatting rule 20

Removing conditional formatting rules 22

Summarizing Data with Subtotals 23

Grouping Related Data 24

Consolidating Data from Multiple Worksheets 26

Consolidating by position 27

Consolidating by category 28

Chapter 2: Working with Data-Analysis Tools 31

Working with Data Tables 31

Creating a basic data table 32

Creating a two-input data table 34

Skipping data tables when calculating workbooks 36

Analyzing Data with Goal Seek 37

Analyzing Data with Scenarios 39

Create a scenario 39

Apply a scenario 41

Edit a scenario 41

Delete a scenario 42

Optimizing Data with Solver 42

Understanding Solver 43

The advantages of Solver 43

When should you use Solver? 43

Loading the Solver add-in 45

Optimizing a result with Solver 46

Adding constraints to Solver 48

Save a Solver solution as a scenario 51

Chapter 3: Introducing Excel Tables 53

What Is a Table and Why Should I Care? 53

Building a Table 55

Getting the data from an external source 56

Converting a range to a table 56

Basic table maintenance 58

Analyzing Table Information 59

Displaying simple statistics 59

Adding a column subtotal 60

Sorting table records 62

Filtering table records 64

Clearing a filter 66

Turning off AutoFilter 66

Applying a predefined AutoFilter 66

Applying multiple filters 69

Applying advanced filters 69

Chapter 4: Grabbing Data from External Sources 75

What’s All This About External Data? 75

Exporting Data from Other Programs 77

Importing External Data into Excel 78

Importing data from an Access table 78

Importing data from a Word table 79

Introducing text file importing 80

Importing a delimited text file 81

Importing a fixed-width text file 82

Importing data from a web page 83

Importing an XML file 85

Querying External Databases 87

Defining a data source 88

Querying a data source 91

It’s Sometimes a Raw Deal 95

Chapter 5: Scrub-a-Dub-Dub: Cleaning Data 97

Editing Your Imported Workbook 97

Deleting unnecessary columns 98

Deleting unnecessary rows 99

Resizing columns 99

Resizing rows 99

Erasing unneeded contents in a cell or range 100

Formatting numeric values 100

Copying worksheet data 101

Moving worksheet data 101

Replacing data in fields 102

Cleaning Data with Text Functions 103

The CLEAN function 103

The CONCAT function 104

The EXACT function 105

The FIND function 105

The LEFT function 106

The LEN function 107

The LOWER function 107

The MID function 107

The NUMBERVALUE function 108

The PROPER function 109

The REPLACE function 109

The RIGHT function 110

The SEARCH function 111

The SUBSTITUTE function 111

The TEXT function 112

The TEXTJOIN function 112

The TRIM function 112

The UPPER function 113

The VALUE function 113

Converting text function formulas to text 114

Using Validation to Keep Data Clean 114

Chapter 6: Analyzing Table Data with Functions 119

The Database Functions: Some General Remarks 120

Retrieving a Value from a Table 121

Summing a Column’s Values 122

Counting a Column’s Values 123

Averaging a Column’s Values 125

Determining a Column’s Maximum and Minimum Values 126

Multiplying a Column’s Values 127

Deriving a Column’s Standard Deviation 128

Calculating a Column’s Variance 130

Part 2: Analyzing Data with PivotTables and PivotCharts 133

Chapter 7: Creating and Using PivotTables 135

Understanding PivotTables 135

Exploring PivotTable Features 137

Building a PivotTable from an Excel Table 138

Creating a PivotTable from External Data 141

Refreshing PivotTable Data 143

Refreshing PivotTable data manually 144

Refreshing PivotTable data automatically 144

Adding Multiple Fields to a PivotTable Area 145

Pivoting a Field to a Different Area 146

Grouping PivotTable Values 147

Grouping numeric values 147

Grouping date and time values 148

Grouping text values 148

Filtering PivotTable Values 149

Applying a report filter 149

Filtering row or column items 151

Filtering PivotTable values 151

Filtering a PivotTable with a slicer 152

Chapter 8: Performing PivotTable Calculations 155

Messing around with PivotTable Summary Calculations 156

Changing the PivotTable summary calculation 156

Trying out the difference summary calculation 158

Applying a percentage summary calculation 160

Adding a running total summary calculation 162

Creating an index summary calculation 164

Working with PivotTable Subtotals 166

Turning off subtotals for a field 166

Displaying multiple subtotals for a field 167

Introducing Custom Calculations 168

Formulas for custom calculations 168

Checking out the custom calculation types 169

Understanding custom calculation limitations 169

Inserting a Custom Calculated Field 171

Inserting a Custom Calculated Item 173

Editing a Custom Calculation 175

Deleting a Custom Calculation 176

Chapter 9: Building PivotCharts 177

Introducing the PivotChart 177

Understanding PivotChart pros and cons 178

Taking a PivotChart tour 179

Understanding PivotChart limitations 180

Creating a PivotChart 181

Creating a PivotChart from a PivotTable 181

Embedding a PivotChart on a PivotTable’s worksheet 181

Creating a PivotChart from an Excel table 183

Working with PivotCharts 185

Moving a PivotChart to another sheet 185

Filtering a PivotChart 186

Changing the PivotChart type 188

Adding data labels to your PivotChart 189

Sorting the PivotChart 190

Adding PivotChart titles 191

Moving the PivotChart legend 193

Displaying a data table with the PivotChart 194

Part 3: Discovering Advanced Data Analysis Tools 197

Chapter 10: Tracking Trends and Making Forecasts 199

Plotting a Best-Fit Trend line 200

Calculating Best-Fit Values 202

Plotting Forecasted Values 204

Extending a Linear Trend 206

Extending a linear trend using the fill handle 206

Extending a linear trend using the Series command 207

Calculating Forecasted Linear Values 208

Plotting an Exponential Trend Line 210

Calculating Exponential Trend Values 211

Plotting a Logarithmic Trend Line 213

Plotting a Power Trend Line 215

Plotting a Polynomial Trend Line 216

Chapter 11: Analyzing Data with Statistics 219

Counting Things 220

Counting numbers 220

Counting nonempty cells 221

Counting empty cells 221

Counting cells that match criteria 221

Counting cells that match multiple criteria 222

Counting permutations 223

Counting combinations 224

Averaging Things 225

Calculating an average 225

Calculating a conditional average 225

Calculating an average based on multiple conditions 226

Calculating the median 227

Calculating the mode 227

Finding the Rank 228

Determining the Nth Largest or Smallest Value 230

Calculating the nth highest value 231

Calculating the nth smallest value 231

Creating a Grouped Frequency Distribution 232

Calculating the Variance 233

Calculating the Standard Deviation 234

Finding the Correlation 235

Chapter 12: Analyzing Data with Descriptive Statistics 237

Loading the Analysis ToolPak 238

Generating Descriptive Statistics 240

Calculating a Moving Average 243

Determining Rank and Percentile 245

Generating Random Numbers 247

Creating a Frequency Distribution 250

Chapter 13: Analyzing Data with Inferential Statistics 253

Sampling Data 254

Using the t-Test Tools 256

Performing a z-Test 259

Determining the Regression 261

Calculating the Correlation 264

Calculating the Covariance 265

Using the Anova Tools 267

Performing an f-test 268

Part 4: The Part of Tens 271

Chapter 14: Ten Things You Ought to Know about Statistics 273

Descriptive Statistics Are Straightforward 274

Averages Aren’t So Simple Sometimes 274

Standard Deviations Describe Dispersion 275

An Observation Is an Observation 276

A Sample Is a Subset of Values 276

Inferential Statistics Are Cool but Complicated 277

Probability Distributions Aren’t Always Confusing 278

Uniform distribution 279

Normal distribution 280

Parameters Aren’t So Complicated 281

Skewness and Kurtosis Describe a Probability Distribution’s Shape 281

Confidence Intervals Seem Complicated at First, but Are Useful 282

Chapter 15: Ten Ways to Analyze Financial Data 285

Calculating Future Value 286

Calculating Present Value 287

Determining Loan Payments 288

Calculating a Loan Payment’s Principal and Interest 289

Calculating Cumulative Loan Principal and Interest 289

Finding the Required Interest Rate 290

Determining the Internal Rate of Return 291

Calculating Straight-Line Depreciation 292

Returning the Fixed-Declining Balance Depreciation 292

Determining the Double-Declining Balance Depreciation 293

Chapter 16: Ten Ways to Raise Your PivotTable Game 295

Turn the PivotTable Fields Task Pane On and Off 296

Change the PivotTable Fields Task Pane Layout 296

Display the Details Behind PivotTable Data 298

Apply a PivotTable Style 300

Create a Custom PivotTable Style 301

Preserve PivotTable Formatting 303

Rename the PivotTable 304

Turn Off Grand Totals 305

Reduce the Size of PivotTable Workbooks 306

Use a PivotTable Value in a Formula 306

Appendix: Glossary of Data Analysis and Excel Terms 309

Index 319