F1 Get the Most out of Excel! the Ultimate Excel Tip Help Guide: Excel 97, Excel 2000, Excel 2002, Excel 2003

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