John Walkenbach's Favorite Excel 2010 Tips and Tricks [NOOK Book]


Build robust Excel 2010 apps quickly and efficiently

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 easy-to-understand advice regarding all aspects of Excel, this book shares improved ways of...

See more details below
John Walkenbach's Favorite Excel 2010 Tips and Tricks

Available on NOOK devices and apps  
  • NOOK Devices
  • Samsung Galaxy Tab 4 NOOK 7.0
  • Samsung Galaxy Tab 4 NOOK 10.1
  • NOOK HD Tablet
  • NOOK HD+ Tablet
  • NOOK eReaders
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK for Web

Want a NOOK? Explore Now

NOOK Book (eBook)
$22.99 price
(Save 42%)$39.99 List Price
Note: This NOOK Book can be purchased in bulk. Please email us for more information.


Build robust Excel 2010 apps quickly and efficiently

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 easy-to-understand 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.

Read More Show Less
  • John Walkenbach's Favorite Excel...
    John Walkenbach's Favorite Excel...  

Product Details

  • ISBN-13: 9780470877791
  • Publisher: Wiley
  • Publication date: 6/17/2010
  • Series: Mr. Spreadsheet's Bookshelf, #8
  • Sold by: Barnes & Noble
  • Format: eBook
  • Edition number: 1
  • Pages: 528
  • Sales rank: 915,881
  • File size: 11 MB
  • Note: This product may take a few minutes to download.

Meet the Author

John Walkenbach, arguably the foremost authority on Excel, has written hundreds of articles and created the award-winning Power Utility Pak. His 50-plus books include Excel 2010 Power Programming with VBA, Excel 2010 Formulas, and the Excel 2010 Bible, all published by Wiley. Visit his popular Spreadsheet Page at
Read More Show Less

Table of Contents

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 Built-In 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 Drop-Down 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 Non-Numeric 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 Worksheet-Level Names 196

Tip 93: Working with Pre-1900 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 Single-Criterion Conditional Sums 252

Tip 119: Calculating Multiple-Criterion Conditional Sums 254

Tip 120: Looking Up an Exact Value 256

Tip 121: Performing a Two-Way Lookup 258

Tip 122: Performing a Two-Column 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 Self-Expanding 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 High-Low 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: Page-Numbering 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 Error-Checking Features 419

Tip 196: Identifying Formula Cells 421

Tip 197: Dealing with Floating-Point 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

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)