How to Create a Database in
Excel: with Easy Steps
If you design your Excel workbook correctly, you can
use it as a database easily. The main key point is that
you have to design your workbook correctly. You can
sort data in many ways; you can filter the database to
see only the data that matches some specific criteria.
create an Excel-based database.
Step 1: Enter Data
The columns in the database are called fields. You can
add as much as necessary.
So, the fields in this database are StdID, StdName,
State, Age, and Department.
You can now enter data into the database easily.
Every new input will be added to the first empty row
after the fields.
We did some. Let us show you how we enter another
entry.
Say this is the input that has to be inserted into the
database:
StdID: 1510060,
StdName: Jimmy,
State: Florida,
Student’s Age: 23,
Department: ME
So, you see entering data into an Excel database is
pretty basic.
Step 2: Don’t Leave Any Row Blank
When you enter data into a database, you cannot
leave a row empty.
Say after the last row, I put some data in the 2nd row
from it:
StdID: 1510060,
StdName: Jimmy,
State: Florida,
Student’s Age: 23,
Department: ME
This is a clear breakdown of this database. Although it
might happen that some cells in a row could be
empty. Let’s say something like this is legal.
Along the same lines, another rule is that there will
be no completely empty column in a database.
What happens as soon as Excel encounters a
completely blank row or column is that it’s unable to
include that row or column in the database. For Excel,
this database is now divided into two parts, an entirely
new and unconnected set of information. Whatever
functions you plan to perform, it will not perform your
data on this disconnected piece of information. For
example, something as simple as filtering will be
unsuccessful, as you can tell by experience.
Step 3: Explore Two Necessary
Terms
The next thing you have to know is that each
individual row in a database is known as Records.
All the rows are Records. We have marked a few here
for clarity.
Furthermore, all these columns are Fields. The
headings of the columns are known as Field Names.
So, StdID, StdName, State, Age, and Department are
the five Field Names of this database.
Step 4: Create the Excel Table
To create a table, follow the steps below.
Firstly, go to the Insert tab and click on
the Table command.
Next, a window called Create Table will appear.
Now, select the cell range i.e. $B$4:$F$10 by
dragging the cursor simply.
Don’t forget to check the box before My table has
headers option.
Immediately, a table is created. Tada!!!
Also, you can filter data using the drop-down arrows
that appear at the headings of each column.
Step 5: Use Database Tools
Database tools can come in handy with your data
analysis and interpretation. You can and should learn
more about the database tools.
Step 6: Expand the Database
Now that everything is up and running, you can start
adding more fields and records (you see what we did
there) to your database. It’s as basic as Step 1.
Step 7: Complete Database
Formatting
The last and final step is formatting the database
columns. There are so many tools to format the cells
in a database. You can work with Cell Styles, you can
use the styles under the Format As Table drop-
down, and you can work with the commands in
the Format Cells dialog box. You can use
the Custom Number Format. All these techniques are
described in our previous lectures.
So, there you go! You created your very own database
in Excel (until you master Access, or you run out of
Excel space and processors).
How to Create a Searchable
Database in Excel
Sometimes, we need to search for our expected data
from a huge data source. For that reason, we may
need a searchable database from where we can easily
get our data. To create a searchable database, you
need to follow some simple steps.
📌 Steps:
First of all, select cell F5 and write down the formula.
=FILTER(C5:C10,ISNUMBER(SEARCH(Database!
C5,C5:C10)),”Not Found”)
Formula Breakdown:
SEARCH function→ Generally, it searches for a certain
value that you demand.
ISNUMBER function→ It is a logical function that
returns TRUE if the output of the SEARCH function is
a number. Otherwise, it will return False.
FILTER function→ Basically, it filters the output value
according to your desired criteria.
Press ENTER and use the Fill Handle tool.
Then, your output is shown just like the picture
below.
After that, select cell C4 and go to the Data tab
>> Data tools >> Data Validation.
A dialog box will pop out named Data Validation.
Select Settings >> then select List in
the Allow section >> enter your Filtered cell in
the Source box. So, put the following formula in
the Source box.
=$F$5#
go to the Error Alert option.
In the Error Alert, uncheck the box named Show
error alert after invalid data is entered.
Press OK.
Finally, a Searchable Database is ready for you! Now,
if you type “P” in the B4 cell, you’ll see the full
employee’s name “Peter” automatically.
How to Create a Database in
Excel That Updates
Automatically
The data that we enter in a database needs to
be updated automatically. For these, we’ll create
a Pivot Table for the Source dataset. After enabling
the Refresh feature, we can automatically update the
newly entered data in our previously created Pivot
Table. Follow the steps to do that.
📌 Steps:
First of all, select all the data from the cell. Go to
the Insert tab >> select PivotTable >> From
Table/Range.
A Pivot Table will be created. From there, you can
select the columns you want to use to update.
Finally, right-click on any cell, then select
the Refresh command, and the Pivot Table will
automatically update your data if you change it in
your main worksheet.
How to Create a Relational
Database in Excel
A Relational Database mainly identifies relations
between several different worksheets. The Relational
Database helps us to quickly look for and pull out
certain information. It can display the same data
values in several ways.
Let’s say, we have two databases, i.e. Database1 and
Database2. Databaset1 contains Employee names
with their Salary whereas Database2 consists of
Employee names with their Designation. Now, we want
to create a relational database between the two
databases based on the Employee field. Please follow
the steps below to do that.
📌 Steps:
Initially, select the entire range from Dataset2.
Then, go to the Insert tab >> PivotTable >> From
Table/Range.
After that, go to another worksheet
named Dataset1 and create a table that we
discussed before.
Note: You can use the keyboard shortcut CTRL +
T for creating a table.
Then, a Pivot Table will appear, and you have to
select the field you want to make a relation with. For
example, here, we select
the Designation and Salary columns from two
different worksheets.
After selecting the data, click on the CREATE option
under All in the PivotTable Fields dialog box.
Finally, our Relational Database will be created, as
shown in the picture below.
Practice Section
We have provided a practice section on each sheet on
the right side for your practice. Please do it by
yourself.
