Foreword |
|
xv | |
Preface |
|
xvii | |
Acknowledgments |
|
xxi | |
|
PART ONE Using Excel Efficiently |
|
|
1 | (92) |
|
|
3 | (12) |
|
|
8 | (1) |
|
|
8 | (2) |
|
Appendix: AutoFill in Excel 2003 and Excel Mac 2011 |
|
|
10 | (3) |
|
|
13 | (1) |
|
|
14 | (1) |
|
Chapter 2 Selecting Efficiently in Excel |
|
|
15 | (6) |
|
|
18 | (1) |
|
|
19 | (2) |
|
Chapter 3 Formulas, Functions, and Relative and Absolute Addressing |
|
|
21 | (10) |
|
Relative and Absolute Addressing |
|
|
22 | (3) |
|
|
25 | (3) |
|
Appendix: Doing It in Mac Excel 2011 |
|
|
28 | (1) |
|
|
29 | (1) |
|
|
30 | (1) |
|
Chapter 4 Naming Cells and Ranges |
|
|
31 | (14) |
|
|
31 | (2) |
|
|
33 | (1) |
|
Using the Name Menu to Create Names |
|
|
34 | (4) |
|
Appendix: Using the Names Menu in Excel 2003 and Mac Excel 2011 |
|
|
38 | (3) |
|
|
41 | (1) |
|
|
42 | (3) |
|
Chapter 5 Conditional and Advanced Conditional Formatting in Excel |
|
|
45 | (12) |
|
Simple Conditional Formatting; Adding a Rule |
|
|
45 | (1) |
|
New Conditional Formatting Features |
|
|
46 | (3) |
|
Advanced Conditional Formatting |
|
|
49 | (3) |
|
Appendix: Using the Conditional Formatting in Excel 2003 |
|
|
52 | (1) |
|
|
53 | (2) |
|
|
55 | (2) |
|
|
57 | (24) |
|
|
57 | (1) |
|
Creating a Chart Using the Menu |
|
|
57 | (4) |
|
Adding More Data to an Existing Chart |
|
|
61 | (3) |
|
|
64 | (2) |
|
Three-Dimensional Charts---Column and Pie |
|
|
66 | (4) |
|
|
70 | (3) |
|
Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 |
|
|
73 | (8) |
|
Chapter 7 Sparklines and Advanced Topics in Excel Charts |
|
|
81 | (12) |
|
|
81 | (2) |
|
|
83 | (7) |
|
|
90 | (1) |
|
|
91 | (2) |
|
PART TWO IF Functions and Text Manipulations |
|
|
93 | (20) |
|
|
95 | (8) |
|
|
95 | (3) |
|
|
98 | (1) |
|
Nested IF---Payroll Example |
|
|
99 | (2) |
|
Appendix: Using the IF Function in Mac Excel 2011 |
|
|
101 | (1) |
|
|
102 | (1) |
|
|
102 | (1) |
|
Chapter 9 Text Manipulation |
|
|
103 | (10) |
|
|
103 | (5) |
|
Appendix: Using Text to Column in Excel 2003 |
|
|
108 | (3) |
|
|
111 | (1) |
|
|
112 | (1) |
|
PART THREE Statistical Tools |
|
|
113 | (52) |
|
Chapter 10 Descriptive Statistics |
|
|
115 | (6) |
|
|
115 | (2) |
|
Appendix: Descriptive Statistics with the Mac Excel 2011 |
|
|
117 | (2) |
|
|
119 | (1) |
|
|
119 | (2) |
|
Chapter 11 Frequency Distributions |
|
|
121 | (10) |
|
|
126 | (3) |
|
Appendix: Frequency Distributions with Mac Excel 2011 |
|
|
129 | (1) |
|
|
129 | (1) |
|
|
130 | (1) |
|
Chapter 12 Statistical Regression |
|
|
131 | (10) |
|
Using the Scatter Chart in Excel |
|
|
131 | (2) |
|
Linear Regression---Using Excel Functions |
|
|
133 | (4) |
|
Appendix: Using the Chart Feature to Create a Trend Line in Excel 2003 and Mac Excel 2011 |
|
|
137 | (3) |
|
|
140 | (1) |
|
|
140 | (1) |
|
Chapter 13 Data Analysis---The Excel Easy to Use Statistics Add-In |
|
|
141 | (10) |
|
|
141 | (2) |
|
Frequency Distribution Using Histogram |
|
|
143 | (5) |
|
Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003 |
|
|
148 | (1) |
|
|
148 | (1) |
|
|
149 | (2) |
|
Chapter 14 Data Analysis---Multi-Regression |
|
|
151 | (14) |
|
Simple or Single Variable Regression |
|
|
151 | (1) |
|
|
152 | (8) |
|
Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003 |
|
|
160 | (1) |
|
|
160 | (1) |
|
|
161 | (4) |
|
PART FOUR What-If Analysis |
|
|
165 | (46) |
|
Chapter 15 Naming Cells---For Meaningful Decision Making and Modeling |
|
|
167 | (10) |
|
One More Example---If You Have a Ready Model |
|
|
168 | (2) |
|
Appendix: Create and Apply Names in Excel 2003 and Mac Excel 2011 |
|
|
170 | (5) |
|
|
175 | (1) |
|
|
175 | (2) |
|
Chapter 16 What-If Analysis and Goal Seek |
|
|
177 | (8) |
|
|
178 | (4) |
|
Appendix: Goal Seek in Excel 2003 and Mac Excel 2011 |
|
|
182 | (1) |
|
|
183 | (1) |
|
|
183 | (2) |
|
Chapter 17 Sensitivity Analysis One and Two-Way Data Tables |
|
|
185 | (11) |
|
|
187 | (2) |
|
Data Tables---One More Example |
|
|
189 | (1) |
|
Break-Even Point (BEP) Chart |
|
|
190 | (2) |
|
Appendix: Sensitivity Analysis One and Two-Way Data Tables for Excel 2003 and Mac Excel 2011 |
|
|
192 | (1) |
|
|
193 | (2) |
|
|
195 | (1) |
|
Chapter 18 Using Scroll Bars for Sensitivity Analysis |
|
|
196 | (15) |
|
|
203 | (2) |
|
Appendix: Adding a Scroll Bar in Excel 2003, 2007, and Mac Excel 2011 |
|
|
205 | (2) |
|
|
207 | (1) |
|
|
208 | (3) |
|
PART FIVE Multi-Page Systems and Lookups |
|
|
211 | (18) |
|
Chapter 19 Multi-Page Budgets---Going to the Third Dimension |
|
|
213 | (8) |
|
|
213 | (4) |
|
A Second Example---Bakers' Supplies |
|
|
217 | (2) |
|
|
219 | (1) |
|
|
220 | (1) |
|
|
221 | (8) |
|
Range---Approximate Match Lookup |
|
|
221 | (3) |
|
|
224 | (3) |
|
|
227 | (1) |
|
|
228 | (1) |
|
PART SIX The Data Menu and Ribbon |
|
|
229 | (78) |
|
|
231 | (10) |
|
Sorting by Multiple Parameters |
|
|
233 | (3) |
|
Appendix: Sorting in Excel 2003 and Mac Excel 2011 |
|
|
236 | (2) |
|
|
238 | (1) |
|
|
239 | (2) |
|
|
241 | (8) |
|
|
241 | (2) |
|
|
243 | (1) |
|
|
243 | (1) |
|
Appendix: AutoFilter in Excel 2003 |
|
|
244 | (2) |
|
|
246 | (1) |
|
|
247 | (2) |
|
Chapter 23 Data Forms and Features Eliminated In Excel 2007 and 2010 |
|
|
249 | (6) |
|
Appendix: The Data Form in Excel 2003 or Earlier versions and Mac Excel 2011 |
|
|
251 | (1) |
|
|
252 | (1) |
|
|
253 | (2) |
|
Chapter 24 Group and Outline Data |
|
|
255 | (8) |
|
Appendix: Group and Outline Data Excel 2003 and Mac Excel 2011 |
|
|
258 | (2) |
|
|
260 | (1) |
|
|
261 | (2) |
|
Chapter 25 Excel Subtotals |
|
|
263 | (12) |
|
One More Example---Function Applied to Multiple Categories |
|
|
265 | (1) |
|
|
265 | (4) |
|
Second Example---Larger Database |
|
|
269 | (1) |
|
Appendix: Creating Subtotals in Excel 2003 and Mac Excel 2011 |
|
|
269 | (4) |
|
|
273 | (1) |
|
|
273 | (2) |
|
|
275 | (14) |
|
|
275 | (7) |
|
Appendix: The Pivot Table in Excel 2003 and Mac Excel 2011 |
|
|
282 | (4) |
|
|
286 | (1) |
|
|
286 | (3) |
|
Chapter 27 Data Mining Using Pivot Tables |
|
|
289 | (12) |
|
Appendix: Advanced PivotTable Techniques in Excel 2003 |
|
|
296 | (1) |
|
|
297 | (1) |
|
|
298 | (3) |
|
Chapter 28 Using Slicers to Filter Pivot Tables |
|
|
301 | (6) |
|
|
302 | (2) |
|
Sharing Slicers for More Than One Pivot Table |
|
|
304 | (1) |
|
|
305 | (1) |
|
|
306 | (1) |
|
PART SEVEN Excel Financial Tools |
|
|
307 | (34) |
|
Chapter 29 NPV and IRR---Evaluating Capital Investments |
|
|
309 | (6) |
|
|
309 | (2) |
|
IRR---Internal Rate of Return |
|
|
311 | (2) |
|
|
313 | (1) |
|
|
313 | (2) |
|
Chapter 30 Unconventional Financial Functions: XNPV and XIRR |
|
|
315 | (4) |
|
Excel XNPV---The Net Present Value Function for Uneven Intervals |
|
|
315 | (1) |
|
Excel XIRR---The Internal Rate of Return Function for Uneven Intervals |
|
|
316 | (2) |
|
|
318 | (1) |
|
|
318 | (1) |
|
Chapter 31 Frequently Used Financial Functions |
|
|
319 | (6) |
|
|
320 | (3) |
|
|
323 | (1) |
|
|
323 | (2) |
|
Chapter 32 Amortization Tables |
|
|
325 | (12) |
|
|
325 | (9) |
|
|
334 | (1) |
|
|
334 | (3) |
|
Chapter 33 Accounting Depreciation Functions |
|
|
337 | (4) |
|
SLD Straight Line Depreciation |
|
|
337 | (1) |
|
SYD Sum of the Years Digits |
|
|
337 | (2) |
|
|
339 | (1) |
|
|
339 | (2) |
|
PART EIGHT Using the Solver Add-In |
|
|
341 | (24) |
|
Chapter 34 Beyond the Goal Seek---More Than One Changing Cell? Use the Solver |
|
|
343 | (10) |
|
Example---Break-Even Point |
|
|
343 | (1) |
|
|
343 | (6) |
|
Appendix: Using the Solver in Excel 2003 and Mac Excel 2011 |
|
|
349 | (1) |
|
|
350 | (2) |
|
|
352 | (1) |
|
Chapter 35 The Solver Add-In---Optimizer |
|
|
353 | (12) |
|
|
353 | (2) |
|
Nonlinear Example Using the Solver |
|
|
355 | (5) |
|
Appendix: Using the Solver in Excel 2003 and Mac 2011 |
|
|
360 | (1) |
|
|
360 | (1) |
|
|
360 | (5) |
Appendix Summary Case Study---Supply Chain Management Example |
|
365 | (20) |
About the Author |
|
385 | (2) |
Index |
|
387 | |