Guest Author - Chris Curtis

Excel provides some powerful tools for list management. With these tools you can:
• Sort
• Filter
• Find
• Validate Entries
• Create Sub-total
• Categorize using Group and Outline
• Summarize using Pivot Tables

In short, these tools provide the means for powerful and meaningful analysis of your data. However, keep in mind that Excel is not a database management tool and thus, its use should be limited to managing lists of data.

Like a database, Excel lists are organized into rows representing a record of information and columns representing fields with each record in the list containing similar information. There are certain standards that should be applied to an Excel list to optimize the effectiveness of the data tools as follows:

• Begin each new list on a separate worksheet.
• Avoid putting non-list data on the list worksheet. When necessary to the integrity of the list, place critical data above or below the list with at least one blank row between the list and other data.
• Begin the list by adding column labels. Choose a formatted style that is different that the data format so that Excel can interpret the list boundaries.
• Do not separate the data from the column labels by inserting a blank row or by using a row containing a dashed line, asterisk, or any other type of non-data symbols.. Use the cell border formatting feature to visually distinguish between the column labels and the data.
• Each column should contain the same type of data for each record in your list.
• Do not mix data types in a column. For example, monetary data should all be entered as numbers and formatted as desired, some of the entries should not use text such as "Two Dollars"
• Do not insert blank rows or columns within your list. Excel interprets the list boundaries using blank rows and columns as the defining limits.
• Align data in columns using Excel alignment tools such as centering, left and right alignments and the indent tools. Leading and trailing spaces in a data cell will affect the sorting and filtering features.
• Use the sub-total, group and outline commands to create category breaks within your data. Do not manually insert sub-total rows or use blank rows to create category breaks.

Once your list has been established, you can readily add data to the bottom of the list. Excel will extend formulas and formatting to the new data as long as the previous three rows contain like formulas and formatting. It is best to add the data to the bottom of the list and resort or refilter as needed. You will need to remove any sub-totals, groupings or filtering before resorting your list after adding new records. Once the new data has been incorporated and sorted appropriately, you can then reapply any sub-total, groupings or filtering that is needed.

List management is one of the most powerful tools Excel offers to users. Learning to organize the data most effectively to take advantage of the tools that are offered is worth the time.