John Walkenbach's Favorite Excel 2010 Tips and TricksISBN: 9780470475379
504 pages
July 2010

Known as "Mr. Spreadsheet," John Walkenbach's name is synonymous with excellence in computer books that explain the complexities of various topics. With this collection of favorite Excel tips and tricks, you get a unique look at ways to make Excel 2010 more efficient than you ever imagined.
Packed with easytounderstand advice regarding all aspects of Excel, this book shares improved ways of speeding up application development with Excel and maximizing the power of Excel to create robust applications.
 Addresses the extensive changes to the 2010 version of Excel and shares tricks and shortcuts for making your Excel experience as successful and efficient as possible
 Reveals ways to deal with function arguments, create "impossible" charts, and tame the Ribbon bar
 Discusses absolute vs. relative references, change data entry orientation, and sort more than three columns
 Demonstrates ways to enter fake data for testing purposes
With John Walkenbach's Favorite Excel 2010 Tips and Tricks, you'll get a jump start on mastering the extensive changes to the 2010 version of Excel.
Introduction 1
Part I: Basic Excel Usage
Tip 1: Understanding Excel Versions 9
Tip 2: Maximizing Ribbon Efficiency 12
Tip 3: Understanding Protected View 15
Tip 4: Selecting Cells Efficiently 17
Tip 5: Making “Special” Range Selections 20
Tip 6: Undoing, Redoing, and Repeating 22
Tip 7: Discovering Some Useful Shortcut Keys 25
Tip 8: Navigating Sheets in a Workbook 26
Tip 9: Resetting the Used Area of a Worksheet 27
Tip 10: Understanding Workbooks versus Windows 28
Tip 11: Customizing the Quick Access Toolbar 30
Tip 12: Customizing the Ribbon 34
Tip 13: Accessing the Ribbon with Your Keyboard 37
Tip 14: Recovering Your Work 38
Tip 15: Customizing the Default Workbook 40
Tip 16: Using Document Themes 42
Tip 17: Hiding User Interface Elements 47
Tip 18: Hiding Columns or Rows 50
Tip 19: Hiding Cell Contents 52
Tip 20: Taking Pictures of Ranges 53
Tip 21: Performing Inexact Searches 55
Tip 22: Replacing Formatting 57
Tip 23: Changing the Excel Color Scheme 60
Tip 24: Limiting the Usable Area in a Worksheet 61
Tip 25: Using an Alternative to Cell Comments 64
Tip 26: Understanding the Excel Help System 65
Tip 27: Making a Worksheet “Very Hidden” 68
Tip 28: Working with the Backstage View 70
Part II: Data Entry
Tip 29: Understanding the Types of Data 73
Tip 30: Moving the Cell Pointer after Entering Data 77
Tip 31: Selecting a Range of Input Cells before Entering Data 78
Tip 32: Using AutoComplete to Automate Data Entry 79
Tip 33: Removing Duplicate Rows 81
Tip 34: Keeping Titles in View 83
Tip 35: Automatically Filling a Range with a Series 84
Tip 36: Working with Fractions 87
Tip 37: Resizing the Formula Bar 89
Tip 38: Proofing Your Data with Audio 91
Tip 39: Controlling Automatic Hyperlinks 93
Tip 40: Entering Credit Card Numbers 95
Tip 41: Using the Excel BuiltIn Data Entry Form 96
Tip 42: Customizing and Sharing AutoCorrect Entries 98
Tip 43: Restricting Cursor Movement to Input Cells 100
Tip 44: Controlling the Office Clipboard 102
Tip 45: Creating a DropDown List in a Cell 104
Part III: Formatting
Tip 46: Using the Mini Toolbar 109
Tip 47: Indenting Cell Contents 110
Tip 48: Quick Number Formatting 112
Tip 49: Creating Custom Number Formats 113
Tip 50: Using Custom Number Formats to Scale Values 117
Tip 51: Using Custom Date and Time Formatting 119
Tip 52: Examining Some Useful Custom Number Formats 120
Tip 53: Updating Old Fonts 123
Tip 54: Understanding Conditional Formatting Visualization 125
Tip 55: Showing Text and a Value in a Cell 128
Tip 56: Merging Cells 130
Tip 57: Formatting Individual Characters in a Cell 131
Tip 58: Displaying Times That Exceed 24 Hours 132
Tip 59: Fixing NonNumeric Numbers 134
Tip 60: Adding a Frame to a Range 135
Tip 61: Dealing with Gridlines, Borders, and Underlines 136
Tip 62: Inserting a Watermark 138
Tip 63: Adding a Background Image to a Worksheet 140
Tip 64: Wrapping Text in a Cell 141
Tip 65: Seeing All Characters in a Font 143
Tip 66: Entering Special Characters 145
Tip 67: Using Named Styles 147
Part IV: Basic Formulas and Functions
Tip 68: Using Formula AutoComplete 153
Tip 69: Knowing When to Use Absolute References 155
Tip 70: Knowing When to Use Mixed References 157
Tip 71: Changing the Type of a Cell Reference 159
Tip 72: Converting a Vertical Range to a Table 160
Tip 73: AutoSum Tricks 162
Tip 74: Using the Status Bar Selection Statistics Feature 164
Tip 75: Converting Formulas to Values 166
Tip 76: Transforming Data without Using Formulas 167
Tip 77: Transforming Data by Using Temporary Formulas 168
Tip 78: Deleting Values While Keeping Formulas 170
Tip 79: Summing Across Sheets 171
Tip 80: Dealing with Function Arguments 173
Tip 81: Annotating a Formula without Using a Comment 175
Tip 82: Making an Exact Copy of a Range of Formulas 176
Tip 83: Monitoring Formula Cells from Any Location 178
Tip 84: Displaying and Printing Formulas 179
Tip 85: Avoiding Error Displays in Formulas 181
Tip 86: Using Goal Seeking 183
Tip 87: Understanding the Secret about Names 185
Tip 88: Using Named Constants 187
Tip 89: Using Functions in Names 189
Tip 90: Creating a List of Names 191
Tip 91: Using Dynamic Names 193
Tip 92: Creating WorksheetLevel Names 196
Tip 93: Working with Pre1900 Dates 198
Tip 94: Working with Negative Time Values 200
Part V: Useful Formula Examples
Tip 95: Calculating Holidays 205
Tip 96: Calculating a Weighted Average 208
Tip 97: Calculating a Person’s Age 209
Tip 98: Ranking Values 211
Tip 99: Converting Inches to Feet and Inches 213
Tip 100: Using the DATEDIF Function 214
Tip 101: Counting Characters in a Cell 216
Tip 102: Numbering Weeks 218
Tip 103: Using a Pivot Table Instead of Formulas 220
Tip 104: Expressing a Number as an Ordinal 224
Tip 105: Extracting Words from a String 226
Tip 106: Parsing Names 228
Tip 107: Removing Titles from Names 230
Tip 108: Generating a Series of Dates 231
Tip 109: Determining Specific Dates 233
Tip 110: Displaying a Calendar in a Range 236
Tip 111: Various Methods of Rounding Numbers 237
Tip 112: Rounding Time Values 240
Tip 113: Using the New AGGREGATE Function 241
Tip 114: Returning the Last Nonblank Cell in a Column or Row 244
Tip 115: Using the COUNTIF Function 246
Tip 116: Counting Cells That Meet Multiple Criteria 247
Tip 117: Counting Nonduplicated Entries in a Range 251
Tip 118: Calculating SingleCriterion Conditional Sums 252
Tip 119: Calculating MultipleCriterion Conditional Sums 254
Tip 120: Looking Up an Exact Value 256
Tip 121: Performing a TwoWay Lookup 258
Tip 122: Performing a TwoColumn Lookup 260
Tip 123: Performing a Lookup by Using an Array 262
Tip 124: Using the INDIRECT Function 264
Tip 125: Creating Megaformulas 267
Part VI: Conversions and Mathematical Calculations
Tip 126: Converting Between Measurement Systems 273
Tip 127: Converting Temperatures 275
Tip 128: Solving Simultaneous Equations 276
Tip 129: Solving Recursive Equations 278
Tip 130: Generating Random Numbers 280
Tip 131: Calculating Roots 282
Tip 132: Calculating a Remainder 283
Part VII: Charts and Graphics
Tip 133: Creating a Text Chart Directly in a Range 287
Tip 134: Selecting Elements in a Chart 290
Tip 135: Creating a SelfExpanding Chart 293
Tip 136: Creating Combination Charts 294
Tip 137: Creating a Gantt Chart 297
Tip 138: Creating a Gauge Chart 299
Tip 139: Using Pictures in Charts 301
Tip 140: Plotting Mathematical Functions 303
Tip 141: Using HighLow Lines in a Chart 306
Tip 142: Linking Chart Text to Cells 307
Tip 143: Creating a Chart Template 308
Tip 144: Saving a Chart as a Graphics File 309
Tip 145: Saving a Range as a Graphic Image 311
Tip 146: Making Charts the Same Size 312
Tip 147: Resetting All Chart Formatting 314
Tip 148: Freezing a Chart 316
Tip 149: Creating Picture Effects with a Chart 318
Tip 150: Creating Sparkline Graphics 319
Tip 151: Selecting Objects on a Worksheet 321
Tip 152: Making a Greeting Card 323
Tip 153: Enhancing Text Formatting in Shapes 325
Tip 154: Using Images as Line Chart Markers 327
Tip 155: Changing the Shape of a Cell Comment 329
Tip 156: Adding an Image to a Cell Comment 330
Tip 157: Enhancing Images 331
Part VIII: Data Analysis and Lists
Tip 158: Using the Table Feature 335
Tip 159: Working with Tables 338
Tip 160: Using Formulas with a Table 341
Tip 161: Numbering Rows in a Table 345
Tip 162: Using Custom Views with Filtering 347
Tip 163: Putting Advanced Filter Results on a Different Sheet 349
Tip 164: Comparing Two Ranges by Using Conditional Formatting 350
Tip 165: Randomizing a List 353
Tip 166: Filling the Gaps in a Report 355
Tip 167: Creating a List from a Summary Table 357
Tip 168: Finding Duplicates by Using Conditional Formatting 360
Tip 169: Creating a Quick Frequency Tabulation 362
Tip 170: Controlling References to Cells within a Pivot Table 365
Tip 171: Grouping Items by Date in a Pivot Table 366
Tip 172: Unlinking a Pivot Table from Its Source 369
Tip 173: Using Pivot Table Slicers 371
Part IX: Working with Files
Tip 174: Understanding the New Excel File Formats 377
Tip 175: Importing a Text File into a Worksheet Range 379
Tip 176: Getting Data from a Web Page 381
Tip 177: Displaying a Workbook’s Full Path 385
Tip 178: Using Document Properties 387
Tip 179: Inspecting a Workbook 389
Tip 180: Finding the Missing No to All Button When Closing Files 391
Tip 181: Getting a List of Filenames 392
Tip 182: Using Workspace Files 394
Part X: Printing
Tip 183: Controlling What Gets Printed 397
Tip 184: Displaying Repeated Rows or Columns on a Printout 399
Tip 185: Printing Noncontiguous Ranges on a Single Page 400
Tip 186: Preventing Objects from Printing 403
Tip 187: PageNumbering Tips 404
Tip 188: Adding and Removing Page Breaks 406
Tip 189: Saving to a PDF File 407
Tip 190: Making Your Printout Fit on One Page 408
Tip 191: Printing the Contents of a Cell in a Header or Footer 410
Tip 192: Copying Page Setup Settings Across Sheets 412
Tip 193: Printing Cell Comments 413
Tip 194: Printing a Giant Banner 414
Part XI: Spotting, Fixing, and Preventing Errors
Tip 195: Using the Excel ErrorChecking Features 419
Tip 196: Identifying Formula Cells 421
Tip 197: Dealing with FloatingPoint Number Problems 424
Tip 198: Removing Excess Spaces 426
Tip 199: Viewing Names Graphically 428
Tip 200: Locating Phantom Links 429
Tip 201: Understanding Displayed versus Actual Values 430
Tip 202: Tracing Cell Relationships 431
Part XII: Basic VBA and Macros
Tip 203: Learning about Macros and VBA 435
Tip 204: Recording a Macro 437
Tip 205: Executing Macros 439
Tip 206: Understanding Functions Versus Subs 442
Tip 207: Creating Simple Worksheet Functions 444
Tip 208: Describing Function Arguments 447
Tip 209: Making Excel Talk 449
Tip 210: Understanding Custom Function Limitations 450
Tip 211: Executing a Ribbon Command with a Macro 451
Tip 212: Understanding Security Issues Related to Macros 453
Tip 213: Using a Personal Macro Workbook 455
Index 457