How to Automate Data Entry in Excel: A Quick Tutorial

If your work involves entering a lot of data manually in Microsoft Excel,  you probably already know that it feels like a never-ending task. Fortunately, there are some techniques to help make Excel data entry a more streamlined and automated process. In this article, we will go through how to automate tedious data entry in Excel.

How to enable the Developer Tab in Excel

Before automating data entry, you need to enable the “Developer” option to code in Visual Basic Applications (VBA). The Developer option is usually unchecked for most users. Let’s learn how to get the Developer Tab to appear first.

STEP 1: On a new Sheet, right-click on Insert.

STEP 2: Right-click on the Ribbon on the top of your Excel window.

STEP 3: Consequently, click on “Customize the Ribbon”.

STEP 4: Check the “Developer” Box from it, and click “Ok”.

You should now see the Developer Tab on the Excel ribbon.

How to make a fillable form in Excel

Creating a fillable form instead of directly entering data into Excel is the first step in automating most data entry work. The process of creating a fillable form can be divided into Creating a Layout, Making the form interactive, and Finishing up.

1. Creating form Layout

Your form’s user interface will generally include the data you want to collect, and maybe even your company title and logo.

STEP 1: Find and click the “Page Layout” tab.

STEP 2: Under the “Page Layout” tab, you should find “Size”. From the drop-down that appears, click any size you want for your layout.

Note: Dotted grids should appear on your Excel Sheet that indicates STEP 2 was configured correctly.

STEP 3: Type your heading. We will be changing the font size, alignment, and text format.

• STEP 3.1 Skew Row 1 and Column A to your liking.

  

• STEP 3.2 Make the text bolder and adjust its size.
• STEP 3.3 Add your questions, dropdowns, and checkboxes. You should add these first because merging and centering first will cause problems.

  

STEP 4: Highlight your heading cells and click on the “Merge & Center” option under the “Home” tab. This allows you to align your heading anywhere you want.

STEP 5 (Optional): Go to the “Insert” tab and click on “Pictures” to add a logo if you might need to brand your form.

2. Making the form interactive

Forms generally need dropdown lists and checkboxes. In this part, we will be making the layout interactive by adding these elements.

ADDING A DROPDOWN

STEP 1: Create a “New Sheet” from the bottom of the page with the little plus sign.

STEP 2: Type in your dropdown options on any column you want in the new sheet. Select any one of the options, and press “Ctrl + T” on your keyboard. Finally, click on “Ok”.

STEP 3: After Step 2, your list will turn into a table with a heading. Customize your heading to be the display of your dropdown list on the layout page.

STEP 4: Go back to the layout sheet from the bottom of your Excel window, highlight the cell you want to put the dropdown menu inside, and find “Data Validation” under the “Data” tab.

STEP 5: Under “Settings”, select “List” from the “Allow:” dropdown.

STEP 7: Put your cursor on the “Source” box and navigate back to the table we had created earlier.

STEP 8: Select the whole table as the source and click “Ok”.

ADDING CHECKBOXES

Checkboxes can be found under the “Developer” tab and can be inserted anywhere. Here’s how to add checkboxes to your form.

STEP 1: Find “Insert” and click on the little checkbox sign.

STEP 2: Drag anywhere on the screen to add checkboxes.

STEP 3: Create Labels for the checkboxes.

Note:  To interact with a checkbox, you will have to highlight the cell they are placed on and right-click on the checkbox you want to modify.

3. Finishing touches

In order to make the form look cleaner, we may start by removing the gridlines, removing extra cells, and adding some lines around the titles.

REMOVING GRIDLINES

To remove the gridlines, go to the “Page Layout” tab and uncheck the “View” option under “Gridlines”

REMOVING EXTRA CELLS

Optionally, you might want to remove any cells beyond the dotted lines to make the form neater.

STEP 1: Select the column right beside the dotted line and press “Ctrl+Shift+→” on your keyboard. This should select all the cells to the end of your Excel Sheet.

STEP 2: Right-click anywhere on the highlighted bars and select “Hide”.

STEP 3: Scroll down to the bottom dotted line, highlight the row below it, and press “Ctrl+Shift+ ↓”.

STEP 4: Right-click anywhere on the highlighted bars and select “Hide” again.

HIDING EXTRA SHEETS

Ideally, the person filling out your form will only see the layout page. For this, we will be hiding the other sheets you may have attached to your layout page for dropdown options.

• STEP 1: Right-click on the sheet you want to hide and select ‘Hide’.

  

Now that your fillable form is ready, you can distribute it to the people you want to get the data from.

How to create a data entry form in Excel

STEP 1: We start with a small data list. Convert it into a table by selecting any space inside the data list and press “Ctrl+T”.

STEP 2:  If you already have a heading for your data, check the option “My table has headers” and click on “Ok”.

STEP 3: Now, right-click the ribbon on the top of your Excel window and select “Customize Quick Access Toolbar”.

STEP 4: Click the “Choose commands from” dropdown menu and select “Commands Not in the Ribbon”.

STEP 5: Find and “Add>>” the ‘Form’ option from the dropdown.

STEP 6: Find the “Form” option at the top of your screen and click it.

STEP 7: Your data entry form is now created!

Here are a few things you need to know about this form:

1. “New” lets you add new entries to your table.
2. “Clear” clears the data you just typed but haven’t entered into the Excel table.
3. “Criteria” is the search enabler, once you click on it, you can find things according to their categories.
4. “Find Next” finds the next data related to your search and “Find Prev” finds the previous one.
5. “Delete” lets you delete existing entries and “Restore” lets you restore data you have typed but haven’t entered into the table yet.
6. You can press the “Enter” key on your keyboard to add data to your table after you are done typing.
7. You have to toggle the “Form” button after clicking “Criteria” to start entering data again.

How to automate data entry using VBA

To take your form experience to the next level, you can also create “native” window forms using the help of Microsoft VBA. With VBA’s native forms, the end-user won’t have to see the open Microsoft Excel and fill in the sheet themselves. However, it’s an advanced process and requires some basic coding in VBA. We have included the procedure in the following steps. For complete tutorials on VBA, be sure to check out Microsoft’s complete documentation and guides for VBA.

1. To use VBA, you’ll have to save a new sheet in the *.xlsm format.
2. The second part of the process is creating a “database” sheet.
3. Now by opening the VBA window, the layout of the form fields is defined.
4. We can then customize the data entry logic, validations, and command buttons with the help of VBA code.

And that’s a wrap! Microsoft Excel comes with a  ton of functionalities that make day-to-day operations easier. We hope this article was useful for helping with automating some tedious aspects of your workflow. For more on business automation, please visit our blog for more in-depth guides and tutorials!