Sparks Fly
[Home] [PBS VideoDatabase] [Social Studies] [TSU] [UVA]

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.

Database Structure

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.

Sorting Lists

A single sort on one field is the most commonly used and the easiest.

• Click in any cell within your database.  (Do not click a cell above the field names, below the last record, or to the right of your last field name in any row.)
• Choose Data, Sort.
• Click the Sort by drop-down list to see a list of field names and select one.
• Determine the sort order A-Z or Z-A.
• Click OK.

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 Sort 

• Click in cell within database that contains the data
• Choose Data, Sort.
• Click the Sort By drop-down list and select a field for the first level of the sort.
• Click the Then by drop-down list and choose a second field by which to sort.
• If you want a third field follow the same direction
• Select the sort order, Ascending or Descending
• Click OK

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

• Click any cell in the database that contains data
• Choose Data, Filter, AutoFilter.  A down-pointing arrow appears to the right of each field name in your database.
• Click the arrow to the right of the field you want to filter.
• To redisplay all your records click the filtered field's arrow (which is now blue) and choose All or choose Data, Filter, Show All.
• Choose Blanks from the list of entries to see any records that are missing data for a specific field

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.

• Click in a cell and choose Data, Filter, AutoFilter.
• Choose the first field to filter, click the arrow to the right of the field.
• Choose the entry from the list
• Continue to arrow your displayed group of records by clicking another field's AutoFilter arrow and choose an entry from that list.
• Repeat the process for as many fields as needed to narrow the list to the records you require.

Custom Filters

Custom Filters require a comparison, such as greater than, less than, or equal to.  

• AutoFilter on, click the arrow to the right of the field you want to filter
• Choose Custom.
• The field name selected appears in the dialog box followed by a drop-down list containing comparisons.  Select one.
• Press Tab or click inside the text box to the right of the comparison.  Type a value (text or numbers) or choose from the list.
• Click OK.

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