For our Excel database with an input mask, we have created a dynamic table with a fictitious stock list containing tea varieties as an example. In addition to the article number, batch number, article description, this also includes the storage location, the quantity and the purchase price. In the end, the goal should be that we have our database in our worksheet, as well as a button to open an input mask that we can use to add new items, and a button that we can use to search the database. You can read about how to create a dynamic table in Excel here >>>

It shouldn’t all look fancy at first, it’s basically just about the pure functionality of the whole thing.

see fig. (click to enlarge)

As a rule, the function that we need next is not activated by default in Excel, which is why many find it difficult to create such an input mask at all. The reason why many actually useful commands and functions in Excel are not activated by default is that otherwise the user interface of Excel will become even more confusing for most people than it already is.

To activate the function we need, proceed as follows:

• On the right side of the title bar, click the small down arrow
• From there, select “More Commands“.
• You are now in the Excel options that you would otherwise reach via “File” – “Options“.
• Select the item “Customize the ribbon” and on the right next to it in the drop-down list under “Choose commands” then “All commands“
• This list is sorted alphabetically. Look for the “Mask” or “Form” command

Now we’ve come a long way. Because we created our dynamic table in Excel, and we implemented the required “Mask” or “Form” command in our desktop. Next, click in the cell with the first entry under the first column heading, and then select your newly created item “Mask” or “Form”. The mask then automatically adopts the column headings as input fields and opens immediately.

We could actually leave it that way and simply select the “Mask” item on the tab, and the input mask would open in Excel. But we want to assume that such a list is not only used by the creator, but that it is perhaps on a shared drive, so that the list can also be edited by several users who may not be very familiar with Excel.

So we need a solution to organize the whole thing via a button that is permanently visible and functional in the spreadsheet. To do this, we proceed as follows:

• Click on the “Developer Tools” main tab and select “Button” under “Insert“.
• If the developer tools are not yet activated for you, you will find the solution here with us >>>
• Now your mouse pointer will turn into a kind of drawing tool, and you can simply hold down the left mouse button and draw a button of any size and anywhere (regardless of cell boundaries).
• As soon as you have created the button, a window will open asking for a macro assignment.

If you have already created a macro, you can assign it here. But I assume that this is not the case, which is why you first have to create a macro in Excel. In order to find the macros function, you also need to activate the developer tools again! If you have never done this before, you will also find the solution here with us >>>

In the end it should look like the image below. By the way, the input mask automatically gets the name of the spreadsheet in your workbook. If you want to have a different name there, you can simply give the table a different name. You can find out how this works and how to work with the name manager in Excel here >>>

After we have successfully assigned the macro to our button, we can now click on the button and the desired input mask opens with exactly the fields that also have the column headings of our dynamic table. Using this mask, entries can now be easily added to the table, but also removed. And we could also search for entries using the “Criteria” item in the search mask.

There is also the possibility to create a search form in Excel with help tables, but they all suffer from the fact that they have disadvantages when outputting the data record. Then either nothing is output if not all parameters have been entered in the search, or too much data is displayed because some parameters, such as the order date, can also be the same. And then so many data records are displayed that you could have searched for them manually.

The easiest way is to use it via the “Home” tab and then “Find and Select“, or to use the keyboard shortcut “CTRL+F“.