How to Create Data Lists in Excel Spreadsheets

List of data
Adam Gault / Getty Images

Whatever data you have, a spreadsheet, like Excel, is a great place to store it. Excel has built-in tools to help you keep track of data, and to find specific information when you want it. With its hundreds of ​columns and thousands of rows, an Excel spreadsheet can hold an enormous amount of data.

Excel is also simpler to use than a full-fledged database program such as Microsoft Access. Data can be entered easily into the spreadsheet, and, with just a few clicks of the mouse you can sort through your data and find what you want.

of 03

Entering Data into Excel Tables

Screenshot of Excel showing blank spots in a data table

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, Excel's data tools can be used to search, sort, and filter the records to find specific information.

Entering Your Table Data

The first step in creating a table is to enter the data. When doing so, it is important to ensure that it is entered correctly. As an example, be sure not to leave any empty cells when creating your table, such an action may cause problems in the future.

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, the program is more likely to give you back the results you want.

of 03

Excel's Rows and Columns

Screenshot of Excel showing a data row

Rows in a Table

As mentioned above, rows of data are known as records. When entering records keep these guidelines in mind:

  • Leave no blank rows in the table being created, not even between the headings and the first row of data.
  • A record can contain data about only one specific item.
  • A record must also contain ALL the data in the list about that item. There can't be information about an item in more than one row.
  • To create a list, a table of data needs to contain at least two data records.

Columns in a Table

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 that all the data in a column is entered using the same format. If you start entering numbers as digits (such as 10 or 20) keep it up. Don't change part way through and begin entering numbers as words (such as ten or twenty). Be consistent.
  • Leave no blank columns in the table.
  • To create a list, a table needs to be at least two columns wide.
of 03

Creating a List in Excel

Screenshot of Excel showing list creation and management

Using The Filter Option

Once the data has been correctly entered into a table with proper headers, it can be converted to a list using the Filter option.

  1. Select any one cell in the table.
  2. Choose Home > Sort & Filter > Filter from the Home tab.
  3. The table will now show small drop-down arrows next to each header.

Managing Your Data Lists

  • There will be down-pointing arrows beside each field name that open drop-down menus when clicked on.
  • The drop-down menus contain options for sorting the list by any of the field names and for searching the list for records that match certain criteria.