Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
Article Categories

Posts Tagged ‘Data Lists’

Build a Data List in Excel

imageI am among the first to point out that Excel is not a data manager. Yet, at the same time, Excel does have some useful features for working with data, especially small data collections. Such lists can be used directly for data analysis, for example, or indirectly as the data source for mail merges in Word.

Regardless of how the list will be used there are four fundamental rules for setting up an Excel data list:

  • The list should have a header row of unique names for each column in the list
  • There should be no blank columns in the list
  • There should be no blank rows in the list
  • Similar data should appear in one column

The last rule may be misunderstood because there is one other guideline for setting up any list. Data in any column should not be grouped. For example, in a name and address list, newcomers are tempted to list a person’s full name in a single cell. This can become problematic very soon.

For example, if you use a single cell for people’s full names, you can then sort the list only into ascending or descending order by the full name. Depending on how you actually enter the name information, a meaningful sort may prove to be impossible.


So, while first name, middle name and last name are similar types of information –they are all names, after all – they are really different and deserve their own cells and therefor their own columns. Separating names into Full Name, Middle Name and Last Name, allows you to then sort the list by Last Name; where several people have the same last name, those groups can be further sorted by First and Middle Names.

Separate columns for First, Last, and Middle names, makes your data far more flexible to work with in other ways. For example, having the first name separate from the last name makes it possible to address a letter to John Smith, but have a salutation in the letter like, “Dear John.”

The video illustrates just how easy it is to create a simple data list. In coming articles, I will discuss how to enhance the list to provide additional columns (fields) for mail merging and how to set up the list as mail merge data source.