What are LIST & TABLE PRINCIPLES for Spreadsheet Users

What you SEE vs. what you USE
In your workbook & spreadsheet design, differentiate between the DATA that you USE and the DATA that you SEE. In some cases, where your workbook is simple, your SOURCE DATA and REPORT DATA can be cast in the same structure. But in MOST cases, the data structure that is required for ease of maintenance, analysis and reporting is NOT in the same form as what you want to SEE; the REPORT.

Lists & Tables
List: A collection of related data usually in a single column.
Table: A group of related data elements (Columns) populated with data.

Consider putting you Lists on a separate sheet from your Report. Consider putting each Table on a separate sheet, with the Sheet Tab as the Table Name.

Each List & Table Column should have a Unique Descriptive Heading, not too long. For Lists I like to use names like EmployeeList, ProductList, etc. The reason I like to use the word List in my List heading is that more than often, I also have a Table Column Heading somewhere else like Employee or Product.

Use ONE ROW for the Unique Descriptive Heading.

Each List & Table needs to be Isolated from other data by at least one empty row/column around the List or Table unless the List or Table starts in Column A or Row 1. Depending on other factors, like the use of the OFFSET Function for defining Dynamic Range Names, there might be other restrictions related to data outside the List or Table.

Each List & Table needs to be Contiguous. There can be no empty rows or columns within the List or Table.

Your Table Structure should not include repeated similar data elements, which often are themselves Data in Headings. A typical example would be...
[tt]
DeptID Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
[/tt]
where Amounts would be stored under each respective Month for each DeptID. This type of structure is known as a non-normalized data structure. It might be the way that you would want to SEE your data, but it is NOT a good way to STORE your data.

This data structure would be better...
[tt]
DeptID Date Amount
[/tt]
It is better for the following reasons...
1) Each row has a complete date. The respective months of the previous example are not complete dates, primarily because they are missing YEAR. From this structure, you will be able to report using the built-in PivotTable Wizard and summarize by Year & Month, Year & Quarter or Year all in mere SECONDS! And it can look just like the first example.

2) It can be analyzed and reported using a plethora of Excel analysis and reporting tools.

Another typical mistake is to put the data for each respective week, month, department, region, salesman, etc. on separate sheets or in separate workbooks. This is as good as printing a report and sticking it in a file drawer. It is virtually inaccessible and unusable. In many cases, this data is a valuable CORPORATE ASSET that is rendered USELESS by segmentation. Much historical data can be used to aid in making decisions. This data may be used to plot former trends or generate forecasts.

Multiple Row Data values, that in a REPORT might be displayed once, are REPEATED ON EACH ROW. Then, in your REPORT, show the summarization whatever way you choose.

Here is Excel HELP on some important guidelines...
[quote Excel_HELP][blue]Guidelines for entering data on a worksheet[/blue]

Microsoft Excel has a number of features that make it easy to manage and analyze data. To take advantage of these features, enter data in your worksheet according to the following guidelines.
[blue]
Data organization[/blue]

Put similar items in one column Design the data so that all rows have similar items in the same column.

Keep the range separate [red]Leave at least one blank column and one blank row between the related data range and other data on the worksheet. Excel can then more easily detect and select the range when you sort, filter, or insert automatic subtotals.[/red]

Position critical data above or below the range Avoid placing critical data to the left or right of the range; the data might be hidden when you filter the range.

Show rows and columns Make sure any hidden rows or columns are displayed before making changes to the range. When rows and columns in a range are not showing, data can be deleted inadvertently.
[blue]
Data format[/blue]

Use formatted column labels Create column labels in the first row of the range of data. Excel uses the labels to create reports and to find and organize data. Use a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format you assign to the data in the range. Format the cells as text before you type the column labels.

Use cell borders When you want to separate labels from data, use cell bordersù not blank rows or dashed linesù to insert lines below the labels.

Avoid blank rows and columns [red]Avoid putting blank rows and columns in the range so that Excel can more easily detect and select the related data range[/red].

Don't type leading or trailing spaces Extra spaces at the beginning or end of a cell affect sorting and searching. Instead of typing spaces, indent the text within the cell.

Extend data formats and formulas When you add new rows of data to the end of a data range, Excel extends consistent formatting and formulas. Three of the five preceding cells must use the same format for a format to be extended. All of the preceding formulas must be consistent for a formula to be extended.
[blue]
List feature[/blue]

You can designate a [red]contiguous range of cells[/red] on your worksheet as a list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.). When you create a list, data defined by the list can be manipulated independently of data outside of the list. After you create a list, you can use list features to quickly sort, filter, total, or publish the data contained within the list.

You can also use the list feature to compartmentalize sets of related data by organizing that data using multiple lists on a single worksheet.
[/quote]