How to Create a Database with a Form in Excel

An Excel Data Form is a handy tool that simplifies data entry by allowing you to input one complete record at a time, without the need to scroll horizontally between columns. The below screenshot represents the interface of the Excel Form.

Step 1 – Insert Dataset with Column Headings

• Ensure your existing dataset includes column headings (e.g., Fruits, ProductID, States, UnitPrice, and Sales).
• Column headings are essential for using the Excel Form.

Step 2 – Create an Excel Table

• Select the entire dataset (including column headings) or place the cursor in any cell within the dataset.
• Press CTRL + T to create an Excel table (alternatively, go to the Insert tab and select Table).
• In the Create Table dialog box, check the box for My table has headers.

Step 3 – Add Forms to Ribbon or QAT (Quick Access Toolbar)

• By default, Excel doesn’t provide the Form command in the ribbon.
• To add it:
  • Go to File, click on Options or right-click the blank space in the Excel ribbon and choose Customize the Ribbon.
  • In the Excel Options dialog, click the New Tab option.

  

  • • Rename the new group as Form and select it.

    

    • • From the Choose commands from dropdown, pick All Commands.
      • Add the Form command to the group.

      

      • Alternatively, add the Form command to the QAT:
        • Customize the QAT (upper side of the ribbon).
        • Choose More Commands.

        

        

        The Form command will be in the QAT as depicted in the below image.

        

        Step 4 – Input Data Using the Form

        • Choose any cell within the table.
        • Access the Form command from the New Tab or QAT.

        

        • The entire table is automatically added to the Excel Form.
        • For example, if you’re observing the first record (e.g., Bananas), the Form will display it.

        

        Regardless of the cell you choose, the Form shows the first record.

        Essential Tips

        When you create a database in Excel with Form, you should know about these basic things that would be beneficial for you.

        Tip 1 – To Add a New Record

        When creating a database with the Form:

        • To add a new record:
          • Insert column headings and convert them into an Excel Table (press CTRL + T).

          

          • • Select any cell within the table and use the Form command.
            • Manually enter data for each field (e.g., Fruits: Bananas) and click New.

            

            You can easily add a new record.

            

            Tip 2 – To Delete a Record

            • Locate the record (e.g., Blueberries at B10:F10 cells).

            

            • Scroll down after activating the Form command to find the desired record.

            

            

            • You’ll get the following message. Press OK.

            

            The record has been deleted.

            

            Tip 3 – Updating or Editing a Record

            

            Note: If the Restore button doesn’t work, simply press ENTER.

            

            Tip 4 – Searching with Criteria within the Database Using Form

            To find a specific record (e.g., the State of Ohio):

            

            

            • Type Ohio in the box next to the States field.
            • Choose the Find Prev button to locate the previous record (1 of 7).

            

            

            

            

            Tip 5 – Closing Excel Form

            • To close the Excel Form:
              • Click the Close icon in the upper right corner of the interface.
              • Alternatively, press the Close button (Esckey).

              

              Tip 6 – Keyboard Shortcut to Creating a Database in Excel Form

              While working in the Excel Form:

              • Press TAB to move to the next field sequentially.

              

              • Use SHIFT + TAB to go to the previous field.

              

              • Press ENTER to navigate to the next record without scrolling down.

              

              Download Practice Workbook

              You can download the practice workbook from here: