The article shows some templates of how to make a fillable form in Excel. This is very useful to store data on official and business activities. Also, fillable forms can be useful for surveys, online orders, job application forms, etc. In this article, I’ll show you some effective templates which will be easy and simple for you to use in the practical field.
The following image shows you the 1st template that I created.
Download Free Templates
5 Examples to Make a Fillable Data Entry Form in Excel
1. Making an Excel Fillable Form for Employee Information
If you want basic information from an employee, it can be very easy for him/her to put them in the fillable form of this section. Suppose your name is Shawn and you are a Full Time employee. You have some other colleagues. We put some necessary information in Sheet2 of the workbook. We also stored some important named ranges in that sheet. Please download the file and observe Sheet2 before you go through the description below.
Steps:
- First, make a rough template like the following picture. This doesn’t contain any formula or code. You can insert rows or columns of your choice too.
- You can see that the image contains some Check Boxes. To insert them, go to Developer >> Insert >> Check Box icon from Form Control.
- Put them in the name column same as the above picture.
- Now we are going to create some Data Validation lists.
- First, we are making a list for the Employee Status. To create the list, go to Data >> Data Validation.
- Select List from the Allow: section and type the Statuses in the Source
- Click OK.
- After that, create another list for the Year of Birth. Keep in mind that we used a named range for the year from Sheet2.
- Similarly, we created a Data Validation list for the Service Duration of the employees.
- Now type the following formula in cell B7 and press ENTER. The formula has some named ranges Part_Timer, Full_Timer, and Contractual which you can find in Sheet2 of the workbook.
=IF(B4="Part-Time",Part_Timer,IF(B4="Full-Time",Full_Timer,IF(B4="Contractual",Contractual,"")))
This formula uses the IF Function and it will return the names of employees based on their status. If no status is selected, it will return nothing.
- After that, type the following formula in cell C7.
=IF(B4="Part-Time",Residence_2,IF(B4="Full-Time",Residence_1,IF(B4="Contractual",Residence_3,"")))
This formula will also return the residences of selected employees based on their status. This formula also has named ranges Residence_1, Residence_2 and Residence_3 from Sheet2.
- Now make a list for the supervisor’s name. The Source reference can be found in Sheet2.
- After that, type the following formula in cell C15.
=IF(C14="","",VLOOKUP(C14,Table1,2,FALSE))
The formula uses the IF and VLOOKUP Functions and it will return the Designation of the supervisor based on a name. You can find their name in a table of Sheet2.
- Again, write down this formula in cell C16 and press ENTER.
=IF(C14="","",VLOOKUP(C14,Table1,3,FALSE))
This will return the Department of your supervisor based on his name.
Now your fillable form is set. If you want to put more data, you can use a similar formula or named ranges or a Data Validation list. In case you are wondering what’s in Sheet2, here’s an image of it. You can see the named ranges in the top left of this image. Check the named ranges in the downloaded file if you want. The Year column has more data below, I could not take the full screenshot because of space.
- Now let’s see how this fillable form Insert your status from the Data Validation list.
- You will see the names of your fellow employees based on their statuses. Select your year of birth and put a tick in the Check Box beside your name. Your residence will be automatically updated beside your name.
- Fill the other fields on your own.
Finally, you are able to make a fillable form to store the employee information.
Read More: How to Create Data Entry Form with Drop Down List in Excel (2 Methods)
Similar Readings
- Create an Autofill Form in Excel (Step by Step Guide)
- How to Create an Excel Data Entry Form without a UserForm
- Automate Data Entry in Excel (2 Effective Ways)
- How to Restrict Data Entry in Excel Cell (2 Simple Methods)
2. Creating a Fillable Data Entry Form in Excel
Here, I’ll show you how to create a fillable form for data entry with the help of a built-in Excel command. Please go through the following description for a better understanding.
Steps:
- First, type some headings like the following picture.
- Select the Heading row and convert it to a table.
- After that, go to the File
- Later, go to Options.
- After that, select Customized Ribbons >> Insert (You can choose any other tab too) >> New Group >> Rename…
- Give your group a name, I gave it ‘Insert Form’.
- Later, click OK.
- After that, select Commands Not in the Ribbon from the ‘Choose Command from’ section.
- Select Form and Add it to the Insert Form group that you created.
- Click OK.
- Now, select the header row and go to Insert >> Form
- A dialog box will show up. Put the employee data in it and click on New.
- By doing this, you add this employee data in the table.
- Enter another data and click New.
Finally, you will see this data also appear in the table.
Thus you can make a fillable data entry file in Excel.
Read More: Types of Data Entry in Excel (A Quick Overview)
3. Making Fillable Form from Available Templates of Excel
The easiest way to use a fillable form in Excel is to use a built-in template. There are tons and tons of fillable form templates in the Microsoft Excel store. Please read the description below.
Steps:
- First, go to the File tab.
- After that, go to New and search form in the Search Bar.
- Press ENTER to search and you will find a lot of templates. Select any of them according to your convenience. I selected Small business profit and loss statement
After that, you will see your template being downloaded. You can use it after the download.
Thus you can use a fillable form from the Excel store.
4. Use of Microsoft OneDrive to Make Fillable Form
You can also use Microsoft Office to make fillable forms. Say you made a fillable form in OneDrive, but you can still use it in Excel as a fillable form. Let’s go through the procedure below.
Steps:
- First, go to your OneDrive account and select New >> Forms for Excel
- After that, give your form a name.
- Later, add a section by clicking Add new.
- You will see some form options after that. Suppose you want to insert names first. So you should select Text.
- After that, type Name as the number one option.
- Then you can put other options. I want a Gender section so I choose Choice where anyone can put their gender in the form. However, in Excel, we may not see form in the same way.
- After that, add the gender.
- Later, I added some sections of my wish.
- After that, go to Preview.
You will see how the Fillable Form will look like to the user.
- Click on Submit.
- Now go to your Excel file and select the File
- Later, select Open >> OneDrive >> Fillable Form
- After that, you will see the fillable options appear in the Excel workbook as a table. There were some unnecessary columns in the table. I hide and deleted them for convenience.
- I put some data in the table to show you how this table works.
Thus you can make a fillable form using Microsoft OneDrive.
5. Using Microsoft Office Account Apps to Make Fillable Form
You can also make a fillable form using Microsoft Office. The procedure is given below.
Steps:
- First, go to your Office account and search for forms in the Search Bar. Select Forms.
- After that, click on the Untitled form.
- Give your form a name.
The remaining process is the exact same as described in the Previous Section.
Thus you can also make a fillable form using Microsoft Office.
Practice Section
Here, I’m giving you one of the forms of this article so that you can make it on your own.
Conclusion
In the end, we can surmise that you have the basic idea of how to make a fillable form in Excel. This is very important in our daily official and business activities. If you have any other idea or feedback regarding this article. Please share them in the comment section. This will help me enrich my upcoming articles.
Related Articles
- Populate an Excel Spreadsheet from a Web Form
- How to Create a Data Log in Excel (2 Suitable Ways)
- Automatically Insert Timestamp Data Entries in Excel (5 Methods)
- How to Create Data Entry Form in Excel VBA (with Easy Steps)