• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL




About Excel

How to Use This Book

About Excel

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.

How to Use This Book

A good place to start is with bad choices. Chapter 1, “Misusing Excel as a Database Management Tool,” gives you the details of several horrid examples.

Chapter 2, “Excel's Data Management Features,” discusses ways to use Excel's worksheet functions to locate and rearrange data on the worksheet.

There are some ways to set up a worksheet—that is, to lay out its entries—that make it much easier for you to manage the data. Chapter 3, “Excel's Lists, Names, and Filters,” shows you how to arrange your data effectively.

Excel has a variety of ways for you to get data from other sources into your worksheets. Chapter 4, “Importing Data: An Overview,” walks you through one of the most powerful of these: external data ranges. This chapter also introduces Microsoft Query and establishing pivot tables based on imported data.

Chapter 5, “Using Microsoft Query,” goes much further into using Microsoft Query to acquire external data. You'll see how to connect external data tables together and use selection criteria to design exactly the data import you're after. You'll also see how to manage the external data range so that it refreshes itself automatically, fills down adjacent formulas, maintains password protection, and so on.

As good as Excel's data import capabilities are, there are a few tricks and traps to be aware of. Chapter 6, “Importing Data: Further Considerations,” discusses how to avoid null values, grouping fields in pivot tables, changing your criteria each time you run a query, and how to set up and refresh Web queries.

The remainder of Managing Data with Excel is concerned with automating the exchange of data between Excel and true relational databases. Especially when you're moving data out of Excel into another container, the most powerful methods involve Visual Basic for Applications, or VBA. Chapter 7, “VBA Essentials Reviewed,” uses lots of sample code to show you how to use loops, understand the object model, declare variables, establish With structures, and work with the macro recorder. All these techniques are discussed in terms of their use in managing data.

Chapter 8, “Opening Databases,” introduces two important libraries that you use in VBA so that your code will have direct access to structures in databases—tables, fields, records, queries, and so on. By using these libraries with VBA, you can manage databases entirely from the context of Excel.

If you're going to manage data in a database from inside Excel, it helps to know how to do it from a database management system. Chapter 9, “Managing Database Objects,” shows you how to create tables, fields, and queries directly, using the database management system's user interface.

With Chapter 9's review of managing data structures from inside a database as a basis, the next chapter shows you how to do the same thing from inside Excel. Chapter 10, “Defining Fields and Records with ActiveX Data Objects and Data Access Objects,” has plenty of examples of manipulating them using a combination of VBA and DAO, and of VBA and ADO.

Chapter 11, “Getting Data from Access and into Excel with ADO and DAO,” examines the most efficient ways to use VBA, ADO, and DAO to move data from a database and into an Excel worksheet. These techniques are especially important when the data can't just be brought back all at once, as with an external data range, but when your code needs to do additional work with the data. You're walked through the development of a lengthy block of VBA code that places the data retrieved from the database in precise locations on the worksheet.

Chapter 11 focuses on getting data into Excel from a database. Chapter 12, “Controlling a Database from Excel Using ADO and DAO,” looks at the other direction of data flow—from Excel to the database. You'll see how to add new records to database tables, edit existing records, and delete those you no longer need—all by using a combination of VBA and DAO or VBA and ADO.

Special Elements

There are several different types of information that we've included in this book to help you along.

Case Studies

A case study is a problem or situation that you might encounter in the course of your work with Excel—almost always, one that requires some extra ingenuity to deal with. All the case studies in this book come directly from situations I've encountered at my company's client sites. But because I really want to do more work for them, I'm not mentioning any names.

Notes, Tips, and Cautions


A note is just an extra little tidbit about the topic being discussed. You can easily skip over these, but you might miss a gold nugget!


A tip is the spot where I can relay my own experiences with Excel in the real world and offer suggestions and tricks to help you use Excel more effectively.


These are the ones you don't want to skip. The caution will help keep you out of common pitfalls or alert you to potential problems.

Cross References

You'll find helpful references to other parts of the book when a topic is covered in more than one way in different chapters or when there is related information to the discussion at hand.

→ A cross reference is formatted like this and points you to other useful areas in the book.


You'll find that we've employed some specific conventions to help you easily find what you are looking for and to distinguish certain elements from the rest of the text. The following list outlines those conventions:

  • Mono Font: Mono font is used on most of the code you'll find in this book. Whether it's a function name, VBA code, SQL statements, or any other type of code listing, you'll see it in the mono font.

  • Italic: When you see a word in italics that is to let you know that the word is a new term that is being defined in that location.

  • Bold: In numbered lists, we've bolded items such as menus, buttons, and check boxes so that you can easily pick out the items as you work through the steps.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint