How to create a database in Excel (make in 8 easy steps)

Don’t know how to create a simple database in Excel? In this article, I will show how you can make a database in Excel in just simple 8 steps.

Do you find MS Access as a complex tool to use as a database? Then Excel is a great tool to do that.

Let’s learn the technique…

How to create a database in Excel

Unfortunately, they don’t teach hardcore Excel or MS Office tools in our respective schools and colleges. So, all the complicated functions and laws you read in your extremely awesome degree become just a theory, unsupported by real-life data. You’d think that it’s only data. But you should be well aware of the fact that it’s lovingly called a data dump. Yes, it’s so much in the quantity that you’d need a dumpster to tow it out of sight.

Hence it’s of extreme importance to be able to arrange this dump in a much more readable format – a format that at least vaguely looks like that theoretical concept that defined the phenomenon for this data.

MS Office functions come in extremely handy when struggling in such situations. Access is the standard database you should be using, but it’s a little complicated to learn and master before that first business meeting of your life tomorrow. Excel on the other hand is a little more familiar and easier to handle.

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 different ways; you can filter the database to see only the data that matches some specific criteria.

So, in this post, we will take an example and demo the steps in which you will be able to create an Excel-based database.

Step 1: Entering the data

The columns in a database are called fields. You can add as many may be necessary.

So, the Fields of this database are StdID, StdName, State, Age, Department, and Class Teacher.

Create Database in Excel - Image 1

You can now enter data into the database easily. Every new input will be added to the first empty row after the Fields.

I did some. Let me show you how I enter another entry.

Say this is the input that has to be inserted into the database:

StdID: 1030456042,

StdName: Jemmy Fox,

State is Oklahoma,

Student age 25,

Department CSE,

And the class teacher is Mr. John.

Database creation steps in Excel image 2

So, you see entering data into an Excel database is pretty basic.

Step 2: Entering Data Correctly

When you will enter data into a database, you cannot leave a row empty. This is strictly prohibited.

Say after the last row, I put some data in the 2nd row from it:

StdID is 103457045,

StdName is Jackson,

State is New York,

Age 23,

Department ETE,

And the class teacher is Mr. Balmar.

Database creation steps in Excel image 3

This is a clear breakdown of this database.

Although it might happen that some of the cells in a row could be empty. Let’s say something like this is legal.

Database creation steps in Excel image 4

On the same lines, another rule is: there will be no completely empty column in a database.

database creation steps in excel image 5

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 on doing with, your data will not be performed on this disconnected piece of information. For example, something as simple as filtering will be unsuccessful as you can tell by experience.

Read More: How to Use Database Functions in Excel (With Examples)

Step 3: Know that the Rows are called Records

The next thing you have to know is: each individual row in a database is known as records.

All the rows are records. Here I have marked a few for clarity.

Database creation steps in Excel image 6

Step 4: Know that the Columns are called Fields

All these columns are Fields. The headings of the columns are known as Field Names.

Database creation steps in Excel image 7

So StdID, StdName, State, Age, Department, and Class Teacher are six Field Names of this database.

Note: It is important to format the Field Names differently than the other rows in the database. These table Field Names are formatted with different styles than other cells in the table.

Step 5: Creating the Table

To create a table: Just select any cell in the data range, Insert tab, and click on the Table command.

Database creation steps in Excel image 8

Create Table dialog box appears. I take what Excel suggests. Yes, my table has headers. Click OK, and a table is created. Tada!!!

Database creation steps in Excel image 9

You can filter data using the drop-down arrows that appear at the headings of each column. If you want to learn table manipulation more go visit the Table portion here.

Step 6: Using the Database Tools

The database tools can come in handy with your data analysis and interpretation. You can and should learn more about these tools here.

Step 7: Expanding the Database

Now that everything is up and running, you can start adding more fields and records (you see what I did there) to your database. It’s as basic as Step 1.

Read More: How to Create a Database That Updates Automatically in Excel

Step 8: Completing the 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).

This is again a short summary of how to create a database in Excel.

Database creation steps in Excel image 10

Read More: How to Create a Simple Database in Excel VBA

Download the working file

For further clarification, the working files have been added with a bonus sample of a Banking Database. Drill down through the 8 steps to understand how this database was created and can be used!

Related Articles



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

  1. Hi Kawser

    Ofcourse you can use excel as database, but the strength of joining 2 or more fields in separate tables is really in the database model. Also using 2 million rows in a table is to slow in excel.
    But maybe i’m on the advantaged side, as i know both programs very well.

    If you can prove that i’m wrong i’ll be very interested.

    • Nope, Jan. You’re absolutely right. Excel can be used as a database when your database is not heavy and need a small load of query on it.

  2. Thank you so much Dr. Kawser!


  3. Very good, thanks

  4. Hi Dr.Kawser,
    We have a very large excel file We would like to treat it as a database by using queries.SO I read we need to connect MS access to treat Excel as DB to connect using DB connector.
    Is there any alternative to not use MC Access.

  5. hi I am trying to create a database with patients information where I can open a stats sheet and by entering maybe the patients registration number I can maybe press ctrl +m and pull the other information that pertains to the patient from the database can u help please

  6. Reply
    Ashley Epps-Smith Sep 13, 2021 at 8:08 PM

    I have raw data with titles and need to turn into a database. The data has patient addresses, emails and phone numbers. can you advise on how you assisted Sherry?

Leave a reply