How to Create a Library Database in Excel (with Easy Steps)

Watch Video – Create a Library Database in Excel

Step 1 – Enable Form in Quick Access Toolbar

Go to the quick access toolbar on the top of the Excel tab and click on the downward-facing arrow at the end of it.

Click on More Commands from the drop-down menu.

The Excel Options box will open up. On the left of it, select Quick Access Toolbar.
Select All Commands under the Choose commands from option.
Choose Form as the command from the list below as shown in the figure below.
Once selected, click on Add.

Click on OK.

You will now have the form feature available in the quick access toolbar.

Step 2 – Create Headers for Dataset

Once you have enabled the form, it is now time to set up the database. First, we have to create headers for our database. This is very important as it helps when you track down data later on in the database.

So enter suitable headers in this step to create your library database in Excel. We have selected the following headers for the demonstration.

Let’s make some modifications to make the headers stand out.

Step 3 – Input First Entry

Let’s enter the first row of data manually so that it becomes a chart that can be converted into a table later on.

Make sure that stuff like date or phone number is in your desired format, if you have one, in this step.

Step 4 – Insert Table

Select a cell anywhere within the dataset.
Go to the Insert tab on your ribbon.
Select Table from the Tables group.

In the Create Table box that appeared, make sure the My table has headers option is selected.
Click OK.

The range will now be converted into a table with filter buttons.

Step 5 – Create New Entries

Select a cell anywhere in the table.
Choose the form you have added on the quick access toolbar in the first step.

A box will now open up with the name of the spreadsheet. To add a new entry, click on New.

Now you will be able to add new entries in the empty field.

You can click on New again to keep adding new entries to the table. For example, we have added another entry in the following figure.

Once you are done entering, click Close. You will notice the database will get automatically updated.

If you want to update the database, later on, follow this step from the beginning.

Step 6 – Modify Existing Records

Select any cell within the table.
Click on the form on the quick access toolbar.

Move up or down to go to the second entry in the form.

Change your desired values. Here, we are changing the due date to 8/18/2020.

Click on Close.

You will now see the due date of the second entry of the database will get updated.

Step 7 – Remove Existing Records

Select a cell in the database.
Choose the form you created from the quick access toolbar.

Navigate to the entry you want to remove. As we are removing the second entry for the demonstration, we are choosing the second one.

Click on Delete.

Select OK in the warning box.

You will see the (previously entered) second entry is not in the database anymore.

Step 8 – Restrict Particular Data Entries

Let’s assume in our library, everybody has to return books by August 2020. So no due date entries will be valid after 31st August.

Select the due date column.

Go to the Data tab on your ribbon.
Select Data Validation from the Data Tools group.

Select the Settings tab on the Data Validation box.
Choose Date in the Allow options.
In the End date box, enter the value 8/30/2020.

Click on OK.

Now no due date can’t be entered that is past August 2020. If you try to add one, either through the form or manually, you will see the following error message.