John Walkenbach's favorite Excel 2010 tips & tricks

John Walkenbach’s Favorite Excel® 2010 Tips & Tricks......Page 3
Contents at a Glance......Page 7
Table of Contents......Page 9
What You Should Know......Page 23
VBA code listings......Page 24
Mouse conventions......Page 25
Entering VBA Code......Page 26
How to Use This Book......Page 27
Reach Out......Page 28
Part I: Basic Excel Usage......Page 29
Understanding Excel Versions......Page 31
Maximizing Ribbon Efficiency......Page 34
Understanding Protected View......Page 37
Selecting noncontiguous ranges......Page 39
Selecting entire columns......Page 40
Selecting multisheet ranges......Page 41
Making “Special” Range Selections......Page 42
Undoing......Page 44
Repeating......Page 45
Discovering Some Useful Shortcut Keys......Page 47
Navigating Sheets in a Workbook......Page 48
Resetting the Used Area of a Worksheet......Page 49
Understanding Workbooks versus Windows......Page 50
Adding new commands to the Quick Access toolbar......Page 52
Performing other Quick Access toolbar actions......Page 55
Customizing the Ribbon......Page 56
Accessing the Ribbon with Your Keyboard......Page 59
Recovering unsaved work......Page 60
Creating a book.xltx template......Page 62
Using Document Themes......Page 64
Applying a theme......Page 66
Customizing a theme......Page 67
From the Excel Options dialog box......Page 69
A setting that requires a macro......Page 71
Unhiding......Page 72
Hiding Cell Contents......Page 74
Creating a static image of a range......Page 75
Creating a live image of a range......Page 76
Performing Inexact Searches......Page 77
Replacing Formatting......Page 79
Changing the Excel Color Scheme......Page 82
Setting the ScrollArea property......Page 83
Using worksheet protection......Page 84
Using an Alternative to Cell Comments......Page 86
Understanding the Excel Help System......Page 87
Making a Worksheet “Very Hidden”......Page 90
Working with the Backstage View......Page 92
Part II: Data Entry......Page 93
Tips and Where to Find Them......Page 94
Entering values......Page 95
Entering dates and times......Page 96
Entering text......Page 97
Entering formulas......Page 98
Moving the Cell Pointer after Entering Data......Page 99
Selecting a Range of Input Cells before Entering Data......Page 100
Using AutoComplete to Automate Data Entry......Page 101
Removing Duplicate Rows......Page 103
Keeping Titles in View......Page 105
Automatically Filling a Range with a Series......Page 106
Working with Fractions......Page 109
Resizing the Formula Bar......Page 111
Using the speech commands......Page 113
Turning off automatic hyperlinks......Page 115
Removing existing hyperlinks......Page 116
Entering Credit Card Numbers......Page 117
Using the Excel Built-In Data Entry Form......Page 118
Customizing and Sharing AutoCorrect Entries......Page 120
Restricting Cursor Movement to Input Cells......Page 122
Controlling the Office Clipboard......Page 124
Creating a Drop-Down List in a Cell......Page 126
Part III: Formatting......Page 129
Tips and Where to Find Them......Page 130
Using the Mini Toolbar......Page 131
Indenting Cell Contents......Page 132
Quick Number Formatting......Page 134
Creating Custom Number Formats......Page 135
Parts of a number format string......Page 136
Custom number format codes......Page 137
Using Custom Number Formats to Scale Values......Page 139
Using Custom Date and Time Formatting......Page 141
Formatting percentages......Page 142
Repeating text......Page 143
Suppressing certain types of entries......Page 144
Updating Old Fonts......Page 145
Data bars......Page 147
Icon sets......Page 148
Using the TEXT function......Page 150
Using a custom number format......Page 151
Merging Cells......Page 152
Formatting Individual Characters in a Cell......Page 153
Displaying Times That Exceed 24 Hours......Page 154
Fixing Non-Numeric Numbers......Page 156
Adding a Frame to a Range......Page 157
Dealing with Gridlines, Borders, and Underlines......Page 158
Inserting a Watermark......Page 160
Adding a Background Image to a Worksheet......Page 162
Wrapping Text in a Cell......Page 163
Seeing All Characters in a Font......Page 165
Entering Special Characters......Page 167
Using the Style Gallery......Page 169
Modifying an existing style......Page 170
Creating new styles......Page 171
Merging styles from other workbooks......Page 172
Part IV: Basic Formulas and Functions......Page 173
Tips and Where to Find Them......Page 174
Using Formula AutoComplete......Page 175
Knowing When to Use Absolute References......Page 177
Knowing When to Use Mixed References......Page 179
Changing the Type of a Cell Reference......Page 181
Converting a Vertical Range to a Table......Page 182
AutoSum Tricks......Page 184
Using the Status Bar Selection Statistics Feature......Page 186
Converting Formulas to Values......Page 188
Transforming Data without Using Formulas......Page 189
Transforming Data by Using Temporary Formulas......Page 190
Deleting Values While Keeping Formulas......Page 192
Summing Across Sheets......Page 193
Dealing with Function Arguments......Page 195
Annotating a Formula without Using a Comment......Page 197
Making an Exact Copy of a Range of Formulas......Page 198
Monitoring Formula Cells from Any Location......Page 200
Displaying and Printing Formulas......Page 201
Using the ISERROR function......Page 203
Using the IFERROR function......Page 204
Using Goal Seeking......Page 205
Understanding the Secret about Names......Page 207
Using Named Constants......Page 209
Using Functions in Names......Page 211
Creating a List of Names......Page 213
Using Dynamic Names......Page 215
Creating Worksheet-Level Names......Page 218
Working with Pre-1900 Dates......Page 220
Working with Negative Time Values......Page 222
Part V: Useful Formula Examples......Page 225
Tips and Where to Find Them......Page 226
Presidents’ Day......Page 227
Labor Day......Page 228
Christmas Day......Page 229
Calculating a Weighted Average......Page 230
Method 3......Page 231
Ranking Values......Page 233
Converting Inches to Feet and Inches......Page 235
Using the DATEDIF Function......Page 236
Counting the occurrences of a substring in a cell......Page 238
Numbering Weeks......Page 240
Inserting subtotals......Page 242
Using a pivot table......Page 244
Expressing a Number as an Ordinal......Page 246
Extracting the last word of a string......Page 248
Extracting all except the first word of a string......Page 249
Parsing Names......Page 250
Removing Titles from Names......Page 252
Using formulas......Page 253
Determining the day of the week......Page 255
Determining the nth occurrence of a day of the week in a month......Page 256
Determining a date’s quarter......Page 257
Displaying a Calendar in a Range......Page 258
Rounding to the nearest multiple......Page 259
Using the INT and TRUNC functions......Page 260
Rounding to n significant digits......Page 261
Rounding Time Values......Page 262
Using the New AGGREGATE Function......Page 263
Returning the Last Nonblank Cell in a Column or Row......Page 266
Using the COUNTIF Function......Page 268
Using “And” criteria......Page 269
Using “Or” criteria......Page 271
Combining “And” and “Or” criteria......Page 272
Counting Nonduplicated Entries in a Range......Page 273
Summing only negative values......Page 274
Summing values based on a date comparison......Page 275
Using “And” criteria......Page 276
Using “And” and “Or” criteria......Page 277
Looking Up an Exact Value......Page 278
Using a formula......Page 280
Using implicit intersection......Page 281
Performing a Two-Column Lookup......Page 282
Using an array......Page 284
Specifying rows indirectly......Page 286
Making a cell reference unchangeable......Page 287
Creating Megaformulas......Page 289
Part VI: Conversions and Mathematical Calculations......Page 293
Tips and Where to Find Them......Page 294
Converting Between Measurement Systems......Page 295
Converting Temperatures......Page 297
Solving Simultaneous Equations......Page 298
Solving Recursive Equations......Page 300
Using the RANDBETWEEN function......Page 302
Using the Analysis Toolpak add-in......Page 303
Calculating Roots......Page 304
Calculating a Remainder......Page 305
Part VII: Charts and Graphics......Page 307
Tips and Where to Find Them......Page 308
Creating a Text Chart Directly in a Range......Page 309
Selecting with the mouse......Page 312
Selecting with the Chart Element control......Page 313
Creating a Self-Expanding Chart......Page 315
Creating Combination Charts......Page 316
Creating a Gantt Chart......Page 319
Creating a Gauge Chart......Page 321
Using Pictures in Charts......Page 323
Plotting single-variable mathematical functions......Page 325
Plotting two-variable mathematical functions......Page 326
Using High-Low Lines in a Chart......Page 328
Linking Chart Text to Cells......Page 329
Creating a Chart Template......Page 330
Method 2: Save as an HTML file......Page 331
Method 3: Use a VBA macro......Page 332
Saving a Range as a Graphic Image......Page 333
Making Charts the Same Size......Page 334
Resetting All Chart Formatting......Page 336
Converting range references into arrays......Page 338
Creating Picture Effects with a Chart......Page 340
Creating Sparkline Graphics......Page 341
The Go to Special dialog box......Page 343
The Select Object tool......Page 344
Making a Greeting Card......Page 345
Enhancing Text Formatting in Shapes......Page 347
Using Images as Line Chart Markers......Page 349
Changing the Shape of a Cell Comment......Page 351
Adding an Image to a Cell Comment......Page 352
Enhancing Images......Page 353
Part VIII: Data Analysis and Lists......Page 355
Tips and Where to Find Them......Page 356
Range versus table......Page 357
Creating a table......Page 358
Adding new rows or columns......Page 360
Sorting and filtering a table......Page 361
Working with the total row......Page 363
Using formulas within a table......Page 364
Referencing data in a table......Page 366
Numbering Rows in a Table......Page 367
Using Custom Views with Filtering......Page 369
Putting Advanced Filter Results on a Different Sheet......Page 371
Comparing Two Ranges by Using Conditional Formatting......Page 372
Randomizing a List......Page 375
Filling the Gaps in a Report......Page 377
Creating a List from a Summary Table......Page 379
Finding Duplicates by Using Conditional Formatting......Page 382
Creating a Quick Frequency Tabulation......Page 384
Controlling References to Cells within a Pivot Table......Page 387
Grouping Items by Date in a Pivot Table......Page 388
Unlinking a Pivot Table from Its Source......Page 391
Using Pivot Table Slicers......Page 393
Part IX: Working with Files......Page 397
Tips and Where to Find Them......Page 398
The Office compatibility pack......Page 399
Saving a file for use with an older version of Excel......Page 400
Importing a Text File into a Worksheet Range......Page 401
Pasting refreshable information......Page 403
Opening the Web page directly......Page 405
Use a formula......Page 407
Use a macro......Page 408
Using Document Properties......Page 409
Inspecting a Workbook......Page 411
Finding the Missing No to All Button When Closing Files......Page 413
Getting a List of Filenames......Page 414
Using Workspace Files......Page 416
Part X: Printing......Page 417
Tips and Where to Find Them......Page 418
Adjusting common page setup settings......Page 419
Displaying Repeated Rows or Columns on a Printout......Page 421
Breaking out the Camera tool......Page 422
Shooting with the Camera......Page 423
Preventing Objects from Printing......Page 425
Changing the starting page number......Page 426
Removing page breaks you’ve added......Page 428
Saving to a PDF File......Page 429
Making Your Printout Fit on One Page......Page 430
Printing the Contents of a Cell in a Header or Footer......Page 432
Copying Page Setup Settings Across Sheets......Page 434
Printing Cell Comments......Page 435
Printing a Giant Banner......Page 436
Part XI: Spotting, Fixing, and Preventing Errors......Page 439
Tips and Where to Find Them......Page 440
Using the Excel Error-Checking Features......Page 441
Using conditional formatting......Page 443
Dealing with Floating-Point Number Problems......Page 446
Removing Excess Spaces......Page 448
Viewing Names Graphically......Page 450
Locating Phantom Links......Page 451
Understanding Displayed versus Actual Values......Page 452
Identifying precedents......Page 453
Identifying dependents......Page 454
Part XII: Basic VBA and Macros......Page 455
Tips and Where to Find Them......Page 456
What can a macro do?......Page 457
Examining the macro......Page 459
Testing the macro......Page 460
Assign the macro to a button......Page 461
Add a button to the Ribbon......Page 462
VBA functions......Page 464
Determine whether a cell contains a formula......Page 466
Extracting the nth element from a string......Page 467
Describing Function Arguments......Page 469
Making Excel Talk......Page 471
Understanding Custom Function Limitations......Page 472
Executing a Ribbon Command with a Macro......Page 473
Understanding Security Issues Related to Macros......Page 475
Using a Personal Macro Workbook......Page 477
Index......Page 479