How to Create Data Lists in Excel Spreadsheets

It's easy to keep track of specific information

What to Know

  • Select a cell > Home tab > Sort & Filter > Filter. Next, select a column header arrow to filter or sort the data.
  • To guard against data errors, leave no blank rows or columns in the table.

An Excel spreadsheet can hold an enormous amount of data; Excel has built-in tools to help you find specific information when you want to retrieve it. Here's how to create, filter, and sort a data list in Excel 2019, 2016, 2013, 2010; Excel for Microsoft 365; Excel Online; and Excel for Mac.

Create a Data List in Excel

After you've correctly entered data into a table and included the proper headers, convert the table to a list.

  1. Select a cell in the table.

  2. Select Home > Sort & Filter > Filter.

  3. Column header arrows appear to the right of each header.

    A screenshot showing a data list in Excel that contains filter buttons
  4. When you select a column header arrow, a filter menu appears. This menu contains options to sort the list by any of the field names and to search the list for records that match certain criteria.

  5. Sort your data list to find whatever specific data you want to retrieve.

Note that a table of data must contain at least two data records before a list is created.

Basic Excel Table Information

The basic format for storing data in Excel is a table. In a table, data is entered in rows. Each row is known as a record. Once a table has been created, use Excel's data tools to search, sort, and filter the records to find specific information.

A screenshot showing data that is entered correctly into rows and columns in Excel

Columns

While rows in the table are referred to as records, the columns are known as fields. Each column needs a heading to identify the data it contains. These headings are called field names. Field names are used to ensure that the data for each record is entered in the same sequence.

Make sure to enter the data in a column using a consistent format. For example, If numbers are entered as digits (such as 10 or 20,) keep it up; don't change partway through and begin entering numbers as words (such as "ten" or "twenty").

It's also important to leave no blank columns in the table, and note that the table must contain at least two columns before a list is created.

Guard Against Data Errors

When creating the table, make sure the data is entered correctly. Data errors caused by incorrect data entry are the source of many problems related to data management. If the data is entered correctly in the beginning, you'll get the results you want.

To guard against data errors, leave no blank rows in the table being created, not even between the headings and the first row of data. Make sure each record contains data about only one specific item, and that each record contains all the data about that item. There can't be information about an item in more than one row.

Was this page helpful?