How To Input Data Using Excel Form Data Without Macros

Table of Contents[Show]
How To Input Data Using Excel Form Data Without Macros

Excel Data Entry Form - Inputting data in an Excel table is not difficult, you only need to type something in a cell and then move to another cell to input other data. It's just that if the data columns are too many and exceed the width of the monitor screen, you might lose focus.

In this excel tutorial blog we have discussed how to input data in Excel. Apart from using the method of entering data as described on the page, Excel actually has a hidden feature that you can use to make it easier to enter data in a table.

The feature is “ Form Data ". You need to consider using Form data without VBA when inputting data on very wide data rows or many columns, so you need to shift the Excel display horizontally repeatedly.

Use Form data as another alternative to simplify your work in adding, editing, finding, and or deleting rows of data in an excel table.

Understanding Excel Form Data

DATA FORM is an Excel feature in the form of a dialog box that will automatically display up to 32 column headers as labels where each label contains an entry box that can be used to add or edit related data.

Understanding Excel Form Data

 

With Form Data, you can add new data, find specific rows of records with navigation menus, or change data fields and delete certain rows of data.

When you activate this Excel Form Data, data rows arranged horizontally or horizontally will be displayed vertically.

If the corresponding cell contains excel formulas, you cannot change the excel formulas and the form data will only display the results.

By default the command button to activate the form data feature is not displayed on the excel ribbon or menu bar, for that you must first remove it from its hiding place.

How to Activate the Form Data Menu

Before using this Form Data tool, you must first activate the Form command button which Excel hides by default. You do this by customizing the Ribbon in Excel or adding this menu to the QAT (Quick Access Toolbar).

If you have previously read the two pages of the tutorial, I am sure that the tutorial on how to activate this Form Data will be easy for you to follow.

Displaying the Form Data Button on the Excel Ribbon

Following are the steps to activate the form data command or button on the excel ribbon:

Displaying the Form Data Button on the Excel Ribbon

 

  1. Activate the Customize Ribbon dialog box by opening or Right clicking on any part of the ribbon then selecting menu .Tab FILE--Options--Customize Ribbon
  2. After the options appear Customize the ribbon, in the Choose Command From select section All Commands then find and select the command menu Form....
  3. On the right, make New Group or New TAB as a new place to place the Form Data command.
  4. If you have clicked Add to add the Data Form command or menu to the new Group or TAB.
  5. Click OK and you're done.

Displaying the Form Data Button on the Quick Access Toolbar (QAT)

If you will often need this Form Data, my suggestion is to put this Form command on QAT so that it is easier and faster to access, do the following:

How to Display the Form Data Button on the Quick Access Toolbar (QAT)

 

  1. Select the small triangle in the QAT section then select the More Commands menu on the menu options that appear so that the options appear Customize the Quick Access Toolbar.
  2. Another way to display the Customize the Quick Access Toolbar options is to select .Tab FILE--Options--Quick Access Toolbar
  3. After the options appear Customize the Quick Access Toolbar, in the Choose Command From select section All Commands then find and select the command menu Form....
  4. After finding click Add.
  5. Click OK and you're done.

After you have successfully brought up the Command Form, your Microsoft Office Excel is ready to be used to further take advantage of one of the Excel Data Form tools or features.

The next step you need is to prepare a data table that will be used to place the data that we will input through the Form Data Feature.

How to Use Excel Form Data

With the Data Form feature or tool in Excel, you can add, edit and delete records and perform filters to only display data records that meet certain criteria.

This feature is especially useful if you have a lot of rows and you want to avoid scrolling or shifting sheets repeatedly right and left.

Before using form data, the data table that you will use to save your data records should be formatted first as a Table Format.

If you have created the table format then you just need to activate the Form Data by selecting the button or menu that we have previously appeared on.

Data Entry Form in Excel
 

How to Add New Data

To add new data via Form Data:

  1. In the Form data select menu NEW.
  2. Then enter new data for rows that will be added to the available fields.
  3. After finishing typing the complete data for 1 row of data, press Enter to save changes and add the data to the last row of the data range or table.

How to Search and Find Data with the Navigation Menu

  1. To move to the next data, click Find Next.
  2. To move to the previous data, click Find Prev.
  3. You can also move between data using the scrollbar provided.
  4. To be able to move 10 lines at once, click the empty center of the scrollbar.

How to Search and Find Data with Specific Criteria

If the data in the table is too much, you can also filter with certain criteria so that the data displayed on the Data Form is not too much or in accordance with the filter criteria you use.

To filter the data displayed on the Form, do the following steps:

  1. Click the menu Criteria.
  2. Specify the filter criteria you want in the appropriate field column fields.
  3. Click Find Next or Find Prev to move between filtered data.

You can also use wildcard characters to filter this Form data.

Use To find
? (question mark) Any single character. For example, m? mom to display data containing "marry" and "mary"
* (star sign) Any number of characters. For example, pi * to display data that contains "bananas" and "pindang"
~ (tilde) followed by?, *, or ~ To include Question mark, Star sign or Tilde in criteria. For example, who ~? will find "who?"


How to Change or Edit Data

To change a data with Excel Form Data, the method is as follows:

  1. Find the row that you want to change so that it appears on the Form Data.
  2. Change the data in these rows as you need.
  3. After finishing changing the data, press Enter to update the row of data and the Form Data will automatically move to the next row.

How to Delete Specific Data Rows

To delete data with Excel Form Data, the method is as follows:

  1. Find the row you want to delete so that it appears on the Form Data.
  2. Click Menu DELETE
  3. If a warning notification appears for permanent data deletion, select OK to delete data and Form Data automatically moves to the next line.

How to Close Form Data

How to close this Form Data is quite easy, you only need to click the menu CLOSE or button in x the upper right corner of the Form Data.

The width of the fields in the Form Data follows the largest column width in your data table. If you feel that the width of the fields in the data form is too small, please expand one of the columns in the data table according to your needs.

Easy enough, right? Next, please practice the tutorial above and if you find a problem, please submit it in the comments column provided.

Leave a Comment