IT Tip #3 - Excel as a database

In the first of these Tips and Tricks blogs, I looked at using Word and Excel to produce Mail Merged documents, however, there was a presumption that a “Data Source” was already available in either Excel or Access. In this blog, I will look at the creation of a data source in Excel (because it is somewhat simpler than in Access!) and some of the features within Excel that allow us to take full advantage of the database that we have produced. A later one will explore some of the more advanced areas of database manipulation.

Database Terminology
Field

• A field is a column within an Excel database.

Record

• A record is a row within an Excel database

Field Name

• In an Excel database the top row of the database will normally hold the names of each field (column). It is usually descriptive text describing the function of the particular field.
• Field names are not essential for some of the database (List Management) functions, such as sorting a list. However, to use the full power of the system, you will need to define field names.

To create an Excel database

• Enter the information in the normal way, remembering to include field names in the top row. Also, remember that your data should be in the second row of your database; do not leave a blank row before inputting the data.

• Use the Tab key to proceed to the next field (or Shift+Tab to go to the preceding field). You can insert or remove rows or columns in the normal way.

• In fact, there is nothing special concerning the creation of an internal database; it is just like creating any other spreadsheet type information.

• If when entering data into a cell it is the same as in the cell above there is a neat trick that will save you typing - Press the following key combination: Ctrl+’ (apostrophe) the contents of the cell above are copied.

• You will also note that the AutoComplete “function” will automatically complete a text entry based on other entries that you’ve already made in the column. In addition, right click on the cell where you want to input information and choose Pick from List. Excel then displays a list box that shows all of the entries in the column. Click on the one that you want and it is entered into the cell.

To sort the data within a database

• Select any cell within the database range.

• From the Data drop down menu, select the Sort command, which will open the Sort dialog box.

• You will see that Excel automatically places a field name in the Sort by box. This is because the active cell was in the column for that field name when the Sort command was issued

• WARNING: If your database contains a Header Row, but you specify No header row in the Sort dialog box, then the header will be sorted (and get mixed up) along with the rest of the information contained within the database.

Filtering the database with Autofilter

• Click on any cell within the database.

• From the Data drop down menu, select Filter, and then select AutoFilter.

• Drop down controls will be displayed next to each field name. Select the one that you want to use, then choose the item that you want to select (e.g. a county or town). Only those records matching the filter will then be displayed.