We Buy This Book Back!
EXCEL-LENT DATA ANALYSIS
Excel Data Analysis For Dummies, 5th Edition is the book Excel users need to improve their data analysis skills in Excel. Step-by-step guides make analyzing your data simple. Don’t take a chance on inaccurate data---keep everything in line with the most popular spreadsheet application on the market.
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
An electronic version of this book is available through VitalSource.
This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.
By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.
You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.
More details can be found here.
A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.
Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.
Please view the compatibility matrix prior to purchase.
Other versions by this Author