How to make a data entry form in Excel (and drop-down lists)

Spreadsheet tools · Published September 27, 2026

Use Excel's built-in data form, drop-down lists and input rules to enter rows quickly and consistently, or collect answers with a shared online form.

The short answer

For most logs, you don't need a separate form. Turn your list into an Excel Table, add drop-down lists and input rules with Data › Data Validation, and type straight into the next empty row. The rules stop typos, and the Table carries formulas and formatting down for you.

If you prefer a one-record-at-a-time dialog, Excel for Windows has a built-in data form you can add to the Quick Access Toolbar. To collect entries from other people, use Microsoft Forms (answers land in a workbook) or Google Forms.

Start with a Table

The example in this guide is a simple expense log. Each row is one receipt, and each column has one kind of value.

1. Type the headers in row 1. Leave no blank columns or rows.
2. Click any cell in the list and press Ctrl+T (or use Insert › Table). Check My table has headers and select OK.
3. Type a new entry in the first empty row under the Table. The Table grows to include it.

If you add a formula to a Table column, such as a tax amount, Excel fills it down the whole column and into each new row, so nobody has to copy it by hand.

Add a drop-down list

Put the allowed choices on a separate sheet, for example a sheet named Lists with the heading Category in A1 and Travel, Meals, Supplies and Software below it. Make that list a Table too. Microsoft notes that drop-downs based on a Table update when you add or remove items.

1. Select the Category cells in your log (C2 downward).
2. Go to Data › Data Validation.
3. On the Settings tab, set Allow to List.
4. In Source, select the items on the Lists sheet without the header. For a short fixed list you can type the items instead, separated by commas: Travel, Meals, Supplies.
5. Keep In-cell dropdown checked and select OK.

Input messages and error alerts

On the Input Message tab, type a short hint that appears when someone selects the cell, such as "Pick a category." On the Error Alert tab, choose a style:

• Stop refuses anything that isn't allowed.
• Warning and Information show your message but let the person keep the entry.

Set rules for numbers, dates and text

The same dialog handles other checks. Select the cells, open Data › Data Validation and pick an option under Allow:

• Amount: choose Decimal, set Data to greater than and Minimum to 0. Text and negative numbers are refused.
• Date: choose Date and between, then enter the first and last dates you accept, such as the start and end of the year.
• Note: choose Text Length and less than or equal to 100 to keep notes short.

Prevent duplicate entries

Receipt numbers should appear only once. Select A2 down to the end of the column, open Data › Data Validation, set Allow to Custom and enter this formula:

=COUNTIF($A:$A,A2)=1

When someone types a receipt number, Excel counts how many times it appears in column A, including the new entry. A count of 1 means the number is new, so it's accepted. If someone types R-1002 again, the count is 2 and the Stop alert blocks it. Because A2 has no dollar signs, Excel adjusts it for each row you selected.

The built-in data form (Excel for Windows)

The data form shows one row at a time as a dialog, with a box for each column. It's handy for wide tables where the columns run off the screen. The Form command isn't on the ribbon, so add it first:

1. Go to File › Options › Quick Access Toolbar, or click the arrow next to the Quick Access Toolbar and choose More Commands.
2. Under Choose commands from, pick All Commands.
3. Find Form… in the list, select Add, then OK.
4. Click any cell in your Table and select the new Form button.

Use New to add a row, Find Prev and Find Next to move through records, and Criteria to search. Some limits from Microsoft's help page:

• The form handles up to 32 columns. With more, Excel says there are too many fields.
• It needs a header row and no blank lines in the data.
• Formula results are shown but can't be edited in the form.
• You can't print the form.

Microsoft documents the data form for Excel for Windows only. Excel for Mac doesn't include it, and Microsoft's help page doesn't list Excel for the web. On those, use a Table with drop-downs, or Microsoft Forms.

Collect entries from other people

Microsoft Forms linked to Excel

1. Open the workbook in Excel for the web (or a recent Microsoft 365 version on Windows) and go to Insert › Forms › New Form.
2. Add your questions. Use choice questions for categories, like a drop-down.
3. Share the form link. Responses sync into a linked table in the workbook.

Availability depends on your Microsoft 365 account and Excel version. Microsoft warns that adding your own tables or formulas to the response sheet can stop the sync, so do your calculations on another sheet.

Google Forms

In Google Forms, open the form, select Responses, then Link to Sheets. Responses go into a Google Sheets spreadsheet. To work on them in Excel, use More › Download responses (.csv) and convert the file.

Troubleshooting

• Data Validation is grayed out. The sheet may be protected or shared. Unprotect it first.
• No drop-down arrow appears. Check that In-cell dropdown is on and that you selected the right cells.
• New categories don't appear in the list. The source isn't a Table, or the new item was typed below a blank row. Add it directly under the last item.
• Long numbers are flagged as duplicates when they aren't. Users have reported the COUNTIF rule misfiring on values longer than 15 digits, such as tracking numbers. This rule compares the values exactly instead:

=SUMPRODUCT(--($A$2:$A$1000=A2))=1

Once entries are clean, totals are easy. The guide to SUMIFS and COUNTIFS shows how to total the log by category or month.

In short

Make your list a Table, add drop-downs and rules with Data Validation, and block duplicate IDs with a COUNTIF rule. Use the Windows data form if you like a dialog, and Microsoft Forms or Google Forms when other people need to add rows.