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.
Reihe
Auflage
Sprache
Verlagsort
Verlagsgruppe
Zielgruppe
Illustrationen
Maße
Höhe: 23.3 cm
Breite: 18.9 cm
Dicke: 2.5 cm
Gewicht
ISBN-13
978-0-470-47537-9 (9780470475379)
Schweitzer Klassifikation
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 spreadsheetpage.com.
Autor*in
J-Walk and Associates, Inc.
Introduction.
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.
