Excel Database with Input Form and Search Function
Microsoft Access is actually predestined for databases with the Office software and was also developed for it. But a database can also be implemented in Excel. Because what is a database? Right, nothing more than a list of entries containing different parameters and variables. With a dynamic table (which is the first step) we already have a good start. Because you can filter this list and also search for entries per column.
But that’s not always enough. What we want is an input form that we can activate via a button and add new entries to our Excel database. And ideally also a function for searching within this database. That’s what this tutorial is supposed to be about.

Excel Database with Input Form and Search Function
Microsoft Access is actually predestined for databases with the Office software and was also developed for it. But a database can also be implemented in Excel. Because what is a database? Right, nothing more than a list of entries containing different parameters and variables. With a dynamic table (which is the first step) we already have a good start. Because you can filter this list and also search for entries per column.
But that’s not always enough. What we want is an input form that we can activate via a button and add new entries to our Excel database. And ideally also a function for searching within this database. That’s what this tutorial is supposed to be about.

Dynamic spreadsheet in Excel
Dynamic spreadsheet in Excel
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)
In the following sections we will go through the whole thing step by step so that you can implement it yourself.
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)
In the following sections we will go through the whole thing step by step so that you can implement it yourself.
Add more commands in Excel
Add more commands in Excel
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
If you have found the point and clicked on it, you will see that the “Add” button is activated between the two windows “Choose commands” and “Customize the ribbon“. However, this will not yet work or will issue an error message. Because if you want to add new commands you have to create a new “Custom Group“. To do this, proceed as follows:
- Click on one of the “+ symbols” behind any main tab to which you want to add the new group.
- It is recommended to choose as close as possible. That’s why we chose the main “Data” tab.
- Then click on “New Group” and give it any name.
- We simply chose “input mask” or “input form” here.
- Now you can add the Mask command to your custom group and the Mask menu item will appear under the main Data tab as well.
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
If you have found the point and clicked on it, you will see that the “Add” button is activated between the two windows “Choose commands” and “Customize the ribbon“. However, this will not yet work or will issue an error message. Because if you want to add new commands you have to create a new “Custom Group“. To do this, proceed as follows:
- Click on one of the “+ symbols” behind any main tab to which you want to add the new group.
- It is recommended to choose as close as possible. That’s why we chose the main “Data” tab.
- Then click on “New Group” and give it any name.
- We simply chose “input mask” or “input form” here.
- Now you can add the Mask command to your custom group and the Mask menu item will appear under the main Data tab as well.
see fig. (click to enlarge)
Input form with button in Excel
Input form with button in Excel
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 >>>
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 >>>
Search function in Excel
Search function in Excel
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“.
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“.
Popular Posts:
The best backup solutions for your data
Keep your data safe and secure! Discover our best backup solutions for your valuable information now. Because safety is the be-all and end-all - and we have the perfect tips.
Internet Addiction – A serious look at a growing problem
Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.
Integrate and use ChatGPT in Excel – is that possible?
ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.
Main keyboard shortcuts in Windows 10/11
Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.
Encrypt USB stick – These options are available
Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.
With the webcam to your dream job: Preparation for the video job interview!
Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.
Popular Posts:
The best backup solutions for your data
Keep your data safe and secure! Discover our best backup solutions for your valuable information now. Because safety is the be-all and end-all - and we have the perfect tips.
Internet Addiction – A serious look at a growing problem
Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.
Integrate and use ChatGPT in Excel – is that possible?
ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.
Main keyboard shortcuts in Windows 10/11
Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.
Encrypt USB stick – These options are available
Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.
With the webcam to your dream job: Preparation for the video job interview!
Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.