Hacks #1-15 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 little 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 follow to ensure that you're creating spreadsheets that are as efficient as possible. The 80/20 Rule Perhaps the most important rule to follow when designing a spreadsheet is to take a long-term view and 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% of your time planning your spreadsheet and about 20% 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 the planning gets easier after you've done it for a while. Remember that spreadsheets are about making it easy for users to get correct information, not just about presenting information that looks good only once. Structural Tips Without a doubt, the number one mistake most Excel users make when creating 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 features, 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 65,536 rows but only 256 columns. With this in mind, you should set up tables with column headings going across the first row of 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 omit the repeated data by using 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 spreadsheets. 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 to you, it might look terrible to someone else. Some very good universal colors 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 features 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 common 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 numbers 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 formula 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 alternative, use "Center across selection," found on the Alignment tab of the Format Cells dialog under the Horizontal tab. Formula Tips Another enormous mistake users often make in Excel formulas is referencing 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 new data), 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 reference 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 incorporate new data as it is added to a table by using dynamic named ranges, discussed in [Hack #42]). Another common problem with poorly designed spreadsheets is painfully slow recalculation. Many people suggest that shifting calculation mode into Manual via Tools  Options Calculations 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. 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 disconnect 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 apply 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 [Hack #66]. Also, the Excel Help offers some very good examples on how you 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 PivotTables, 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. | 