How To Insert Data Into A Worksheet Using Excel Data Entry Form – Step By Step Guide

A data entry or input form is just a way to quickly enter data into a cell or group of cells of a worksheet. It is mostly used when the Excel spreadsheet is too big or long for the screen. Instead of scrolling to and fro to enter data into the various columns and rows, you use a data entry form which relieves you of the scrolling stress.

Guides on how to insert data using the excel data entry form

For example, to insert records in a sales record worksheet for the month of January which has a column for each day of the month, you will have move to and fro, but a data entry form makes the data entry process faster, easier and less stressful.

The Data input Forms option is normally located from the Quick Access tool bar. But by default, the Data entry Form is hidden and needs to be called out. When it is called out, the icon resides in the Quick Access toolbar. The Quick Access toolbar located above the tab bar as shown below.

This is part 2 of chapter 7 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous part (part 1), I explicitly explained how to create a custom worksheet template in MS Excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series.

HOW TO CALL OUT THE DATA ENTRY FORM ICON AND CUSTOMIZE THE QUICK ACCESS TOOL BAR IN EXCEL

Customizing the Quick Access tool bar helps you to locate and position several options and commands such as the data form command icon, which are not available in the MS Excel Quick Access tool bar and tabs by default. It also allows you to customize each tab and call out tabs like the Developers tab and the Add-Ins tab which are hidden by default. All these are available in the Excel Option dialogue box.

To Call Out the Data Input or Entry Form Icon In any Version of MS Excel:

1. If you are using MS Excel 2007 and lower versions, click the Office button located in the extreme top left of the Excel workbook screen. Then click Excel Option which is the last option in the resulting menu list.

If you are using MS Excel 2010 and later versions, click the File menu or tab and then click Options. This prompts the Excel Option.

2. If you are using MS Excel 2007 and lower versions, click Customize located on the left menu.

If you are using MS Excel 2010 and later version, click the Quick Access tool bar option on the left section of the dialogue box as shown below. The rest of the steps are the same for all versions of MS Excel.

3. To add the Data Form option to the Quick Access toolbar, click the Choose Commands From drop down arrow and select Commands Not in the Ribbon from the resulting menu options.

4. Select Form from the resulting option and click the Add>> button located in between the second and the third column of the dialogue box as shown below.

NOTE:

To add or remove any other command to or from the Quick Access toolbar, Select the command from the Excel Options dialogue box and click the Add>> or the Remove<< button as the case may be.

5. Click OK to return to exit the dialogue box and return to your worksheet.

6. Next, type a column title for each of the days in the month of January in separate columns of the worksheet. For example, DAY 1, DAY 2, DAY3, …, DAY 31.

7. Highlight the column title row, which is normally row 1. Then click the Data Form icon you just added in the Quick Access tool bar (the last icon there). A notification dialogue box pops up asking if you would like to use the highlighted row as your column title. Click OK.

8. This prompts the Data Form dialogue box showing all the columns you highlighted.

9. Enter values for each of the column title’s text box as shown below.

10. Click the New button to start a new row or click the Delete button to delete the previous row or data entered as shown below. Click the Close button when you are through.