Let’s Get Started!
Forms are especially useful if you have many columns and wish to avoid horizontal scrolling.
For our example, we will use a small table with only five columns:
- Name
- Department
- Hire Date
- Salary
- Status
It doesn’t matter if you are working with a small table, or a table with dozens of columns; the effort is the same.
For most users, populating the table would be accomplished by typing the information directly into the cells below the column headings.
If you wish to edit existing information, the user needs to find the data in the table, select the cell, get back into edit mode, and perform the update.
An alternate way to perform data entry as well as the updates and edits, is to use the Form tool.
Launching the Form Tool
If you look on the Data ribbon (or any other ribbon) you will have a difficult time locating the Form button. This is because it’s not visible by default; you must ask for it (saying “please” never hurts.)
We will add the Form button to the Quick Access Toolbar (QAT). To add the Form button, click the small downward pointing arrowhead to the right of the QAT and select More Commands…
In the Excel Options dialog box, change the Choose Commands From: dropdown from Popular Commands to Commands Not in the Ribbon. Scroll down the left panel until you see Form… and click one time to select. Click the Add button in the middle to move the Form option to the right-side list.
Now that we have the Form tool in our QAT, click somewhere in the data that makes up your table and click the Form button.
NOTE: If you do not have your cursor located within the data when you click the Form button, the feature will not launch, and you will receive the following error message.
Adding New Records via the Form
To add a new record to the table, click the New button. This will clear the form of any existing data and allow you to enter a new record.
Once you have filled in the fields with the new record’s data, press ENTER to submit the new information to the table.
Additional Features of the Form Tool
Updating Existing Records
The Form tool can be used to scroll through the records of a table, examining each one by one in a user-friendly interface. This is accomplished by pressing the Find Prev and Find Next buttons or by using the vertical scroll bar.
If you come across a record with incorrect information, you can update the field and press Enter to commit the new data to the existing record.
Searching for Records
To place the form in search mode, press the button labeled Criteria.
This allows us to enter a myriad of search criteria and have the form return and display only records that meet that criteria.
Example 1: If we enter “James” in the Person field and press Find Next, we can step through and view each record that begins with the word “James”.
Example 2: If you wish to locate ALL records that contain the word “James”, you can employ wildcards to broaden the qualifications. By entering “*james” you will discover all records that contain the word “james” regardless of position.
Example 3: You can also use relational operators to discover records, such as “>” greater than, “<” less than, “=” equal to, etc…
If you wanted to locate all records from a specific date, you could enter something like “=1/1/2019”. If you wanted to locate all records with a salary greater than or equal to $100000, you could enter “>=100000”.
NOTE: If you accidentally alter a displayed record and you wish to revert to the original record information, click the Restore button. This assumes you have not pressed ENTER and committed the data to the table. If that occurs, you can fall back and use UNDO to restore the information.
Using Data Validation with Forms
If you apply Data Validation rules to a table, those rules carry over to the Forms tool.
Example 1: If we want to ensure that an entered date must be less than or equal to today’s date, we can select the date field and click Data (tab) -> Data Tools (group) -> Data Validation.
In the Data Validation dialog box, on the Settings tab, click the dropdown for Allow and select “Date”. Next, click the dropdown for Data and select “greater than or equal to”. In the Start Date field, enter the below formula.
=TODAY()
This formula is a great way to create a sort of “moving target” so you don’t have to update the rule every day to reflect a new date.
We can also add a custom error message by selecting the Error Alert tab and entering text in the Error Message field, such as “Please enter a date on or before today’s date.”
If we return to the form and enter a data greater than today’s date, we will be presented with the following message.
Data Validation “Limitation”
One of the oddities of using Data Validation with Forms is in the use of Lists.
When you apply a List rule to a column in a table, the dropdown feature does not carry over to the form.
Although this feature is not presented, the logic/restriction of the rule is still in effect. If you were to type in a set of text that is not defined as an allowable selection, your data will be rejected just as it would in a normal Data Validation scenario.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
