How to Create a Fillable Form in Excel (Data Entry)

Thinking of fillable forms, the first thing that comes to mind is probably an editable PDF or a Google Form.

Gotcha! You can also create fillable forms in Microsoft Excel and believe me, it is a very effective way to collect and manage data. Whether you’re putting together a customer feedback form or a research survey form, this tutorial will help you get through.

We are going to touch user-friendly options like checkboxes and drop-downs and smart options like data validation to create forms that are not only visually appealing but effective.

Here’s your free practice workbook for this guide that you can download and tag with us along the way 🚀

Table of Contents

What are Excel forms?

An Excel form is a dialogue box that asks you to populate pre-defined fields up to a certain length.

It looks like below 👀

It is used to collect data. As you can see in the image above, it offers multiple fields that you can populate and create multiple rows in your data. Helps you to organize information easily and without errors.

More about how you create it in Excel and how it works comes in the sections below.

Create a Fillable Form in Excel

If you’re searching for the option to add a form to Excel somewhere on the ribbon, there’s a 90% chance you’ll not find it there.

That’s because the form is not available in Excel, by default. But the good news, it’s not even difficult to add 🤯

Follow these simple steps to add the option to create a form in Excel.

Step 1) Go to the File tab > Excel Options.

Step 2) From the pane on the left, click on Customize Ribbon.

Step 3) Choose All Commands.

Step 4) Scroll down the list of commands to find the command Form.

Step 5) From the list of tabs on the right, choose the tab where you want this command added (for example I am choosing the Home tab).

Step 6) Click on New Group to create a custom group under the Home tab.

Step 7) Click on Add to add the command to create a Form in the Home tab like here.

Now that we have the Form button added to the Home tab, it’s time we go on, and create a form in Excel 💪

I am going to take the example of a customer feedback form (let’s assume for a restaurant). Let’s say each customer has to fill this out before they leave after they’ve had their meal.

And here’s how we do it.

Step 1) Launch your Excel sheet.

Step 2) Make columns for the fields you want to include in the feedback form.

I need to populate the survey form for the customer’s views on their visit to the restaurant, so my relevant fields include the customer’s name, contact number, frequency of visits, and feedback on different aspects of the service 📝

I am going to populate all these fields as columns with column headers in my Excel sheet as follows.

Step 3) Select the cell range for the form (including the column headers).

Step 4) Go to the Insert tab > Tables > Table to convert it into a table.

Step 5) Press okay to convert the specified range into a table.

Step 6) Now click anywhere within the table.

Step 7) Go to the Home tab > your custom group > Form button.

You will see the customized data entry form dialog box pop up for our relevant fields 😎

Step 8) Type whatever you want to within each of the fields like here.

Step 9) Click on close to close this data entry form and see the data populated in your Excel table.

Step 10) Or, if you want to add further data rows, click on New and add another row of information.

Doing so you can add as many rows of information as you want.

This is simple. You can navigate your customers to this feedback form to capture their feedback, seamlessly and without any errors 🛡

Here is more about the buttons you see on the form.

• New: By clicking on the new button, you can start populating a new entry in the form by automatically saving the previous one.
• Delete: The delete button deletes the current field entries from the form.
• Restore: The Restore button restores the immediately previous position of the field entries.
• Find Previous / Find Next: Use both these buttons to toggle to the previous and the next information entry.
• Criteria: You can define criteria for the people filling out the form to follow/take a cue from before they begin filling it.

By clicking on the Criteria button, you can populate the field entries as you do normally. However, this will not be stored as an entry in the Excel table. You can go back to filling in the form entries by clicking on the Form button and continue recording entries once you’ve set up the criteria.

And this is how you use the Form tool of Excel to create a fillable form.

But what if you want to restrict some fields? For example, we only want the customers to enter their contact number in the field of contact number and not any text (like not available or private number, etc.) 🤔

You can apply data validation rules to the table below.

Step 1) Select the column where the contact numbers are to be populated.

Step 2) Go to the Data tab > Data tools group > Data Validation.

Step 3) Set the restriction to the whole number only.

Step 4) Set the rule to greater than 0 (every contact number will be greater than 0, hopefully).

Step 5) Click ok.

Now, if you try entering anything other than whole numbers in the field for contact numbers within the Form dialog box, you get the following error ⚠

This way you can restrict the data entered by the users in the form to a desired input type.

Similarly, you can create a drop-down list by again going to Data validation tools and setting up the rule for a list of values that you can then stipulate yourself.

Say I want the users to give feedback on the Food, Ambience, and everything else as Excellent, Good, Satisfactory, or Poor only 🥇

No other subjective comments.

Step 6) Select the columns where this feedback is to be populated.

Step 7) Go to the Data tab > Data tools group > Data Validation.

Step 8) In the Validation criteria, select List.

Step 9) Write the desired grades in the Source field separated by a comma.

Step 10) Click on Okay.

Now launch the Form dialog box and try entering any comment other than these pre-defined grades in the relevant fields.

Excel will prevent you from doing so by giving the following error 👉

Pro Tip!

Note that making a drop-down list of feedback options to choose from does help prevent undesired input. However, unlike within the worksheet, where when you click on a data-validated cell, Excel launches the drop-down list of options to choose an input from, the Form dialog box won’t show the drop-down list of options.

This might confuse the users about populating the field entries while they try to fill out the form. To avoid that, you must guide the users (through some other way) that certain field entries have restricted input criteria that must be followed.

Potential problems with Excel forms

The Form tool of Excel helps data entry in big time but, has its fair share of problems. Here’s what you should bear in mind 🧠

• You cannot use any formulas in it.
• Doesn’t allow any formatting options (like what if a customer wants to give the feedback that the food was “JUST OUT OF THIS WORLD!!”. Like that, boldened and italicized). They can’t do that with the Form dialog box.
• It doesn’t allow advanced field types like drop-down lists, radio buttons, checkboxes, etc.
• No in-built data validation rules. You have to set them up separately.
• The Form tool is only limited to the active worksheet of an Excel file. Using a single form, you cannot input data across multiple worksheets or workbooks
• You have little control over how the form looks or behaves beyond the default settings provided by Excel.

With these limitations, the Form tool of Excel is best for simpler data entry tasks and may not be the best option for complex data entries.

Conclusion

This step-by-step guide comprehensively covers the end-to-end steps to create a form in MS Excel that’s functional, user-friendly, and dynamic 💡

An Excel form can be specifically designed to meet your specific data entry needs. By using the data validation tool, you can streamline the input process to make sure there are no data entry errors and there is consistency in data collection.

Creating fillable forms in Excel is a skill that can take your data game to the next level. If you enjoyed reading this tutorial, make sure you check out the following Excel blogs by Spreadsheeto too.

• How to Lock Formulas in Excel (But Allow Data Entry)
• How to Use Data Validation in Excel: Full Tutorial (2024)
• How to Add Yes or No Options in Excel (Drop-down & Box)