How to Create a Database with Form in Excel

Get FREE Advanced Excel Exercises with Solutions!

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


Watch Video – Create a Database with Form in Excel



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.

How to Create a Database in Excel with Form Keyboard Shortcut in Excel Form What Is 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.

How to Create a Database in Excel with Form

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.

Create an Excel Table

After pressing OK, you’ll get the following table.

How to Create a Database in Excel with Form

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.

How to Create a Database in Excel with Form Adding Form Command in Excel Ribbon

  • Later, specify the Display name as Form, and press OK.

How to Create a Database in Excel with Form

  • 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.

How to Create a Database in Excel with Form

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.

Customize Quick Access Toolbar

  • Later, choose the Form command while selecting the All Commands from the option under Choose commands from.
  • Finally, press the Add and OK buttons.

How to Create a Database in Excel with Form

So, you’ll get the Form command in the QAT as depicted in the below image.

How to Create a Database in Excel with Form

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.

How to Create a Database in Excel with Form

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.

How to Create a Database in Excel with Form

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).

Adding a New Record

  • 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.

How to Create a Database in Excel with Form Adding a New Record

Thus, you can add a new record easily.

Adding a New Record


2. Deleting a Record from Database in Excel Form

Assuming that you want to delete the record about Blueberries (located at B10:F10 cells).

Deleting a Record

  • You have to find the record first. So, scroll down the icon after activating the Form command as illustrated in the following screenshot.

How to Create a Database in Excel with Form Deleting a Record

  • Now, you get the desired record (6 of 8). Just, click the Delete button.

Deleting a Record

  • Immediately, you’ll get the following message. Then, press OK.

Deleting a Record

So, the output excluding the record will be as follows.

Deleting a Record


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.

How to Create a Database in Excel with Form Updating or Editing a Record

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.

Updating or Editing a Record

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.

Searching with Criteria

  • Click over the Criteria button initially.

Searching with Criteria

  • Next, type Ohio in the box after the States field and choose the Find Prev button to find the previous record.

Searching with Criteria

Shortly, you’ll get your desired record (1 of 7).

Searching with Criteria

But if you want to get the next record, pick the Find Next button.

How to Create a Database in Excel with Form Searching with Criteria within Form

Therefore, you’ll get the record (4 of 7) having the States as Ohio.

Searching with Criteria


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).

Closing Excel Form


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.

How to Create a Database in Excel with Form Keyboard Shortcut in Excel Form

Unlikely, enter SHIFT + TAB keys for going to the previous field.

How to Create a Database in Excel with Form Keyboard Shortcut in Excel Form

Moreover, press ENTER key to get the next record instead of scrolling down.

How to Create a Database in Excel with Form Keyboard Shortcut in Excel Form

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.


Related Articles

Md. Abdul Kader
Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo