Excel Database, Tables, Records and Fields

01
of 01

Database Terms: Table, Records, and Fields in Excel

Excel Database, Tables, Records and Fields
Excel Database, Tables, Records and Fields. © Ted French

Excel as a Database

Excel is first and foremost electronic spreadsheet programs nor does it have the data management capabilities of relational database programs such as SQL Server and  Microsoft Access.

What it can do, however, is serve as a simple or flat-file database that fills the data management requirements in many situations.

In Excel, data is organized into tables using the rows and columns of a worksheet. The more recent versions of the program have a table feature, which makes it easy to enter, edit, and manipulate data.

Each individual piece of data or information about a subject - such as a part number or a person's address - is stored in a separate worksheet cell and referred to as a field.

Tables

A database is a collection of related information stored in one or more computer files in an organized fashion.

Normally the information or data is organized into tables. A simple or flat-file database, such as Excel, holds all information about one subject in a single table.

Relational databases, on the other hand, consist of a number of tables with each table containing information about different, but related, topics.

The information in a table, is organized in such a way that it can easily be

  • updated;
  • sorted;
  • corrected;
  • filtered or searched for specific information.

Records

In database terminology, a record holds all the information or data about one specific object that has been entered into the database.

In Excel, records are normally organized in worksheet rows with each cell in the row containing one item of information or value.

Fields

Each individual item of information in a database record - such as a telephone number or street number - is referred to as a field.

In Excel, the individual cells of a worksheet serve as fields, since each cell can contain a single piece of information about an object.

Field Names

It is vital that data be entered in an organized fashion into a database so that it can be sorted or filtered to find specific information.

To ensure that data is entered in the same order for each record, headings are added to each column of a table. These column headings are referred to as field names.

In Excel, the top row of a table contains the field names for the table. This row is usually referred to as a header row.

Example

In the image above, all information gathered for one student is stored in an individual row or record in the table. Each student, no matter how much or how little information is gathered has a separate row in the table.

Each cell within a row is a field containing one piece of that information. The field names in the header row help ensure that the data stays organized by keeping all the data on a specific topic - such as name or age - in the same column for all students.

Excel's Data Tools

Microsoft has included a number of data tools to make it easier to work with the large amounts of data stored in Excel tables and to help keep it in good condition.

Using a Form for Records

One of those tools that makes it easy to work with individual records is the data form. A form can be used to find, edit, enter, or delete records in tables containing up to 32 fields or columns.

The default form includes a list of field names in the order they are arranged in the table, to ensure that records are entered correctly. Next to each field name is a text box for entering or editing the individual fields of data.

While it is possible to create custom forms, creating and using the default form is much easier and often it is all that is needed.

Remove Duplicate Data Records in Excel

A common problem with all databases is data errors. In addition to simple spelling mistakes or missing fields of data, duplicate data records can be a major concern as a data table grows in size.

Another of Excel's data tools can be used to remove these duplicate records - either exact or partial duplicates.

Sorting Data in Excel

Sorting means to reorganize data according to a specific property - such as sorting a table alphabetically by last name or chronologically from oldest to youngest. Excel's sort options include sorting by one or more fields, custom sorting - such as by date or time, and sorting by rows which makes it possible to reorder the fields in a table.