How to create multiple data entry forms under 10 seconds without VBA. If you select a cell inside your data and press ALT + D + O you can work even faster because the form opens immediately without having to create an icon in the Quick Access Toolbar (QAT).
The number of columns in our row of data can be large. Data entry becomes difficult. It can require horizontal scrolling and can be time consuming. Using a data form to add, edit, find, and delete rows can make life easy.
A data form can provides a quick and comfortable way to enter or display one complete row of information in a range or table without scrolling horizontally. You will find that using a data form can make data entry easier than moving from one column to another when you have multiple columns of data which cannot be viewed on the screen easily. We can use a data form that lists the column headings as labels. Of course. if you need combo-boxes, list-boxes or spin buttons you’ll have to create a form using the ‘user-form‘.
Microsoft Excel can automatically generate a built-in data form for our range or table. The form displays all column headers as labels in a dialog box. Each label has a neighboring blank text box in which you can enter data for each column. You can have up to a maximum of 32 columns. In a data form, you can enter new rows of data, find data by navigating forward and backward, or edit and delete data. You can also search for data based on on criteria. If a cell contains a formula, the formula result is displayed in the data form. You cannot edit the formula by using the data form and the calculations are displayed automatically.
You cannot print a data form. Because a data form is a modal dialog box, you cannot use either the Excel Print command or Print button until you close the data form. However, you can use the Windows Print Screen key to make an image of the form, and then copy and paste it to Microsoft Paint. Now you can print the form after editing it to your requirements.
How to add the Form button to the ribbon:
- Add a column header to each column in the range or table. MS Excel uses these column headers to create labels for each field on the form
- Ensure that there are no blank lines in the range of data.
- Select a cell in the range or table to which you want to add the form
- Click the arrow next to the Quick Access Toolbar, and then click More Commands
- In the Choose commands from box, click All Commands, and then check the check-box next to the Form button Form Control button in the list
- Click Add
- And then click OK
- On the Quick Access Toolbar, click Form Control button
- Note: If a message appears stating “Too many fields in the data form,” you need to reduce the number of columns, because a data form can contain only up to 32 columns. One solution is to insert a blank column or two to break one range into two ranges. If required you can now create a separate data form for the columns to the right of the blank column.
How to use the data form
You can use a data form to add, find, change, and delete rows in a range or table.
How to add a new row of data
- Click New in the data form
- Type the data for the new row in the fields
- To move to the next field in the row, press Tab. To move to the previous field, press Shift+Tab
- After you have finished typing data, press Enter to save your changes. You will notice that a new row of data has been added to the bottom of the range or table
- Note: Before you press Enter, you can undo any changes by clicking Restore in the data form. Any data that you have typed in the fields will be discarded
How to navigate through the data using the data form:
- To move through rows one at a time, use the scroll bar arrows in the data form
- To move through multiple rows at a time, click the scroll bar in the area between the arrows
- To move to the next row in the range or table, click Find Next
- To move to the previous row in the range or table, click Find Prev
How to find a row by entering search criteria
- Click Criteria, and then enter the comparison criteria in the data form
- All items that begin with the comparison criteria are filtered. For example, if you type the text ‘Ma’ as a criterion, Excel finds “Maria” and “Mackenzie”
- To find text values that share a few characters, use a wildcard character like ? or * as your criterion
|?||Any single character|
For example, Ma?on finds “Mason”
|* (asterisk)||Any number of characters|
For example, *west finds “Northwest” and “Southwest”
- To find rows that match the criteria, click Find Next or Find Prev
- To return to the data form so that you can add, change, or delete rows, click Form
How to edit or change data in a row
- Find the row you wish to change
- Change the data in the row
- After you finish editing data, press Enter to update the row
- Excel moves to the next row automatically
- Note: Before you press Enter, you can undo any changes by clicking Restore
How to delete a row
- Find the row that you want to delete
- Click Delete
- Excel prompts you to confirm the operation.
- You cannot undo a row deletion after you confirm it
How to close the data form
- Click Close in the data form
- You will be returned to the worksheet
Reference: Learn about data forms