Leading the way in Microsoft Office Development

What is a List ?

A list is just the name given to a table of data.

When you create a list in Excel you should try and adhere to the following:

The first row should contain unique column labels.

Each column should contain a consistent type of data.

The information should be split into as many columns as possible to enable maximum sorting and filtering.

Try and avoid including any blank rows or columns. If you want to include spaces you should adjust the row height.

Each list should be placed on a separate worksheet.

What is Filtering ?

This allows you to hide rows that do not match your criteria.

Filtering a list or table of data can be useful especially when you want to find a particular record.

There are two types of filtering that can be used AutoFilter and Advanced Filter.

AutoFilter is for simple criteria and Advanced Filter is for more complex criteria.

You can apply filtering to a list or table by selecting (Data > Filter) sub menu.

The Show All command can be used to reset your filter to display all the rows.

AutoComplete

AutoComplete is the automatic filling in of your text when you start typing and is switched on by default

AutoComplete matches only exact cell entries, not individual words in a cell. This does not work when entering or editing formulas.

The AutoComplete and Pick From List features make it a lot quicker to enter repetitive text.

AutoFilter

Allows you to quickly display only the rows your are interested in.

This method can be used to only display the record

You can only have one AutoFilter per worksheet, which means that you cannot display several tables with different filters on the same worksheet.

You can change the filter at any time by just selecting another entry from one of the drop-downs

Using filters to locate records encourages you to maintain consistent data as any spelling mistakes or mis-typed entries will appear as additional entries in the AutoFilter drop-down lists.

To apply an Autofilter just select any cell in the list of data and select (Data > Filter > AutoFilter).

You do not have to include a header row on your table of data but if you don't then the first row will always be visible ??

Advanced Filter

This is for more complex filtering

The main difference with this is that the criteria is specified in a range of cells separate from your data list.

The field names that appear in the header rows can be used to identify the columns you want to apply filters to.

To apply an Advanced Filter just select any cell in the list of data and select (Data > Filter > Advanced).

There must be at least one blank row or column separating the criteria and the data list.

List Toolbar and List Submenu

This toolbar was added in Excel 2003 for the purpose of updating and publishing lists to a SharePoint site.

If you do not use SharePoint you can ignore the list toolbar as well the List submenu on the Data drop-down list.

Things to Remember

When you apply a filter the original data is unchanged, the rows that do not match are just hidden temporarily.

Excel only allows one list to be filtered on a worksheet at any one time.