Microsoft Office

How To Create Data Entry Form In Microsoft Excel

To create a data entry form in Microsoft Excel, organise your records under clear column headings, then add the Form command to the Quick Access Toolbar. Select a cell in your data and open Form to enter records. For a custom layout with drop-down lists.

Which licences fit your PC setup?

Prices are an AUD snapshot, not a live quote. Confirm variant and total on the listing.

Prepare your worksheet before creating the form

The built-in Form method below uses desktop Excel for Windows. You do not need the Developer tab or VBA for this method.

Decide what one record represents before choosing fields. For an expense log, a record might contain Date, Reference, Category, Description and Amount. Keep each expense on its own row rather than combining several expenses in one cell.

  1. Enter a descriptive heading for each field in the first row of your data area.
  2. Add a sample record beneath the headings so you can check the structure.
  3. Keep the data in a continuous block, with no completely blank rows or columns separating records.
  4. Use consistent formats for dates and amounts. Format reference codes as Text before entering them if leading zeroes matter.
  5. Select the headings and sample record, choose Insert > Table, confirm that the table has headers, and select OK.

A table gives your records a defined area. Avoid putting unrelated notes immediately below it, where new records need room to grow.

Add Excel's built-in Form command

The command is available through toolbar customisation, as described in Microsoft's guide to finding the Form button.

  1. Open File > Options > Quick Access Toolbar.
  2. Under Choose commands from, select All Commands.
  3. Select Form from the command list, then choose Add.
  4. Select OK, click inside your table, and open Form from the Quick Access Toolbar.

Enter, find and edit records

The form shows your column headings as field labels. It supports up to 32 columns; formula results appear without allowing you to edit the formulas. These behaviours are covered in Microsoft's data form instructions.

  1. Select New to start a record.
  2. Enter each value, using Tab to move forwards or Shift+Tab to move backwards.
  3. Press Enter to add the record to the bottom of the table.
  4. Use Find Prev or Find Next to locate an existing record, change its fields, then press Enter to save the changes.
  5. Select Close to return to the worksheet, then save the workbook.

Use Criteria to search by field values. Restore discards unsaved changes to the current record. Take care with Delete: after you confirm a row deletion in the form, you cannot undo it.

Use a worksheet layout when you need drop-down lists

The built-in dialog suits straightforward records. If users need visible instructions, drop-down choices or a printable layout, build an input area directly on a worksheet. Ordinary cells can act as entry fields; a floating text box is unnecessary for typing values.

For repeated records, keeping the entry fields in table columns avoids an extra transfer step. A separate input sheet needs a deliberate way to copy or append its values to your records. Linking cells or adding a button alone does not create that process.

Add rules for entries made in cells

Use data validation to guide direct worksheet entry. For example, an expense category can use a list, while an amount can require a positive decimal. Follow Microsoft's data validation guidance to choose the appropriate rule.

  1. Select the input cells, excluding the column heading.
  2. Choose Data > Data Validation.
  3. Select List for predefined choices, or another suitable type such as Date or Decimal.
  4. Enter the list source or permitted limits.
  5. Add an input message and a clear error alert, then select OK.

Test both acceptable and unacceptable entries through the actual entry method people will use. A worksheet drop-down does not become a drop-down inside the built-in Form dialog.

Add controls only when they serve a purpose

For a worksheet that needs a list box or other control, enable Developer through File > Options > Customize Ribbon. Choose Developer > Insert, select a suitable Form control and draw it on the sheet.

Use Format Control to configure the relevant input range and linked cell. Controls have different outputs: a list box can return the selected item's position rather than its text. Check that output before using it in calculations. Microsoft's overview of Excel forms and controls explains the distinction between worksheet controls and VBA UserForms.

Protect and test a worksheet input area

For a worksheet layout, unlock the cells people should edit before protecting the sheet. Select those cells, open Format Cells > Protection and clear Locked. Then choose Review > Protect Sheet and set the permitted actions. Microsoft's worksheet protection guide explains these settings.

Test entry again after protection, including any linked cells and controls. Check that users can complete a record while headings and formulas remain protected.

Resolve setup problems before sharing the workbook

If Office activation prevents editing, resolve that before testing the form. The guides to activating Microsoft Office using Command Prompt and finding an Office product key using Command Prompt cover related licensing tasks; their applicability depends on your Office installation and licence.

For other workbook tasks, browse the Microsoft Office guides.

Next, enter a sample record, edit it through your chosen form and reopen the saved workbook to check that the values are stored in the correct columns.

Other editions for separate workloads

Other editions for separate workloads; prices in AUD, confirm the total on the listing.

How to check this worked

  1. Open Settings, System, Activation and confirm the edition shown.
  2. Enter the key and run activation; expect a green tick within a minute.
  3. Reboot and re-check activation survives the restart.

FAQ

What else should you know before buying?

What do I need before starting?
Read the requirements and confirm the edition first; the steps assume the machine is compatible.
How do I know the licence is genuine?
Activate on Microsoft's servers and check Settings, System, Activation. A working activation is the test.
Is this a subscription?
Keys sold here are one-time lifetime licences. Microsoft 365 plans are billed by Microsoft instead.
What if the key fails?
Email hi@ms.codes with the order number and a screenshot. Replacement or refund applies within 30 days.

Check who prepared and reviewed this guide

Written by
MS.Codes editorial
Reviewed by
Support team

Last verified: (source review; see test status above).

Recent Post