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 using the rows and columns in a worksheet. The table feature makes it easy to enter, edit, and manipulate data.

These instructions apply to Excel versions 2019, 2016, 2013, 2010, and Excel for Office 365.

Database Terms: 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. Often the data is organized into tables 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 many tables with each one containing information about different, but related topics.

Records

In database terminology, a record holds all the information or data about one specific object in the database. In Excel, each cell in a worksheet contains one item of information or value.

Fields

Each 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 to organize a database so that you can sort or filter the data to find specific information. Adding column headings, known as field names, makes it easier to enter data in the same order for each record.

Screenshot of Excel showing an example table

Sample Database

In the image above, each student has a separate row in the table which contains all available information about them.

Each cell in a row is a field containing one piece of 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 several data tools to make it easier to work with vast amounts of data stored in Excel tables and to help keep it in good condition.

Using a Form for Records

One of those tools is the data form. You can use it 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 users enter records 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 often 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 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.