Joseph Rubin9780974636832, 0974636835
Table of contents :
Team DDU……Page 1
Table of Contents……Page 14
Part 1 – What’s New in Excel 2002 & 2003?……Page 31
Tip 1: What’s New in Excel 2003?……Page 32
Tip 2: What’s New in Excel 2002/2003: Menu, Cell Shortcut Menu and Keyboard Shortcuts……Page 35
Tip 3: Let Excel Read Text for You……Page 39
Part 2 – Working Inside……Page 40
Tip 4: Moving to the Last (or First) Cell in a Range……Page 41
Tip 5: Selecting Cells in Horizontal or Vertical Ranges……Page 42
Tip 6: Quickly Selecting a Range of Cells……Page 43
Tip 7: Selecting a Range of Non-Adjacent Cells……Page 44
Tip 8: Selecting the Current Region/List……Page 45
Tip 9: Moving the Cell Pointer Around a Selected Range……Page 46
Tip 10: Selecting Special Cells……Page 47
Tip 11: Selecting a Column(s) or Row(s) Using Keyboard Shortcuts……Page 48
Tip 12: Selecting Cells from Any Cell to the Last Cell in the Sheet’s Used Range……Page 49
Tip 13: Moving Between Unprotected Cells in a Protected Sheet……Page 50
Tip 14: Selecting All Sheet Cells……Page 51
Tip 15: Quickly Selecting a Range in the Workbook……Page 52
Tip 16: Selecting a Sheet in the Workbook……Page 53
Tip 17: Selecting a Sheet from a Sorted Sheets List……Page 54
Tip 18: Moving Between Open Workbooks……Page 55
Tip 19: Adding a Custom Keyboard Shortcut……Page 56
Tip 20: Copying, Cutting and Pasting (Moving)……Page 59
Tip 21: Fast Copying & Pasting in Adjacent Cells……Page 60
Tip 22: Fast Copying & Pasting Right or Down in a List……Page 61
Tip 23: Copying & Pasting Cell Content to Thousands of Cells……Page 62
Tip 24: Pasting Multiple Copied Ranges Using the Clipboard……Page 63
Tip 25: Inserting a Copied Cell(s) Without Overwriting Existing Data……Page 64
Tip 26: Expanded Paste Icon Features in Excel 2002 & Excel 2003……Page 65
Tip 27: Copying & Pasting Cell Content Along with Formatting……Page 66
Tip 28: Copying Cell Content Across Sheets……Page 67
Tip 29: Copying/Moving a Cell(s) Between Sheets/Workbooks……Page 68
Tip 30: Preventing Moving to the Next Cell After Typing……Page 69
Tip 31: Quickly Entering Text into Cells from a List of Previous Entries or a Validation List……Page 70
Tip 32: Preventing Duplicates While Entering Data……Page 71
Tip 33: Validating Number Entries……Page 72
Tip 34: Validating Text Entries……Page 73
Tip 35: Quickly Entering a Long Company or Person Name……Page 74
Tip 36: Transposing Cells……Page 75
Tip 37: Finding/Finding & Replacing in a Single Sheet or in All Sheets……Page 76
Tip 38: Displaying the Format Cells Dialog Box……Page 78
Tip 39: Copying Formatting……Page 79
Tip 40: Changing the Standard Font……Page 80
Tip 41: Hiding the Display of Zero Values……Page 81
Tip 42: Merging and Centering with a Toggle Button in Excel 2002/2003……Page 82
Tip 43: Changing the Indentation in a Cell……Page 83
Tip 44: Automatically Wrapping Text in a Cell……Page 84
Tip 45: Manually Wrapping Text in a Cell……Page 85
Tip 46: Adding a Shortcut for Wrapping Text……Page 86
Tip 47: Preventing Text from Spilling Over……Page 87
Tip 48: Dividing a First Left Heading Title……Page 88
Tip 49: Changing the Cell Formatting from Date to Number……Page 89
Tip 50: Coloring Numbers Based on Specific Criteria Using Conditional Formatting……Page 90
Tip 51: Changing a Numeric Heading to Text……Page 91
Tip 52: “Tearing Off” Palettes from Toolbars……Page 92
Tip 53: Coloring Numbers Based on Specific Criteria……Page 93
Tip 54: Coloring One Record in Each Group in a Range……Page 94
Tip 55: Coloring Even Numbered Rows in a List……Page 95
Tip 56: Inserting Special Symbols into Custom Formats……Page 96
Tip 57: Automating the Insertion of the Euro Symbol……Page 98
Tip 58: Symbols Used to Create Custom Formatting……Page 99
Tip 59: Formatting a Negative Number with Parentheses……Page 101
Tip 60: Replacing Zeros with Dashes in Number Formatting……Page 102
Tip 61: Rounding Numbers to Thousands……Page 103
Tip 62: Using the Round Function to Round Numbers to Thousands……Page 104
Tip 63: Rounding Numbers to Thousands with Hundreds as a Decimal……Page 105
Tip 64: Rounding Numbers to Millions……Page 106
Tip 65: Rounding Numbers to Millions with Thousands as a Decimal……Page 107
Tip 66: Changing the Default Style……Page 108
Tip 67: Creating and Saving Custom Styles……Page 109
Tip 68: Copying Styles from One Workbook to Another……Page 110
Tip 69: Displaying Figures in Financial Reports Rounded to Thousands……Page 111
Part 3 – Excel Environment……Page 112
Tip 70: Cell Function Returns Sheet Name, Workbook Name and Path……Page 113
Tip 71: Checking Cell Information at a Distance……Page 114
Tip 72: Adding Data, Charts, or Other Information to a Cell Comment……Page 115
Tip 73: Using a Picture to Watch Cell Values in Other Sheets……Page 116
Tip 74: Using the Validation Input Message as a Cell Comment……Page 117
Tip 75: Linking a Cell to a Text Box……Page 118
Tip 76: Removing the Smart Tag Indicator……Page 119
Tip 77: Changing the Name of the Cell Comment’s Author……Page 120
Tip 78: Changing the Default Cell Comment Format……Page 121
Tip 79: Changing the Cell Comment Shape……Page 122
Tip 80: Copying a Cell Comment……Page 123
Tip 81: Deleting All Cell Comments in a Sheet……Page 124
Tip 82: Printing Cell Comments……Page 125
Tip 83: Viewing All Data in a Sheet……Page 126
Tip 84: Increasing/Decreasing the Screen Magnification Percentage……Page 128
Tip 85: Making a Cell the First Cell in the Active Window……Page 129
Tip 86: Hiding/Unhiding a Row(s) or Column(s)……Page 131
Tip 87: Protecting Data by Hiding a Row(s) and Column(s)……Page 132
Tip 88: Allowing Multiple Users to Edit Specified Ranges……Page 134
Tip 89: Coloring Gridlines……Page 135
Tip 90: Hiding Gridlines on the Screen and When Printing……Page 136
Tip 91: Reducing the Used Area in a Sheet……Page 137
Tip 92: Using Task Panes……Page 138
Tip 93: Showing All Items When Choosing from a Menu……Page 139
Tip 94: Customizing an Icon……Page 140
Tip 95: Icons Disappearing from the Toolbar……Page 141
Tip 96: Removing Icons Performing Two Different Tasks……Page 142
Tip 97: Using the Empty Space in the Menu Bar by Adding Useful Icons……Page 143
Tip 98: Adding and Saving a New Customized Toolbar……Page 144
Tip 99: Backing Up Customized Changes to Toolbars and Menus……Page 146
Tip 100: Changing the Default Number of Sheets in a New Workbook……Page 147
Tip 101: Changing the Font Size of the Sheet Name……Page 148
Tip 102: Coloring Sheet Tabs……Page 149
Tip 103: Copying or Moving a Sheet……Page 150
Tip 104: Sorting Sheets in Ascending Order……Page 152
Tip 105: Grouping/Ungrouping Sheets……Page 153
Tip 106: Inserting a New Sheet from a Template Sheet……Page 154
Tip 107: Protecting the Sheet or the Cells in the Sheet……Page 155
Tip 108: Adding the Properties Icon to the Standard Toolbar……Page 156
Tip 109: Limiting the Movement in a Protected Sheet……Page 157
Tip 110: Limiting the Movement in an Unprotected Sheet……Page 158
Tip 111: Preventing Hidden Sheets from Appearing……Page 159
Tip 112: Opening/Closing a Workbook(s)……Page 161
Tip 113: Changing the Number of Files Shown in the Recently Used File List……Page 162
Tip 114: Changing the Default Auto-Save File Location……Page 163
Tip 115: Auto-Recovering/Auto-Saving a Workbook……Page 164
Tip 116: Recovering Data from a Corrupted Workbook That Cannot Be Opened……Page 165
Tip 117: Displaying the Workbook’s Path in the Address Box……Page 167
Tip 118: Adding the Path of a Saved Workbook to the Title Bar……Page 168
Tip 119: Removing Personal Information from a Workbook……Page 170
Tip 120: Protecting a Workbook……Page 171
Tip 121: Adding Passwords to Prevent Unauthorized Opening of a Workbook……Page 172
Tip 122: Protecting Workbooks with a Digital Signature……Page 173
Tip 123: Forgotten Your Password?……Page 174
Tip 124: Saving a Customized Workbook as a Template……Page 175
Tip 125: Opening a Template File Automatically When Starting Microsoft Excel……Page 176
Tip 126: Opening Workbooks from a List of Hyperlinks……Page 177
Tip 127: Opening a Copy of an Existing Workbook……Page 178
Tip 128: Using a Workspace to Open a Number of Workbooks at Once……Page 179
Part 4 – Text, Dates, Times……Page 180
Tip 129: Combining Text from Different Cells……Page 181
Tip 130: Combining Text and Formatted Values……Page 183
Tip 131: Applying Formatting Only to Cells Containing Text……Page 184
Tip 132: Extracting Characters from Text Using Text Formulas……Page 185
Tip 133: Extracting Characters from Text Without Using Formulas……Page 186
Tip 134: Separating First and Last Names Using Formulas……Page 187
Tip 135: Separating First and Last Names Without Using Formulas……Page 188
Tip 136: Converting Text to Its Proper Case……Page 189
Tip 137: Converting Text to Uppercase……Page 190
Tip 138: Converting Uppercase Letters to Lowercase……Page 191
Tip 139: Problem: The Characters Are in Reverse Order……Page 192
Tip 140: Problem: Unnecessary Empty Characters in Cells……Page 193
Tip 141: Problem: Values Not Formatting as Numbers……Page 194
Tip 142: Problem: The Minus Sign Appears to the Right of the Number……Page 196
Tip 143: Problem: Date Formatting Cannot Be Changed……Page 197
Tip 144: Getting Continuously Refreshed Data from a Web Site in Excel 97……Page 198
Tip 145: Getting Continuously Refreshed Data from a Web Site in Excel 2000……Page 200
Tip 146: Getting Continuously Refreshed Data from a Web Site in Excel 2002 & 2003……Page 202
Tip 147: Entering the Current Date into a Cell……Page 205
Tip 148: Changing the Default Date Separator from a Slash to a Dot When Entering a Date into a Cell……Page 206
Tip 149: Customizing Date Formatting……Page 207
Tip 150: Quickly Typing Dates into Cells……Page 208
Tip 151: Filling a Range with a Series of Dates……Page 209
Tip 152: Calculating Differences Between Dates……Page 211
Tip 153: Calculating the Week Number……Page 212
Tip 154: Calculating the Quarter Number……Page 213
Tip 155: Calculating the Date at the End of a Month……Page 214
Tip 156: Adding a Custom Function That Returns the Quarter Number……Page 215
Tip 157: Validating Date Entries……Page 216
Tip 158: Displaying the Serial Number Behind the Date……Page 217
Tip 159: Combining Text and a Formatted Date……Page 218
Tip 160: Entering the Current Time into a Cell……Page 219
Tip 161: Displaying the Serial Number Behind the Time……Page 220
Tip 162: Totaling Time Values……Page 221
Tip 163: Calculating the Difference Between Hours……Page 222
Tip 164: Calculating Time Differences Between Regions of the World……Page 223
Tip 165: Rounding Hours Up……Page 224
Tip 166: Converting Time to Decimal Values……Page 225
Tip 167: Converting a Number to a Time Value……Page 226
Part 5 – Summing & Counting……Page 227
Tip 168: Adding, Subtracting, Multiplying and Dividing Without Using Formulas……Page 228
Tip 169: Multiplying a Range by -1 in One Operation……Page 229
Tip 170: Performing a Quick Calculation in the Formula Bar……Page 230
Tip 171: Seeing Calculation Results Immediately……Page 231
Tip 172: Additional Functions in AutoSum……Page 232
Tip 173: Fast Summation……Page 233
Tip 174: Inserting Subtotals Quickly……Page 236
Tip 175: Extending the Range of the SUM Formula……Page 237
Tip 176: Summing Values at the Intersection of Two Ranges……Page 238
Tip 177: Summing Values from Cells in Different Sheets……Page 239
Tip 178: Summing Stock Lists……Page 240
Tip 179: Summing Rounded Numbers……Page 241
Tip 180: The SUMIF Formula – Using Comparison Operators (< >) as Criteria……Page 244
Tip 181: Totaling Two Ranges Using the SUMIF Formula……Page 245
Tip 182: Summing Using Text Characters as Criteria……Page 246
Tip 183: COUNT Formulas……Page 247
Tip 184: Counting the Number of Cells Containing Text in a Range……Page 248
Tip 185: Counting Based on Multiple Criteria……Page 249
Tip 186: The Euro Currency Tools Add-In……Page 251
Part 6 – Formulas……Page 252
Tip 187: Range Name Syntax……Page 253
Tip 188: Defining a Range Name……Page 254
Tip 189: Deleting a Range Name……Page 255
Tip 190: Automatically Defining Names for Ranges in Lists……Page 256
Tip 191: Using a Range Name in a Formula……Page 257
Tip 192: Saving a Frequently Used Formula/Numeric Value in the Define Name Dialog Box……Page 258
Tip 193: Automatically Updating a Range Name Reference……Page 260
Tip 194: Inserting/Editing Formulas……Page 261
Tip 195: Entering Formulas More Quickly by Shortening Sheet Names……Page 263
Tip 196: Nesting Formulas……Page 264
Tip 197: Changing an Absolute Reference to a Relative Reference or Vice Versa……Page 265
Tip 198: Copying a Formula from a Cell While Keeping the Absolute Reference or Relative Reference……Page 266
Tip 199: Copying Formulas from a Range of Cells Without Changing the Absolute or Relative References……Page 267
Tip 200: Copying Formulas and Pasting Them in a Transposed Direction Without Changing the Relative References……Page 268
Tip 201: Array Formulas – the Technical Side……Page 270
Tip 202: Create a Custom Function……Page 271
Tip 203: Pasting Values……Page 273
Tip 204: Displaying Formula Syntax……Page 275
Tip 205: Displaying Formulas and Values of the Same Cells……Page 276
Tip 206: Selecting Cells That Contain Formulas……Page 277
Tip 207: Formatting Cells Containing Formulas……Page 278
Tip 208: Protecting Cells Containing Formulas in a Protected Sheet……Page 279
Tip 209: Protecting Cells Containing Formulas in an Unprotected Sheet……Page 281
Tip 210: Adding a Comment to a Formula……Page 282
Tip 211: Printing Formula Syntax……Page 283
Tip 212: Stepping into a Formula……Page 284
Tip 213: Moving Between Precedent and Dependent Cells……Page 285
Tip 214: Circular References……Page 289
Tip 215: Iteration……Page 290
Tip 216: Avoiding Error Displays when Formulas Return Result Calculations……Page 291
Tip 217: Selecting/Formatting Cells Containing Errors……Page 292
Tip 218: Tracing Errors in Formula Results……Page 293
Tip 219: Quickly Creating Links Between Ranges……Page 294
Tip 220: Unintentionally Creating Links Between Workbooks……Page 295
Tip 221: Canceling the Update Links Message While Opening a Linked Workbook……Page 296
Tip 222: Breaking Links Between Workbooks……Page 297
Tip 223: Changing the Source Link……Page 298
Tip 224: Finding and Deleting Unwanted and Orphaned Links……Page 299
Tip 225: Vlookup Formula – Organizing the Data Table……Page 300
Tip 226: Vlookup Formula – Eliminating Errors……Page 301
Tip 227: Index Formula vs Vlookup Formula……Page 302
Tip 228: Adding a Combo Box……Page 304
Tip 229:The Power Combination……Page 306
Part 7 – Printing & Mailing……Page 312
Tip 230: Adding the Page Setup Icon to the Excel Menu Bar……Page 313
Tip 231: Copying the Page Setup to Other Sheets……Page 314
Tip 232: Removing All Page Breaks from the Sheet……Page 315
Tip 233: Inserting a Watermark Behind the Text……Page 316
Tip 234: Printing a Page Number in Portrait While the Page Is in Landscape Layout……Page 317
Tip 235: Hiding Data Before Printing……Page 320
Tip 236: Preventing the Printing of Objects……Page 321
Tip 237: Printing Non-Continuous Areas……Page 322
Tip 238: Adding Continuous Page Numbers While Printing……Page 324
Tip 239: Preventing a Printed Area from Extending onto an Extra Page……Page 325
Tip 240: Hiding Errors in Cells Before Printing……Page 326
Tip 241: Adding a Picture to a Header/Footer……Page 327
Tip 242: Adding Information to Header/Footer Sheets in a Workbook……Page 329
Tip 243: Adding the Full Path of the Saved File to the Header/Footer……Page 330
Tip 244: Adding the Path Address to the Header/Footer……Page 331
Tip 245: Saving Defined Page Setups for Future Printing……Page 332
Tip 246: Adding the Custom Views Icon to the Excel Menu Bar……Page 333
Tip 247: Quickly Printing a Page from the Workbook Using a Custom View……Page 334
Tip 248: Printing an Already Saved Report Using Report Manager……Page 335
Tip 249: Creating a Custom Report Manager……Page 338
Tip 250: Reducing the Workbook Size for Quick Sending via E-Mail……Page 340
Tip 251: E-mailing an Excel Workbook, Sheet, Data or Chart……Page 341
Part 8 – Lists, Analyzing Data……Page 342
Tip 252: Creating List Objects……Page 343
Tip 253: Sorting Rules & List Structure……Page 344
Tip 254: How Excel Sorts Lists……Page 345
Tip 255: Sorting by Columns……Page 346
Tip 256: Sorting by Custom Lists……Page 347
Tip 257: Deleting Empty Rows……Page 349
Tip 258: Drawing Lines Between Sorted Groups……Page 350
Tip 259: Coloring Rows Based on Text Criteria……Page 351
Tip 260: Applying Colors to Maximum/Minimum Values in a List……Page 352
Tip 261: Quick Filtering Using the AutoFilter Icon……Page 353
Tip 262: Sort Ascending/Descending While Using AutoFilter……Page 354
Tip 263: Summing the Visible Filtered List……Page 355
Tip 264: Coloring and Filtering Lists According to Criteria……Page 357
Tip 265: Filtering by More Than Two Criteria……Page 358
Tip 266: Filtering by the Date Field……Page 359
Tip 267: Saving Filtering Criteria……Page 360
Tip 268: Printing a Filtered List……Page 361
Tip 269: Filtering a Range into a List of Unique Records……Page 362
Tip 270: Filtering a List into Unique Records Using the COUNTIF Formula……Page 363
Tip 271: Adding the Subtotals Icon to the Menu Bar……Page 364
Tip 272: Quickly Removing Subtotals……Page 365
Tip 273: Hiding Subtotal Level Buttons……Page 366
Tip 274: Adding Subtotals to a Date Field……Page 367
Tip 275: Adding Additional Functions to Subtotals……Page 368
Tip 276: Adding Subtotals to Primary and Secondary Fields……Page 369
Tip 277: Printing Each Subtotal Group on a Separate Page……Page 370
Tip 278: Deleting the Word “Total” from the Subtotal Rows……Page 371
Tip 279: Copying the Subtotals Summary……Page 372
Tip 280: Applying Styles to Subtotal Rows……Page 373
Tip 281: Applying Colors to Subtotal Rows……Page 374
Tip 282: Coloring Two Subtotal Levels in Different Colors……Page 376
Tip 283: Adding the Auto Outline, Clear Outline, and Show Outline Symbols Icons to the Toolbar……Page 378
Tip 284: Hiding Outline Symbols……Page 379
Tip 285: Adding Groups and Outlines Manually……Page 380
Tip 286: Copying or Applying Color to Groups and Outlines……Page 381
Tip 287: Consolidating Lists……Page 382
Tip 288: Using Different Functions to Consolidate Lists……Page 384
Tip 289: Consolidating Lists While Adding Links to the Source Data……Page 385
Tip 290: Coloring and/or Copying Subtotals from Consolidated Lists Containing Links……Page 386
Tip 291: Summing Stock Lists……Page 387
Tip 292: Summing Sales Quantities and Amounts for Several Lists, or Presenting the Figures Side-by-Side……Page 389
Tip 293: Summing Monthly Trial Balance Figures……Page 391
Tip 294: Comparing Lists……Page 393
Tip 295: Comparing Lists Using the COUNTIF Formula……Page 395
Tip 296: Rules for Organizing the Source Data in Excel Sheets……Page 396
Tip 297: PivotTable Report Terminology……Page 397
Tip 298: Creating a PivotTable Report……Page 398
Tip 299: Setting the Number of Fields Displayed in the Page Layout……Page 402
Tip 300: Automatically Refreshing a PivotTable Report……Page 403
Tip 301: Adding/Deleting Subtotals……Page 404
Tip 302: Hiding Items……Page 405
Tip 303: Displaying the Top/Bottom Number of Items……Page 406
Tip 304: Formatting a PivotTable Report……Page 407
Tip 305: Inserting a Chart……Page 408
Tip 306: Printing a PivotTable Report……Page 409
Tip 307: Adding Sub-Details to an Item……Page 410
Tip 308: Getting Drill-Down Details……Page 411
Tip 309: Grouping Text Fields……Page 412
Tip 310: Adding a Calculated Field……Page 413
Tip 311: Grouping the Date Field by Number of Days……Page 415
Tip 312: Grouping the Date Field by Days, Months, Quarters, and Years……Page 418
Tip 313: Grouping the Date Field by Week Number……Page 420
Tip 314: Grouping the Date Field by Quarters in a Fiscal Reporting Year……Page 421
Tip 315: Adding a Calculated Percentage Field……Page 422
Tip 316: Adding a Data Field That Calculates the Difference Between Two Data Fields……Page 424
Tip 317: Adding a Data Field That Calculates Percentages from One Item of the Row Field……Page 426
Tip 318: Adding a Data Field That Calculates Percentage Difference from the Previous Item……Page 427
Tip 319: Adding a Data Field That Calculates the Difference from the Previous Item……Page 428
Tip 320: Adding a Running Balance Calculation Column……Page 430
Tip 321: Retrieving Data from a PivotTable Report……Page 432
Tip 322: Adding Additional PivotTable Reports Using the Same Data Source……Page 434
Index……Page 435
Reviews
There are no reviews yet.