EXCEL HACKSTM Other resources from O’Reilly Related titles Excel 2007: The Missing Manual Office 2007: The Missing Manual Excel Scientific and Engi- neering Cookbook Writing Excel Macros with VBA Windows XP Hacks Access Hacks Word Hacks SQL Hacks Hacks Series Home hackszine.com is a community site for developers and power users of all stripes. Readers learn from each other as they share their favorite tips and tools for Mac OS X, Linux, Google, Windows XP, and more. oreilly.com oreilly.com is more than a complete catalog of O’Reilly books. You’ll also find links to news, events, articles, we- blogs, sample chapters, and code examples. oreillynet.com is the essential portal for developers inter- ested in open and emerging technologies, including new platforms, programming languages, and operating systems. Conferences O’Reilly brings diverse innovators together to nurture the ideas that spark revolutionary industries. We special- ize in documenting the latest tools and systems, translating the innovator’s knowledge into useful skills for those in the trenches. Visit conferences.oreilly.com for our upcoming events. Safari Bookshelf (safari.oreilly.com) is the premier online reference library for programmers and IT professionals. Conduct searches across more than 1,000 books. Sub- scribers can zero in on answers to time-critical questions in a matter of seconds. Read the books on your Book- shelf from cover to cover or simply flip to the page you need. Try it today with a free trial. EXCEL HACKSTM SECOND EDITION David and Raina Hawley Beijing • Cambridge • Farnham • Köln • Paris • Sebastopol • Taipei • Tokyo Excel Hacks™ by David and Raina Hawley Copyright © 2007 O’Reilly Media, Inc. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (safari.oreilly.com). For more information, contact our corporate/institutional sales department: (800) 998-9938 or corporate@oreilly.com. Editors: Brian Sawyer and Brian Jepson Technical Editor Tom Sgouros Production Editor: Adam Witwer Copyeditor: Derek Di Matteo Proofreader: Tolman Creek Design Indexer: Tolman Creek Design Cover Designer: Hanna Dyer Interior Designer: David Futato Illustrators: Robert Romano and Jessamyn Read Printing History: March 2004: First Edition. June 2007: Second Edition. Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of O’Reilly Media, Inc. The Hacks series designations, Excel Hacks, the image of a trowel, and related trade dress are trademarks of O’Reilly Media, Inc. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O’Reilly Media, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps. While every precaution has been taken in the preparation of this book, the publisher and authors assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein. Small print: The technologies discussed in this publication, the limitations on these technologies that technology and content owners seek to impose, and the laws actually limiting the use of these technologies are constantly changing. Thus, some of the hacks described in this publication may not work, may cause unintended harm to systems on which they are used, or may not be consistent with applicable user agreements. Your use of these hacks is at your own risk, and O’Reilly Media, Inc. disclaims responsibility for any damage or expense resulting from their use. In any event, you should take care that your use of these hacks does not violate any applicable laws, including copyright laws. This book uses RepKover™, a durable and flexible lay-flat binding. ISBN-10: 0-596-52834-5 ISBN-13: 978-0-596-52834-8 [M] For our girls, Aleisha and Kate vii Contents Credits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Chapter 1. Reducing Workbook and Worksheet Frustration . . . . . . . . . . . . . 1 1. Create a Personal View of Your Workbooks 5 2. Enter Data into Multiple Worksheets Simultaneously 8 3. Prevent Users from Performing Certain Actions 11 4. Prevent Seemingly Unnecessary Prompts 15 5. Hide Worksheets So That They Cannot Be Unhidden 19 6. Customize the Templates Dialog and Default Workbook 21 7. Create an Index of Sheets in Your Workbook 23 8. Limit the Scrolling Range of Your Worksheet 26 9. Lock and Protect Cells Containing Formulas 30 10. Find Duplicate Data Using Conditional Formatting 34 11. Find Data That Appears Two or More Times Using Conditional Formatting 35 12. Tie Custom Toolbars to a Particular Workbook 36 13. Outsmart Excel’s Relative Reference Handler 38 14. Remove Phantom Workbook Links 39 15. Reduce Workbook Bloat 42 16. Extract Data from a Corrupt Workbook 45 Chapter 2. Hacking Excel’s Built-in Features . . . . . . . . . . . . . . . . . . . . . . . . . 48 17. Validate Data Based on a List on Another Worksheet 48 18. Control Conditional Formatting with Checkboxes 50 viii | Contents 19. Identify Formulas with Conditional Formatting 54 20. Count or Sum Cells That Meet Conditional Formatting Criteria 56 21. Highlight Every Other Row or Column 58 22. Create 3-D Effects in Tables or Cells 60 23. Turn Conditional Formatting and Data Validation On and Off with a Checkbox 62 24. Support Multiple Lists in a ComboBox 64 25. Create Validation Lists That Change Based on a Selection from Another List 66 26. Use Replace... to Remove Unwanted Characters 68 27. Convert Text Numbers to Real Numbers 68 28. Extract the Numeric Portion of a Cell Entry 70 29. Customize Cell Comments 71 30. Sort by More Than Three Columns 73 31. Random Sorting 74 32. Manipulate Data with the Advanced Filter 75 33. Create Custom Number Formats 79 34. Add More Levels of Undo to Excel for Windows 84 35. Create Custom Lists 84 36. Boldface Excel Subtotals 85 37. Convert Excel Formulas and Functions to Values 89 38. Automatically Add Data to a Validation List 91 39. Hack Excel’s Date and Time Features 94 40. Enable Grouping and Outlining on a Protected Worksheet 98 41. Prevent Blanks/Missing Fields in a Table 100 42. Provide Decreasing Data Validation Lists 101 43. Add a Custom List to the Fill Handle 102 Chapter 3. Naming Hacks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 44. Address Data by Name 105 45. Use the Same Name for Ranges on Different Worksheets 106 46. Create Custom Functions Using Names 108 47. Create Ranges That Expand and Contract 112 48. Nest Dynamic Ranges for Maximum Flexibility 118 49. Identify Named Ranges on a Worksheet 121 Contents | ix Chapter 4. Hacking PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 50. PivotTables: A Hack in Themselves 124 51. Share PivotTables but Not Their Data 129 52. Automate PivotTable Creation 131 53. Move PivotTable Grand Totals 135 54. Efficiently Pivot Another Workbook’s Data 137 Chapter 5. Charting Hacks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 55. Explode a Single Slice from a Pie Chart 140 56. Create Two Sets of Slices in One Pie Chart 142 57. Create Charts That Adjust to Data 144 58. Interact with Your Charts Using Custom Controls 148 59. Four Quick Ways to Update Your Charts 152 60. Hack Together a Simple Thermometer Chart 157 61. Create a Column Chart with Variable Widths and Heights 160 62. Create a Speedometer Chart 164 63. Link Chart Text Elements to a Cell 171 64. Hack Chart Data So That Empty or FALSE Formula Cells Are Not Plotted 173 65. Add a Directional Arrow to the End of a Line Series 175 66. Place an Arrow on the End of a Horizontal (X) Axis 177 67. Correct Narrow Columns When Using Dates 180 68. Position Axis Labels 181 69. Tornado Chart 184 70. Gauge Chart 186 71. Conditional Highlighting Axis Labels 188 72. Create Totals on a Stacked Column Chart 190 Chapter 6. Hacking Formulas and Functions . . . . . . . . . . . . . . . . . . . . . . . . 193 73. Add Descriptive Text to Your Formulas 193 74. Move Relative Formulas Without Changing References 194 75. Compare Two Excel Ranges 195 76. Fill All Blank Cells in a List 197 77. Make Your Formulas Increment by Rows When You Copy Across Columns 199 78. Convert Dates to Excel Formatted Dates 202 x | Contents 79. Sum or Count Cells While Avoiding Error Values 203 80. Reduce the Impact of Volatile Functions on Recalculation 205 81. Count Only One Instance of Each Entry in a List 206 82. Sum Every Second, Third, or Nth Row or Cell 208 83. Find the Nth Occurrence of a Value 210 84. Make the Excel Subtotal Function Dynamic 212 85. Add Date Extensions 214 86. Convert Numbers with the Negative Sign on the Right to Excel Numbers 215 87. Display Negative Time Values 217 88. Use the VLOOKUP Function Across Multiple Tables 219 89. Show Total Time As Days, Hours, and Minutes 221 90. Determine the Number of Specified Days in Any Month 222 91. Construct Mega-Formulas 224 92. Hack Mega-Formulas that Reference Other Workbooks 226 93. Hack One of Excel’s Database Functions to Take the Place of Many Functions 227 94. Extract Specified Words from a Text String 233 95. Count Words in a Cell or Range of Cells 234 96. Return a Worksheet Name to a Cell 236 97. Sum Cells with Multiple Criteria 239 98. Count Cells with Multiple Criteria 243 99. Calculate a Sliding Tax Scale 246 100. Add/Subtract Months from a Date 251 101. Find the Last Day of Any Given Month 253 102. Calculate a Person’s Age 255 103. Return the Weekday of a Date 256 104. Evaluate a Text Equation 258 105. Lookup from Within a Cell 259 Chapter 7. Macro Hacks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263 106. Speed Up Code While Halting Screen Flicker 263 107. Run a Macro at a Set Time 264 108. Use CodeNames to Reference Sheets in Excel Workbooks 266 109. Connect Buttons to Macros Easily 267 110. Create a Workbook Splash Screen 268 Contents | xi 111. Display a “Please Wait” Message 270 112. Have a Cell Ticked or Unticked upon Selection 271 113. Count or Sum Cells That Have a Specified Fill Color 273 114. Add the Microsoft Excel Calendar Control to Any Excel Workbook 274 115. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop 276 116. Retrieve a Workbook’s Name and Path 279 117. Get Around Excel’s Three-Criteria Limit for Conditional Formatting 280 118. Run Procedures on Protected Worksheets 282 119. Distribute Macros 283 120. Delete Rows Based on a Condition 289 121. Track and Report Changes in Excel 293 122. Automatically Add Date/Time to a Cell upon Entry 297 123. Create a List of Workbook Hyperlinks 298 124. Advanced Find 300 125. Find a Number Between Two Numbers 306 126. Convert Formula References from Relative to Absolute 310 127. Name a Workbook with the Text in a Cell 315 128. Hide and Restore Toolbars in Excel 316 129. Sort Worksheets 319 130. Password-Protect a Worksheet from Viewing 320 131. Change Text to Upper- or Proper Case 322 132. Force Text to Upper- or Proper Case 324 133. Prevent Case Sensitivity in VBA Code 328 134. Display AutoFilter Criteria 329 Chapter 8. Cross-Application Hacks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331 135. Import Data from Access 2007 into Excel 2007 331 136. Retrieve Data from Closed Workbooks 336 137. Automate Word from Excel 344 138. Automate Outlook from Excel 349 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355 xiii 0 Credits About the Authors David and Raina Hawley provide business applications, software, develop- ment, consultancy, training, and tutoring in all aspects of Excel and VBA for Excel through OzGrid Business Applications in Western Australia. David Hawley has spent the last 15 years creating business applications using Excel and VBA for Excel on a day-to-day basis. He produces a monthly newsletter containing information on the use of Excel and VBA for Excel. He runs and maintains one of the largest Excel forums in the world on the OzGrid web site. Raina Hawley lectures in industry and in the college education system, and is a registered workplace assessor. Raina runs the OzGrid office, administra- tion, consultancy, development, and training side of the business, and works in Excel solutions alongside her husband. David and Raina offer hundreds of Excel Add-Ins and business software designed for data analysis in all industry areas through their web site at http://www.ozgrid.com. The web site contains over 50,000 pages of free Excel information. They live in Bunbury, Western Australia, with their two children. Contributors The following people contributed their hacks, writing, and inspiration to this book: • Andy Pope is a programmer working in London. He has been using computers since the mid ’80s. His current role involves writing custom- ized solutions for reporting projects utilizing the MS Office products via VBA. Andy also runs his own web site (http://www.andypope.info). His xiv | Credits contributions to the Excel community have been recognized by Microsoft, which has awarded Andy with MVP status for the past four years. • Dennis Wallentin has been working as an independent Excel consultant since the late ’80s. He utilizes MS Excel and other tools to develop pro- fessional solutions for all sizes of companies, including the public sector both in Sweden and internationally. He is currently focused on Visual Studio Tools for Office System (VSTO). Dennis has a Masters in Busi- ness and Management Accounting. He runs an English web site (http:// www.excelkb.com) and a blog (http://xldennis.wordpress.com). Acknowledgments First and foremost, we would like to thank our parents, Walter and Beryl Fenlon and Mike and Marlene Hawley, for without their love and support, we never would have made it through. Thanks must also go to the team at O’Reilly, first and foremost Brian Saw- yer, for all the hard work that he has put into this book. Andy Pope and Dennis Wallentin must be thanked also for the hacks they contributed, and we have to mention all the visitors to our web site and forum, who helped us to identify some of the most common issues that people face. We would also like to say a special thanks to all moderators and Oz MVPs on our free Q/A forum who share their time and knowledge in such an unselfish way. Finally, we must thank Aleisha and Kate, as always, our inspi- ration. Their understanding and extra efforts to be good while the book was in progress will be remembered!! xv 0 Preface Millions of Microsoft Excel users are busy creating and sharing spread- sheets every day. Indeed, the spreadsheet has grown from a powerful conve- nience to a transformative foundation for many businesses, driving decision- making around the planet. Although Excel is a critical tool, many Excel users knowonly about a subset of its functionality. They utilize the pieces they need, often reusing more complex pieces from existing templates, and don’t dive too deeply into everything Excel has to offer. Odds are good that no single user actually needs every feature in Excel, so this approach is pretty reasonable. At the same time, though, it means a lot of people never get far enough along the learning curve to see the techniques they can use to make their work much easier. With the release of MS Office 2007 comes a newversion of Excel. There are many changes with Excel 2007, the most obvious being the new user inter- face. The introduction of the ribbon provides a results-oriented interface that presents tools when you need them, in a clear and organized fashion. The size of a spreadsheet has also been greatly increased, with the number of col- umns now well over 16,000 and the number of rows over 1,000,000. The total amount of memory that Excel can use has also been increased and is limited only by the maximum available memory Windows will allow on your PC. Other improvements include easier use of PivotTables, conditional format- ting and named ranges, live visual previews, predefined style galleries, table formats, and SmartArt graphics allowing you to use more complicated graphics in your spreadsheets. As most of us are usually required to work across a range of applications, share workbooks, and connect with the Web, Excel 2007 makes this much more user-friendly and easy to manage. xvi | Preface In this book, we have again used real-life situations for the content. Excel 2007 has been used as a base for almost all of the hacks, although most of the hacks can be used in previous versions as well and a few are specific to earlier versions. Differences are highlighted in the text and most of the old menu items can still be found, just in a different place and possibly named slightly differently. Why Excel Hacks? Although it’s possible to accomplish an enormous amount of work using a relatively simple subset of Excel’s capabilities, the software offers a lot of powerful techniques that can leapfrog your work beyond the ordinary with- out requiring that you spend years using and studying Excel. However, most people focus on the content they create—data and formulas, with the occa- sional chart—so moving to more advanced levels of Excel usage seems difficult. There are lots of ways to take advantage of Excel’s capabilities to greatly extend your ability to create great spreadsheets, but that don’t require years of study. These tools, or hacks—quick and dirty solutions to problems, or clever ways of doing things—were created by Excel users looking for simple solutions to complex issues. The hacks in this book are designed to show you what’s possible and how to make them work immediately. You can benefit from these hacks in two important ways. First, you can use the hacks directly as you build and improve your spreadsheets. Second, by studying the hacks and possibly learning a little Visual Basic for Applica- tions (VBA) code, you can customize the hacks to meet your needs precisely. Getting and Using the Hacks To save you the time and effort of typing scripts and spreadsheets by hand, all the hacks (except those that are only a fewlines long or use only the GUI) are available for download from the authors’ web site at http://www.ozgrid.com/ BookExamples/excel-hack2-examples.htm. You’ll undoubtedly want to cut and paste from the examples and modify their contents to make them fit your spreadsheets more precisely. Excel spreadsheets are tremendously diverse, and you’ll want to change things to make them fit your work. How to Use This Book Although this book is divided into chapters, as described in the following section, you can use it in a variety of different ways. One approach is to Preface | xvii think of the book as a toolbox and start by becoming familiar with the tools in each chapter. Then, when a need arises or a problem occurs, you can sim- ply use the right tool for the job. Or, you might decide to browse through the book or read it from cover to cover, studying the procedures and scripts to learn more about Excel. Some of the hacks are helpful in this area because they contain tutorials about complex subjects or well-documented scripts. You also might pick one chapter and see what you find useful to your cur- rent situation or what you might find helpful in the future. How This Book Is Organized Whichever way you choose to use this book, you will probably want to familiarize yourself with the contents first, so here’s a brief synopsis of each chapter and what you’ll find: Chapter 1, Reducing Workbook and Worksheet Frustration Workbooks and worksheets are the primary interface to data in Excel, but sometimes this set of giant open grids doesn’t do precisely what you want. These hacks enable you to manage how users interact with work- sheets, help you find and highlight information, and teach you howto deal with debris and corruption. Chapter 2, Hacking Excel’s Built-in Features Excel includes many built-in features for analyzing and managing data. However, these features often have limitations. The hacks in this chap- ter enable you to extend and automate these features, moving beyond the limited tasks they were designed to perform originally. Chapter 3, Naming Hacks Although cell references such as A2 and IV284:IN1237 are certainly use- ful, as spreadsheets become larger, it’s often easier to reference informa- tion by name. These hacks showyou not only howto name cells and ranges, but also howto create names that adapt to the data in your spreadsheet. Chapter 4, Hacking PivotTables For many Excel users, PivotTables already seem like a complicated but magical hack. The hacks in this chapter teach you howto get the most out of PivotTables by showing you how to extend them and avoid the problems that make them frustrating. Chapter 5, Charting Hacks Excel’s built-in charting capabilities are very useful, but they don’t always provide the best method for viewing spreadsheet data. These hacks teach you howto tweak and combine Excel’s built-in charting capabilities so that you can create customized charts. xviii | Preface Chapter 6, Hacking Formulas and Functions Formulas and functions are at the heart of most spreadsheets, but some- times the way Excel handles them just isn’t quite what you want. These hacks cover subjects ranging from moving formulas around to dealing with datatype issues to improving recalculation time. Chapter 7, Macro Hacks Macros (and VBA) are Excel’s escape hatch, enabling you to build spreadsheets that go well beyond Excel’s own capabilities or develop spreadsheets that look more like programs. These hacks help you make the most of macros, from managing them to using them to extend other features. Chapter 8, Cross-Application Hacks Although most spreadsheets are self-contained, this chapter shows you how you can work with other Microsoft Office applications to get infor- mation into and out of your spreadsheets and into and out of other programs. Windows, Macintosh, and Earlier Excel Versions The hacks in this book were written for Excel 2007 and were tested on pre- vious versions of Excel for Windows and on a Macintosh using Excel 2004. Where steps or menu options differ, the main text shows how to accom- plish the task in Excel 2007, with instructions for “pre-2007” called out in notes or parentheses. Most of the differences between the Windows and Mac platform versions are cosmetic, and most involve changes to key combinations and the occa- sional menu. Where the key combinations differ, they are written with the Windows modifier first, as in Alt/Command(c)-Q, which means Alt-Q for Windows and c-Q on the Macintosh. There are a fewcases, especially in the Visual Basic Editor (VBE), where the interfaces look different and have different menu choices, and these are explained on first encounter. There are also a few Windows-only hacks, using the Windows registry and other features that are supported only on Windows versions of Excel. These are noted in the text. Macintosh users with one-button mice should also note that holding down the Control key while clicking is the equivalent of right-clicking. (Macin- tosh users with two or more buttons can just right-click.) Recent models of Apple MacBook and MacBook Pros allowyou to specify a right-click by holding two fingers on the trackpad and clicking. You must enable this in System Preferences. Preface | xix Most of the hacks should work with any version of Excel from Excel 97 onward; the text will indicate when this isn’t the case. Whenever possible, screenshots were taken using Excel 2007, but the figures are not an indica- tor of which hacks work with which versions. Conventions Used in This Book The following typographical conventions are used in this book: Plain text Indicates cell identifiers, named ranges, menu titles, menu options, menu buttons, and keyboard accelerators (such as Alt and Ctrl). Italic Indicates newterms, URLs, email addresses, filenames, file extensions, pathnames, directories, and variables in text. Constant width Used for commands, options, switches, variables, attributes, keys, func- tions, types, classes, namespaces, methods, modules, properties, param- eters, values, objects, events, event handlers, XML tags, HTML tags, macros, the contents of files, and the output from commands. Constant width bold Used to showcommands or other text that should be typed literally by the user, as well as to emphasize important lines of code. Constant width italic Used in examples, tables, and commands to showtext that should be replaced with user-supplied values. ↵ A carriage return (↵) at the end of a line of code is used to denote an unnatural line break—that is, you should not enter these as two lines of code, but as one continuous line. Multiple lines are used in these cases due to page width constraints. You should pay special attention to notes set apart from the text with the following icons: This icon signifies a tip, suggestion, or general note. This icon indicates a warning or caution. xx | Preface The thermometer icons, found next to each hack, indicate the relative com- plexity of the hack: The following icons, found below each hack, indicate which versions of Excel are compatible with the hack: Works with all versions of Excel Works with Excel 2007 Works with versions of Excel prior to 2007 Works with Excel 2003 Works with Excel 2000 Using Code Examples This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD- ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission. We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Excel Hacks, Second Edition, by David and Raina Hawley. Copyright 2007 O’Reilly Media, Inc., 978-0-596-52834-8.” If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. beginner moderate expert Preface | xxi Safari® Enabled When you see a Safari® Enabled icon on the cover of your favorite technology book, that means the book is available online through the O’Reilly Network Safari Bookshelf. Safari offers a solution that’s better than e-books. It’s a virtual library that lets you easily search thousands of top tech books, cut and paste code sam- ples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com. How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-998-9938 (in the United States or Canada) 707-829-0515 (international or local) 707-829-0104 (fax) We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at: http://www.oreilly.com/catalog/9780596528348/ A collection of spreadsheet files for each individual hack is available at: http://www.ozgrid.com/BookExamples/excel-hack2-examples.htm Visit the official web site of the Hacks series of books: http://www.hackszine.com To comment or ask technical questions about this book, send email to: bookquestions@oreilly.com For more information about our books, conferences, Resource Centers, and the O’Reilly Network, see our web site at: http://www.oreilly.com 1 Chapter 1 C H A P T E R O N E Reducing Workbook and Worksheet Frustration Hacks 1–16 Excel users know that workbooks are a powerful metaphor. But many users are equally aware that dealing with workbooks can cause a huge number of snags. The hacks in this chapter will help you avoid some of these snags while taking advantage of some of the more effective but often overlooked ways in which you can control your workbooks. Before we leap into the hacks, though, it’s worth taking a quick look at some basics that will make it much easier to create effective hacks. Excel is a very powerful spreadsheet application, and you can do incredible things with it. Unfortunately, many people design their Excel spreadsheets with lit- tle foresight, making it difficult for them to reuse or update the spreadsheets they’ve so carefully built. In this section, we provide several tips you can fol- lowto ensure that you’re creating spreadsheets that are as efficient as possible. The 80/20 Rule Perhaps the most important rule to followwhen designing a spreadsheet is to take a long-term viewand never assume you will not need to add more data or formulas to your spreadsheet because chances are good that you will. With that in mind, you should spend about 80 percent of your time planning your spreadsheet and about 20 percent implementing it. Although this can seem extremely inefficient in the short run, we can assure you that the long-term gain will far outweigh the short-term pain and that the plan- ning gets easier after you’ve done it for a while. Remember that spread- sheets are about making it easy for users to get correct information, not just about presenting information that looks good only once. 2 | Chapter 1, Reducing Workbook and Worksheet Frustration Structural Tips Without a doubt, the number one mistake most Excel users make when cre- ating their spreadsheets is that they do not set up and lay out the data in the manner in which Excel and its features expect. Here are, in no particular order, some of the most common mistakes users make when setting up a spreadsheet: • Unnecessarily spreading data over many different workbooks • Unnecessarily spreading data over numerous worksheets • Unnecessarily spreading data over different tables • Having blank columns and rows in tables of data • Leaving blank cells for repeated data The first three items on the preceding list add up to one thing: you should always try to keep related data in one continuous table. Time and time again we see spreadsheets that do not follow this simple rule and thus are limited in their ability to take full advantage of some of Excel’s most powerful fea- tures, including PivotTables, subtotals, and worksheet formulas. In such scenarios, you can use these features to their full potential only when you’ve laid out your data in a very basic table. It is no coincidence that Excel spreadsheets can comprise 1,048,576 rows (65,536 pre-2007) but only 16,384 columns (256 pre-2007). With this in mind, you should set up tables with column headings going across the first rowof your table and related data laid out in a continuous manner directly underneath their appropriate headings. If you find you are repeating the same data over and over for two or more rows in one of these columns, resist the temptation to use blank cells to indicate repetition. Make sure your data is sorted whenever possible. Excel has a rich set of lookup and reference formulas, some of which require that your data be sorted in a logical order. Sorting also will speed the calculation process of many functions significantly. Formatting Tips Moving beyond structure, formatting also can cause problems. Although a spreadsheet should be easy to read and follow, this should rarely be at the expense of efficiency. We are big believers in “keeping it simple.” Far too many people spend tremendous amounts of time formatting their spread- sheets. Although they don’t necessarily realize it, this time frequently comes at the expense of efficiency. Often the overuse of formatting adds size to your workbook, and although your workbook might look like a work of art Chapter 1, Reducing Workbook and Worksheet Frustration | 3 to you, it might look terrible to someone else. Some very good universal col- ors to consider using in your spreadsheets are black, white, and gray. It is always a good idea to leave at least three blank rows above your table (at least three, preferably more). These can then be used for criteria for fea- tures such as Advanced Filter and Database functions. People also tinker with the alignment of cell data. By default, numbers in Excel are right-aligned and text is left-aligned, and there are good reasons to leave it this way. If you start changing this formatting, you will not be able to tell at a glance if the contents of a cell are text or numeric. It is very com- mon for people to reference cells, which look like numbers but in reality are text. If you have altered the default alignment, you will be left scratching your head. Perhaps headings are an exception to this rule. Format cells as text only when completely necessary. All data entered into cells formatted as text become text, even if you meant for them to be num- bers or dates. Worse still, any cell housing a formula that references a text- formatted cell also will be formatted as text. Generally, you do not want for- mula cells to be formatted as text! Merged cells can also cause problems. The Microsoft knowledge base is full of frequently encountered problems with merged cells. As a good alterna- tive, use “Center across selection,” found under Home ➝ Alignment Group. The arrow in the bottom right will display the Format dialog with the Align- ment tab active. Use the Horizontal drop-down to select Center Across Selection or right-click and choose Format Cells from the shortcut menu (pre-2007, Format ➝ Cells). Formula Tips Another enormous mistake users often make in Excel formulas is referenc- ing entire columns. This forces Excel to examine potentially thousands, if not millions, of cells it otherwise could have ignored. Assume, for example, that you have a table of data ranging from cell A1 to cell H1000. You might decide you want to use one or more of Excel’s lookup formulas to extract the required information. Because your table might continue to grow(as you add newdata), it is common to reference the entire table, incorporating all rows. In other words, your reference might look something like A:H, or possibly A1:H65536. You would use this refer- ence so that when new data is added to the table, it will be referenced in the formulas automatically. This is a very bad habit to form and you should almost always avoid it. You still can eliminate the need to constantly update your formula references to 4 | Chapter 1, Reducing Workbook and Worksheet Frustration incorporate newdata as it is added to a table by using dynamic named ranges. Another common problem with poorly designed spreadsheets is painfully slowrecalculation. Many people suggest that shifting calculation mode into Manual via the Office button ➝ Excel ➝ Formulas (pre-2007, Tools ➝ Options ➝ Calculations; Mac OS X, Excel ➝ Preferences ➝ Calculation) will solve this problem. However, this is generally very poor advice, fraught with potential disasters. A spreadsheet is all about formulas and calculations and the results they produce. If you are running a spreadsheet in manual calculation mode, sooner or later you will read some information from your spreadsheet that will not have been updated. Your formulas might be reflecting old values and not the updated values because when you go into manual calculation mode, you must force Excel to recalculate by pressing the F9 key (c-= on Mac OS X). However, it is very easy to forget to do this! Think of it this way. If your car brakes were rubbing and slowing down your car, would you dis- connect the brake pedal and rely on the hand brake instead of fixing the problem? Most of us wouldn’t dream of doing this, but many people don’t hesitate to put their spreadsheets into manual calculation mode. If you need to run your spreadsheet in manual calculation mode, you have a design problem. Address it properly and do not use a “Band-Aid” approach. Array formulas are another common cause of trouble. They are best suited to referencing single cells. If you use them to reference large ranges, do so as infrequently as possible. When large numbers of arrays reference large ranges, your workbook’s performance will suffer, sometimes to the point where it becomes unusable and you are forced to run your spreadsheet in manual calculation mode. Excel’s database functions provide many alternatives to array formulas, as discussed in “Sum or Count Cells While Avoiding Error Values” [Hack #79]. Also, the Excel Help offers some good examples on howyou can use these formulas on large tables of data to return results based on multiple criteria. Another alternative that is often overlooked is the use of Excel’s PivotTable feature, discussed in Chapter 4. Although PivotTables might seem very daunting when first encountered, we highly recommend that you familiarize yourself with this powerful Excel feature because once you master Pivot- Tables, you will wonder how you survived without them! At the end of the day, if you remember nothing else about spreadsheet design, remember that Excel works best when all related data is laid out in one continuous table. That should make the rest of your hacking much easier. Create a Personal View of Your Workbooks #1 Chapter 1, Reducing Workbook and Worksheet Frustration | 5 HACK H A C K #1 Create a Personal View of Your Workbooks Hack #1 Excel enables you to have multiple workbooks showing simultaneously, and to have a customized view of your workbooks arranged in different windows. Then you can save your view workspaces as .xlw files and use them when it suits you. Sometimes when working in Excel, you might need to have more than one workbook open on your screen to make it easier to use or view data from multiple workbooks. The next few paragraphs describe how to do this in a neat and organized way. First, open all the workbooks you will need. To open more than one workbook at a time, select the Office button ➝ Open…, press the Ctrl key (c key on the Mac) while selecting the workbooks you want to open, and then click Open (pre-2007, select File ➝ Open). From any of the workbooks (it doesn’t matter which one), select Windows ➝ View ➝ Arrange All (pre-2007, select Window ➝ Arrange). If “Windows of active workbook” is checked, uncheck it, and then select the window arrangement you prefer and click OK. If you select Tiled, you will be presented with your workbooks in a tiled fashion, as shown with blank workbooks in Figure 1-1. Selecting Horizontal gives you a viewof your workbooks in a single stack, one on top of the other, as in Figure 1-2. Checking the Vertical option will place all your open workbooks side by side, as shown in Figure 1-3. Finally, as shown in Figure 1-4, selecting the Cascade option will layer all open workbooks one on top of the other. Once your workbooks are displayed in your preferred view, you can easily move data between them (e.g., copy, paste, drag and drop). If you think you might want to return to a view you created, you can save this preferred viewas a workspace. To save a workspace, simply select Win- dows ➝ Save Workspace (pre-2007, File ➝ Save Workspace), enter the workspace’s filename in the File Name box, and click OK. When saving your workspace, the file extension will be .xlw rather than the standard .xlsx of Excel 2007. To restore your Excel workspace to one full window of a par- ticular workbook, just double-click the title bar (on the Mac, click the green 6 | Chapter 1, Reducing Workbook and Worksheet Frustration #1 Create a Personal View of Your Workbooks HACK Figure 1-1. Four workbooks in a tiled view Figure 1-2. Four workbooks in a horizontal view Create a Personal View of Your Workbooks #1 Chapter 1, Reducing Workbook and Worksheet Frustration | 7 HACK Figure 1-3. Four workbooks in a vertical view Figure 1-4. Four workbooks in a cascade view 8 | Chapter 1, Reducing Workbook and Worksheet Frustration #2 Enter Data into Multiple Worksheets Simultaneously HACK Zoom button in the upper left of the window) appearing on any one of your workbooks. You can also click the Maximize button on any of the windows in your workspace. Close your workbooks as usual when you’re finished. Whenever you need to open those same workbooks, simply open the .xlw file, and the view you initially set up will be magically restored for all work- books. If you need to open just one of those workbooks, open the file as usual. Any changes you make to the workbooks in the .xlw file will be saved automatically as you close the workspace as a whole, or you can save work- books individually. If you spend a small amount of time setting up some custom views for repet- itive tasks that require multiple open workbooks, you’ll find that these tasks become easier to manage. You might decide to use different views for differ- ent repetitive tasks, depending on what the task is or how you’re feeling that day. H A C K #2 Enter Data into Multiple Worksheets Simultaneously Hack #2 It’s fairly ordinary to need some data to be duplicated in multiple worksheets. You can use Excel’s tool for grouping so that data entered in one workbook can be entered into multiple worksheets simultaneously. But therer is also a quicker and more flexible approach that uses a couple of lines of Visual Basic for Applications (VBA) code. Excel’s built-in mechanism for making data go to multiple places at once is a feature called Group. It works by grouping the worksheets together so that they’re all linked within the workbook. Grouping Worksheets Manually To use the Group feature manually, simply click the sheet into which you will be entering the data, and press the Ctrl key (the c key on the Macin- tosh) while clicking the Name tabs of the worksheets where you want the data to go. When you enter data into any cells on your worksheet, they will be entered automatically in the other grouped worksheets. Mission accomplished. To ungroup your worksheets, either select one worksheet that is not part of the group, right-click any Name tab and select Ungroup Sheets, or Ctrl/c-click the sheets you added to the group. Enter Data into Multiple Worksheets Simultaneously #2 Chapter 1, Reducing Workbook and Worksheet Frustration | 9 HACK When your worksheets are grouped together, you can look up to the title bar and see the word Group in square brack- ets. This lets you knowyour worksheets are still grouped. Unless you have eagle eyes and a mind like a steel trap, how- ever, it is highly likely that you won’t notice this or that you’ll forget you have your worksheets grouped. For this rea- son, we gently suggest you ungroup your sheets as soon as you finish doing what you need to do. Although this method is easy, it means you need to remember to group and ungroup your sheets as needed or else you will inadvertently overtype data from another worksheet. For example, you might want the simultaneous entries to occur only when you are in a particular range of cells. However, simultaneous data entries will occur regardless of the cell you are in at the time. Grouping Worksheets Automatically You can overcome these shortcomings by using some very simple VBA code. For this code to work, it must reside within the private module for the Sheet object. To quickly go to the private module, right-click the Sheet Name tab and select ViewCode. You can then use one of Excel’s sheet events—which are events that take place within your worksheet, such as changing a cell, selecting a range, activating, deactivating, and so on—to move the code into the private module for the Sheet object. In most cases, you will be taken directly to the private mod- ule when you right-click on a workbook or worksheet and select ViewCode. You can confirm that you’re in the private module by looking at the state of the drop-down menu in the upper left of the code window—this window is usually labeled something like “Workbook - sheetname (Code)”. If the drop-down menu says “Workbook” or “Worksheet,” then you in the private module. If it says “(General),” change it before typing in the code. To enable grouping, first name the range of cells you want to have grouped so that the data shows automatically on other worksheets. Enter this code into the private module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("MyRange"), Target) Is Nothing Then 10 | Chapter 1, Reducing Workbook and Worksheet Frustration #2 Enter Data into Multiple Worksheets Simultaneously HACK 'Sheet5 has purposely been placed first as this will 'be the active sheet we will work from Sheets(Array("Sheet5", "Sheet3", "Sheet1")).Select Else Me.Select End If End Sub In this code, we used the named range MyRange. (If you aren’t familiar with named ranges, see “Address Data by Name” [Hack #44].) Change MyRange to the range name you are using on your worksheet. Also change the three sheet names in the code, as shown in Figure 1-5, to the sheet names you want to be grouped. When you’re done, either click the View Microsoft Excel tool, close the module window, or press Alt/c-Q to get back to Excel, then save your workbook. It is important to note that the first sheet name used in the array must be the sheet housing the code, and thus the worksheet on which you will enter the data. Once the code is in place, each time you select any cell on the worksheet, the code checks to see whether the cell you selected (the target) is within the range named MyRange. If it is, the code will automatically group the work- sheets you want grouped. If it isn’t, it will ungroup the sheets simply by acti- vating the sheet you are already on. The beauty of this hack is that there is no need to manually group the sheets and therefore run the risk of forget- ting to ungroup them. This approach can save lots of time and frustration. If you want the same data to appear on other sheets but not in the same cell addresses, use code like this: Private Sub worksheet_Change(ByVal Target As Range) If Not Intersect(Range("MyRange"), Target) Is Nothing Then With Range("MyRange") Figure 1-5. Code for automatically grouping worksheets Prevent Users from Performing Certain Actions #3 Chapter 1, Reducing Workbook and Worksheet Frustration | 11 HACK .Copy Destination:=Sheets("Sheet3").Range("A1") .Copy Destination:=Sheets("Sheet1").Range("D10") End With End If End Sub This code also needs to live within the private module of the Sheet object. Follow the steps described earlier in this hack to get it there. H A C K #3 Prevent Users from Performing Certain Actions Hack #3 Although Excel provides overall protection for workbooks and worksheets, this blunt instrument doesn’t provide limited privileges to users—unless you do some hacking. You can manage user interactions with your spreadsheets by monitoring and responding to events. Events, as the term suggests, are actions that occur as you work with your workbooks and worksheets. Some of the more com- mon events include opening a workbook, saving it, and closing it. You can tell Excel to run some Visual Basic code automatically when any one of these events is triggered. Users can bypass all these protections by disabling macros entirely. Click the Office button and choose Excel Options ➝ Trust Center ➝ Trust Center Settings and press the Macro Settings Button (pre-2007, Tools ➝ Macro ➝ Security). If their security is set to “Disable all macros with notification” (Medium in pre-2007 versions), they’ll be notified of macros in the workbook upon opening it and will be offered the opportunity to turn them off. A security setting of “Disable all macros without notification” (High in older versions) will simply turn them off automatically. On the other hand, if using the spreadsheet requires the use of macros, users might be more likely to have macros turned on. These hacks are a convenience and do not provide heavy-duty data security. On Mac OS X, you cannot control macro protection at this level of detail. Instead, you can select Excel ➝ Preferences ➝ Security and toggle the setting “Warn before opening a file that contains macros.” Preventing Save As... in a Workbook You can specify that any workbook be saved as read-only by choosing Office button ➝ Save ➝ Tools Button ➝ General Options and enabling the “Read- only recommended” checkbox (pre-2007, File ➝ Save As ➝ Tools [Options on the Mac] ➝ General options in the Save options dialog). Doing so can prevent a user from saving any changes he might make to the file, unless he saves it with a different name and/or in a different location. 12 | Chapter 1, Reducing Workbook and Worksheet Frustration #3 Prevent Users from Performing Certain Actions HACK Sometimes, however, you might want to prevent users from being able to save a copy of your workbook to another directory or folder with or with- out a different name. In other words, you want users to be able to save on top of the existing file and not save another copy elsewhere. This is particu- larly handy when more than one person is saving changes to a workbook because you do not end up with a number of different copies of the same workbook, saved with the same name in different folders. The Before Save event you’ll be using has existed since Excel 97. As its name suggests, this event occurs just before a workbook is saved, enabling you to catch the user before the fact, issue a warning, and stop Excel from saving. Before trying this at home, be sure to save your workbook first. Putting this code into place without having saved will prevent your workbook from ever saving. To insert the code, open your workbook and choose Developer ➝ Visual Basic, then select View ➝ Code, and double-click on ThisWorkbook in the Project Explorer (pre-2007, right-click the Excel icon immediately to the left of the File menu item on the worksheet menu bar, and select View Code, as shown in Figure 1-6). You might have to enable the Developer tab (not standard in Excel 2007) by selecting Office button ➝ Excel Options ➝ Popular, checking the option “ShowDeveloper tab in the Ribbon” and clicking OK. Figure 1-6. Quick access menu (in Excel 2003) to the private module for the workbook object Prevent Users from Performing Certain Actions #3 Chapter 1, Reducing Workbook and Worksheet Frustration | 13 HACK This shortcut isn’t available on the Mac. You’ll have to open the Visual Basic Editor (VBE) by pressing Option-F11, or by selecting Tools ➝ Macro ➝ Visual Basic Editor. Once you’re there, Ctrl-click or right-click This Workbook in the Projects window. Type the following code into the VBE, as shown in Figure 1-7, and press Alt/c-Q to get back to Excel proper, then save your workbook: Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim lReply As Long If SaveAsUI = True Then lReply = MsgBox("Sorry, you are not allowed to save this " & _ "workbook as another name. Do you wish to save this " & _ "workbook?", vbQuestion + vbOKCancel) Cancel = (lReply = vbCancel) If Cancel = False Then Me.Save Cancel = True End If End Sub Give it a whirl. Select Office button ➝ Save (pre-2007, File ➝ Save) and your workbook will save as expected. However, select Office button ➝ Save As (pre-2007, File ➝ Save As...) and you’ll be informed that you’re not allowed to save this workbook under any other filename, unless you’ve disabled macros. Note that when you save a workbook in Excel 2007 and it contains either macros or code, you will be prompted to save your workbook as an Excel macro-enabled workbook (*.xlsm) and will be unable to save in the standard Excel file format (*.xlsx). Figure 1-7. Code once it’s entered into the private module (ThisWorkbook) 14 | Chapter 1, Reducing Workbook and Worksheet Frustration #3 Prevent Users from Performing Certain Actions HACK Preventing Users from Printing a Workbook Perhaps you want to prevent users from printing your workbook—and probably having it end up in a recycling bin or left on a desk somewhere in plain sight. Using Excel’s Before Print event, you can stop them in their tracks. Enter the following code, as before, into the VBE: Private Sub workbook_BeforePrint(Cancel As Boolean) Cancel = True MsgBox "Sorry, you cannot Print from this workbook", vbInformation End Sub Press Alt/c-Q when you’re done entering the code to get back to Excel, then save your workbook. Now each time users try to print from this workbook, nothing will happen. The MsgBox line of code is optional, but it’s always a good idea to include it to at least inform users so that they do not hassle the IT department, saying there is a problem with their program! If you want to prevent users from printing only particular sheets in your workbook, use this similar code instead: Private Sub workbook_BeforePrint(Cancel As Boolean) Select Case ActiveSheet.Name Case "Sheet1", "Sheet2" Cancel = True MsgBox "Sorry, you cannot print this sheet from this workbook", _ vbInformation End Select End Sub Notice you’ve specified “Sheet1” and “Sheet2” as the only cases in which printing should be stopped. Of course, these can be the names of any sheets in your workbook; to add more sheets to the list, simply type a comma fol- lowed by the sheet name in quotation marks. If you need to prevent the printing of only one sheet, supply just that one name in quotes and drop the comma. Preventing Users from Inserting More Worksheets Excel lets you protect a workbook’s structure so that users cannot delete worksheets, rearrange the order in which they appear, rename them, and so forth. Sometimes, though, you want to prevent just the addition of more worksheets, while still allowing other structural alterations. The following code will get the job done: Private Sub Workbook_NewSheet(ByVal Sh As Object) Application.DisplayAlerts = False MsgBox "Sorry, you cannot add any more sheets to this workbook", _ vbInformation Prevent Seemingly Unnecessary Prompts #4 Chapter 1, Reducing Workbook and Worksheet Frustration | 15 HACK Sh.Delete Application.DisplayAlerts = True End Sub The code first displays the message box with the message and then immedi- ately deletes the newly added sheet when the user clicks OK from the mes- sage box. The use of Application.DisplayAlerts = False stops the standard Excel warning that asks users if they really want to delete the sheet. With this in place, users will be unable to add more worksheets to the workbook. Another way to prevent users from adding worksheets is to select Review ➝ Changes ➝ Protect Workbook, and then press the Protect Structure and Windows button (pre-2007, Tools ➝ Protection ➝ Protect Workbook..., ensure that the Structure checkbox is checked, and click OK). However, as mentioned at the beginning of this hack, Excel’s worksheet protection is a rather blunt instrument and will also prevent many other Excel features from working. H A C K #4 Prevent Seemingly Unnecessary Prompts Hack #4 Excel’s chattiness can get a little old, always prompting you to confirm actions you just asked it to perform. Quit the conversation and let Excel get back to the action. The types of prompts we are talking about are those that ask you whether you want to enable macros (when you do not have any), or whether you are sure you want to delete a worksheet. Here is how to get rid of the most com- mon prompts once and for all. Enabling Macros When You Don’t Have Any Excel’s memory is like a steel trap when it comes to remembering that you recorded a macro in your workbook. Unfortunately, its memory of macros persists even though you might have since deleted one or more macros via Developer ➝ Macros or Alt/Option-F8 (pre-2007, Tools ➝ Macro ➝ Mac- ros). Reopen the workbook and you’ll still be prompted to enable macros, even though there are none to enable. You’ll be prompted to enable macros only if your security level is set to “Disable all macros with notification” (Medium in pre-2007 versions). If it’s set to “Enable all macros” (Low in pre-2007 versions), macros are enabled without a peep; if it’s set to “Disable all macros without notification” (High in pre-2007 versions), macros are disabled automatically for your protection. 16 | Chapter 1, Reducing Workbook and Worksheet Frustration #4 Prevent Seemingly Unnecessary Prompts HACK When you record a macro, Excel inserts a Visual Basic module to hold your commands and home-brewed functions. Upon opening a workbook, Excel checks for the presence of modules, whether empty or macro-filled. Delet- ing a workbook’s macros deletes any code within the module, not the mod- ule itself—kind of like drinking the last of the milk, yet putting the empty carton back in the fridge. To avoid the unnecessary macro prompt, you need to remove the module. Here’s how to do that. Open the VBE by selecting Developer ➝ Visual Basic under Code options or by pressing Alt/Option-F11 (pre-2007, Tools ➝ Macro ➝ Visual Basic Edi- tor and select View ➝ Project Explorer). On the Macintosh and in Excel 2007, the Projects window is always open, so you don’t need to open the Project Explorer. You’ll see a window like the one shown in Figure 1-8. Find your workbook in the Project Explorer and click the plus sign (+) to its left to expose the workbook’s component parts, particularly the modules if they are not already showing. Right-click each module in turn and choose Remove Module from the context-sensitive menu. Decline the offer to export the modules. Before blithely removing modules that might contain useful code, double-click each module in turn to make certain you don’t need them. Press Alt/c-Q as usual to get back to Excel’s spreadsheet view. Figure 1-8. Project Explorer modules Prevent Seemingly Unnecessary Prompts #4 Chapter 1, Reducing Workbook and Worksheet Frustration | 17 HACK Prompting to Save Nonexistent Changes You might have noticed that sometimes simply opening a workbook and taking a look around is enough to trigger Excel to prompt you to save changes to your personal macro workbook—despite the fact that you’ve made no changes whatsoever. Whether you know it or not, you most likely have a volatile function within your personal macro workbook. A personal macro workbook is a hidden workbook created the first time you record a macro (Tools ➝ Macro ➝ Record NewMacro) and specify “Per- sonal Macro Workbook” in the “Store Macro in” drop-down menu. It is opened each time you use Excel. A volatile function (or formula) is one that automatically recalculates each time you do almost anything in Excel, including opening and closing either the workbook or the entire applica- tion. Two of the most common volatile functions are the Today( ) and Now( ) functions. So, although you might believe you’ve made no changes to the workbook at hand, those volatile functions running in the background might have. This counts as a change and triggers Excel’s prompt to save said invisible changes. If you want Excel to stop prompting you to save changes you didn’t make, you have a couple of options open to you. The most obvious is not to store volatile functions within your personal macro workbook in the first place, and to delete any volatile functions that are already there. Or, if you need volatile functions, you can use this rather simple snippet of code to circum- vent the check by tricking Excel into thinking your personal macro work- book has been saved the moment it opens: Private Sub Workbook_BeforeClose(Cancel As Boolean) Me.Saved = True End Sub This code must live in the private workbook module of your personal macro workbook. To get there from any workbook, select View ➝ Unhide under Windowoptions (pre-2007, Window ➝ Unhide), select Personal.xls from Unhide Workbook, and click OK. Visit the VBE and enter the aforemen- tioned code. Press Alt/c-Q to get back to Excel when you’re done. Of course, if you have a volatile function that you want to recalculate and you want to save the changes, you need to explicitly tell Excel to do so: Private Sub Workbook_BeforeClose(Cancel As Boolean) Me.Save End Sub This macro will save your personal macro workbook automatically each time it is opened. 18 | Chapter 1, Reducing Workbook and Worksheet Frustration #4 Prevent Seemingly Unnecessary Prompts HACK Stopping Excel’s Warning Prompts for Recorded Macros One of the many drawbacks of recorded macros is that, although they’re pretty good at mimicking just about any command, they tend to forget your responses to prompts. Delete a worksheet and you’re prompted for confir- mation; run a macro for the same and you’ll still be prompted. Let’s turn off those prompts. Select Developer ➝ Macros under Code options or Alt/Option-F8 (pre-2007, Tools ➝ Macro ➝ Macros) to bring up a list of your macros. Make sure “All Open Workbooks” is selected in the Macros In: box’s pull-down menu. Select the macro you’re interested in and click the Edit button. Put the cur- sor before the very first line of code—i.e., the first line without an apostro- phe in front of it—and prepend the following: Application.DisplayAlerts = False At the very end of your code, append the following: Application.DisplayAlerts = True Your macro should look something like this: Sub MyMacro( ) ' ' MyMacro Macro ' Deletes the Active worksheet ' ' Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub Note that you’ve turned alerts back on at the end of your macro to re-enable standard Excel prompts while working in Excel. Leave this out, and you’ll see no alerts at all, not even those that might have been good to include. If your macro does not complete for any reason—a runtime error, for instance—Excel might never get to the line of code that turns alerts back on. If this happens, it’s probably wise to quit and restart Excel to set things back to the way they were. Now you know how to use Excel without prompts. Be aware, though, that these prompts are there for a reason. Make sure you fully understand the purpose of a prompt before summarily turning it off. Hide Worksheets So That They Cannot Be Unhidden #5 Chapter 1, Reducing Workbook and Worksheet Frustration | 19 HACK H A C K #5 Hide Worksheets So That They Cannot Be UnhiddenHack #5 Sometimes you want a place for information that users can’t read or modify. Build a backstage into your workbook, a place to keep data, formulas, and other minutiae consumed by, but not seen in, your sheets. A useful practice when setting up a new Excel workbook is to reserve one worksheet for storing information users do not need to see: formula calcula- tions, data validation, lists, useful variables and special values, sensitive data, and so forth. Although you can hide the sheet by selecting View ➝ Hide under Windowoptions (pre-2007, Format ➝ Sheet ➝ Hide), it’s a good idea to ensure that users can’t unhide it by selecting View ➝ Unhide under the Window options (pre-2007, Format ➝ Sheet ➝ Unhide…). You can, of course, simply protect the worksheet. However, this still leaves it in full view—sensitive data, scary formulas, and all. Also, you can’t pro- tect a cell linked into any of the controls available to you from the Forms toolbar. Instead, we’ll fiddle with the worksheet’s Visible property, making it xlVeryHidden. Go to Developer ➝ Visual Basic or Alt/Option-F11 to get to the VBE (pre-2007, go to Tools ➝ Macro ➝ Visual Basic Editor and make sure the Project Explorer window is visible by selecting View ➝ Project Explorer). Find the name of your workbook within the Project Explorer and expand its hierarchy by clicking the + to the left of the workbook’s name. Expand the Microsoft Excel Objects folder within to reveal all your work- book’s worksheets. Select the sheet you want to hide from the Project Explorer and reveal its properties by selecting View ➝ Properties Window(or by pressing F4). Make sure the Alphabetic tab is selected, and look for the Visible property at the very bottom. Click the value box on the right associated with the Visible property and select the last option, 2 - xlSheetVeryHidden, as shown in Figure 1-9. Press Alt/c-Q to return to Excel, then save your changes. The sheet will no longer be visible via the Excel interface and won’t appear as a choice under View ➝ Unhide under Windowoptions (pre-2007, Format ➝ Sheet ➝ Unhide…). Once you have selected 2 - xlSheetVeryHidden from the Properties window, it might appear as though your selection had no effect. This visual bug sometimes occurs and shouldn’t concern you; if the sheet no longer appears when you select View ➝ Unhide under Windowoptions (pre- 2007, Format ➝ Sheet ➝ Unhide…) you knowit had the desired effect. 20 | Chapter 1, Reducing Workbook and Worksheet Frustration #5 Hide Worksheets So That They Cannot Be Unhidden HACK To reverse the process, simply followthe preceding steps, this time selecting -1 - xlSheetVisible. Figure 1-9. Properties window of a worksheet having its visible property set to 2 - xlSheetVeryHidden Customize the Templates Dialog and Default Workbook #6 Chapter 1, Reducing Workbook and Worksheet Frustration | 21 HACK H A C K #6 Customize the Templates Dialog and Default Workbook Hack #6 If you tend to perform the same tasks or use the same spreadsheet layouts again and again, you can build your own Template tab into Excel’s standard Insert Template dialog to provide a quick starting point. Imagine you have a spreadsheet containing days of the year and formulas summarizing various data for the days. You have formatted this spreadsheet beautifully with your company colors, logo, and required formulas, and you need to use it on a daily basis. Instead of reinventing the wheel (or copying and deleting what you don’t need) each day, you can save yourself a lot of time and trouble by creating a template. Excel’s worksheet and workbook templates provide you with a running start on your next project, enabling you to skip the initial setup, formatting, for- mula building, and so on. Saving a template worksheet simply means open- ing a new workbook, deleting all but one worksheet, and then creating the basic template you will be using. Once you’re finished, select Office button ➝ Save As… (pre-2007, File ➝ Save As…) and choose Excel Template (Tem- plate on Mac OS X) from the dialog’s Save As Type (Format on Mac OS X) drop-down list. If your template is to be a workbook template—i.e., it will contain more than one worksheet—again add a new workbook, make all the necessary changes, select Office button ➝ Save As..., and save as an Excel template. Template in hand, you can create a clone at any time by either selecting the Office button ➝ New(pre-2007, File ➝ New…; File ➝ Project Gallery on the Mac) and selecting a workbook template, or by right-clicking the Work- sheet tab and selecting Insert... from the context sensitive menu to insert a newworksheet from a template. Wouldn’t it be nice, though, to have those templates available to you right from Excel’s standard Insert Template dia- log, or to set your preferred workbook as the default? You can, by creating your own Template tab. This hack assumes you have a single installation of Excel running on your computer. If you have multiple copies or versions of Excel installed, this may not work. Creating Your Own Template Tab If you have a slew of templates—workbooks, worksheets, or both—that you use on a regular basis, you can group them together to make it easier for you to manage them. 22 | Chapter 1, Reducing Workbook and Worksheet Frustration #6 Customize the Templates Dialog and Default Workbook HACK From within any workbook, choose the Office button ➝ Save As… (pre- 2007, File ➝ Save As…). Then, from the Files of Type pop-up menu, select Excel Template (.xltx); for older versions, select Template (.xlt). By default Excel will select the standard Templates folder in which all your home- grown templates are kept. If you want to create tabs in which to store your templates, create a sub- folder by using the New Folder button. On Mac OS X, Excel 2004 defaults to your My Templates directory for saving newtemplates, so this step is not needed. Now, go to the Office button, select New, and click the “My templates” option button (for Excel 2000 and above, choose General Templates from the NewWorkbook dialog that will appear; for older versions, select File ➝ New… on the worksheet menu bar). You should now see the tab you cre- ated (named Ozgrid in the screen shot in Figure 1-10) on the dialog floating over your screen. You also should nowsee your Template workbooks and worksheets, as long as you saved them to this folder. In Excel 2007, as a default if you do not create any tabs, your templates are stored under “My templates” in the My Tem- plates tab. Figure 1-10. The Templates dialog Create an Index of Sheets in Your Workbook #7 Chapter 1, Reducing Workbook and Worksheet Frustration | 23 HACK Using a Custom Default Workbook Starting Excel opens a blank default workbook called Book1 containing three blank worksheets. This is fine and dandy if you want a clean slate each time you start Excel. If you’re like us, however, you tend to favor one work- book over the others. So, for us, opening Excel involves dismissing the default workbook and searching for our regular workbook. It sure would be handy to have that favored workbook open at the outset, ready for action. To do so, save your default workbook (template) in the XLSTART folder (generally found in C:\Documents and Settings\Owner\Application Data\ Microsoft\Excel\XLSTART in Windows, and in Applications/Microsoft Office 2004/Office/Startup/Excel under Mac OS X). Once you have done this, Excel will automatically use whichever workbook(s) you have in there as the default. The XLSTART folder is where your personal macro work- book is created and saved automatically when you record a macro. The personal macro workbook is a hidden work- book. You also can have your own hidden workbooks open in the background if you want by opening the required workbook, selecting View ➝ Hide under Windowoptions (pre-2007, Window ➝ Hide), closing Excel, and clicking Yes to save changes to the workbook you just hid. Now place this workbook in your XLSTART folder. All the workbooks you hide and place within the XLSTART folder will open as hidden workbooks each time you start Excel. Don’t be tempted to place too many workbooks into this folder, especially large ones, as all of them will open when you start Excel. Too many open workbooks can greatly slow down Excel’s performance. Naturally, if you change your mind and decide to go back to a blank default workbook, simply remove the appropriate workbook or workbook tem- plate from the XLSTART folder. H A C K #7 Create an Index of Sheets in Your Workbook Hack #7 If you’ve spent much time in a workbook with many worksheets, you know how painful it can be to find a particular worksheet. An index sheet available to every worksheet is a navigational must-have. Using an index sheet will enable you to quickly and easily navigate through- out your workbook so that with one click of the mouse you will be taken exactly where you want to go, without fuss. You can create an index in a few 24 | Chapter 1, Reducing Workbook and Worksheet Frustration #7 Create an Index of Sheets in Your Workbook HACK ways: by hand, auto-generated by code, or as a context-sensitive menu option. Creating an Index Sheet by Hand You might be tempted to simply create the index by hand. Create a new worksheet, call it Index or the like, enter a list of all your worksheets’ names, and hyperlink each to the appropriate sheet by selecting Insert ➝ Hyperlink under Link options (pre-2007, Insert ➝ Hyperlink…) or by pressing Ctrl/c- K. Although this method is probably sufficient for limited instances in which you don’t have too many sheets and they won’t change often, you’ll be stuck maintaining your index by hand. Auto-Generate an Index Using VBA An alternative is to use VBA to auto-generate the index. The following code will automatically create a clickable, hyperlinked index of all the sheets you have in the workbook. The index is re-created each time the sheet that houses the code is activated. This code should live in the private module for the Sheet object. Insert a new worksheet into your workbook and name it something appropriate—Index, for instance. Right-click the index sheet’s tab and select ViewCode from the context menu or select Alt/Option-F11. Enter the following Visual Basic code: Private Sub Worksheet_Activate( ) Dim wSheet As Worksheet Dim l As Long l = 1 With Me .Columns(1).ClearContents .Cells(1, 1) = "INDEX" .Cells(1, 1).Name = "Index" End With For Each wSheet In Worksheets If wSheet.Name <> Me.Name Then l = l + 1 With wSheet .Range("A1").Name = "Start" & wSheet.Index .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _ "Index", TextToDisplay:="Back to Index" End With Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _ SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name End If Next wSheet End Sub Create an Index of Sheets in Your Workbook #7 Chapter 1, Reducing Workbook and Worksheet Frustration | 25 HACK Press Alt/c-Q to get back to your workbook and then save your changes. To make the code run, you will first need to deactivate your worksheet (select another sheet) and select the index sheet. Notice that in the same way you name a range of cells, the code names cell A1 on each sheet Start, plus a unique whole number representing the index number of the sheet. This ensures that A1 on each sheet has a different name. If A1 on your worksheet already has a name, you should consider changing any mention of A1 in the code to something more suitable—an unused cell anywhere on the sheet, for instance. Be aware that if you select the Office button ➝ Prepare ➝ Properties ➝ Document Properties ➝ Advanced Properties ([pre-2007, File ➝ Properties ➝ Summary) and enter a URL as a hyperlink base, the index created from the preceding code might not work. A hyperlink base is a path or URL that you want to use for all hyperlinks with the same base address that are inserted in the current document. Link to the Index from a Context Menu The third way of constructing an index is to add a link to the list of sheets as a context-menu item, keeping it just a right-click away. We’ll have that link open the standard workbook tabs command bar. You generally get to this command bar by right-clicking any of the sheet tab scroll arrows on the bot- tom left of any worksheet, as shown in Figure 1-11. To link that tab’s command bar to a right-click in any cell, enter the follow- ing code in the private module of ThisWorkbook: Private Sub Workbook_SheetBeforeRightClick _ (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Figure 1-11. Tabs command bar displayed by right-clicking the sheet scroll tabs 26 | Chapter 1, Reducing Workbook and Worksheet Frustration #8 Limit the Scrolling Range of Your Worksheet HACK Dim cCont As CommandBarButton On Error Resume Next Application.CommandBars("Cell").Controls("Sheet Index").Delete On Error GoTo 0 Set cCont = Application.CommandBars("Cell").Controls.Add _ (Type:=msoControlButton, Temporary:=True) With cCont .Caption = "Sheet Index" .OnAction = "IndexCode" End With End Sub Next, you’ll need to insert a standard module to house the IndexCode macro, called by the preceding code whenever the user right-clicks in a cell. It is vital that you use a standard module next, as placing the code in the same module as Workbook_SheetBeforeRightClick will mean Excel will not know where to find the macro called IndexCode. Select Insert ➝ Module and enter the following code: Sub IndexCode( ) Application.CommandBars("workbook Tabs").ShowPopup End Sub Press Alt/c-Q to get back to the Excel interface, then save your workbook. Now, right-click within any cell on any worksheet and you should see a new menu item called Sheet Index that will take you right to a list of sheets in the workbook. H A C K #8 Limit the Scrolling Range of Your Worksheet Hack #8 If you move around your spreadsheet a lot, or if you have data you don’t want readers to explore, you might find it convenient to limit the visible area of your spreadsheet to only that which has actual data. All Excel worksheets created in Excel 2007 have a column limit of 16,384 (256, A to IV, in previous versions) and a rowlimit of 1,048,576 (65,536 pre-2007). More often than not, your worksheet uses only a small percent- age of the cells available to you. A nice bit of spring cleaning limits the work- sheet’s scrollable area to just the part containing the data you want a user to see. You then can place data you do not want a user to see outside the scrol- lable area. Doing this also can make it less daunting to scroll around in a worksheet, as it is not uncommon for users to find themselves at row 50,000 and then start screaming that they are unable to find any data in a work- sheet. You can do this by hiding rows and columns, by specifying a valid range, or by activating only the used range. Limit the Scrolling Range of Your Worksheet #8 Chapter 1, Reducing Workbook and Worksheet Frustration | 27 HACK Hiding Rows and Columns The easiest way to establish boundaries is simply to hide all the unused col- umns and rows. On your sheet, locate the last row containing data and select the entire rowbelowit by clicking the rowlabel. Press the Ctrl and Shift keys while pressing the down arrow to select all rows beneath. Select Home ➝ Format ➝ Hide & Unhide ➝ Hide Rows or right click and select Hide (pre-2007, Format ➝ Row ➝ Hide) to hide them all. Do the same thing for unused columns; find the last-used column, select the entire column to the right of it, press the Ctrl and Shift keys while pressing the right arrow, and then again on the Home tab, select Format ➝ Hide & Unhide ➝ Hide Columns (pre-2007, Format ➝ Column ➝ Hide). If all went according to plan, your useful cells should be surrounded by a moat past which you can- not scroll. Specifying a Valid Range The second way to establish boundaries is to specify a valid range in the worksheet’s Properties window. Right-click the sheet’s tab at the bottom left of the window and select View Code from the context menu. If you are using a version of Excel before 2007, you may need to select View ➝ Project Explorer (Ctrl-R) on Windows to visit the Project Explorer (it is always visi- ble on the Mac). If the Properties window isn’t visible, press F4 to make it appear. Select the appropriate worksheet and visit the ScrollArea property in the Properties window. Now, from within the Project Explorer, select the worksheet you want the scroll area limited to, and then, from the Properties window (shown in Figure 1-12), go down to the ScrollArea property. In the associated value field to the right, enter the preferred boundaries of your worksheet— $A$1:$G$50, for instance. You will be unable to scroll outside the area you have specified. Unfortu- nately, Excel will not save this setting after you close the window. This means you need a very simple macro to automatically set the scroll area to the desired range by placing some code in the worksheet_Activate event. Right-click the Sheet Name tab on which the scroll area should be limited, select View Code, and then enter the following: Private Sub Worksheet_Activate ( ) Me.ScrollArea = "A1:G50" End Sub As usual, press Alt/c-Q to return to Excel proper and save your workbook. 28 | Chapter 1, Reducing Workbook and Worksheet Frustration #8 Limit the Scrolling Range of Your Worksheet HACK Although you will not see a visible clue, such as the moat of the first method, you won’t be able to scroll or select anything outside the specified area. Figure 1-12. Project Explorer Properties window Limit the Scrolling Range of Your Worksheet #8 Chapter 1, Reducing Workbook and Worksheet Frustration | 29 HACK Any macro that tries to select a range outside this scroll area (including selections of entire rows and columns) will no longer be able to do so. This is true particularly for recorded macros, as they often use selections. If your macros do select a range outside the scrollable area, you can easily modify any existing macros so that they are not limited to a specific scroll area while operating. Simply select View ➝ Macros under Macro options, or Developer ➝ Macros under Code options, or Alt/Option-F8 (pre-2007, Tools ➝ Macro ➝ Macros…), then locate your macro name, select it, and click Edit. Place the following line of code as the very first line of code: ActiveSheet.ScrollArea = "" As the very last line of code in your macro, place the following: ActiveSheet.ScrollArea = "$A$1:$G$50" So, your code should look something like this: Sub MyMacro( ) ' ' MyMacro Macro ' Macro recorded 19/9/2007 by OzGrid.com ' ' ActiveSheet.ScrollArea = "" Range("Z100").Select Selection.Font.Bold = True ActiveSheet.ScrollArea = "$A$1:$G$50" Sheets("Daily Budget").Select ActiveSheet.ScrollArea = "" Range ("T500").Select Selection.Font.Bold = False ActiveSheet.ScrollArea = "$A$1:$H$25" End Sub Our recorded macro selects cell Z100 and formats it to boldface. It then selects the worksheet named Daily Budget, selects cell T500 on that sheet, and un-bolds it. We added ActiveSheet.ScrollArea = "" so that any cell on the worksheet can be selected and then the scroll area can be set back to our desired range. When we select another worksheet (Daily Budget), we again allowthe code to select any cell on this worksheet and set the scroll area for this worksheet back to the desired range. 30 | Chapter 1, Reducing Workbook and Worksheet Frustration #9 Lock and Protect Cells Containing Formulas HACK Activating Only the Used Range A third method, the most flexible, automatically limits the scroll area to the used range on the worksheet within which you place the code. To use this method, right-click the Sheet Name tab on which you want the scroll area limited, select View Code, and enter the following code: Private Sub Worksheet_Activate( ) Me.ScrollArea = Range(Me.UsedRange, Me.UsedRange(2,2)).Address End Sub Nowpress Alt/c-Q or click the X in the top righthand corner to get back to Excel and save your workbook. The preceding macro will run automatically each time you activate the worksheet in which you placed it. However, you might encounter a prob- lem with this macro when you need to actually enter data outside the exist- ing used range. To avoid this problem, simply use a standard macro that will reset your scroll area back to the full sheet. Select Developer ➝ Visual Basic under Code options (pre-2007, Tools ➝ Macro ➝ Visual Basic Editor), then select Insert ➝ Module, and enter the following code: Sub ResetScrollArea( ) ActiveSheet.ScrollArea = "" End Sub Nowpress Alt/c-Q or click the X in the top-righthand corner to get back to Excel and save your workbook. If you want to, you can make your macro easier to run by assigning it to a shortcut key. Select the viewtab, then Macros, or press Alt/Option-F8 (pre- 2007, Tools ➝ Macro ➝ Macros…). Select ResetScrollArea (the name of your macro), click Options, and assign a shortcut key. Each time you need to add data outside the established bounds of your worksheet, run the ResetScrollArea macro to readjust the borders. After you run the macro, make any changes you were unable to make while the scroll area was limited. When you’re finished, activate any other worksheet and then activate the worksheet you just modified. Activation of the worksheet will cause the code to run and limit the scroll area to the desired range. H A C K #9 Lock and Protect Cells Containing Formulas Hack #9 You may want to let users change cells that contain data without providing them access to change formulas. You can keep cells containing formulas under lock and key without having to protect your entire sheet or workbook. When we create a spreadsheet, most of us need to use formulas of some sort. Sometimes, however, you might not want other users to tamper/delete/ overtype any formulas you included on your spreadsheet. The easiest and Lock and Protect Cells Containing Formulas #9 Chapter 1, Reducing Workbook and Worksheet Frustration | 31 HACK most common way of barring people from playing with your formulas is to protect your worksheet. However, protecting your worksheet doesn’t just prevent users from tampering with your formulas; it also stops users from entering anything at all. Sometimes you do not want to go this far. Three solutions are: locking the formula cells, using data-validation on the for- mula cells, and auto-toggling worksheet protection, although none of these solutions is bulletproof. Locking Formula Cells By default, all cells on a worksheet are locked; however, this has no effect unless worksheet protection has been applied. Here is a very easy way to apply worksheet protection so that only formula cells are locked and protected. Select all cells on your worksheet, either by pressing Ctrl/c-A or by clicking the square at the intersecting point of column A and row1. Then select Home ➝ Format ➝ Lock Cell (under Cells options, toggles to Lock/unlock cells), or if you prefer you can right-click and select Format Cells and on the Protection tab, uncheck the Locked checkbox to remove the tick (pre-2007, select Format ➝ ➝Cells ➝ Protection and uncheck the Locked checkbox to remove the tick). Click OK. Nowselect any single cell, select Home ➝ Find & Select ➝ Go To Special or Ctrl-G, or F5 and click Special (pre-2007, Edit ➝ Go To… ➝ Special). You’ll see a dialog box such as that in Figure 1-13. Figure 1-13. The Go To Special dialog 32 | Chapter 1, Reducing Workbook and Worksheet Frustration #9 Lock and Protect Cells Containing Formulas HACK Select Formulas from the Go To Special dialog and, if needed, limit the for- mulas to the subtypes underneath. Click OK. With only the formula cells selected, under Cells options choose Format ➝ Lock Cell (pre-2007, select Format ➝ Cells ➝ Protection, check the Locked checkbox to insert a tick), and click OK. Nowyou need to protect your sheet. Select Format ➝ Protect sheet under the Cells options (pre-2007, Tools ➝ Protection ➝ Protect Worksheet). Apply a password if required and click OK. The preceding method certainly saves a lot of time and eliminates possible errors locating formulas so that you can protect them. Unfortunately, it can also prevent users from using certain features, such as sorting, formatting changes, aligning text, and many others you might not be concerned with, even when in an unlocked cell. Data Validation Using data validation doesn’t rely on worksheet protection at all, and instead simply prevents accidental overtyping of formula cells. Data validation is far from bulletproof when it comes to pre- venting users from entering nonvalidated data into cells. Users can still paste into a validated cell any data they want and, in doing so, remove the validation from that cell unless the copied cell also contains data validation, in which case this validation would override the original validation. To see what we mean, select any single cell, press Ctrl-G or select Home ➝ Find & Select ➝ Go to Special, or press F5 and click Special (pre-2007, Edit ➝ Go To… ➝ Special). Nowselect Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK. With only the Formula cells selected, select Data ➝ Data Validation, under Data Tools options (pre-2007, Data ➝ Validation). Then, select the Settings page tab, choose Custom from the Allow: box, and in the Formula box, enter ="", as shown in Figure 1-14. Click OK. This method will prevent a user from accidentally overtyping into any for- mula cells, although, as stressed in the earlier warning, it is not a fully secure method and should be used only for accidental overtyping, etc. However, the big advantage to using this method is that all of Excel’s features are still usable on the worksheet. Lock and Protect Cells Containing Formulas #9 Chapter 1, Reducing Workbook and Worksheet Frustration | 33 HACK Auto-Toggle Worksheet Protection This method dynamically turns worksheet protection on and off, but will also enable you to use all of Excel’s features when you are in a cell that is not locked. To start, ensure that only the cells you want protected are locked and that all other cells are unlocked. Right-click the Sheet Name tab, select View Code from the pop-up menu, and enter the following code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Locked = True Then Me.Protect Password:="Secret" Else Me.Unprotect Password:="Secret" End If End Sub If no password is used, omit Password:="Secret". If a password is used, change the word Secret to your password. Press Alt/c-Q or click the X in the top-righthand corner to get back to Excel and save your workbook. If you’re worried about your users getting into the macro and disabling it, you can password-protect your macro by selecting Tools ➝ VBAProject Properties, going to the Pro- tection tab, selecting “Lock Project for Viewing”, and enter- ing a password. Now, each time you select a cell that is locked, your worksheet will auto- matically protect itself. The moment you select any cell that is not locked, your worksheet will unprotect itself. Figure 1-14. Validation formulas 34 | Chapter 1, Reducing Workbook and Worksheet Frustration #10 Find Duplicate Data Using Conditional Formatting HACK This hack doesn’t work perfectly, though it usually works well enough. The keyword used in the code, Target, will refer only to the cell that is active at the time of selection. For this reason, it is important to note that if a user selects a range of cells (with the active cell being an unlocked cell), it is possible for him to delete the entire selection because the target cell is unlocked and, therefore, the worksheet will automatically unprotect itself. H A C K #10 Find Duplicate Data Using Conditional Formatting Hack #10 Excel’s conditional formatting is generally used to identify values in particular ranges, but we can hack it to identify duplicated data within a list or table. The improved functionality in Conditional Formatting in Excel 2007 makes this hack suitable for prior versions only. People frequently have to identify duplicated data within a list or table, and doing this manually can be very time-consuming and error-prone. To make this job much easier, you can hack one of Excel’s standard features, Condi- tional Formatting. Take, for example, a table of data with a range of $A$1:$H$100. Select the top-left cell, A1, and drag it over and down to H100. It is important that A1 be the active cell in your selection, so don’t drag from H100 to A1. Select Format ➝ Conditional Formatting... and, in the Conditional Formatting dia- log box, select Formula Is from the top-left pop-up menu. In the field to its right, enter the following code: =COUNTIF($A$1:$H$100,A1)>1 Click the Format tab (that’s the Format button under Mac OS X), followed by the Patterns tab, and select a color you want applied to visually identify duplicate data. Click OK to return to the Conditional Formatting dialog box and click OK again to apply the formatting. All those cells containing duplicate data should be lit up like a Christmas tree in the color you chose, making it much easier to eyeball duplicate data and delete, move, or alter it as appropriate. It is vital to note that as A1 was the active cell in your selection, the cell address is a relative reference and is not absolute (unlike your table of data, $A$1:$H$100). By using conditional formatting in this way, Excel automati- cally knows to use the correct cell as the COUNTIF criterion. By this we mean that the conditional formatting formula in cell A1 will read as follows: =COUNTIF($A$1:$H$100,A1)>1 Find Data That Appears Two or More Times Using Conditional Formatting #11 Chapter 1, Reducing Workbook and Worksheet Frustration | 35 HACK while in cell A2, it will read: =COUNTIF($A$1:$H$100,A2)>1 in cell A3, it will read: =COUNTIF($A$1:$H$100,A3)>1 and so forth. H A C K #11 Find Data That Appears Two or More Times Using Conditional Formatting Hack #11 While Excel’s Conditional Formatting is vastly improved in Excel 2007 and can easily take care of duplicated data, it still does not offer the facility to identify data that appears two or more times. If you need to identify data that appears two or more times, you can use Conditional Formatting with as many conditions/rules as your system mem- ory will allow (note that you can use up to three different conditions only in versions pre-2007) and color-code each condition for visual identification. To do this, select cell A1 (the cell in the top-left corner) and drag it down to H100. Again, it is important that A1 is the active cell in your selection. Now, select Home ➝ Conditional Formatting ➝ NewRule under Styles options. For Select Rule Type, choose “Use a Formula to determine which cells to format” and then click in the white strip under “Format values where this formula is true.” Pre-2007, select Format ➝ Conditional Formatting… and, from the box containing the text Cell Value Is, select For- mula Is. Click in the white box to the right of Formula Is. Enter the following formula: =COUNTIF($A$1:$H$100,A1)>3 Click the Format button, select a color you want to apply to identify data that appears more than three times, and click OK; or for pre-2007 versions, go to the Patterns page tab, select a color, and click OK. Click NewRule (on the Mac, click Add>>) and repeat the previous steps, entering the following formula (pre-2007, use the Condition 2 box and select Formula Is) and selecting a different color this time: =COUNTIF($A$1:$H$100,A1)=3 36 | Chapter 1, Reducing Workbook and Worksheet Frustration #12 Tie Custom Toolbars to a Particular Workbook HACK Instead of retyping the formula, highlight it after you have entered it, then press Ctrl/c-C to copy, then paste where necessary by pressing Ctrl/c-V to paste, and then change >3 to =3. Click NewRule, repeat the steps a third time (pre-2007, from the Condition 3 box, select Formula Is), and add the following formula: =COUNTIF($A$1:$H$100,A1)=2 Again, select a different color from those previously chosen. You will have different cell colors depending on the number of times your data appears within your table of data. Again, it is vital to note that as A1 was the active cell in your selection, the cell address is a relative reference and is not absolute (unlike your table of data, $A$1:$H$100). By using conditional formatting in this way, Excel automatically knows to use the correct cell as the COUNTIF criterion. In Excel 2007, your Conditions are limited only by your sys- tem memory, whereas in pre-Excel 2007 versions, the limit is 3 Conditions. H A C K #12 Tie Custom Toolbars to a Particular Workbook Hack #12 Although most toolbars you build apply to just about any work you do, sometimes the functionality of a custom toolbar applies to only one workbook in particular. With this hack, you can tie custom toolbars to their respective workbooks. The Quick Access Toolbar options in Excel 2007 make this hack suitable for prior versions only. If you’ve ever created a custom toolbar, you have no doubt noticed that the toolbar is loaded and visible regardless of which workbook you have open. What if your custom toolbar contains recorded macros meant only for a spe- cific workbook? It’s probably best to tie special-purpose custom toolbars to the appropriate workbooks to reduce both clutter and possible confusion. You can do this by inserting some very simple code into the private module of the workbook. To get to this private module, right-click the Excel icon, which you’ll find at the top left of your screen, next to File, and select View Code. Tie Custom Toolbars to a Particular Workbook #12 Chapter 1, Reducing Workbook and Worksheet Frustration | 37 HACK This shortcut isn’t available on the Mac. You’ll have to open the Visual Basic Editor (VBE) by pressing Option-F11 or by selecting Tools ➝ Macro ➝ Visual Basic Editor. Once you’re there, Ctrl-click or right-click This Workbook in the Projects window. Then, enter this code: Private Sub Workbook_Activate( ) On Error Resume Next With Application.CommandBars("MyCustomToolbar") .Enabled = True .Visible = True End With On Error GoTo 0 End Sub Private Sub Workbook_Deactivate( ) On Error Resume Next Application.CommandBars("MyCustomToolbar").Enabled = False On Error GoTo 0 End Sub Change the text MyCustomToolbar to the name of your own custom toolbar. To get back to the Excel interface, close the module window or press Alt/c-Q, then save your workbook. Whenever you open or activate another work- book, your custom toolbar disappears and isn’t accessible. Reactivate the appropriate workbook, and poof! The toolbar’s back. You even can take this down a level, making the custom toolbar available only to a specific worksheet within the workbook. Right-click the Sheet Name tab of the sheet on which you want the toolbar to be accessible and select View Code. Enter this code: Private Sub Worksheet_Deactivate( ) On Error Resume Next Application.CommandBars("MyCustomToolbar").Enabled = False On Error GoTo 0 End Sub Private Sub Worksheet_Activate( ) On Error Resume Next With Application.CommandBars("MyCustomToolbar") .Enabled = True .Visible = True End With On Error GoTo 0 End Sub Now press Alt/c-Q or close the window to get back to Excel. 38 | Chapter 1, Reducing Workbook and Worksheet Frustration #13 Outsmart Excel’s Relative Reference Handler HACK The first procedure, Worksheet_Deactivate( ), will fire automatically each time you leave that particular worksheet to activate another one. The firing of the code changes the Enable property of your custom toolbar to False so that it cannot be seen or displayed. The second procedure is fired each time you activate the worksheet and sets the Enable property of your custom tool- bar to True so that it can be made visible. The line of code that reads Application.CommandBars("MyCustomToolbar").Visible = True simply dis- plays your custom toolbar again, so the user can see it. Switch worksheets and the toolbar is gone; switch back and it reappears like magic. H A C K #13 Outsmart Excel’s Relative Reference Handler Hack #13 In Excel, a formula reference can be either relative or absolute, but sometimes you want to move cells that use relative references without making the references absolute. Here’s how. When a formula needs to be made absolute, you use the dollar sign ($) in front of the column letter and/or rownumber of the cell reference, as in $A$1. Once you do this, no matter where you copy your formula, the for- mula will reference the same cells. Sometimes, however, you may have already set up a lot of formulas that contain not absolute references, but rel- ative references. You would usually do this so that when you copy the origi- nal cell formula down or across, the row and column references change accordingly. If you already set up your formulas using only relative references, or per- haps a mix of relative and absolute references, you can reproduce the same formulas in another range on the same worksheet, another sheet in the same workbook, or perhaps even another sheet in a different workbook. To do this without changing any range references inside the formulas, select the range of cells you want to copy and then select the Home tab ➝ Find & Select ➝ Replace (pre-2007, Edit ➝ Replace…). In the Find What: box, type an equals sign (=) and in the Replace With: box, type an ampersand (&). (Of course, these could be any symbols you are sure are not being used in any of the formulas.) Click Replace All. The equals sign in all the formulas on your worksheet will be replaced with the ampersand sign. You can nowsimply copy this range, paste it to its desired destination, select the range you just pasted, and select the Home tab ➝ Find & Select ➝ Replace (pre-2007, Edit ➝ Replace…). This time replace the ampersand sign with an equals sign (don’t forget to do this with the source range you just copied). Your formulas nowshould be referencing the same cell references as your originals.