Turn jumbles of numbers into graphics, insights, and answers with Excel

With Microsoft Excel, you can, well, excel at data analysis. And Excel Data Analysis For Dummies can help, with clear and easy explanations of the mountain of features for creating, visualizing, and analyzing data. PivotTables, charts, what-if analysis, statistical functions—it's all in here, with examples and ideas for Excel users of all skill levels.

This latest edition covers the most recent updates to Excel and Microsoft 365. You'll beef up your data skills and learn powerful techniques for turning numbers into knowledge. For students, researchers, and business professionals, Excel is the spreadsheet and data application of choice—and Dummies is the best choice for learning how to make those numbers sing.

Learn how to use Excel's built-in data analysis features and write your own functions to explore patterns in your data
Create striking charts and visualizations, and discover multiple ways to tell the stories hidden in the numbers
Clean up large datasets and identify statistical operations that will answer your questions
Perform financial calculations, database operations, and more—without leaving Excel

Excel Data Analysis For Dummies is the go-to resource for Excel users who are looking for better ways to crunch the numbers.

Paul McFedries is the owner of Logophilia Limited, a successful technical writing firm. He has 25 years’ experience writing instructional computer books and is the author of over 100 books that have sold a combined 4 million copies worldwide.

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

Parameters Aren’t So Complicated 289

Skewness and Kurtosis Describe a Probability Distribution’s Shape 289

Confidence Intervals Seem Complicated at First But Are Useful 291

Chapter 15: Ten Ways to Analyze Financial Data 293

Calculating Future Value 294

Calculating Present Value 295

Determining Loan Payments 296

Calculating a Loan Payment’s Principal and Interest 297

Calculating Cumulative Loan Principal and Interest 297

Finding the Required Interest Rate 298

Determining the Internal Rate of Return 299

Calculating Straight-Line Depreciation 300

Returning the Fixed-Declining Balance Depreciation 300

Determining the Double-Declining Balance Depreciation 301

Chapter 16: Ten Ways to Raise Your PivotTable Game 303

Turn the PivotTable Fields Task Pane On and Off 304

Change the PivotTable Fields Task Pane Layout 304

Display the Details Behind PivotTable Data 306

Apply a PivotTable Style 308

Create a Custom PivotTable Style 309

Preserve PivotTable Formatting 311

Rename the PivotTable 312

Turn Off Grand Totals 312

Reduce the Size of PivotTable Workbooks 314

Use a PivotTable Value in a Formula 314

Appendix: Glossary of Data Analysis and Excel Terms 317

Index 327

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.