Excel as a Database
Excel is also a powerful flat database. this means that excel is very useful as a tool to store lists of data. Excel is straight forward and cannot relate one set of data to another but it is a great tool for keeping lists of names, addresses, inventories and is very easy to manage. A Word Mail Merge can be created from an Excel mailing or label list.
A database is made up of three components: records, fields, data. In Excel, fields are created by placing a field name at the top of a column. Continuous columns should be used with no spaces between columns (fields). Each row is a record. Again no blank rows may be placed between the records. Finally the content of the cell is the data. Avoid using calculations within fields when you are using Excel as a database. If you list is sorted or filtered, the results of any formulas will change or become invalid because of the change in relative cell addresses in relation to the formula.
TIP: Keep your field names short.
TIP: If your records extend beyond a single sheet select the row below your field names and choose Window, Freeze Panes. This will keep the field names visible at the top of the screen as you add more records. To unfreeze, choose Windows, Unfreeze Panes.
TIP: Pay special attention to your terminology and abbreviate consistently. The AutoFill will make life easier if you are consistent. You will be entering less data if you abbreviate consistently. Excel will finish you entries correctly if consistency has been maintained. Sorting and filtering will be easier if you are consistent.
A single sort on one field is the most commonly used and the easiest.
You can also select your Database and click the sort buttons on the toolbar if the first cell is the cell you wish to sort upon.
TIP: Excel usually sees the first row of data as field names (header row). If this does not happen, Click My List Has Header Row option at the bottom of the Sort dialog box.
Multiple Level SortTo do this know of sort you must have fields that contain duplicate records. If every field is unique, you can only do a single level sort. Sort by fields that hold duplicate records and start with the highest number of duplicates.
The lowest field in you sort level does not need to have duplicate records. You can customize the sort for Case Sensitive if you wish this will cause Excel to do lowercase first then uppercase text.
Filtering A List
Filtering shows only the records that meet your filter criteria and not all of the records in a database. Excel provides a powerful AutoFilter which allows you to filter on one or more fields and see only the records that pass through the filter.
To use AutoFilter
Filtering on Multiple Fields
If your database has many duplicate fields you may to need to filter on more than one field to get the information you need.
Custom Filters require a comparison, such as greater than, less than, or equal to.
TIP: When entering a value use the wildcard * for a group of characters and ? for a single character.
CAUTION: The most common error is that no records meet the criteria of the comparison.
Return to Spreadsheets Handout
|This page was updated on: 04/10/02|