Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

It is important to understand what data lists are and how you should create them. All too often, the structure and field names used in a data list are not given much thought. Not until hundreds or thousands of data records are created do problems arise. These problems might cause the data to become invalid, force the lists to be restructured, and require that someone enter the data records again from the beginning.

Although lists come in all shapes and sizes, data lists are unique because they are highly structured, sharing common field names and data values. For instance, a simple grocery list contains a number of items, but these items amount to little more than notes about product names and quantities. For some items, you might write down a product category rather than a specific product name—you know in your mind what you want to buy—but of course you don’t use the grocery list for hard-core data analysis.

When you create data lists, be sure of the following:

• All the records in the data list share the same field names. For instance, in one record, if you have the fields First Name, Middle Name, and Last Name, don’t use one Complete Name field for the next record to store the same type of data.

• All the data values for each field are consistent. For example, if the Total Sales field for one record has two decimal places, another record should not have four decimal places for the data in this field.

• All the data values have some type of data in each field, even if the real data value is unknown. For instance, if the data value for a particular field in a record is zero, use the number 0 in the field. If the data value is unknown, use the word Unknown for that data value. If a data value is left blank, it can sometimes be difficult to determine whether the value is zero or the data value was forgotten somehow.

• Only one list should appear on each worksheet. Techniques such as sorting, grouping, and filtering work best when they are restricted to single lists on single worksheets.

• Field names are descriptive but not verbose. For example, a field named First Name is more descriptive than a field named Field A. Likewise, a field named Sales Total might be just as easy to understand as a field named Sales Total That Appears At The Bottom Of The Sales Receipt.

• Fields are separated into their most basic parts. For instance, instead of a Complete Address field, create separate fields that are named Street Address, City, State (or Province or Region), and Zip (or Postal) Code. This makes data analysis tasks such as sorting and filtering much easier.