Excel 2003 Database Tutorial

01
of 09

Excel 2003 Database Overview

Excel 2003 Database Tutorial
Excel 2003 Database Tutorial. © Ted French

At times, we need to keep track of information and a good place to this is in an Excel database file. Whether it is a personal list of phone numbers, a contact list for members of an organization or team, or a collection of coins, cards, or books, an Excel database file makes it easy to enter, store, and find specific information.

Excel has built it tools to help you keep track of data and to find specific information when you want it. As well, with its hundreds of columns and thousands of rows, an Excel spreadsheet can hold an enormous amount of data.

Also see the related tutorial: Excel 2007 / 2010 / 2013 Step by Step Database Tutorial.

02
of 09

Tables of Data

Excel Database Tutorial
Excel Database Tutorial. © Ted French

The basic format for storing data in an Excel database 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 records in the database to find specific information.

Although there are a number of ways you can use these data tools in Excel, the easiest way of doing so is to create what's known as a list from the data in a table.

To follow this tutorial:

  • enter the data as it is shown in the image above.

Tip -- To enter the student ID quickly:

  1. Type the first two ID's - ST348-245 and ST348-246 into cells A5 and A6 respectively.
  2. Highlight the two ID's to select them.
  3. Click on the fill handle and drag it down to cell A13.
  4. The rest of the Student ID's should be entered into cells A6 to A13 correctly.
03
of 09

Entering Data Correctly

Entering Data Correctly
Entering Data Correctly. © Ted French

When ​entering the data, it is important to ensure that it is entered correctly. Other than row 2 between the spreadsheet title and the column headings, do not leave any other blank rows when entering your data. Also, make sure that you don't leave any empty cells.

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.

04
of 09

Rows are Records

Excel Database Tutorial
Excel Database Tutorial. © Ted French

As mentioned, rows of data, in a database 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 database about that item. There can't be information about an item in more than one row.
     
05
of 09

Columns are Fields

Columns are Fields
Columns are Fields. © Ted French

While rows in an Excel database 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.
06
of 09

Creating the List

Creating the Data Table
Creating the Data Table. © Ted French

Once the data has been entered into the table, it can be converted to a list. To do so:

  1. Highlight cells A3 to E13 in the worksheet.
  2. Click on Data > List > Create List from the menu to open the Create List dialog box.
  3. While the dialog box is open, cells A3 to E13 on the worksheet should be surrounded by the marching ants.
  4. If the marching ants surround the ​correct range of cells, click Ok in the Create List dialog box.
  5. If the marching ants do not surround the correct range of cells, highlight the correct range in the worksheet and then click Ok in the Create List dialog box.
  6. The table should be surrounded by a dark border and have drop down arrows added beside each field name.
07
of 09

Using the Database Tools

Using the Database Tools
Using the Database Tools. © Ted French

Once you have created the database, you can use the tools located under the drop down arrows beside each field name to sort or filter your data.

Sorting Data

  1. Click on the drop-down arrow next to the Last Name field name.
  2. Click on the Sort Ascending option to sort the database alphabetically from A to Z.
  3. Once sorted, Graham J. should be the first record in the table and Wilson R. should be the last.

Filtering Data

  1. Click on the drop-down arrow next to the Program field name.
  2. Click on the Business option to filter out any students, not in the business program.
  3. Click OK.
  4. Only two students - G. Thompson and F. Smith should be visible since they are the only two enrolled in the business program.
  5. To show all records, click on the drop-down arrow next to the Program field name.
  6. Click on the All option.
     
08
of 09

Expanding the Database

Expanding an Excel Database
Expanding an Excel Database. © Ted French

To add additional records to your database:

  • A blank row containing an asterisk (cells A14 to E14) is added to the bottom of the database when it is created.
  • Additional data can be added to the database using this blank row.
  • Add the following data:​

    Cell - Data
    A14 - ST348-255
    B14 - Christopher
    C14 - A.
    D14 - 22
    E14 - Science
09
of 09

Completing the Database Formatting

Completing the Database Formatting
Completing the Database Formatting. © Ted French

Note: This step involves using icons located on the Formatting toolbar, which is normally located at the top of the Excel 2003 screen. If it is not present, read how to find Excel toolbars to help you locate it.

  1. Highlight cells A1 to E1 in the worksheet.
  2. Click on the Merge and Center icon on the Formatting Toolbar to center the title.
  3. With cells A1 to E1 still selected, click on the Fill Color icon on the Formatting Toolbar (looks like a paint can) to open the background color drop-down list.
  4. Choose Sea Green from the list to change the background color of cells A1 - E1 to dark green.
  5. Click on the Font Color icon on the Formatting Toolbar (it is a large letter " A ") to open the font color drop down list.
  6. Choose White from the list to change the color of the text in cells A1 - E1 to white.
  7. Highlight cells A2 - E2 in the worksheet.
  8. Click on the Fill Color icon on the Formatting Toolbar to open the background color drop-down list.
  9. Choose Light Green from the list to change the background color of cells A2 - E2 to light green.
  10. Highlight cells A3 - E14 on the worksheet.
  11. Choose Format > AutoFormat from the menus to open the AutoFormat dialog box.
  12. Choose List 2 from the list of options to format cells A3 - E14.
  13. Highlight cells A3 - E14 on the worksheet.
  14. Click on the Center optionicon on the Formatting Toolbar to center align the text in cells A3 to E14.
  15. At this point, if you have followed all the steps of this tutorial correctly, your spreadsheet should resemble the spreadsheet pictured in Step 1 of this tutorial.
Format
mla apa chicago
Your Citation
French, Ted. "Excel 2003 Database Tutorial." ThoughtCo, Jun. 2, 2017, thoughtco.com/excel-2003-database-tutorial-3123426. French, Ted. (2017, June 2). Excel 2003 Database Tutorial. Retrieved from https://www.thoughtco.com/excel-2003-database-tutorial-3123426 French, Ted. "Excel 2003 Database Tutorial." ThoughtCo. https://www.thoughtco.com/excel-2003-database-tutorial-3123426 (accessed January 20, 2018).