How to Create A Data Entry Form in Microsoft Excel.

Photo by Burst on Unsplash

Excel is the widely used spreadsheet software to record and process data. However, when it comes to entering data in an Excel file that has so many fields (columns), it is tedious and inconvenience moving across the column heads back and forward. If the number of entries is small it is manageable but for bigger number of entries, it can be quite a problem to complete.

Activate excel form on the Ribbon.

Excel does have decent data entry form command but it is hidden from the Excel Ribbon command. So the first thing is to  make it visible on the Quick Access toolbar. Follow the these steps to activate the form command.

  1. Click on Customize Quick Access button on the Quick Access toolbar. From the drop down list select More Commands option. This will show the Excel Options dialog box.

2. From the dialog box, under Choose Commands from, select the down arrow and choose Commands Not on Ribbon.

3. The hidden commands list is in alphabetical order so scroll down and select Form.

4. Click the Add button in the middle so the command icon show on the Quick Access Toolbar.

5. Click OK.

The form command will be shown on the Quick Access Toolbar.

Convert the data set as a table.

The next step is to specify the data set as a table.

  1. Enter required field or column headings and then enter at least two or three records.
  2. Click Insert Tab on the ribbon and click Table
  3. From the Insert Table dialog box, check My table has headers.

4. Click OK.

5. From the Design Table contextual tab, uncheck the Filter checkbox to remove the column filters.

Excel will now recognize the data set as a Table.

Activate the Form for the Data Set

  1. Click in cell within the table.
  2. Click on the Form command icon on the Quick Access Toolbar. This will activate the form and it will show in front.
  3. Start entering the data. To move the to next field textbox, press  Tab on the keyboard.. Scroll bar is also available to scroll down.
  4. Once the last field is filled, press the Enter key to post the data into Excel row.

Repeat the process to complete all the records.

Note: To exit close the Form and resume entry, click the Form command again on the Quick Access Toolbar.

Although the form looks like a simple interface but it does help make data entry a lot much easier than entering direct.