Establish Your Database with an Excel Database Function

By Ken Bluttman

All database functions in Excel take a database reference as the first argument. The database area must include headers (field names) in the first row. In the following figure, the first row uses Student ID, Class, Teacher, and Final Grade as headers to the information in each respective column.

Using a database to store student information.

Using a database to store student information.

A great way to work with the database functions is to name the database area and then enter the name, instead of the range address, in the function.

To set up a name, follow these steps:

  1. Select the entire database area.

    Make sure the top row has headers and is included in the selection.

  2. Click the Formulas tab (at the top of the Excel window).

  3. Click Define Name in the Defined Names area.

    The New Name dialog box appears, with the range address set in the Refers To box.

  4. Type a name in the Name Box (or use the suggested name).

  5. Click OK to close the dialog box.

Later, if records are added to the bottom of the database, you have to redefine the named area’s range to include the new rows. You can do this as follows:

  1. Click the Name Manager button on the Excel Formulas tab.

    The Name Manager dialog box appears.

  2. Click the name in the list you want to redefine.

  3. Click the Edit button in the dialog box.

    Excel opens the Edit Name dialog box, shown here, with information about the selected range.

    Updating the reference to a named area.

    Updating the reference to a named area.
  4. Change the reference in the Refers To box.

    You can use the small square button to the right of the Refers To box to define the new reference by dragging the mouse pointer over it. Clicking the small square button reduces the size of the Edit Name dialog box and allows you access to the worksheet. When you are done dragging the mouse over the new worksheet area, press Enter to get back to the Edit Name dialog box.

  5. Click the OK button to save the reference change and close the dialog box.

  6. Click Close.

If you add records to your database range by inserting new rows somewhere in the middle, rather than adding them on at the end, Excel automatically adjusts the reference to the named range.