What is Excel data entry

Data entry in Excel with a data form - created in 30 seconds without VBA!

Microsoft Excel® offers many good functions that are known and used all over the world. On the other hand, there are features that are not yet known or undiscovered, although these lead to amazing results. Such as the simple input mask. In this article I want you to integrated data form in Excel to introduce.

What is a data form?

Data forms are used everywhere to insert data of any kind in tables. These are input masks, the aim of which is to simplify the input of the data.

To meet this requirement, Microsoft Excel® has built in a data form for entering the data in Excel. This makes it very easy to enter new data, change existing ones or search for entries. And the whole thing even works without VBA!

An example:

Before you can use the data form, you must first activate it in Excel (no installation required).

How do I activate the integrated data form in Excel?

In a few minutes you will find out how helpful the integrated data form is. It's hard to believe that Microsoft doesn't display the data form on any ribbon. But where is the data form? Read on.

preparation

In order to be able to activate the form, a table is required for entering the data. The table can still be empty, in this step only column names are required.

activation

To activate the data form, click on any cell in the table or select one of the labels in the table header (if the table is still empty).

Now you can use one of the two options to activate the form:

  • Option 1
    Press + + to go to the Excel options. Then click on the following menu items: Quick access toolbar -> Select commands -> Commands not in the ribbon -> Mask… -> Add -> OK

The icon of the data form then appears in the toolbar for quick access

  • Option 2
    Press + to go to Visual Basic Editor to get. Paste and then run the following VBA macro: Sub DataForm () ActiveSheet.ShowDataForm End Sub

The two ways cause a data form to appear. As you can see, all table labels were automatically transferred to the form.

How does the data form work?

Creation of new data records

To create a new data record, click on New. Fill in all fields with data and click on Shut down.

You can also press the key after you have filled in the last field on the form with a value.

With the key you can jump to the next input field within the form, with the key combination + you get to the previous input field.

If you now look at the example below, you will notice that the field "amount”Cannot be entered. No, this is not an error in the data form. In the column "amount"Is a formula that contains the values ​​in the"quantity" and "price”Multiplied together.

This means that the data form is smart enough to recognize columns where no input is needed.

Navigate the data form

To navigate between the entered data sets, you can either use the Scroll bar or the buttons Find previous and Keep searching use.

Change the records

To edit a data record, you must first navigate to this data record. I described how to do this in the previous step.

Then you can simply adjust the values.

Then click either on Shut down or press the key. Excel saves the entry and automatically displays the next line.

If you are editing a data record (before clicking Close or pressing the key) want to restore the original value, click on Restore. All changes to the current data record are canceled and the original values ​​are displayed in all fields.

Delete records

In order to delete any data record in the table, you must first navigate to this data record and then the button Clear actuate.

A confirmation window will appear.

After your confirmation, the data record will be deleted immediately and irrevocably.

Find specific records

If you are working with a large table, you can use the button criteria find certain records.

For example: if you are looking for certain data records in the table shown, click on criteria, enter the required content in the fields and press. You will be shown data sets that match your search criteria.

Search criteria can also be entered in several input fields at the same time. If you want to return to the data form to add, change or delete rows, click on mask.

Close data form

To close the data form, click on Shut down and the form disappears.

Important information on handling the integrated data form

Yes there are a few restrictions when using the data form that you have to keep in mind.

  • The maximum number of input fields is limited to 32.
  • The Width of all input fields depends on the width of the linked columns in the table. The widest column determined the width of all fields.

If the columns are not wide enough to fully display the content, the data form will behave in the same way.

  • When entering the search criteria you can Wildcard characters use.
    Placeholder symboleffect
    ? (Question mark)Represents any single character

    For example, Schmi? T finds “Schmidt” and “Schmitt”.

    * (Asterisk)Represents any number of characters

    For example, * ost will find “northeast” and “southeast”.

    ~ (Tilde) followed by?, * Or ~Represents a question mark, asterisk, or tilde in the search term. Without a tilde, these three characters serve as wildcard characters.

    For example: gj91 ~? finds "gj91?"

  • When creating a new data record, the new data record is entered at the end of the table or area. For this purpose, the table or the area is extended by Excel. If the extension is not possible due to existing entries, the error message "The list or database cannot be expanded”.
    In order not to get into this situation, please make sure when arranging the data in the data sheet that the area or the table can be expanded downwards from the last line.
  • A data form cannot be printed. Also the Excel command To press or the button To press do not work until you have closed the data form. Alternatively, you can take a screenshot of the form, which you can then copy into an image viewer and print out.

Conclusion

In this article I introduced you to the integrated data form from Excel.

Even if this has few functions and some restrictions, it can still be very helpful if the requirements for a form are not too high.

I hope you can immediately use what you have learned for your best.

And now a quick question for you: Have you ever used the integrated data form?

Please answer in the comment field below.

Author AndreasPublished on