Starting with a Plan

Before you consider building a database in Excel, you need to plan how you want to structure the database. Whether you just think about the plan or write it down on paper, it's advisable to have a plan. That way, you'll save yourself a lot of time and effort because you are less likely to build a database that doesn't work for you.

Structuring Your Database

Consider these helpful questions and answers before structuring your database:

What is the size of the database going to be when I'm finished with it? Well, Excel gives you plenty of room on a worksheet. The size of the database can be as large as your worksheet, 256 columns by 65,536 rows.

What should I know about field names in relation to structuring my database? The field names must be placed in the first row of the database and must contain text. You cannot use values as field names. You can use a field name with a maximum of 255 characters ; however, you should try to use shorter names because you can manage the database columns more easily.

How should I handle the records in my database? Each record must have the same number of fields. But you don't have to fill in each field of the record.

How does Excel handle spaces in data that I enter in the database? Excel doesn't deal with spaces at all. First of all, you cannot use spaces in a field name, and you shouldn't use extra spaces in a record entry. That is, don't "pad" an entry with extra spaces at the beginning or end of an entry.

Do I need to be concerned with upper- and lowercase letters? Excel's answer to this question is no. You can use any combination of uppercase and lowercase letters in your field names and records. Excel ignores capitalization when sorting or searching a database.

Can you plan on using formulas to calculate data in your database? Sure you can. You can create computed fields that evaluate other fields in the database, such as a Total field that would be equal to the Cost field times the Quantity field.