John Walkenbach's Favorite Excel 2010 Tips and Tricks

Part I: Basic Excel Usage.

Tip 1: Understanding Excel Versions.

Tip 2: Maximizing Ribbon Efficiency.

Tip 3: Understanding Protected View.

Tip 4: Selecting Cells Efficiently.

Tip 5: Making "Special" Range Selections.

Tip 6: Undoing, Redoing, and Repeating.

Tip 7: Discovering Some Useful Shortcut Keys.

Tip 8: Navigating Sheets in a Workbook.

Tip 9: Resetting the Used Area of a Worksheet.

Tip 10: Understanding Workbooks versus Windows.

Tip 11: Customizing the Quick Access Toolbar.

Tip 12: Customizing the Ribbon.

Tip 13: Accessing the Ribbon with Your Keyboard.

Tip 14: Recovering Your Work.

Tip 15: Customizing the Default Workbook.

Tip 16: Using Document Themes.

Tip 17: Hiding User Interface Elements.

Tip 18: Hiding Columns or Rows.

Tip 19: Hiding Cell Contents.

Tip 20: Taking Pictures of Ranges.

Tip 21: Performing Inexact Searches.

Tip 22: Replacing Formatting.

Tip 23: Changing the Excel Color Scheme.

Tip 24: Limiting the Usable Area in a Worksheet.

Tip 25: Using an Alternative to Cell Comments.

Tip 26: Understanding the Excel Help System.

Tip 27: Making a Worksheet "Very Hidden".

Tip 28: Working with the Backstage View.

Part II: Data Entry.

Tip 29: Understanding the Types of Data.

Tip 30: Moving the Cell Pointer after Entering Data.

Tip 31: Selecting a Range of Input Cells before Entering Data.

Tip 32: Using AutoComplete to Automate Data Entry.

Tip 33: Removing Duplicate Rows.

Tip 34: Keeping Titles in View.

Tip 35: Automatically Filling a Range with a Series.

Tip 36: Working with Fractions.

Tip 37: Resizing the Formula Bar.

Tip 38: Proofing Your Data with Audio.

Tip 39: Controlling Automatic Hyperlinks.

Tip 40: Entering Credit Card Numbers.

Tip 41: Using the Excel Built-In Data Entry Form.

Tip 42: Customizing and Sharing AutoCorrect Entries.

Tip 43: Restricting Cursor Movement to Input Cells.

Tip 44: Controlling the Office Clipboard.

Tip 45: Creating a Drop-Down List in a Cell.

Part III: Formatting.

Tip 46: Using the Mini Toolbar.

Tip 47: Indenting Cell Contents.

Tip 48: Quick Number Formatting.

Tip 49: Creating Custom Number Formats.

Tip 50: Using Custom Number Formats to Scale Values.

Tip 51: Using Custom Date and Time Formatting.

Tip 52: Examining Some Useful Custom Number Formats.

Tip 53: Updating Old Fonts.

Tip 54: Understanding Conditional Formatting Visualization.

Tip 55: Showing Text and a Value in a Cell.

Tip 56: Merging Cells.

Tip 57: Formatting Individual Characters in a Cell.

Tip 58: Displaying Times That Exceed 24 Hours.

Tip 59: Fixing Non-Numeric Numbers.

Tip 60: Adding a Frame to a Range.

Tip 61: Dealing with Gridlines, Borders, and Underlines.

Tip 62: Inserting a Watermark.

Tip 63: Adding a Background Image to a Worksheet.

Tip 64: Wrapping Text in a Cell.

Tip 65: Seeing All Characters in a Font.

Tip 66: Entering Special Characters.

Tip 67: Using Named Styles.

Part IV: Basic Formulas and Functions.

Tip 68: Using Formula AutoComplete.

Tip 69: Knowing When to Use Absolute References.

Tip 70: Knowing When to Use Mixed References.

Tip 71: Changing the Type of a Cell Reference.

Tip 72: Converting a Vertical Range to a Table.

Tip 73: AutoSum Tricks.

Tip 74: Using the Status Bar Selection Statistics Feature.

Tip 75: Converting Formulas to Values.

Tip 76: Transforming Data without Using Formulas.

Tip 77: Transforming Data by Using Temporary Formulas.

Tip 78: Deleting Values While Keeping Formulas.

Tip 79: Summing Across Sheets.

Tip 80: Dealing with Function Arguments.

Tip 81: Annotating a Formula without Using a Comment.

Tip 82: Making an Exact Copy of a Range of Formulas.

Tip 83: Monitoring Formula Cells from Any Location.

Tip 84: Displaying and Printing Formulas.

Tip 85: Avoiding Error Displays in Formulas.

Tip 86: Using Goal Seeking.

Tip 87: Understanding the Secret about Names.

Tip 88: Using Named Constants.

Tip 89: Using Functions in Names.

Tip 90: Creating a List of Names.

Tip 91: Using Dynamic Names.

Tip 92: Creating Worksheet-Level Names.

Tip 93: Working with Pre-1900 Dates.

Tip 94: Working with Negative Time Values.

Part V: Useful Formula Examples.

Tip 95: Calculating Holidays.

Tip 96: Calculating a Weighted Average.

Tip 97: Calculating a Person's Age.

Tip 98: Ranking Values.

Tip 99: Converting Inches to Feet and Inches.

Tip 100: Using the DATEDIF Function.

Tip 101: Counting Characters in a Cell.

Tip 102: Numbering Weeks.

Tip 103: Using a Pivot Table Instead of Formulas.

Tip 104: Expressing a Number as an Ordinal.

Tip 105: Extracting Words from a String.

Tip 106: Parsing Names.

Tip 107: Removing Titles from Names.

Tip 108: Generating a Series of Dates.

Tip 109: Determining Specific Dates.

Tip 110: Displaying a Calendar in a Range.

Tip 111: Various Methods of Rounding Numbers.

Tip 112: Rounding Time Values.

Tip 113: Using the New AGGREGATE Function.

Tip 114: Returning the Last Nonblank Cell in a Column or Row.

Tip 115: Using the COUNTIF Function.

Tip 116: Counting Cells That Meet Multiple Criteria.

Tip 117: Counting Nonduplicated Entries in a Range.

Tip 118: Calculating Single-Criterion Conditional Sums.

Tip 119: Calculating Multiple-Criterion Conditional Sums.

Tip 120: Looking Up an Exact Value.

Tip 121: Performing a Two-Way Lookup.

Tip 122: Performing a Two-Column Lookup.

Tip 123: Performing a Lookup by Using an Array.

Tip 124: Using the INDIRECT Function.

Tip 125: Creating Megaformulas.

Part VI: Conversions and Mathematical Calculations.

Tip 126: Converting Between Measurement Systems.

Tip 127: Converting Temperatures.

Tip 128: Solving Simultaneous Equations.

Tip 129: Solving Recursive Equations.

Tip 130: Generating Random Numbers.

Tip 131: Calculating Roots.

Tip 132: Calculating a Remainder.

Part VII: Charts and Graphics.

Tip 133: Creating a Text Chart Directly in a Range.

Tip 134: Selecting Elements in a Chart.

Tip 135: Creating a Self-Expanding Chart.

Tip 136: Creating Combination Charts.

Tip 137: Creating a Gantt Chart.

Tip 138: Creating a Gauge Chart.

Tip 139: Using Pictures in Charts.

Tip 140: Plotting Mathematical Functions.

Tip 141: Using High-Low Lines in a Chart.

Tip 142: Linking Chart Text to Cells.

Tip 143: Creating a Chart Template.

Tip 144: Saving a Chart as a Graphics File.

Tip 145: Saving a Range as a Graphic Image.

Tip 146: Making Charts the Same Size.

Tip 147: Resetting All Chart Formatting.

Tip 148: Freezing a Chart.

Tip 149: Creating Picture Effects with a Chart.

Tip 150: Creating Sparkline Graphics.

Tip 151: Selecting Objects on a Worksheet.

Tip 152: Making a Greeting Card.

Tip 153: Enhancing Text Formatting in Shapes.

Tip 154: Using Images as Line Chart Markers.

Tip 155: Changing the Shape of a Cell Comment.

Tip 156: Adding an Image to a Cell Comment.

Tip 157: Enhancing Images.

Part VIII: Data Analysis and Lists.

Tip 158: Using the Table Feature.

Tip 159: Working with Tables.

Tip 160: Using Formulas with a Table.

Tip 161: Numbering Rows in a Table.

Tip 162: Using Custom Views with Filtering.

Tip 163: Putting Advanced Filter Results on a Different Sheet.

Tip 164: Comparing Two Ranges by Using Conditional Formatting.

Tip 165: Randomizing a List.

Tip 166: Filling the Gaps in a Report.

Tip 167: Creating a List from a Summary Table.

Tip 168: Finding Duplicates by Using Conditional Formatting.

Tip 169: Creating a Quick Frequency Tabulation.

Tip 170: Controlling References to Cells within a Pivot Table.

Tip 171: Grouping Items by Date in a Pivot Table.

Tip 172: Unlinking a Pivot Table from Its Source.

Tip 173: Using Pivot Table Slicers.

Part IX: Working with Files.

Tip 174: Understanding the New Excel File Formats.

Tip 175: Importing a Text File into a Worksheet Range.

Tip 176: Getting Data from a Web Page.

Tip 177: Displaying a Workbook’s Full Path.

Tip 178: Using Document Properties.

Tip 179: Inspecting a Workbook.

Tip 180: Finding the Missing No to All Button When Closing Files.

Tip 181: Getting a List of Filenames.

Tip 182: Using Workspace Files.

Part X: Printing.

Tip 183: Controlling What Gets Printed.

Tip 184: Displaying Repeated Rows or Columns on a Printout.

Tip 185: Printing Noncontiguous Ranges on a Single Page.

Tip 186: Preventing Objects from Printing.

Tip 187: Page-Numbering Tips.

Tip 188: Adding and Removing Page Breaks.

Tip 189: Saving to a PDF File.

Tip 190: Making Your Printout Fit on One Page.

Tip 191: Printing the Contents of a Cell in a Header or Footer.

Tip 192: Copying Page Setup Settings Across Sheets.

Tip 193: Printing Cell Comments.

Tip 194: Printing a Giant Banner.

Part XI: Spotting, Fixing, and Preventing Errors.

Tip 195: Using the Excel Error-Checking Features.

Tip 196: Identifying Formula Cells.

Tip 197: Dealing with Floating-Point Number Problems.

Tip 198: Removing Excess Spaces.

Tip 199: Viewing Names Graphically.

Tip 200: Locating Phantom Links.

Tip 201: Understanding Displayed versus Actual Values.

Tip 202: Tracing Cell Relationships.

Part XII: Basic VBA and Macros.

Tip 203: Learning about Macros and VBA.

Tip 204: Recording a Macro.

Tip 205: Executing Macros.

Tip 206: Understanding Functions Versus Subs.

Tip 207: Creating Simple Worksheet Functions .

Tip 208: Describing Function Arguments.

Tip 209: Making Excel Talk.

Tip 210: Understanding Custom Function Limitations.

Tip 211: Executing a Ribbon Command with a Macro.

Tip 212: Understanding Security Issues Related to Macros.

Tip 213: Using a Personal Macro Workbook.

Index.