| Introduction |
|
1 | (4) |
|
|
|
1 | (1) |
|
What You Can Safely Ignore |
|
|
2 | (1) |
|
|
|
2 | (1) |
|
|
|
3 | (1) |
|
|
|
3 | (1) |
|
|
|
4 | (1) |
| Part 1: Getting Started With Data Analysis |
|
5 | (108) |
|
Chapter 1 Learning Basic Data-Analysis Techniques |
|
|
7 | (24) |
|
What Is Data Analysis, Anyway? |
|
|
8 | (1) |
|
|
|
8 | (1) |
|
|
|
8 | (1) |
|
|
|
9 | (1) |
|
Performing what-if analysis |
|
|
9 | (1) |
|
Analyzing Data with Conditional Formatting |
|
|
9 | (13) |
|
Highlighting cells that meet some criteria |
|
|
10 | (1) |
|
Showing pesky duplicate values |
|
|
11 | (2) |
|
Highlighting the top or bottom values in a range |
|
|
13 | (1) |
|
Analyzing cell values with data bars |
|
|
14 | (1) |
|
Analyzing cell values with color scales |
|
|
15 | (1) |
|
Analyzing cell values with icon sets |
|
|
16 | (1) |
|
Creating a custom conditional-formatting rule |
|
|
17 | (3) |
|
Editing a conditional-formatting rule |
|
|
20 | (1) |
|
Removing conditional-formatting rules |
|
|
21 | (1) |
|
Summarizing Data with Subtotals |
|
|
22 | (2) |
|
|
|
24 | (2) |
|
Consolidating Data from Multiple Worksheets |
|
|
26 | (5) |
|
Consolidating by position |
|
|
26 | (2) |
|
Consolidating by category |
|
|
28 | (3) |
|
Chapter 2 Working with Data-Analysis Tools |
|
|
31 | (24) |
|
|
|
32 | (5) |
|
Creating a basic data table |
|
|
32 | (2) |
|
Creating a two-input data table |
|
|
34 | (3) |
|
Skipping data tables when calculating workbooks |
|
|
37 | (1) |
|
Analyzing Data with Goal Seek |
|
|
37 | (2) |
|
Analyzing Data with Scenarios |
|
|
39 | (4) |
|
|
|
40 | (2) |
|
|
|
42 | (1) |
|
|
|
42 | (1) |
|
|
|
43 | (1) |
|
Optimizing Data with Solver |
|
|
43 | (12) |
|
|
|
43 | (1) |
|
|
|
44 | (1) |
|
When should you use Solver? |
|
|
44 | (2) |
|
Loading the Solver add-in |
|
|
46 | (1) |
|
Optimizing a result with Solver |
|
|
47 | (3) |
|
Adding constraints to Solver |
|
|
50 | (2) |
|
Save a Solver solution as a scenario |
|
|
52 | (3) |
|
Chapter 3 Introducing Excel Tables |
|
|
55 | (22) |
|
What Is a Table and Why Should I Care? |
|
|
55 | (2) |
|
|
|
57 | (4) |
|
Getting the data from an external source |
|
|
58 | (1) |
|
Converting a range to a table |
|
|
58 | (2) |
|
|
|
60 | (1) |
|
Analyzing Table Information |
|
|
61 | (16) |
|
Displaying simple statistics |
|
|
61 | (2) |
|
|
|
63 | (1) |
|
|
|
64 | (3) |
|
|
|
67 | (1) |
|
|
|
68 | (1) |
|
|
|
68 | (1) |
|
Applying a predefined AutoFilter |
|
|
69 | (2) |
|
Applying multiple filters |
|
|
71 | (1) |
|
Applying advanced filters |
|
|
72 | (5) |
|
Chapter 4 Grabbing Data from External Sources |
|
|
77 | (22) |
|
What's All This about External Data? |
|
|
77 | (2) |
|
Exporting Data from Other Programs |
|
|
79 | (1) |
|
Importing External Data into Excel |
|
|
80 | (10) |
|
Importing data from an Access table |
|
|
80 | (1) |
|
Importing data from a Word table |
|
|
81 | (1) |
|
Introducing text file importing |
|
|
82 | (1) |
|
Importing a delimited text file |
|
|
83 | (1) |
|
Importing a fixed-width text file |
|
|
84 | (2) |
|
Importing data from a web page |
|
|
86 | (2) |
|
|
|
88 | (2) |
|
Querying External Databases |
|
|
90 | (9) |
|
|
|
91 | (3) |
|
|
|
94 | (5) |
|
Chapter 5 Analyzing Table Data with Functions |
|
|
99 | (14) |
|
The Database Functions: Some General Remarks |
|
|
100 | (1) |
|
Retrieving a Value from a Table |
|
|
101 | (1) |
|
Summing a Column's Values |
|
|
102 | (2) |
|
Counting a Column's Values |
|
|
104 | (1) |
|
Averaging a Column's Values |
|
|
105 | (1) |
|
Determining a Column's Maximum and Minimum Values |
|
|
106 | (2) |
|
Multiplying a Column's Values |
|
|
108 | (1) |
|
Deriving a Column's Standard Deviation |
|
|
109 | (1) |
|
Calculating a Column's Variance |
|
|
110 | (3) |
| Part 2: Analyzing Data Using Pivottables And Pivotcharts |
|
113 | (68) |
|
Chapter 6 Creating and Using PivotTables |
|
|
115 | (22) |
|
Understanding PivotTables |
|
|
116 | (2) |
|
Exploring PivotTable Features |
|
|
118 | (1) |
|
Building a PivotTable from an Excel Range or Table |
|
|
119 | (3) |
|
Creating a PivotTable from External Data |
|
|
122 | (3) |
|
Building a PivotTable from Microsoft Query |
|
|
122 | (1) |
|
Building a PivotTable from a new data connection |
|
|
123 | (2) |
|
Refreshing PivotTable Data |
|
|
125 | (1) |
|
Refreshing PivotTable data manually |
|
|
125 | (1) |
|
Refreshing PivotTable data automatically |
|
|
125 | (1) |
|
Adding Multiple Fields to a PivotTable Area |
|
|
126 | (1) |
|
Pivoting a Field to a Different Area |
|
|
127 | (1) |
|
Grouping PivotTable Values |
|
|
128 | (3) |
|
|
|
128 | (1) |
|
Grouping date and time values |
|
|
129 | (1) |
|
|
|
130 | (1) |
|
Filtering PivotTable Values |
|
|
131 | (6) |
|
|
|
131 | (1) |
|
Filtering row or column items |
|
|
132 | (1) |
|
Filtering PivotTable values |
|
|
133 | (1) |
|
Filtering a PivotTable with a slicer |
|
|
134 | (3) |
|
Chapter 7 Performing PivotTable Calculations |
|
|
137 | (24) |
|
Messing around with PivotTable Summary Calculations |
|
|
138 | (11) |
|
Changing the PivotTable summary calculation |
|
|
138 | (2) |
|
Trying out the difference summary calculation |
|
|
140 | (2) |
|
Applying a percentage summary calculation |
|
|
142 | (2) |
|
Adding a running total summary calculation |
|
|
144 | (3) |
|
Creating an index summary calculation |
|
|
147 | (2) |
|
Working with PivotTable Subtotals |
|
|
149 | (2) |
|
Turning off subtotals for a field |
|
|
149 | (1) |
|
Displaying multiple subtotals for a field |
|
|
149 | (2) |
|
Introducing Custom Calculations |
|
|
151 | (3) |
|
Formulas for custom calculations |
|
|
151 | (1) |
|
Checking out the custom calculation types |
|
|
152 | (1) |
|
Understanding custom calculation limitations |
|
|
152 | (2) |
|
Inserting a Custom Calculated Field |
|
|
154 | (1) |
|
Inserting a Custom Calculated Item |
|
|
155 | (2) |
|
Editing a Custom Calculation |
|
|
157 | (1) |
|
Deleting a Custom Calculation |
|
|
158 | (3) |
|
Chapter 8 Building PivotCharts |
|
|
161 | (20) |
|
Introducing the PivotChart |
|
|
161 | (4) |
|
Understanding PivotChart pros and cons |
|
|
162 | (1) |
|
|
|
163 | (1) |
|
Understanding PivotChart limitations |
|
|
164 | (1) |
|
|
|
165 | (4) |
|
Creating a PivotChart from a PivotTable |
|
|
165 | (1) |
|
Embedding a PivotChart on a PivotTable's worksheet |
|
|
165 | (1) |
|
Creating a PivotChart from an Excel range or table |
|
|
166 | (3) |
|
|
|
169 | (14) |
|
Moving a PivotChart to another sheet |
|
|
169 | (1) |
|
|
|
170 | (2) |
|
Changing the PivotChart type |
|
|
172 | (1) |
|
Adding data labels to your PivotChart |
|
|
173 | (1) |
|
|
|
174 | (1) |
|
|
|
175 | (2) |
|
Moving the PivotChart legend |
|
|
177 | (1) |
|
Displaying a data table with the PivotChart |
|
|
178 | (3) |
| Part 3: Discovering Advanced Data-Analysis Tools |
|
181 | (98) |
|
Chapter 9 Dealing with Data Models |
|
|
183 | (20) |
|
Understanding Excel Data Models |
|
|
184 | (8) |
|
Creating a relationship between tables |
|
|
186 | (1) |
|
Importing related external data tables |
|
|
187 | (2) |
|
Basing a PivotTable on multiple, related tables |
|
|
189 | (3) |
|
Managing a Data Model with Power Pivot |
|
|
192 | (7) |
|
Enabling the Power Pivot add-in |
|
|
192 | (1) |
|
Adding a table to the Data Model |
|
|
193 | (1) |
|
Importing related tables from an external data source |
|
|
193 | (2) |
|
Viewing table relationships |
|
|
195 | (1) |
|
Viewing relationship details |
|
|
196 | (1) |
|
Creating a relationship between tables with Power Pivot |
|
|
197 | (2) |
|
|
|
199 | (1) |
|
Creating a PivotTable or PivotChart from Your Data Model |
|
|
200 | (3) |
|
Chapter 10 Tracking Trends and Making Forecasts |
|
|
203 | (24) |
|
Plotting a Best-Fit Trend Line |
|
|
204 | (2) |
|
Calculating Best-Fit Values |
|
|
206 | (2) |
|
Plotting Forecasted Values |
|
|
208 | (2) |
|
|
|
210 | (2) |
|
Extending a linear trend using the fill handle |
|
|
211 | (1) |
|
Extending a linear trend using the Series command |
|
|
211 | (1) |
|
Calculating Forecasted Linear Values |
|
|
212 | (2) |
|
Plotting an Exponential Trend Line |
|
|
214 | (2) |
|
Calculating Exponential Trend Values |
|
|
216 | (2) |
|
Plotting a Logarithmic Trend Line |
|
|
218 | (2) |
|
Plotting a Power Trend Line |
|
|
220 | (2) |
|
Plotting a Polynomial Trend Line |
|
|
222 | (1) |
|
Creating a Forecast Sheet |
|
|
223 | (4) |
|
Chapter 11 Analyzing Data Using Statistics |
|
|
227 | (18) |
|
|
|
228 | (5) |
|
|
|
228 | (1) |
|
|
|
229 | (1) |
|
|
|
229 | (1) |
|
Counting cells that match criteria |
|
|
229 | (1) |
|
Counting cells that match multiple criteria |
|
|
230 | (1) |
|
|
|
231 | (1) |
|
|
|
232 | (1) |
|
|
|
233 | (3) |
|
|
|
233 | (1) |
|
Calculating a conditional average |
|
|
233 | (1) |
|
Calculating an average based on multiple conditions |
|
|
234 | (1) |
|
|
|
235 | (1) |
|
|
|
235 | (1) |
|
|
|
236 | (2) |
|
Determining the Nth Largest or Smallest Value |
|
|
238 | (2) |
|
Calculating the nth highest value |
|
|
238 | (1) |
|
Calculating the nth smallest value |
|
|
239 | (1) |
|
Creating a Grouped Frequency Distribution |
|
|
240 | (1) |
|
|
|
241 | (1) |
|
Calculating the Standard Deviation |
|
|
242 | (1) |
|
|
|
243 | (2) |
|
Chapter 12 Analyzing Data Using Descriptive Statistics |
|
|
245 | (16) |
|
Loading the Analysis ToolPak |
|
|
246 | (1) |
|
Generating Descriptive Statistics |
|
|
247 | (3) |
|
Calculating a Moving Average |
|
|
250 | (3) |
|
Determining Rank and Percentile |
|
|
253 | (2) |
|
Generating Random Numbers |
|
|
255 | (3) |
|
Creating a Frequency Distribution |
|
|
258 | (3) |
|
Chapter 13 Analyzing Data Using Inferential Statistics |
|
|
261 | (18) |
|
|
|
262 | (2) |
|
|
|
264 | (3) |
|
|
|
267 | (2) |
|
Determining the Regression |
|
|
269 | (2) |
|
Calculating the Correlation |
|
|
271 | (2) |
|
Calculating the Covariance |
|
|
273 | (1) |
|
|
|
274 | (2) |
|
|
|
276 | (3) |
| Part 4: The Part Of Tens |
|
279 | (38) |
|
Chapter 14 Ten Things You Ought to Know about Statistics |
|
|
281 | (12) |
|
Descriptive Statistics Are Straightforward |
|
|
282 | (1) |
|
Averages Aren't So Simple Sometimes |
|
|
282 | (1) |
|
Standard Deviations Describe Dispersion |
|
|
283 | (2) |
|
An Observation Is an Observation |
|
|
285 | (1) |
|
A Sample Is a Subset of Values |
|
|
285 | (1) |
|
Inferential Statistics Are Cool But Complicated |
|
|
285 | (2) |
|
Probability Distributions Aren't Always Confusing |
|
|
287 | (2) |
|
|
|
287 | (1) |
|
|
|
288 | (1) |
|
Parameters Aren't So Complicated |
|
|
289 | (1) |
|
Skewness and Kurtosis Describe a Probability Distribution's Shape |
|
|
289 | (2) |
|
Confidence Intervals Seem Complicated at First But Are Useful |
|
|
291 | (2) |
|
Chapter 15 Ten Ways to Analyze Financial Data |
|
|
293 | (10) |
|
|
|
294 | (1) |
|
Calculating Present Value |
|
|
295 | (1) |
|
Determining Loan Payments |
|
|
296 | (1) |
|
Calculating a Loan Payment's Principal and Interest |
|
|
297 | (1) |
|
Calculating Cumulative Loan Principal and Interest |
|
|
297 | (1) |
|
Finding the Required Interest Rate |
|
|
298 | (1) |
|
Determining the Internal Rate of Return |
|
|
299 | (1) |
|
Calculating Straight-Line Depreciation |
|
|
300 | (1) |
|
Returning the Fixed-Declining Balance Depreciation |
|
|
300 | (1) |
|
Determining the Double-Declining Balance Depreciation |
|
|
301 | (2) |
|
Chapter 16 Ten Ways to Raise Your PivotTable Game |
|
|
303 | (14) |
|
Turn the PivotTable Fields Task Pane On and Off |
|
|
304 | (1) |
|
Change the PivotTable Fields Task Pane Layout |
|
|
304 | (2) |
|
Display the Details Behind PivotTable Data |
|
|
306 | (2) |
|
|
|
308 | (1) |
|
Create a Custom PivotTable Style |
|
|
309 | (2) |
|
Preserve PivotTable Formatting |
|
|
311 | (1) |
|
|
|
312 | (1) |
|
|
|
312 | (2) |
|
Reduce the Size of PivotTable Workbooks |
|
|
314 | (1) |
|
Use a PivotTable Value in a Formula |
|
|
314 | (3) |
| Appendix: Glossary Of Data Analysis And Excel Terms |
|
317 | (10) |
| Index |
|
327 | |
