How to Create an Excel Database
How to Create an Excel Database Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office 299 299 people found this article helpful
Cell A14: ST348-255
Cell B14: Christopher
Cell C14: A.
Cell D14: 22
Cell E14: Science
How to Create a Database in Excel
Track contacts, collections, and other data
By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on January 30, 2021 Reviewed by Michael Barton Heine Jr Reviewed by Michael Barton Heine Jr Michael Heine is a CompTIA-certified writer, editor, and Network Engineer with 25+ years' experience working in the television, defense, ISP, telecommunications, and education industries. lifewire's editorial guidelines Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint OutlookWhat to Know
Enter data in the cells in columns and rows to create a basic database.If using headers, enter them into the first cell in each column.Do not leave any rows blank within the database. This article explains how to create a database in Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac, Excel for Android, and Excel Online.Enter the Data
The basic format for storing data in an Excel database is a table. Once a table has been created, use Excel's data tools to search, sort, and filter records in the database to find specific information. To follow along with this tutorial, enter the data as it is shown in the image above.Enter the Student IDs Quickly
Type the first two ID's, ST348-245 and ST348-246, into cells A5 and A6, respectively. Highlight the two ID's to select them. Drag the fill handle to cell A13. The rest of the Student ID's are entered into cells A6 to A13 correctly.Enter Data Correctly
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 initially, the program is more likely to give you back the results you want.Rows Are Records
Each row of data in a database is known as a record. When entering records, keep these guidelines in mind: Do not leave any blank rows in the table. This includes not leaving a blank row between the column headings and the first row of data.A record must 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.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.Data in a column must be entered using the same format. If you start entering numbers 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). Be consistent.The table must not contain any blank columns.Create the Table
Once the data has been entered, it can be converted into a table. To convert data into a table: Highlight the cells A3 to E13 in the worksheet.Select the Home tab.Select Format as Table to open the drop-down menu.Choose the blue Table Style Medium 9 option to open the Format as Table dialog box.While the dialog box is open, cells A3 to E13 on the worksheet are surrounded by a dotted line.If the dotted line surrounds the correct range of cells, select OK in the Format as Table dialog box.If the dotted line does not surround the correct range of cells, highlight the correct range in the worksheet and then select OK in the Format as Table dialog box. Drop-down arrows are added beside each field name, and the table rows are formatted in alternating light and dark blue.Use the Database Tools
Once you have created the database, use the tools located under the drop-down arrows beside each field name to sort or filter your data. Sort Data Select the drop-down arrow next to the Last Name field.Select Sort A to Z to sort the database alphabetically.Once sorted, Graham J. is the first record in the table, and Wilson R is the last. Filter Data Select the drop-down arrow next to the Program field.Select the checkbox next to Select All to clear all check boxes.Select the checkbox next to Business to add a check mark to the box.Select OK.Only two students, G. Thompson and F. Smith are visible because they are the only two students enrolled in the business program.To show all records, select the drop-down arrow next to the Program field and select Clear Filter from "Program."Expand 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 changes into a two-headed arrow.Press and hold 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 A14: ST348-255
Cell B14: Christopher
Cell C14: A.
Cell D14: 22
Cell E14: Science