How to Create a Database in Excel

Step 1- Enter Data

The fields in this database are StdID, StdName, State, Age, and Department.

Insert the data:

StdID: 1510060,

StdName: Jimmy,

State: Florida,

Student’s Age: 23,

Department: ME

Step 2 – Don’t Leave Any Row Blank

The database can have neither blank rows nor blank columns.

Step 3 – Fields and Records

All rows are Records.

All columns are Fields. The headings of the columns are Field Names.

Read More: How to Create Student Database in Excel

Step 4 – Create the Excel Table

• In the Insert tab, click Table.

• In Create Table select $B$4:$F$10.
• Check My table has headers.

The table is created.

Filter data by clicking the drop-down arrow in the column you want to filter.

Step 5 – Use Database Tools

Database tools improve data analysis and interpretation.

Step 6 – Expand the Database

Add more fields and records to your database.

Step 7 – Complete Database Formatting

To format cells in a database, use Cell Styles,  Format As Table or Format Cells. Custom Number Format is also an option.

Create a Searchable Database in Excel

Steps:

• Select F5 and enter the following formula.

• Press ENTER and use the Fill Handle tool.

The output is displayed:

• Select C4, go to the Data tab >> Data tools >> Data Validation.

• In the Data Validation dialog box, select Settings >> List in the Allow section >> enter your Filtered cell in the Source box and enter the following formula:

• In the Error Alert tab, uncheck the box Show error alert after invalid data is entered.
• Click OK.

A searchable database is created.

Read More: How to Create a Library Database in Excel

Create a Database that Updates Automatically in Excel

 Steps:

• Select all data. Go to the Insert tab >> select PivotTable >> From Table/Range.

A Pivot Table will be created.

• Select the columns you want to update.

• Right-click any cell and  Refresh. The Pivot Table will automatically update.

Read More: How to Create a Client Database in Excel

Create a Relational Database in Excel

Steps:

• Select the entire range Dataset2.

• Go to the Insert tab >> PivotTable >> From Table/Range.

• Go to another sheet Dataset1, here, and create a table.

Note: You can use CTRL + T to create a table.

• Select the field you want to relate to in Pivot Table. Designation and Salary from two different worksheets were selected here.

• In the PivotTable Fields, choose All and click CREATE.

A relational database will be created.

Practice Section

Practice here.

Download Practice Workbook

Download the following workbook and practice.

Related Articles

• How to Create a Recipe Database in Excel
• How to Create an Employee Database in Excel

<< Go Back To Database in Excel | Learn Excel