Md. Abdul Kader
In many cases, you might need to create a database in Excel. Luckily, Excel provides a data entry Form to accomplish such a task efficiently instead of doing it traditionally. In this instructive session, I’ll present you with a step-by-step guide on how to create a database in Excel with Form including some essential things related to the Form.
Download Practice Workbook
What Is a Data Entry Form in Excel?
In general, users input their data cell by cell in Excel. While inputting in this way, you may get troubled particularly if you want to produce a larger database. In such a situation, you may utilize the data entry Form to handle numerous data effectively. The below screenshot represents the interface of the Excel Form.
4 Steps to Create a Database with Form in Excel
Now, you’re going to explore the step-by-step process of creating a database in Excel with Form. However, in this section, I’ll show how to apply the Excel Form from an existing dataset. And, you’ll see the process of adding a new record for not having any dataset in the later section.
Step 01: Insert Dataset with Column Headings
- Firstly, you need to look over the existing dataset whether column headings (i.e. Fruits, Product ID, States, Unit Price, and Sales) are available or not.
Note: Needless to say, it is mandatory to keep the column headings in the dataset for using the Excel Form.
Step 02: Create Excel Table
- Secondly, select the entire dataset including the column headings, or keep the cursor over any cell within the dataset.
- Next, press CTRL + T to create an Excel table (Alternatively, you can go to Insert tab > Table).
- When you see the Create Table dialog box, don’t forget to check the box before My table has headers option.
After pressing OK, you’ll get the following table.
Step 03: Add Forms in Ribbon or QAT (Quick Access Toolbar)
Most likely, you won’t get the Form command in your Excel ribbon as it is not provided by the default. So, you have to add this command.
- Initially, go to File tab > Options or right-click over the blank space of the Excel ribbon > choose the Customize the Ribbon option.
- In the Excel Options dialog box, click over the New Tab option. Then, pick the Rename option after selecting the New Group (Custom) option.
- Later, specify the Display name as Form, and press OK.
- More importantly, while keeping the cursor over the created Form (Custom) group, pick the All Commands option from the drop-down list of the Choose commands from.
- Finally, choose the Form command > Add button > OK button as shown in the below screenshot.
Alternatively, you can keep the Form command in the Quick Access Toolbar (QAT).
- Select the icon of the Customize Quick Access Toolbar located on the upper side of your excel ribbon.
- Therefore, pick the More Commands option.
- Later, choose the Form command while selecting the All Commands from the option under Choose commands from.
- Finally, press the Add and OK buttons.
So, you’ll get the Form command in the QAT as depicted in the below image.
Step 04: Input Data Using the Form
Right now, you’re ready to utilize the Form command!
- Before anything else, choose any cell within the table and pick the Form command either from the New Tab or QAT.
Eventually, your entire table is added automatically inside the Excel Form. For example, you’re observing the first record (1 of 8) i.e. Bananas in the below figure.
Note: No matter which cell you choose, the Form will show you the first record. Let’s say, you select the B10 cell, and then turn on the Form command, you’ll get the record containing Bananas!
Read More: How to Create a Simple Database in Excel VBA
Essential Things While Creating Database with Form
When you create a database in Excel with Form, you should know about these basic things that would be beneficial for you certainly.
1. Adding New Record to Create Database in Excel Form
This feature will be fruitful for you if you want to add a new record for the first time. Also, you can add the new record to the existing dataset if necessary.
- Firstly, you have to insert column headings (as shown in the below image) and convert them into an Excel table (press simply CTRL + T).
- Subsequently, pick the Form command while selecting any cell within the table.
- Then, insert the data manually for every field (e.g. Fruits: Bananas), and click over the New button.
Thus, you can add a new record easily.
2. Deleting a Record from Database in Excel Form
Assuming that you want to delete the record about Blueberries (located at B10:F10 cells).
- You have to find the record first. So, scroll down the icon after activating the Form command as illustrated in the following screenshot.
- Now, you get the desired record (6 of 8). Just, click the Delete button.
- Immediately, you’ll get the following message. Then, press OK.
So, the output excluding the record will be as follows.
3. Updating or Editing a Record in Excel Form
Sometimes, you may need to update or edit data within a record. Let’s say, you want to update the Sales in the F6 cell as $4000.
- First of all, go to the second record (2 of 7).
- Then, type $4000 in the Sales box and press the ENTER key.
Note: In some cases, the Restore button doesn’t work. Press the ENTER key in such a case.
So, the updated output will be as follows.
Read More: How to Create a Database That Updates Automatically in Excel
4. Searching with Criteria within Database Using Form
If you want to search for a particular record, you can also do that. For example, you may find the record containing the State of Ohio.
- Click over the Criteria button initially.
- Next, type Ohio in the box after the States field and choose the Find Prev button to find the previous record.
Shortly, you’ll get your desired record (1 of 7).
But if you want to get the next record, pick the Find Next button.
Therefore, you’ll get the record (4 of 7) having the States as Ohio.
5. Closing Excel Form
To close the Excel Form, choose the Close icon located at the upper right corner of the interface or press the Close button (the keyboard shortcut is the Esc key).
6. Keyboard Shortcut to Create a Database in Excel Form
Last but not the least, the following keyboard shortcut might be handy while working in Excel Form.
So, press the TAB key for going to the next fields sequentially.
Unlikely, enter SHIFT + TAB keys for going to the previous field.
Moreover, press ENTER key to get the next record instead of scrolling down.
Read More: How to Use Database Functions in Excel (With Examples)
Conclusion
That’s the end of today’s session. This is how you can create a database in Excel with Form. I firmly believe this article would be highly beneficial for you. Anyway, don’t forget to share your thoughts.
