Abrar-ur-Rahman Niloy
Most people use Microsoft Excel for accounting, tracking, or forecasting purposes. However, Excel can also be used as an excellent database software too. Because Excel is pretty powerful as a data entry and management software. With the help of Excel’s built-in tools, we can easily create any kind of database in it. This tutorial will demonstrate how you can create a library database in Excel and how to use a form to enter values in the database.
Download Practice Workbook
You can download the workbook used for the demonstration from the link below.
Watch Video – Create a Library Database in Excel
Overview of Library Database
By definition, a database refers to an organized collection of data that is stored and accessed electronically. This storage of data can be both in a local system or in an online cloud system.
So it is safe to say that, a library database is the organized collection of data such as books, papers, or different publications along with other information such as their author and/or publish date, due date, etc. It can also contain academic journals, newspapers, and magazines as its entries too.
Step-by-Step Procedure to Create a Library Database in Excel
To create a library database or any other database in Excel, we are mainly going to use Excel’s table feature. It has manyfold advantages. Firstly, it can automatically update new entries within a chart if you enter them manually. Then there comes the usage of the “form” feature in Excel that you can use with the table. And most importantly, you can filter or sort out data however you need- which is one of the key points of using a database.
In the following, we will be going over the step-by-step process to create a database and an entry form.
Step 1: Enable Form in Quick Access Toolbar
Microsoft Excel has a very handy “form” feature that helps input data into a table. We are going to use this feature as the entry method for our database. By default, Excel doesn’t yet have the feature in a ribbon. But you can easily add it to the quick access toolbar and use it afterward.
Follow these steps to enable the feature.
- First of all, 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.
- Then click on More Commands from the drop-down menu.
- As a result, the Excel Options box will open up. On the left of it, select Quick Access Toolbar.
- Then select All Commands under the Choose commands from option.
- After that select Form as the command from the list below it as shown in the figure below.
- Once selected, click on Add.
- Finally, 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. It is very important as it helps 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 so that it becomes a chart that can be converted into a table later on. We are resorting to the manual method to enter these values.
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
Now let’s convert the range into a table.
- For that, select a cell anywhere within the dataset.
- Then go to the Insert tab on your ribbon.
- After that, select Table from the Tables group.
- In the Create Table box that appeared, make sure the My table has headers option is selected. Then click on OK.
The range will now be converted into a table with filter buttons.
Similar Readings
- How to Create a Relational Database in Excel (With Easy Steps)
- Intro to Relational Database Management System (RDBMS) Concepts!
- How to Create a Database with Form in Excel
- How to Create a Searchable Database in Excel (2 Quick Tricks)
Step 5: Create New Entries
Up to this point, we can safely say that the database is ready to work with. You can now either enter new values under the previous row or use the form to update the database. If you enter values manually, it will automatically update the values into the table. It will help the newly added rows to sort or filter out later on. But we are choosing to go for the form entries because the interface is easy to work with for non-Excel users.
To create new entries with the form, follow these steps.
- First, select a cell anywhere in the table.
- Then select the form you have added on the quick access toolbar in the first step.
- At this instant, 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 still keep clicking on New and keep adding new entries to the table. For example, we have added another entry in the following figure.
- Once you are done entering, click on 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
You can also update any existing record with this form too. Let’s say we want to update the second entry’s due date to 8/18/2020. Follow these steps to see how we can do that.
- First, select any cell within the table.
- Then click on the form on the quick access toolbar.
- Now move up or down to go to the second entry in the form.
- After that, change your desired values. Here, we are changing the due date to 8/18/2020.
- Finally, 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
Similarly, you can remove existing records from the database too. We are removing the second entry for the demonstration here. Follow these steps to see how we can do that.
- Firstly, select a cell in the database.
- Then select the form from the quick access toolbar.
- Now navigate to the entry you want to remove. As we are removing the second entry for the demonstration, we are choosing the second one.
- After that, click on Delete.
- Finally, click on 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
You can also restrict some columns to take in irregular values. For example, let’s assume in our library, everybody has to return books within August 2020. So no due date entries will be valid after 31st august.
Follow these steps to see how we can restrict that.
- First, select the due date column.
- Now go to the Data tab on your ribbon.
- Then select Data Validation from the Data Tools group.
- After that, select the Settings tab on the Data Validation box.
- Then select Date in the Allow options.
- Now select an old start date as it is not our main concern here. But in the End date box, enter the value 8/30/2020.
- Once you are done, 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.
Read More: How to Use Database Functions in Excel (With Examples)
Conclusion
This is how you can create a library database or any other database in Excel. Hopefully, you were able to follow these steps with ease while creating your own database. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
For more guides like this, visit Exceldemy.com.
