How to Create an Automated Data Entry Form in Excel

by

This Excel form collects new employee details on one sheet and, when you click Submit, saves them as a new row on a database sheet and clears the form. The Submit button runs a macro you record, so there’s no code to write.

Built withData Validation, option buttons, IF and a recorded macro
Saved asExcel Macro-Enabled Workbook (.xlsm)
Works inExcel 2010 and later for Windows

Video

Step 1: Design the form

  1. Type Employee Data Form in B2, merge B2:F2, and give it a dark green fill with white, bold, 20 pt text.
  2. In column B, type the labels: Employee Name, Employee Code, Email ID, Date of Birth, Department, Location.
  3. In column E, type: Basic Salary, HRA, Medical, Conveyance, Deductions, Net Pay.
  4. Give the input cells a light grey fill and an outside border. Use Format Painter to copy the look.
  5. Format the salary cells with a thousands separator, and Date of Birth as a short date.

Step 2: Drop-down, option buttons and formulas

Department drop-down: select the Department cell → Data → Data Validation → List, and type the source: Technical,Admin,Accounts,HR.

Location buttons: on the Developer tab, choose Insert → Option Button and draw two buttons labelled Site and Office. Align them, then right-click one → Format Control and link it to a spare cell. That cell shows 1 when Site is chosen and 2 for Office. In the Location cell, turn the number into text:

=IF(H7=1,"Site","Office")

(Use your linked cell in place of H7.)

Net pay:

=F3+F4+F5+F6-F7

That’s Basic + HRA + Medical + Conveyance − Deductions; use your own cell addresses.

Step 3: The database sheet

  1. Rename the form sheet Form, and add a new sheet called Database.
  2. Copy the form labels, click B2 on Database and use Paste Special → Transpose so they become column headings.
  3. Give the headings a blue fill and white text, and set the date and number formats for each column.

Step 4: Record the Submit macro

Turn on Developer → Record Macro, name it CollectData, and do these steps exactly once:

  1. Go to the Database sheet and select B3. Choose Home → Insert → Insert Sheet Rows, and pick Format Same As Below from the options button.
  2. Go to the Form sheet and copy the input cells.
  3. Back on Database, click B3 and choose Paste Special → Values with Transpose ticked.
  4. Go to the Form sheet and clear the input cells. Leave the formula cells alone.
  5. Click Stop Recording.

Insert a button (Developer → Insert → Button), assign CollectData to it and label it Submit. Every entry goes into row 3, and earlier entries move down.

Step 5: Protect and save

  1. Select the input cells → Format Cells → Protection → untick Locked.
  2. Review → Protect Sheet and set a password. Now people can type only in the input cells.
  3. Save as Excel Macro-Enabled Workbook (.xlsm), or the macro is lost.

Things to know

  • If the sheet is protected, the macro can’t clear the cells. Add Unprotect and Protect lines at the start and end of the macro, or record it before protecting.
  • People opening the file need to click Enable Content for the button to work.
  • For bigger databases with search and photos, see the Access data entry form.

Just need the number? Use the salary calculator or the HRA calculator on Quick Easy Calculators, our sister site.

Related