Microsoft Excel

Part One: Using Excel Efficiently - Page 25
Part One: Using Excel Efficiently - Page 26
Chapter 1: AutoFill - Page 27
Chapter 1: AutoFill - Page 28
Chapter 1: AutoFill - Page 29
Chapter 1: AutoFill - Page 30
Chapter 1: AutoFill - Page 31
AutoFill Options - Page 32
AutoFill Options - Page 33
Appendix: AutoFill in Excel 2003 and Excel Mac 2011 - Page 34
Appendix: AutoFill in Excel 2003 and Excel Mac 2011 - Page 35
Mac Excel 2011 - Page 36
Review Questions - Page 37
Answers - Page 38
Chapter 2: Selecting Efficiently in Excel - Page 39
Chapter 2: Selecting Efficiently in Excel - Page 40
Chapter 2: Selecting Efficiently in Excel - Page 41
Review Questions - Page 42
Answers - Page 43
Answers - Page 44
Chapter 3: Formulas, Functions, and Relative and Absolute Addressing - Page 45
Relative and Absolute Addressing - Page 46
Relative and Absolute Addressing - Page 47
Relative and Absolute Addressing - Page 48
Other Functions - Page 49
Other Functions - Page 50
Other Functions - Page 51
Appendix: Doing It in Mac Excel 2011 - Page 52
Review Questions - Page 53
Answers - Page 54
Chapter 4: Naming Cells and Ranges - Page 55
Chapter 4: Naming Cells and Ranges - Page 56
Naming a Range of Cells - Page 57
Using the Name Menu to Create Names - Page 58
Using the Name Menu to Create Names - Page 59
Using the Name Menu to Create Names - Page 60
More About Names - Page 61
Appendix: Using the Names Menu in Excel 2003 and Mac Excel 2011 - Page 62
Appendix: Using the Names Menu in Excel 2003 and Mac Excel 2011 - Page 63
Appendix: Using the Names Menu in Excel 2003 and Mac Excel 2011 - Page 64
Review Questions - Page 65
Answers - Page 66
Answers - Page 67
Answers - Page 68
Chapter 5: Conditional and Advanced Conditional Formatting in Excel - Page 69
New Conditional Formatting Features - Page 70
New Conditional Formatting Features - Page 71
New Conditional Formatting Features - Page 72
Advanced Conditional Formatting - Page 73
Advanced Conditional Formatting - Page 74
Advanced Conditional Formatting - Page 75
Appendix: Using the Conditional Formatting in Excel 2003 - Page 76
Review Questions - Page 77
Review Questions - Page 78
Answers - Page 79
Answers - Page 80
Chapter 6: Excel Charts - Page 81
Chapter 6: Excel Charts - Page 82
Chapter 6: Excel Charts - Page 83
Chapter 6: Excel Charts - Page 84
Adding More Data to an Existing Chart - Page 85
Very Important Facts about All Excel Charts: Selecting a Series - Page 86
Very Important Facts about All Excel Charts: Selecting a Series - Page 87
More about Charts - Page 88
Scatter Charts and Line Charts - Page 89
Three-Dimensional Charts—Column and Pie - Page 90
Three-Dimensional Charts—Column and Pie - Page 91
Three-Dimensional Charts—Column and Pie - Page 92
Three-Dimensional Charts—Column and Pie - Page 93
Pie Charts - Page 94
Pie Charts - Page 95
Pie Charts - Page 96
Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 - Page 97
Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 - Page 98
Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 - Page 99
Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 - Page 100
Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 - Page 101
Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 - Page 102
Mac Excel 2011 - Page 103
Mac Excel 2011 - Page 104
Chapter 7: Sparklines and Advanced Topics in Excel Charts - Page 105
Chapter 7: Sparklines and Advanced Topics in Excel Charts - Page 106
More about Charts - Page 107
Surface Charts - Page 108
Animating Charts - Page 109
Animating Charts - Page 110
Animating Charts - Page 111
Animating Charts - Page 112
Animating Charts - Page 113
Review Questions - Page 114
Answers - Page 115
Answers - Page 116
Part Two: IF Functions and Text Manipulations - Page 117
Part Two: IF Functions and Text Manipulations - Page 118
Chapter 8: IF Functions - Page 119
Chapter 8: IF Functions - Page 120
Chapter 8: IF Functions - Page 121
Nested IF Functions - Page 122
Nested IF—Payroll Example - Page 123
Nested IF—Payroll Example - Page 124
Appendix: Using the IF Function in Mac Excel 2011 - Page 125
Review Questions - Page 126
Chapter 9: Text Manipulation - Page 127
Chapter 9: Text Manipulation - Page 128
Opening Text Files - Page 129
Opening Text Files - Page 130
One More Example - Page 131
Appendix: Using Text to Column in Excel 2003 - Page 132
Opening Text Files in Excel 2003 - Page 133
Opening Text Files in the Mac 2011 - Page 134
Review Questions - Page 135
Answers - Page 136
Part Three: Statistical Tools - Page 137
Part Three: Statistical Tools - Page 138
Chapter 10: Descriptive Statistics - Page 139
Chapter 10: Descriptive Statistics - Page 140
Appendix: Descriptive Statistics with the Mac Excel 2011 - Page 141
Appendix: Descriptive Statistics with the Mac Excel 2011 - Page 142
Review Questions - Page 143
Review Questions - Page 144
Chapter 11: Frequency Distributions - Page 145
Chapter 11: Frequency Distributions - Page 146
Chapter 11: Frequency Distributions - Page 147
Chapter 11: Frequency Distributions - Page 148
Chapter 11: Frequency Distributions - Page 149
One More Example - Page 150
One More Example - Page 151
One More Example - Page 152
Appendix: Frequency Distributions with Mac Excel 2011 - Page 153
Answers - Page 154
Chapter 12: Statistical Regression - Page 155
Chapter 12: Statistical Regression - Page 156
Linear Regression—Using Excel Functions - Page 157
Linear Regression—Using Excel Functions - Page 158
Linear Regression—Using Excel Functions - Page 159
One More Example - Page 160
Appendix: Using the Chart Feature to Create a Trend Line in Excel 2003 and Mac Excel 2011 - Page 161
Appendix: Using the Chart Feature to Create a Trend Line in Excel 2003 and Mac Excel 2011 - Page 162
Appendix: Using the Chart Feature to Create a Trend Line in Excel 2003 and Mac Excel 2011 - Page 163
Review Questions - Page 164
Chapter 13: Data Analysis—The Excel Easy to Use Statistics Add-In - Page 165
Chapter 13: Data Analysis—The Excel Easy to Use Statistics Add-In - Page 166
Frequency Distribution Using Histogram - Page 167
One More Example - Page 168
One More Example - Page 169
One More Example - Page 170
One More Example - Page 171
Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003 - Page 172
Answers - Page 173
Answers - Page 174
Chapter 14: Data Analysis—Multi-Regression - Page 175
Multi-Regression - Page 176
Multi-Regression - Page 177
Multi-Regression - Page 178
Multi-Regression - Page 179
Multi-Regression - Page 180
Multi-Regression - Page 181
Multi-Regression - Page 182
Multi-Regression - Page 183
Appendix: Using Data Analysis in Excel Mac and Excel 2003 - Page 184
Answers - Page 185
Answers - Page 186
Answers - Page 187
Answers - Page 188
Part Four: What-If Analysis - Page 189
Part Four: What-If Analysis - Page 190
Chapter 15: Naming Cells—For Meaningful Decision Making and Modeling - Page 191
One More Example—If You Have a Ready Model - Page 192
One More Example—If You Have a Ready Model - Page 193
Appendix: Create and Apply Names in Excel 2003 and Mac Excel 2011 - Page 194
One More Example—If You Have a Ready Model - Page 195
Mac Excel 2011 - Page 196
Mac Excel 2011 - Page 197
Mac Excel 2011 - Page 198
Review Questions - Page 199
Review Questions - Page 200
Chapter 16: What-If Analysis and Goal Seek - Page 201
Goal Seek - Page 202
Goal Seek - Page 203
One More Example—Break-Even Point Analysis - Page 204
One More Example—Break-Even Point Analysis - Page 205
Appendix: Goal Seek in Excel 2003 and Mac Excel 2011 - Page 206
Review Questions - Page 207
Review Questions - Page 208
Chapter 17: Sensitivity Analysis—One-and Two-Way Data Tables - Page 209
Chapter 17: Sensitivity Analysis—One-and Two-Way Data Tables - Page 210
Two-Way Table - Page 211
Two-Way Table - Page 212
Data Tables—One More Example - Page 213
Break-Even Point (BEP) Chart - Page 214
Break-Even Point (BEP) Chart - Page 215
Appendix: Sensitivity Analysis—One-and Two-Way Data Tables for Excel 2003 and Mac Excel 2011 - Page 216
Mac Excel 2011 - Page 217
Mac Excel 2011 - Page 218
Answers - Page 219
Answers - Page 220
Answers - Page 221
Answers - Page 222
Chapter 18: Using Scroll Bars for Sensitivity Analysis - Page 223
Chapter 18: Using Scroll Bars for Sensitivity Analysis - Page 224
Chapter 18: Using Scroll Bars for Sensitivity Analysis - Page 225
Chapter 18: Using Scroll Bars for Sensitivity Analysis - Page 226
Scroll Bar Limitations - Page 227
Scroll Bar Limitations - Page 228
Appendix: Adding a Scroll Bar in Excel 2003, 2007, and Mac Excel 2011 - Page 229
Excel 2007 - Page 230
Review Questions - Page 231
Answers - Page 232
Answers - Page 233
Answers - Page 234
Part Five: Multi-Page Systems and Lookups - Page 235
Part Five: Multi-Page Systems and Lookups - Page 236
Chapter 19: Multi-Page Budgets—Going to the Third Dimension - Page 237
Chapter 19: Multi-Page Budgets—Going to the Third Dimension - Page 238
Chapter 19: Multi-Page Budgets—Going to the Third Dimension - Page 239
Chapter 19: Multi-Page Budgets—Going to the Third Dimension - Page 240
A Second Example—Bakers' Supplies - Page 241
A Second Example—Bakers' Supplies - Page 242
Review Questions - Page 243
Answers - Page 244
Chapter 20: Lookup Tables - Page 245
Chapter 20: Lookup Tables - Page 246
Chapter 20: Lookup Tables - Page 247
An Exact Match Lookup - Page 248
An Exact Match Lookup - Page 249
An Exact Match Lookup - Page 250
Review Questions - Page 251
Answers - Page 252
Part Six: The Data Menu and Ribbon - Page 253
Part Six: The Data Menu and Ribbon - Page 254
Chapter 21: Sorting Data - Page 255
Chapter 21: Sorting Data - Page 256
Sorting by Multiple Parameters - Page 257
Sorting by Multiple Parameters - Page 258
Sorting by Multiple Parameters - Page 259
Appendix: Sorting in Excel 2003 and Mac Excel 2011 - Page 260
Excel 2011 - Page 261
Review Questions - Page 262
Answers - Page 263
Answers - Page 264
Chapter 22: AutoFilter - Page 265
Chapter 22: AutoFilter - Page 266
Below and Above Average - Page 267
Appendix: AutoFilter in Excel 2003 - Page 268
Appendix: AutoFilter in Excel 2003 - Page 269
Review Questions - Page 270
Answers - Page 271
Answers - Page 272
Chapter 23: Data Forms and Features Eliminated in Excel 2007 and 2010 - Page 273
Chapter 23: Data Forms and Features Eliminated in Excel 2007 and 2010 - Page 274
Appendix: The Data Form in Excel 2003 or Earlier versions and Mac Excel 2011 - Page 275
Excel 2011 - Page 276
Answers - Page 277
Answers - Page 278
Chapter 24: Group and Outline Data - Page 279
Chapter 24: Group and Outline Data - Page 280
Chapter 24: Group and Outline Data - Page 281
Appendix: Group and Outline Data Excel 2003 and Mac Excel 2011 - Page 282
Mac Excel 2011 - Page 283
Review Questions - Page 284
Answers - Page 285
Answers - Page 286
Chapter 25: Excel Subtotals - Page 287
Chapter 25: Excel Subtotals - Page 288
One More Example—Function Applied to Multiple Categories - Page 289
One More Example—Function Applied to Multiple Categories - Page 290
One More Example—Function Applied to Multiple Categories - Page 291
One More Example—Function Applied to Multiple Categories - Page 292
Second Example—Larger Database - Page 293
Excel 2011 - Page 294
Visible Cells Only Icon in Excel 2011 - Page 295
Visible Cells Only Icon in Excel 2011 - Page 296
Review Questions - Page 297
Review Questions - Page 298
Chapter 26: Pivot Tables - Page 299
Chapter 26: Pivot Tables - Page 300
Chapter 26: Pivot Tables - Page 301
Chapter 26: Pivot Tables - Page 302
Chapter 26: Pivot Tables - Page 303
Chapter 26: Pivot Tables - Page 304
Chapter 26: Pivot Tables - Page 305
Appendix: The Pivot Table in Excel 2003 and Mac Excel 2011 - Page 306
Excel 2011 - Page 307
Excel 2011 - Page 308
Excel 2011 - Page 309
Review Questions - Page 310
Review Questions - Page 311
Review Questions - Page 312
Chapter 27: Data Mining Using Pivot Tables - Page 313
Chapter 27: Data Mining Using Pivot Tables - Page 314
Chapter 27: Data Mining Using Pivot Tables - Page 315
Chapter 27: Data Mining Using Pivot Tables - Page 316
Chapter 27: Data Mining Using Pivot Tables - Page 317
Chapter 27: Data Mining Using Pivot Tables - Page 318
Chapter 27: Data Mining Using Pivot Tables - Page 319
Appendix: Advanced PivotTable Techniques in Excel 2003 - Page 320
Review Questions - Page 321
Answers - Page 322
Answers - Page 323
Answers - Page 324
Chapter 28: Using Slicers to Filter Pivot Tables - Page 325
Format a Slicer - Page 326
Format a Slicer - Page 327
Sharing Slicers for More Than One Pivot Table - Page 328
Review Questions - Page 329
Answers - Page 330
Part Seven: Excel Financial Tools - Page 331
Part Seven: Excel Financial Tools - Page 332
Chapter 29: NPV and IRR—Evaluating Capital Investments - Page 333
Chapter 29: NPV and IRR—Evaluating Capital Investments - Page 334
IRR—Internal Rate of Return - Page 335
An Example Using the IRR function - Page 336
Review Questions - Page 337
Review Questions - Page 338
Chapter 30: Unconventional Financial Functions: XNPV and XIRR - Page 339
Excel XIRR—The Internal Rate of Return Function for Uneven Intervals - Page 340
Excel XIRR—The Internal Rate of Return Function for Uneven Intervals - Page 341
Review Questions - Page 342
Chapter 31: Frequently Used Financial Functions - Page 343
Similar Excel Functions - Page 344
FV—Future Value - Page 345
Rate - Page 346
Review Questions - Page 347
Review Questions - Page 348
Chapter 32: Amortization Tables - Page 349
The Amortization Table - Page 350
The Amortization Table - Page 351
The Amortization Table - Page 352
Principal—PPMT Function - Page 353
Interest—IPMT Function - Page 354
Interest—IPMT Function - Page 355
CUMIPMT and CUMPRINC - Page 356
CUMIPMT and CUMPRINC - Page 357
Review Questions - Page 358
Review Questions - Page 359
Review Questions - Page 360
Chapter 33: Accounting Depreciation Functions - Page 361
Chapter 33: Accounting Depreciation Functions - Page 362
Review Questions - Page 363
Review Questions - Page 364
Part Eight: Using the Solver Add-In - Page 365
Part Eight: Using the Solver Add-In - Page 366
Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver - Page 367
Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver - Page 368
Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver - Page 369
Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver - Page 370
Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver - Page 371
Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver - Page 372
Appendix: Using the Solver in Excel 2003 and Mac Excel 2011 - Page 373
Review Questions - Page 374
Review Questions - Page 375
Answers - Page 376
Chapter 35: The Solver Add-In—Optimizer - Page 377
Chapter 35: The Solver Add-In—Optimizer - Page 378
Nonlinear Example Using the Solver - Page 379
Nonlinear Example Using the Solver - Page 380
Nonlinear Example Using the Solver - Page 381
Nonlinear Example Using the Solver - Page 382
Nonlinear Example Using the Solver - Page 383
Appendix: Using the Solver in Excel 2003 and Mac 2011 - Page 384
Appendix: Using the Solver in Excel 2003 and Mac 2011 - Page 385