| In the late 1990s, I read a whitepaper a market research report that found that most Excel worksheets use no formulas. That is, most Excel users employ Excel more as a storehouse of data than as a tool for analysis. That finding troubled me. Excel isn't designed as a means of storing or retrieving data. Yes, you can use Excel in that way and, according to the market research, many people do so. That's not how Excel's developers intended it to be used. They meant us to use Excel to analyze data, not to manage it. If you used Excel, you were expected to use functions like AVERAGE(), RATE(), and PI(), not to just stuff letters and numbers into worksheet cells. But it's the marketplace that decides how a product is used, not the design team. And the marketplace has decided to use Excel to store data, regardless of its designers' intentions. Okay, I can understand that. The marketplace finds that Excel is a good device for storing and arranging data, and the marketplace is usually right. I agree with the marketplace and I suspect you do too. Excel worksheets are a wonderful place to put data. So, this question arises: Given that we want to use Excel to store and manage data, what are the best ways to go about doing that? There's no good answer to that question, at least none that's simultaneously short and clear and informative. The answer depends in part on how much data you have to deal with, and in part on how you need to structure the data, and in part on whether you can use worksheet formulas to summarize and analyze the data. Excel can offer you a terrific way to store data, as most of its users have found. It's flexible enough to let you define how you want to store your data. You need not comply with structures that are forced on you by other applications, such as Access and SQL Server. Excel, for example, does not require that you put different records in different rows. It does not, as do other applications, require that you put different variables in different columns. This flexibility can be very handy, especially when the way that the data appears is important. But that flexibility comes at a cost. Suppose that you want to put mailing addresses into an Excel worksheet, as shown in Figure I.1. Figure I.1. This arrangement makes the most sense if you want to see how mailing labels might look. 
The data layout shown in Figure I.1 is a handy one. In particular, it mimics the way that you would want the information to appear on envelope labels. But, as usual, you pay for your convenience. For example, what if you wanted to know how many addresses were in California? Or in San Francisco? Or on Falcon Street in San Francisco? The layout used in Figure I.1 isn't a good basis for that sort of analysis or, for that matter, any sort of analysis. Although it's not a good basis, you can deal with it. The right formula can, for example, count the number of addresses, laid out as in Figure I.1, in any given state, in any given city, on any given street. This book shows you how to create those formulas. More importantly, this book shows you how to manage your data in ways that make it easier to analyze and summarize the information. For example, another possible layout of the data in an Excel worksheet appears in Figure I.2. Figure I.2. This layout is best if you want to summarize or otherwise manage your data. 
The difference between Figure I.1 and Figure I.2 underscores the flexibility that Excel offers you. It's great that you can decide to put a recipient's name in rows 1, 5, 9, and so on, as in Figure I.1, or to put them all in the same column, as in Figure I.2. Excel lets you decide. Other applications, database management systems in particular, don't let you make that sort of design decision. There are good reasons that database management systems are so persnickety, and those reasons also apply to Excel. When it comes to analyzing the data, it's much more efficient to lay it out as in Figure I.2 than I.1. But when it comes to viewing the data as individual records, layouts such as the one shown in Figure I.1 can be much more effective. So, how do you choose between the two? You might not have to choose. The right approach can give you the best of both layouts. If you manage your data correctly, you can have the convenience of layouts such as the one shown in Figure I.1 and the efficiency of layouts such as the one shown in Figure I.2. So, you can have your cake and eat it too, but first you need to make the right arrangements. You'll need to know how to use Excel's array formulas. You'll need to get acquainted with Excel's data management functions. Visual Basic for Applications is required in many cases, as are its near neighbors, Data Access Objects and ActiveX Data Objects. The intent of this book is to give you the tools you need to decide how to store your data using Excel, using a database management system such as Access and how best to implement the choice you make. That puts you in the best position to manage the data. | 