Manage Data in Excel with Databases, Tables, Records, and Fields

Businessmen studying graphs on an interactive screen in business meeting
Monty Rakusen / Getty Images

Excel does not 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 database that fills data management requirements.

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.

Database Terms: Table, Records, and Fields

Bar graph on screen
Sean Gladwell / Getty Images

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. The information in a table is organized in such a way that it can easily be updated, sorted, corrected, and filtered.

A simple 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.

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. Column headings are referred to as field names.

Screenshot of Excel showing an example table

Example Database

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 Management Tools

database
enot-poloskun / Getty Images

Additionally, 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

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 names 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.