Using Excel to store data records is an oft-used — if sometimes frustrating — alternative to setting up a database. However, the data entry form in Excel is a pivotal tool. This article provides all the steps necessary to get started with the Excel data entry form. If you’re looking for something easier, download the free Excel template to get started immediately.
Use this free template to create a customized data entry form that allows you to apply multiple restrictions to a field (such as numeric and five digits long for a ZIP code field), make a field mandatory, or make it unique from all other records (for example, for an order number). Enter the number of fields needed for each record, select a title and options for each field in the record, and then move to the second sheet to start entering data.
Excel offers the ability to make data entry easier by using a form, which is a dialog box with the fields for one record. The form allows data entry, a search function for existing entries, and the ability to edit or delete the data.
The example below has two fields per record. The form allows up to 32 fields per record.
In addition to the navigation buttons, you can use the following keyboard commands to navigate through the form:
In addition to being able to enter data quickly and accurately, the form allows users to see more content without scrolling because the data appears in a vertical format (rather than horizontal). The form can also include data validation, including a dropdown list of pre-selected items in a column or ensuring that an entry meets certain criteria (such as a date or character length).
In general, the steps are to select an area in the sheet, insert a table, add titles at the top of the columns, and then find the form option in the data menu. To create a form, see the steps below for your version of Excel.
These instructions also apply to Excel 365, and to Excel 2019 for PC. These steps were created with a different version of Excel so the interface will look different, but the steps are the same.
Type data into the first field, tab to the next field, and repeat until all fields are completed. Press Enter on your keyboard to save the data and move to the next record.
Finding a Record
Once you have entered data, you may want to find a record. To do so, click Criteria, enter the search term into the appropriate field (e.g. “Chicago” into a city field). Click Find Next. The first matching record will appear in the form (the records displayed in the sheet won’t be filtered). Click Find Next to scroll through the remaining records, or click Find Previous to scroll upward. You can use wildcard characters such as “*” and “?” when searching the records.
Once the desired record is displayed in the form, select the field you want to change and edit the text. Hit Enter on your keyboard to save changes.
Deleting a Record
Once the desired record is displayed in the form, click Delete, then click OK.
The steps for adding, finding, editing, and deleting a record are the same as for Excel 2016, so follow the steps above. The appearance may be slightly different. In general, the steps are to select an area in the sheet, insert a table, add titles at the top of the columns, and then find the form option in the data menu.
In general, the steps are to select an area in the sheet, insert a table, add titles at the top of the columns, and then find the form option in the data menu. You can follow the steps for adding, finding, editing, and deleting a record in Excel 2016 above. Note that the interface may be slightly different.
Select an area in the sheet, insert a table, add titles at the top of the columns, and then find the form option in the data menu. Follow the steps above for Excel 2016 to add, find, edit, or delete. The interface may be slightly different.
A drop-down menu presents a predefined list of options in a data entry form field. This is useful when you need to enter a set list of employees, products, part numbers, etc. into a field.
Note: In some older versions of Excel, the dropdown will not appear in the form, but the field will still be restricted to the list in on the data sheet.
In addition to a restricted list, the form fields can have other restrictions. Select the column and click Data on the ribbon, click Validate, click the Allow field, and then choose one of the options below:
A submit button is a user-created control button that runs a macro when clicked. The macro can be any valid macro, such as sorting data, removing blank rows, or saving a sheet as a PDF.
There are two ways to create a button. One uses ActiveX, the other uses Visual Basic (VBA), so they are beyond the scope of this article. If you have experience with ActiveX or Visual Basic, Microsoft's support site offers instructions.
A UserForm is an Excel tool that is similar to a data entry form. However, it is more flexible because it allows the use of checkboxes, radio buttons, multiple selections, and scrolling lists to enter data in addition to text boxes. Creating Userforms requires knowledge of Visual Basic. If you have experience with Visual Basic, a tutorial for creating UserForms can be found here or here.
A data entry form can be created with VBA in the same way as a UserForm in Excel.
Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change.
The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed.
When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.
