Microsoft Excel 2010 Tips & Tricks

Part I: Basic Excel Usage - Page 29
Part I: Basic Excel Usage - Page 30
Understanding Excel Versions - Page 31
Understanding Excel Versions - Page 32
Understanding Excel Versions - Page 33
Maximizing Ribbon Efficiency - Page 34
Maximizing Ribbon Efficiency - Page 35
Maximizing Ribbon Efficiency - Page 36
Understanding Protected View - Page 37
Understanding Protected View - Page 38
Selecting Cells Efficiently - Page 39
Selecting entire rows - Page 40
Selecting multisheet ranges - Page 41
Making “Special” Range Selections - Page 42
Making “Special” Range Selections - Page 43
Undoing, Redoing, and Repeating - Page 44
Redoing - Page 45
Redoing - Page 46
Discovering Some Useful Shortcut Keys - Page 47
Navigating Sheets in a Workbook - Page 48
Resetting the Used Area of a Worksheet - Page 49
Understanding Workbooks versus Windows - Page 50
Understanding Workbooks versus Windows - Page 51
Customizing the Quick Access Toolbar - Page 52
Customizing the Quick Access Toolbar - Page 53
Customizing the Quick Access Toolbar - Page 54
Performing other Quick Access toolbar actions - Page 55
Customizing the Ribbon - Page 56
Customizing the Ribbon - Page 57
Customizing the Ribbon - Page 58
Accessing the Ribbon with Your Keyboard - Page 59
Recovering Your Work - Page 60
Recovering Your Work - Page 61
Customizing the Default Workbook - Page 62
Customizing the Default Workbook - Page 63
Using Document Themes - Page 64
Using Document Themes - Page 65
Applying a theme - Page 66
Customizing a theme - Page 67
Customizing a theme - Page 68
Hiding User Interface Elements - Page 69
Hiding User Interface Elements - Page 70
A setting that requires a macro - Page 71
Hiding Columns or Rows - Page 72
Hiding Columns or Rows - Page 73
Hiding Cell Contents - Page 74
Taking Pictures of Ranges - Page 75
Creating a live image of a range - Page 76
Performing Inexact Searches - Page 77
Performing Inexact Searches - Page 78
Replacing Formatting - Page 79
Replacing Formatting - Page 80
Replacing Formatting - Page 81
Changing the Excel Color Scheme - Page 82
Limiting the Usable Area in a Worksheet - Page 83
Using worksheet protection - Page 84
Using worksheet protection - Page 85
Using an Alternative to Cell Comments - Page 86
Understanding the Excel Help System - Page 87
Understanding the Excel Help System - Page 88
Understanding the Excel Help System - Page 89
Making a Worksheet “Very Hidden” - Page 90
Making a Worksheet “Very Hidden” - Page 91
Working with the Backstage View - Page 92
Part II: Data Entry - Page 93
Tips and Where to Find Them - Page 94
Understanding the Types of Data - Page 95
Entering dates and times - Page 96
Entering text - Page 97
Entering formulas - Page 98
Moving the Cell Pointer after Entering Data - Page 99
Selecting a Range of Input Cells before Entering Data - Page 100
Using AutoComplete to Automate Data Entry - Page 101
Using AutoComplete to Automate Data Entry - Page 102
Removing Duplicate Rows - Page 103
Removing Duplicate Rows - Page 104
Keeping Titles in View - Page 105
Automatically Filling a Range with a Series - Page 106
Automatically Filling a Range with a Series - Page 107
Automatically Filling a Range with a Series - Page 108
Working with Fractions - Page 109
Working with Fractions - Page 110
Resizing the Formula Bar - Page 111
Resizing the Formula Bar - Page 112
Proofing Your Data with Audio - Page 113
Proofing Your Data with Audio - Page 114
Controlling Automatic Hyperlinks - Page 115
Removing existing hyperlinks - Page 116
Entering Credit Card Numbers - Page 117
Using the Excel Built-In Data Entry Form - Page 118
Using the Excel Built-In Data Entry Form - Page 119
Customizing and Sharing AutoCorrect Entries - Page 120
Customizing and Sharing AutoCorrect Entries - Page 121
Restricting Cursor Movement to Input Cells - Page 122
Restricting Cursor Movement to Input Cells - Page 123
Controlling the Office Clipboard - Page 124
Controlling the Office Clipboard - Page 125
Creating a Drop-Down List in a Cell - Page 126
Creating a Drop-Down List in a Cell - Page 127
Creating a Drop-Down List in a Cell - Page 128
Part III: Formatting - Page 129
Tips and Where to Find Them - Page 130
Using the Mini Toolbar - Page 131
Indenting Cell Contents - Page 132
Indenting Cell Contents - Page 133
Quick Number Formatting - Page 134
Creating Custom Number Formats - Page 135
Parts of a number format string - Page 136
Custom number format codes - Page 137
Custom number format codes - Page 138
Using Custom Number Formats to Scale Values - Page 139
Using Custom Number Formats to Scale Values - Page 140
Using Custom Date and Time Formatting - Page 141
Examining Some Useful Custom Number Formats - Page 142
Displaying fractions - Page 143
Displaying a negative sign on the right - Page 144
Updating Old Fonts - Page 145
Updating Old Fonts - Page 146
Understanding Conditional Formatting Visualization - Page 147
Color scales - Page 148
Color scales - Page 149
Showing Text and a Value in a Cell - Page 150
Using a custom number format - Page 151
Merging Cells - Page 152
Formatting Individual Characters in a Cell - Page 153
Displaying Times That Exceed 24 Hours - Page 154
Displaying Times That Exceed 24 Hours - Page 155
Fixing Non-Numeric Numbers - Page 156
Adding a Frame to a Range - Page 157
Dealing with Gridlines, Borders, and Underlines - Page 158
Dealing with Gridlines, Borders, and Underlines - Page 159
Inserting a Watermark - Page 160
Inserting a Watermark - Page 161
Adding a Background Image to a Worksheet - Page 162
Wrapping Text in a Cell - Page 163
Wrapping Text in a Cell - Page 164
Seeing All Characters in a Font - Page 165
Seeing All Characters in a Font - Page 166
Entering Special Characters - Page 167
Entering Special Characters - Page 168
Using Named Styles - Page 169
Modifying an existing style - Page 170
Creating new styles - Page 171
Merging styles from other workbooks - Page 172
Part IV: Basic Formulas and Functions - Page 173
Tips and Where to Find Them - Page 174
Using Formula AutoComplete - Page 175
Using Formula AutoComplete - Page 176
Knowing When to Use Absolute References - Page 177
Knowing When to Use Absolute References - Page 178
Knowing When to Use Mixed References - Page 179
Knowing When to Use Mixed References - Page 180
Changing the Type of a Cell Reference - Page 181
Converting a Vertical Range to a Table - Page 182
Converting a Vertical Range to a Table - Page 183
AutoSum Tricks - Page 184
AutoSum Tricks - Page 185
Using the Status Bar Selection Statistics Feature - Page 186
Using the Status Bar Selection Statistics Feature - Page 187
Converting Formulas to Values - Page 188
Transforming Data without Using Formulas - Page 189
Transforming Data by Using Temporary Formulas - Page 190
Transforming Data by Using Temporary Formulas - Page 191
Deleting Values While Keeping Formulas - Page 192
Summing Across Sheets - Page 193
Summing Across Sheets - Page 194
Dealing with Function Arguments - Page 195
Dealing with Function Arguments - Page 196
Annotating a Formula without Using a Comment - Page 197
Making an Exact Copy of a Range of Formulas - Page 198
Making an Exact Copy of a Range of Formulas - Page 199
Monitoring Formula Cells from Any Location - Page 200
Displaying and Printing Formulas - Page 201
Displaying and Printing Formulas - Page 202
Avoiding Error Displays in Formulas - Page 203
Using the IFERROR function - Page 204
Using Goal Seeking - Page 205
Using Goal Seeking - Page 206
Understanding the Secret about Names - Page 207
Understanding the Secret about Names - Page 208
Using Named Constants - Page 209
Using Named Constants - Page 210
Using Functions in Names - Page 211
Using Functions in Names - Page 212
Creating a List of Names - Page 213
Creating a List of Names - Page 214
Using Dynamic Names - Page 215
Using Dynamic Names - Page 216
Using Dynamic Names - Page 217
Creating Worksheet-Level Names - Page 218
Creating Worksheet-Level Names - Page 219
Working with Pre-1900 Dates - Page 220
Working with Pre-1900 Dates - Page 221
Working with Negative Time Values - Page 222
Working with Negative Time Values - Page 223
Working with Negative Time Values - Page 224
Part V: Useful Formula Examples - Page 225
Tips and Where to Find Them - Page 226
Calculating Holidays - Page 227
Easter - Page 228
Columbus Day - Page 229
Calculating a Weighted Average - Page 230
Calculating a Person’s Age - Page 231
Calculating a Person’s Age - Page 232
Ranking Values - Page 233
Ranking Values - Page 234
Converting Inches to Feet and Inches - Page 235
Using the DATEDIF Function - Page 236
Using the DATEDIF Function - Page 237
Counting Characters in a Cell - Page 238
Counting Characters in a Cell - Page 239
Numbering Weeks - Page 240
Numbering Weeks - Page 241
Using a Pivot Table Instead of Formulas - Page 242
Using a Pivot Table Instead of Formulas - Page 243
Using formulas - Page 244
Using formulas - Page 245
Expressing a Number as an Ordinal - Page 246
Expressing a Number as an Ordinal - Page 247
Extracting Words from a String - Page 248
Extracting all except the first word of a string - Page 249
Parsing Names - Page 250
Parsing Names - Page 251
Removing Titles from Names - Page 252
Generating a Series of Dates - Page 253
Generating a Series of Dates - Page 254
Determining Specific Dates - Page 255
Determining the date of the most recent Sunday - Page 256
Determining the last day of a month - Page 257
Displaying a Calendar in a Range - Page 258
Various Methods of Rounding Numbers - Page 259
Rounding currency values - Page 260
Rounding to n significant digits - Page 261
Rounding Time Values - Page 262
Using the New AGGREGATE Function - Page 263
Using the New AGGREGATE Function - Page 264
Using the New AGGREGATE Function - Page 265
Returning the Last Nonblank Cell in a Column or Row - Page 266
Returning the Last Nonblank Cell in a Column or Row - Page 267
Using the COUNTIF Function - Page 268
Counting Cells That Meet Multiple Criteria - Page 269
Counting Cells That Meet Multiple Criteria - Page 270
Using “Or” criteria - Page 271
Combining “And” and “Or” criteria - Page 272
Counting Nonduplicated Entries in a Range - Page 273
Calculating Single-Criterion Conditional Sums - Page 274
Summing values based on a different range - Page 275
Calculating Multiple-Criterion Conditional Sums - Page 276
Using “Or” criteria - Page 277
Looking Up an Exact Value - Page 278
Looking Up an Exact Value - Page 279
Performing a Two-Way Lookup - Page 280
Using implicit intersection - Page 281
Performing a Two-Column Lookup - Page 282
Performing a Two-Column Lookup - Page 283
Performing a Lookup by Using an Array - Page 284
Performing a Lookup by Using an Array - Page 285
Using the INDIRECT Function - Page 286
Specifying worksheet names indirectly - Page 287
Specifying worksheet names indirectly - Page 288
Creating Megaformulas - Page 289
Creating Megaformulas - Page 290
Creating Megaformulas - Page 291
Creating Megaformulas - Page 292
Part VI: Conversions and Mathematical Calculations - Page 293
Tips and Where to Find Them - Page 294
Converting Between Measurement Systems - Page 295
Converting Between Measurement Systems - Page 296
Converting Temperatures - Page 297
Solving Simultaneous Equations - Page 298
Solving Simultaneous Equations - Page 299
Solving Recursive Equations - Page 300
Solving Recursive Equations - Page 301
Generating Random Numbers - Page 302
Using the Analysis Toolpak add-in - Page 303
Calculating Roots - Page 304
Calculating a Remainder - Page 305
Calculating a Remainder - Page 306
Part VII: Charts and Graphics - Page 307
Tips and Where to Find Them - Page 308
Creating a Text Chart Directly in a Range - Page 309
Creating a Text Chart Directly in a Range - Page 310
Creating a Text Chart Directly in a Range - Page 311
Selecting Elements in a Chart - Page 312
Selecting with the keyboard - Page 313
Selecting with the keyboard - Page 314
Creating a Self-Expanding Chart - Page 315
Creating Combination Charts - Page 316
Creating Combination Charts - Page 317
Creating Combination Charts - Page 318
Creating a Gantt Chart - Page 319
Creating a Gantt Chart - Page 320
Creating a Gauge Chart - Page 321
Creating a Gauge Chart - Page 322
Using Pictures in Charts - Page 323
Using Pictures in Charts - Page 324
Plotting Mathematical Functions - Page 325
Plotting two-variable mathematical functions - Page 326
Plotting two-variable mathematical functions - Page 327
Using High-Low Lines in a Chart - Page 328
Linking Chart Text to Cells - Page 329
Creating a Chart Template - Page 330
Saving a Chart as a Graphics File - Page 331
Method 3: Use a VBA macro - Page 332
Saving a Range as a Graphic Image - Page 333
Making Charts the Same Size - Page 334
Making Charts the Same Size - Page 335
Resetting All Chart Formatting - Page 336
Resetting All Chart Formatting - Page 337
Freezing a Chart - Page 338
Freezing a Chart - Page 339
Creating Picture Effects with a Chart - Page 340
Creating Sparkline Graphics - Page 341
Creating Sparkline Graphics - Page 342
Selecting Objects on a Worksheet - Page 343
The Select Object tool - Page 344
Making a Greeting Card - Page 345
Making a Greeting Card - Page 346
Enhancing Text Formatting in Shapes - Page 347
Enhancing Text Formatting in Shapes - Page 348
Using Images as Line Chart Markers - Page 349
Using Images as Line Chart Markers - Page 350
Changing the Shape of a Cell Comment - Page 351
Adding an Image to a Cell Comment - Page 352
Enhancing Images - Page 353
Enhancing Images - Page 354
Part VIII: Data Analysis and Lists - Page 355
Tips and Where to Find Them - Page 356
Using the Table Feature - Page 357
Creating a table - Page 358
Creating a table - Page 359
Working with Tables - Page 360
Deleting rows or columns - Page 361
Deleting rows or columns - Page 362
Using Formulas with a Table - Page 363
Using formulas within a table - Page 364
Using formulas within a table - Page 365
Referencing data in a table - Page 366
Numbering Rows in a Table - Page 367
Numbering Rows in a Table - Page 368
Using Custom Views with Filtering - Page 369
Using Custom Views with Filtering - Page 370
Putting Advanced Filter Results on a Different Sheet - Page 371
Comparing Two Ranges by Using Conditional Formatting - Page 372
Comparing Two Ranges by Using Conditional Formatting - Page 373
Comparing Two Ranges by Using Conditional Formatting - Page 374
Randomizing a List - Page 375
Randomizing a List - Page 376
Filling the Gaps in a Report - Page 377
Filling the Gaps in a Report - Page 378
Creating a List from a Summary Table - Page 379
Creating a List from a Summary Table - Page 380
Creating a List from a Summary Table - Page 381
Finding Duplicates by Using Conditional Formatting - Page 382
Finding Duplicates by Using Conditional Formatting - Page 383
Creating a Quick Frequency Tabulation - Page 384
Creating a Quick Frequency Tabulation - Page 385
Creating a Quick Frequency Tabulation - Page 386
Controlling References to Cells within a Pivot Table - Page 387
Grouping Items by Date in a Pivot Table - Page 388
Grouping Items by Date in a Pivot Table - Page 389
Grouping Items by Date in a Pivot Table - Page 390
Unlinking a Pivot Table from Its Source - Page 391
Unlinking a Pivot Table from Its Source - Page 392
Using Pivot Table Slicers - Page 393
Using Pivot Table Slicers - Page 394
Using Pivot Table Slicers - Page 395
Using Pivot Table Slicers - Page 396
Part IX: Working with Files - Page 397
Tips and Where to Find Them - Page 398
Understanding the New Excel File Formats - Page 399
Saving a file for use with an older version of Excel - Page 400
Importing a Text File into a Worksheet Range - Page 401
Importing a Text File into a Worksheet Range - Page 402
Getting Data from a Web Page - Page 403
Getting Data from a Web Page - Page 404
Opening the Web page directly - Page 405
Opening the Web page directly - Page 406
Displaying a Workbook’s Full Path - Page 407
Display the Document panel - Page 408
Using Document Properties - Page 409
Using Document Properties - Page 410
Inspecting a Workbook - Page 411
Inspecting a Workbook - Page 412
Finding the Missing No to All Button When Closing Files - Page 413
Getting a List of Filenames - Page 414
Getting a List of Filenames - Page 415
Using Workspace Files - Page 416
Part X: Printing - Page 417
Tips and Where to Find Them - Page 418
Controlling What Gets Printed - Page 419
Controlling What Gets Printed - Page 420
Displaying Repeated Rows or Columns on a Printout - Page 421
Printing Noncontiguous Ranges on a Single Page - Page 422
Shooting with the Camera - Page 423
Shooting with the Camera - Page 424
Preventing Objects from Printing - Page 425
Page-Numbering Tips - Page 426
Page-Numbering Tips - Page 427
Adding and Removing Page Breaks - Page 428
Saving to a PDF File - Page 429
Making Your Printout Fit on One Page - Page 430
Making Your Printout Fit on One Page - Page 431
Printing the Contents of a Cell in a Header or Footer - Page 432
Printing the Contents of a Cell in a Header or Footer - Page 433
Copying Page Setup Settings Across Sheets - Page 434
Printing Cell Comments - Page 435
Printing a Giant Banner - Page 436
Printing a Giant Banner - Page 437
Printing a Giant Banner - Page 438
Part XI: Spotting, Fixing, and Preventing Errors - Page 439
Tips and Where to Find Them - Page 440
Using the Excel Error-Checking Features - Page 441
Using the Excel Error-Checking Features - Page 442
Identifying Formula Cells - Page 443
Identifying Formula Cells - Page 444
Identifying Formula Cells - Page 445
Dealing with Floating-Point Number Problems - Page 446
Dealing with Floating-Point Number Problems - Page 447
Removing Excess Spaces - Page 448
Removing Excess Spaces - Page 449
Viewing Names Graphically - Page 450
Locating Phantom Links - Page 451
Understanding Displayed versus Actual Values - Page 452
Tracing Cell Relationships - Page 453
Identifying dependents - Page 454
Part XII: Basic VBA and Macros - Page 455
Tips and Where to Find Them - Page 456
Learning about Macros and VBA - Page 457
Learning about Macros and VBA - Page 458
Recording a Macro - Page 459
Testing the macro - Page 460
Executing Macros - Page 461
Assign the macro to a shape - Page 462
Assign the macro to a shape - Page 463
Understanding Functions Versus Subs - Page 464
Understanding Functions Versus Subs - Page 465
Creating Simple Worksheet Functions - Page 466
Returning a worksheet name - Page 467
Returning a worksheet name - Page 468
Describing Function Arguments - Page 469
Describing Function Arguments - Page 470
Making Excel Talk - Page 471
Understanding Custom Function Limitations - Page 472
Executing a Ribbon Command with a Macro - Page 473
Executing a Ribbon Command with a Macro - Page 474
Understanding Security Issues Related to Macros - Page 475
Understanding Security Issues Related to Macros - Page 476
Using a Personal Macro Workbook - Page 477
Using a Personal Macro Workbook - Page 478