Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets

David Hawley, Raina Hawley0596528345, 9780596528348

Millions of users create and share Excel spreadsheets every day, but few go deeply enough to learn the techniques that will make their work much easier. There are many ways to take advantage of Excel’s advanced capabilities without spending hours on advanced study. Excel Hacks provides more than 130 hacks – clever tools, tips and techniques – that will leapfrog your work beyond the ordinary.Now expanded to include Excel 2007, this resourceful, roll-up-your-sleeves guide gives you little known “backdoor” tricks for several Excel versions using different platforms and external applications. Think of this book as a toolbox. When a need arises or a problem occurs, you can simply use the right tool for the job. Hacks are grouped into chapters so you can find what you need quickly, including ways to: * Reduce workbook and worksheet frustration – manage how users interact with worksheets, find and highlight information, and deal with debris and corruption. * Analyze and manage data – extend and automate these features, moving beyond the limited tasks they were designed to perform. * Hack names – learn not only how to name cells and ranges, but also how to create names that adapt to the data in your spreadsheet. * Get the most out of PivotTables – avoid the problems that make them frustrating and learn how to extend them. * Create customized charts – tweak and combine Excel’s built-in charting capabilities. * Hack formulas and functions – subjects range from moving formulas around to dealing with datatype issues to improving recalculation time. * Make the most of macros – including ways to manage them and use them to extend other features. * Use the enhanced capabilities of Microsoft Office 2007 to combine Excel with Word, Access, and Outlook.

---

Table of contents :
Excel Hacks, 2nd edition……Page 1
Contents……Page 8
Contributors……Page 14
Acknowledgments……Page 15
Preface……Page 16
How to Use This Book……Page 17
How This Book Is Organized……Page 18
Windows, Macintosh, and Earlier Excel Versions……Page 19
Conventions Used in This Book……Page 20
Using Code Examples……Page 21
How to Contact Us……Page 22
The 80/20 Rule……Page 24
Formatting Tips……Page 25
Formula Tips……Page 26
Create a Personal View of Your Workbooks……Page 28
Grouping Worksheets Manually……Page 31
Grouping Worksheets Automatically……Page 32
Preventing Save As… in a Workbook……Page 34
Preventing Users from Inserting More Worksheets……Page 37
Enabling Macros When You Don’t Have Any……Page 38
Prompting to Save Nonexistent Changes……Page 40
Stopping Excel’s Warning Prompts for Recorded Macros……Page 41
Hide Worksheets So That They Cannot Be Unhidden……Page 42
Creating Your Own Template Tab……Page 44
Create an Index of Sheets in Your Workbook……Page 46
Auto-Generate an Index Using VBA……Page 47
Link to the Index from a Context Menu……Page 48
Limit the Scrolling Range of Your Worksheet……Page 49
Specifying a Valid Range……Page 50
Lock and Protect Cells Containing Formulas……Page 53
Locking Formula Cells……Page 54
Data Validation……Page 55
Auto-Toggle Worksheet Protection……Page 56
Find Duplicate Data Using Conditional Formatting……Page 57
Find Data That Appears Two or More Times Using Conditional Formatting……Page 58
Tie Custom Toolbars to a Particular Workbook……Page 59
Outsmart Excel’s Relative Reference Handler……Page 61
Remove Phantom Workbook Links……Page 62
Reduce Workbook Bloat……Page 65
Clean Up Your Macros……Page 66
Cleaning Corrupted Workbooks……Page 67
If You Can Open Your Workbook……Page 68
If You Cannot Open Your File……Page 69
Method 1: Named Ranges……Page 71
The Pros and Cons of Both Methods……Page 72
Setting Up Checkboxes for Conditional Formatting……Page 73
Toggling Number Highlighting On and Off……Page 75
Identify Formulas with Conditional Formatting……Page 77
Count or Sum Cells That Meet Conditional Formatting Criteria……Page 79
An Alternate Path……Page 80
Highlight Every Other Row or Column……Page 81
Highlighting Dynamically……Page 82
Create 3-D Effects in Tables or Cells……Page 83
Using a 3-D Effect on a Table of Data……Page 84
Turn Conditional Formatting and Data Validation On and Off withaCheckbox……Page 85
Support Multiple Lists in a ComboBox……Page 87
Create Validation Lists That Change Based on a Selection from Another List……Page 89
Convert Text Numbers to Real Numbers……Page 91
Using the TEXT Functions……Page 92
Extract the Numeric Portion of a Cell Entry……Page 93
Customize Cell Comments……Page 94
Adding a Picture……Page 95
Sort by More Than Three Columns……Page 96
Random Sorting……Page 97
Manipulate Data with the Advanced Filter……Page 98
Create Custom Number Formats……Page 102
Create Custom Lists……Page 107
Boldface Excel Subtotals……Page 108
Hacking the Hack……Page 110
Using a Macro……Page 112
Automatically Add Data to a Validation List……Page 114
Adding Beyond 24 Hours……Page 117
Time and Date Calculations……Page 118
A Date Bug?……Page 120
Enable Grouping and Outlining on a Protected Worksheet……Page 121
Prevent Blanks/Missing Fields in a Table……Page 123
Provide Decreasing Data Validation Lists……Page 124
Add a Custom List to the Fill Handle……Page 125
Address Data by Name……Page 128
Use the Same Name for Ranges on Different Worksheets……Page 129
Using Relative References……Page 130
Create Custom Functions Using Names……Page 131
Using Names with Intersect……Page 133
Create Ranges That Expand and Contract……Page 135
Nest Dynamic Ranges for Maximum Flexibility……Page 141
Method 1……Page 144
Method 2……Page 145
PivotTables: A Hack in Themselves……Page 147
Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability?……Page 148
Creating Tables and Lists for Use in PivotTables……Page 149
PivotTable Creation……Page 151
Share PivotTables but Not Their Data……Page 152
Automate PivotTable Creation……Page 154
Save Time with a Macro……Page 156
Move PivotTable Grand Totals……Page 158
Efficiently Pivot Another Workbook’s Data……Page 160
Explode a Single Slice from a Pie Chart……Page 163
Create Two Sets of Slices in One Pie Chart……Page 165
Create Charts That Adjust to Data……Page 167
Plotting the Last x Number of Readings……Page 170
Using a Dynamic Named Range Linked to a Scrollbar……Page 171
Using a Dynamic Named Range Linked to a Drop-Down List……Page 172
Using Drag-and-Drop……Page 175
Using the Formula Bar……Page 177
Dragging the Bounding Area……Page 178
Using Paste Special……Page 179
Hack Together a Simple Thermometer Chart……Page 180
Create a Column Chart with Variable Widths and Heights……Page 183
Create a Speedometer Chart……Page 187
Link Chart Text Elements to a Cell……Page 194
Using #N/A to Plot Blank Cells……Page 196
Add a Directional Arrow to the End of a Line Series……Page 198
Place an Arrow on the End of a Horizontal (X) Axis……Page 200
In Older Excel Versions……Page 201
Correct Narrow Columns When Using Dates……Page 203
Changing Label Position……Page 204
Tornado Chart……Page 206
Gauge Chart……Page 209
Conditional Highlighting Axis Labels……Page 211
Create Totals on a Stacked Column Chart……Page 213
Add Descriptive Text to Your Formulas……Page 216
Move Relative Formulas Without Changing References……Page 217
Method 1: Using True or False……Page 218
Method 2: Using Conditional Formatting……Page 219
Fill All Blank Cells in a List……Page 220
Method 2: Filling Blanks via a Macro……Page 221
Make Your Formulas Increment by Rows When You Copy AcrossColumns……Page 222
Convert Dates to Excel Formatted Dates……Page 225
Sum or Count Cells While Avoiding Error Values……Page 226
Reduce the Impact of Volatile Functions on Recalculation……Page 228
Before Excel 2007……Page 229
Using a Pivot Table……Page 230
Using an Array Formula……Page 231
Using DSUM……Page 232
Find the Nth Occurrence of a Value……Page 233
Make the Excel Subtotal Function Dynamic……Page 235
Add Date Extensions……Page 237
Convert Numbers with the Negative Sign on the Right toExcelNumbers……Page 238
Display Negative Time Values……Page 240
Method 2: Using the TEXT Function……Page 241
Use the VLOOKUP Function Across Multiple Tables……Page 242
Show Total Time As Days, Hours, and Minutes……Page 244
Determine the Number of Specified Days in Any Month……Page 245
Construct Mega-Formulas……Page 247
Hack Mega-Formulas that Reference Other Workbooks……Page 249
Hack One of Excel’s Database Functions to Take the Place ofManyFunctions……Page 250
Using DCOUNT to Filter on Two Criteria……Page 253
Making the Comparison Operators Interchangeable……Page 254
Getting the Last Word……Page 256
Count Words in a Cell or Range of Cells……Page 257
Putting It Together……Page 258
Create a List of Worksheet Names……Page 259
Use the List in Formulas……Page 260
Make the Range Address Variable……Page 261
SUMIF……Page 262
DSUM……Page 263
SUM and IF……Page 265
Array Formulas……Page 266
SUMPRODUCT……Page 268
Using IF/SUM……Page 269
Using a VLOOKUP Formula……Page 270
Method 1……Page 272
Method 2……Page 273
EDATE……Page 274
Without EDATE……Page 275
Using EOMONTH……Page 276
Using a Custom Function……Page 277
Calculate a Person’s Age……Page 278
Get the Weekday as a Number……Page 279
Return the Weekday as Weekday Text……Page 280
Evaluate a Text Equation……Page 281
Lookup from Within a Cell……Page 282
Keeping It Clean and Global……Page 283
Lookup Scale……Page 284
Speed Up Code While Halting Screen Flicker……Page 286
Run a Macro at a Set Time……Page 287
Use CodeNames to Reference Sheets in Excel Workbooks……Page 289
Connect Buttons to Macros Easily……Page 290
Create a Workbook Splash Screen……Page 291
Display a “Please Wait” Message……Page 293
Have a Cell Ticked or Unticked upon Selection……Page 294
Count or Sum Cells That Have a Specified Fill Color……Page 296
Add the Microsoft Excel Calendar Control to Any ExcelWorkbook……Page 297
Password-Protect and Unprotect All Excel Worksheets inOneFellSwoop……Page 299
Retrieve a Workbook’s Name and Path……Page 302
Get Around Excel’s Three-Criteria Limit for ConditionalFormatting……Page 303
Run Procedures on Protected Worksheets……Page 305
Distribute Macros……Page 306
Add a Menu Item……Page 308
With AutoFilter……Page 312
Without AutoFilter……Page 314
Track Changes on a Particular Worksheet……Page 316
Track Changes on All Worksheets in One Workbook……Page 318
Automatically Add Date/Time to a Cell upon Entry……Page 320
Create a List of Workbook Hyperlinks……Page 321
The Code……Page 322
Advanced Find……Page 323
The UserForm……Page 324
The Code……Page 325
Toolbar……Page 328
Find a Number Between Two Numbers……Page 329
The Code……Page 330
Running the Hack……Page 332
The code……Page 333
The code……Page 335
The Code……Page 338
Hide and Restore Toolbars in Excel……Page 339
Coding the Toolbar Show and Restore……Page 340
The Code……Page 342
Password-Protect a Worksheet from Viewing……Page 343
The Code……Page 344
The Code……Page 345
Force Text to Upper- or Proper Case……Page 347
Uppercase……Page 348
Running the Hack……Page 349
Hacking the Hack……Page 350
Option Compare Text……Page 351
Display AutoFilter Criteria……Page 352
Import Data from Access 2007 into Excel 2007……Page 354
The Code……Page 356
Running the Hack……Page 358
Retrieve Data from Closed Workbooks……Page 359
Excel 2007 and Windows Vista……Page 360
The code……Page 361
Running the hack……Page 363
The code……Page 364
The Code……Page 367
Running the Hack……Page 371
Automate Outlook from Excel……Page 372
The Code……Page 373
Running the Hack……Page 376
Index……Page 378