Creating Fully Automated User Forms in Excel [No VBA] PART 1

Want to create an impressive user data entry form in Excel? You’re at the right place!

This article is a comprehensive and advanced step-by-step guide to create fully automated user forms in Microsoft Excel. You can collect, store, and process the data provided by your customers via the user form created by you.

We will also learn to integrate the form with a database in the backend in Excel. Read till the bottom and find out how!

Also read: Creating a Fully Automated Attendance Sheet in Excel [PART 1]

Steps to creating fully automated user forms in Excel

Let’s get started with this advanced Excel tutorial to create a data entry form!

Creating the form fields

The first step is to construct the form fields and be able to enter data into them.

List down the form field names in alternative cells, vertically.
Now, merge the cells where the data will be entered by users.
Press and hold CTRL and select the blank cells to merge them.
Click Merge & Center in the Home tab.
Set the text alignment to Left Align (recommended).

Displaying colors in blank and non-blank

Now, we will make the form display a specific color when the entry fields are blank and after data is entered in them.

This color formatting is suitable for fields where users have to type in data in the form. But it is your choice, the form is completely customizable to your will.

Select the data entry fields by pressing and holding CTRL.
Go to the Home tab.
Press Conditional Formatting.
Under the Highlight cell rules section, click on More rules.
Choose Blanks in the Format only cells with menu.
Click on Format below.
Go to the Fill tab and choose a color you want to display when the fields are blank.
Click OK.
Now choose No Blanks and click Format.
Choose a color you want to display when the fields are filled in the Fill tab.

Learn more: 11 Ways to Use Conditional Formatting in Excel – The Ultimate Guide

Displaying ticks for non-blank entries (Optional)

If you wish to display a tick or any other symbol you like, follow the steps below.

This is a completely optional section of this tutorial; you may skip it if you like.

Select the adjacent cell to an entry field where you want the symbol to display.
Type =IF(select the entry=””,””,”P”).
Press ENTER.
Copy and paste the formula beside the fields you want the symbol.

This formula signifies that if the field is blank, the adjacent cell will also be blank. But if this is not true, meaning, if the field has data entered in it, it will display “P”.

Select the cells where you have pasted the formula together.
Go to the Home tab.
Choose the Wingdings 2 font.

You can see that that there is a tick symbol beside the fields that non-blank. But there is no symbol beside blank cells.

Let us start customizing the tick symbol a little more for better aesthetics. But if you like it this way, you’re good to go!

Select the cells with the formula applied to them.
Go to the Home tab.
Click Conditional Formatting.
Under Highlight cell rules, click More rules.
Choose No Blanks in the Format only cells with menu.
Click on Format.
In the Fill tab, choose a cell color you want to display when the ticks appear.
In the Font tab, choose a color for the tick symbol.
Click OK and then again.

Try entering data in the fields and check if the tick symbol displays correctly.

Creating a field with a dropdown list

Drop-down lists are the most interesting and the most crucial part of any form. Let’s learn how to make a field that has dropdown lists in it.

The users can select an option from the dropdown list provided. Follow the steps to create one.

Select the field to which you want to add a dropdown list.
Go to the Data tab.
Click on Data Validation.
Choose List in Allow.
Type the options you wish to add separated with commas.
Click OK.

You will see that a dropdown list gets created in that field.

Adding an option form in a field

Let’s learn to add an option button in a field that will allow one of two or more options to be selected.

We have added a new field name named Newsletter Subscription to understand option buttons better. Follow the steps below to add an option button.

First, you need to add the Developer tab in the Excel ribbon.

Right-click on the Excel ribbon.
Click Customize the ribbon.
Check Developer and click OK.

To insert the options button, follow these steps.

Go to the Developer tab.
Pull down on Insert.
Click on the Option button (Form control) symbol.
Draw the button in the field.

Press and hold CTRL and click on the option name to edit or move it.
Double-click on the name and rename the button as you like.
Press and hold CTRL on the button and drag it to the side to make a copy.
Or, copy (CTRL+C) and paste (CTRL+V) the button.

Note that you can only select one option from the multiple options buttons added.

The further steps are all about integrating this user form with a database to record the entries, creating a Save or Export data button to store the data in the database, recording different types of entries, and more!

Creating Fully Automated User Forms in Excel [Without VBA] PART 2

Conclusion

This tutorial was a part 1 guide to creating a fully automated user form from scratch and adding useful features to it in Microsoft Excel. So far, we learned to make fields with options buttons, dropdown lists and inserting symbols in the form, and much more- all automatically.

Follow the advanced PART 2 guide to learn to integrate the form with a database, create a button to save the data after filling the form, recording results, and more prime automation topics!