John Walkenbach's Favorite Excel 2010 Tips and Tricks
John Wiley & Sons Ltd (Verlag)
978-0-470-47537-9 (ISBN)
- Titel ist leider vergriffen;
keine Neuauflage - Artikel merken
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.
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.
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
| Erscheint lt. Verlag | 9.7.2010 |
|---|---|
| Reihe/Serie | Mr. Spreadsheet's Bookshelf |
| Zusatzinfo | ill |
| Verlagsort | Chichester |
| Sprache | englisch |
| Maße | 185 x 230 mm |
| Gewicht | 744 g |
| Themenwelt | Informatik ► Office Programme ► Excel |
| ISBN-10 | 0-470-47537-4 / 0470475374 |
| ISBN-13 | 978-0-470-47537-9 / 9780470475379 |
| Zustand | Neuware |
| Haben Sie eine Frage zum Produkt? |
aus dem Bereich
