How to Create a Database in Excel

01
of 10

Excel Database Overview

Excel Database Tutorial
Excel Database Tutorial. Ted French

Microsoft Excel Database Files

 

Related tutorial: Microsoft Excel 2003 Step by Step Database Tutorial.

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.

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

The steps in this tutorial are:

  1. Entering the Data - How data is stored
  2. Entering Data Correctly - How to avoid common errors
  3. Rows are Records - Organizing your data
  4. Columns are Fields- Organizing your data (con't)
  5. Creating the Table - Create the database
  6. Using the Database Tools - Sorting and filtering data
  7. Expanding the Database - Adding new records
  8. Completing the Database Formatting - Formatting the title and cell color
02
of 10

Tables of Data

Excel Database Tutorial
Excel Database Tutorial. Ted French

Tables of Data

The basic format for storing data in an Excel database is a table.

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.

To follow this tutorial:

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

Tip - To enter the Student ID's 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 10

Entering Data Correctly

Enter the data correctly for a list
Enter the data correctly for a list. Ted French

Leave no Empty Rows or Cells

When enter 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 10

Rows are Records

A data record in an Excel database
A data record in an Excel database. Ted French

Rows are Records

Each individual row of data, in a database is known as a record. When entering records keep these guidelines in mind:

  • Leave no blank rows in the table being created. This includes NOT leaving a blank row between the column 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 10

Columns are Fields

Field names in an Excel table
Field names in an Excel table. Ted French

Columns are Fields

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.

  • Do not leave blank columns in the table.
06
of 10

Creating the Table

Using the Create List dialog box in Excel
Using the Create List dialog box in Excel. Ted French

Create the Table

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

  1. Highlight the cells A3 to E13 in the worksheet.

  2. Click on the Home tab.

  3. Click on the Format as Table option on the ribbon to open the drop down menu.

  4. Choose the blue Table Style Medium 9 option to open the Format as Table dialog box.

  5. While the dialog box is open, cells A3 to E13 on the worksheet should be surrounded by the marching ants.

  6. If the marching ants surround the correct range of cells, click Ok in the Format as Table dialog box.

  7. 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 Format as Table dialog box.

  8. The table should have the drop down arrows added beside each field name and the table rows should be formatted in alternating light and dark blue.

07
of 10

Using the Database Tools

Excel's Database Tools
Excel's Database Tools. Ted French

Using the Database Tools

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 A to Z option to sort the database alphabetically.

  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 check box next to the Select All option to clear all check boxes.

  3. Click on the check box next to the Business option to add a check mark to the box.

  4. Click OK.

  5. Only two students - G. Thompson and F. Smith should be visible since they are the only two enrolled in the business program.

  6. To show all records, click on the drop down arrow next to the Program field name.

  7. Click on the Clear Filter from "Program" option.

08
of 10

Expanding the Database

Entering New Records in an Excel Database
Entering New Records in an Excel Database. Ted French

Expanding the Database

To add additional records to your database:

  • Place your mouse pointer over the small dot in the bottom right hand corner of the table.

  • The mouse pointer will change into a two - headed arrow.

  • When this happens, click and hold down the right mouse button and drag the pointer down to add a blank row to the bottom of the database.

  • Add the following data to this new row:

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

Completing the Database Formatting

Formatting an Excel Database
Formatting an Excel Database. Ted French

To complete the database formatting:

  1. Highlight cells A1 to E1 in the worksheet.

  2. Click on the Home tab.

  3. Click on the Merge and Center option of the ribbon to center the title.

  4. Click on the Fill Color (looks like a paint can) on the ribbon to open the fill color drop down list.

  5. Choose Blue, Accent 1 from the list to change the color of the background in cells A1 - E1 to dark blue.

  6. Click on the Font Color icon on the Formatting Toolbar (it is a large letter " A ") to open the font color drop down list.

  7. Choose White from the list to change the color of the text in cells A1 - E1 to white.

  8. Highlight cells A2 - E2 in the worksheet.

  9. Click on the Fill Color on the ribbon to open the fill color drop down list.

  10. Choose Blue, Accent 1, Lighter 80 from the list to change the color of the background in cells A2 - E2 to light blue.

  11. Highlight cells A4 - E14 in the worksheet.

  12. Click on the Center option on the ribbon to center align the text in cells A14 to E14.

  13. 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.
10
of 10

Completing the Database Formatting

Database functions

Syntax: Dfunction(Database_arr , Field_str|num , Criteria_arr)

Where Dfunction is one of the following:

  • DAVERAGE
  • DCOUNT
  • DCOUNTA
  • DGET
  • DMAX
  • DMIN
  • DPRODUCT
  • DSTDEV
  • DSTDEVP
  • DSUM
  • DVAR
  • DVARP

Type: Database

Database functions are particularly handy when Google Sheets is used to maintain structured data, like a database. Each database function, Dfunction, computes the corresponding function on a subset of a cell range regarded as a database table. Database functions take three arguments:

  • Database_arr is a range, embedded array or array generated by an array expression that is structured so that each row after Row 1 is a database record, and each column is a database field. Row 1 contains the labels for each field.
  • Field_str|num indicates which column (field) contains the values to be averaged. This can be expressed as either the field name (text string) or the column number, where the left-most column would be represented as 1.
  • Criteria_arr is a range, embedded array or array generated by an array expression that is structured such that the first row contains the field name(s) to which the criterion (criteria) will be applied, and subsequent rows contain the conditional test(s).

The first row in Criteria specifies field names. Every other row in Criteria represents a filter, which is a set of restrictions on the corresponding fields. Restrictions are described using Query-by-Example notation, and can include a value to match or a comparison operator followed by a comparison value. Examples of restrictions are: "Chocolate", "42", ">= 42", "<> 42". An empty cell means no restriction on the corresponding field.

A filter matches a database row if all the filter restrictions (the restrictions in the filter's row) are met. A database row (record) satisfies Criteria if and only if at least one filter matches it. A field name may appear more than once in the Criteria range to allow multiple restrictions that apply simultaneously (for example, temperature >= 65 and temperature <= 82).

DGET is the only database function that doesn't aggregate values. DGET returns the value of the field specified in the second argument (similarly to a VLOOKUP) only when exactly one record matches Criteria; otherwise, it returns an error indicating no matches or multiple matches