Simple Data Input Form for Tables in Excel - No VBA Required

.

How to use a pop-up input form to add and remove data from any Table in Excel – this requires only a single click on an Excel button and does not use any VBA (unless you want to automate the process with in-worksheet buttons).

This feature uses a hidden command called “Form” in Excel and it will work on any set of data that is kept as an actual Table (Insert Tab Table).

The Data Input Form that you will work with here allows you to do these things:
- Input new records very quickly.
- Delete records very quickly.
- Go through an entire Table of data inside of a pop-up form.
- Filter and search through the Table and display only the relevant records.
- Perform multi-field filters on the data in the Table.
- Revert accidental changes made to a record in the Table.

This is a great feature to use, especially when you have a Totals Row at the bottom of your Tables and you want to make it easier to input new data into them.

Resources:

#msexcel #tutorial #exceltips #freetutorial #exceltips #excel

[RonMarotte]

Wow! I didn’t even know I needed this, but I do! Love this shortcut.

[philipboakes]

You're such a great teacher, thanks.

[KeninRichfield]

This was very easy to follow and setup a form from an existing spreadsheet that I turned into a table. However, the spreadsheet has formulas that sum data across the columns. =sum(D2:F2 ).When entering a new record, the formula in the previous record is no longer correct. Is there a way to format the formula so it does not change when entering new records. Looking forward to your response Thank you.

[RandyMcNeil]

How do you get drop down validation into the data entry form? In your example, it could be the Employee name to insure they are always spelled correctly.

[cooljoker]

thank ytou very much easy mizi haha
great explain and thanks for the code

[ChaoticBean]

How do you program the form to number a table. For example: receipt number=RA0001, RA0002, RA0003, and so on. Then have the form prefill that receipt number

[cooljoker]

buut the quation if you have a table with more columns and to have some functions there so how you can add entry form with special columns because not everything is easy like u made
i have to entre 6 data to culomns and i have like 12 culomns to calculate others row

[bertvantol]

But what if I want to send my Excel application to somebody else who didn't put the button to the top bar ??
Can I even start the form then from a macro ??

[KevinLamb-dl]

I am using Excel Ver 16.74. I do not see the "Forms" option available for the ribbon... Is there another option, or, is "Forms" not available in Ver 16?

[dexe]

Nice, but VBA is needed for button, not working in O365 ;(

[jesusjim]

after searching around I find that the Mac version doesn't have the "Form" option. explains why I could not find it :/

[ftezzey]

didn't work for me! says that doesn't recognize the headers of the table

[Ready_Set_Boom]

Too bad it doesn’t work with data validation drop downs.

[tinplategeektoo]

A big downside is that the inbuilt form is only in American English so any data shown in the form is translated from its locale version into American values - currencies are shown in Dollars, dates are MM/DD/YY. Shame really as it works well otherwise.