Take Excel to the next level
Excel is the world’s leading spreadsheet application. It’s a key module in Microsoft Office—the number-one productivity suite—and it is the number-one business intelligence tool. An Excel dashboard report is a visual presentation of critical data and uses gauges, maps, charts, sliders, and other graphical elements to present complex data in an easy-to-understand format.
Excel Data Analysis For Dummies explains in depth how to use Excel as a tool for analyzing big data sets. In no time, you’ll discover how to mine and analyze critical data in order to make more informed business decisions.
- Work with external databases, PivotTables, and Pivot Charts
- Use Excel for statistical and financial functions and data sharing
- Get familiar with Solver
- Use the Small Business Finance Manager
If you’re familiar with Excel but lack a background in the technical aspects of data analysis, this user-friendly book makes it easy to start putting it to use for you.
Table of Contents:
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
