In Excel: format the headings as a Table (Ctrl+T), then add the built-in Form command to the Quick Access Toolbar via File ▸ Options ▸ Quick Access Toolbar ▸ All Commands ▸ Form, and click it with a cell in the table selected.
Type the field names as a single header row and press Ctrl+T to make the range a Table.
File ▸ Options ▸ Quick Access Toolbar, set "Choose commands from" to All Commands, select Form, click Add, then OK.
Click any cell inside the table and press the Form button on the Quick Access Toolbar.
Use New to add a record, Find Prev/Find Next to browse, and Criteria to search.
Add Data ▸ Data Validation rules on the columns first so the form fields are constrained.
For checkboxes and option buttons, enable Developer (File ▸ Options ▸ Customize Ribbon) and use Insert ▸ Form Controls with a linked cell.
For responses from outside the team, build it in Microsoft Forms and let it write into the workbook.
What this does
Excel has a built-in record-at-a-time data entry form that is hidden from the ribbon entirely — you have to add it to the Quick Access Toolbar yourself. It gives one field per column, with New, Delete, Find Prev, Find Next and Criteria buttons, and it caps out at 32 columns. Beyond that there are three other routes. Data validation turns cells into controlled inputs with drop-downs and rules, which is the lowest-effort way to make a sheet form-like. Form controls from the Developer tab add checkboxes, option buttons and combo boxes that write to linked cells. And Microsoft Forms collects responses from people who never open Excel and drops them straight into a workbook — the right answer when the respondents are outside your team. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. Treat “create an order form in excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a layout choice that keeps the sheet readable and sortable into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
Put headings Date, Customer, Product, Qty, Price in A1:E1 and press Ctrl+T. Then File ▸ Options ▸ Quick Access Toolbar ▸ Choose commands from: All Commands ▸ Form ▸ Add ▸ OK. Click any cell in the table and press the new Form button: a dialog appears with five labelled boxes, and each New writes a row and clears the fields. Add Data ▸ Data Validation ▸ List on the Product column first, and the form field becomes a controlled entry rather than free text. Typing straight into a grid is where the bad rows come from — wrong column, skipped field, free-text spelling of a category — and a form plus validation removes most of that at the point of entry. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
In Google Sheets
Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. Treat “create an order form in excel” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
Common mistakes
- Hunting for Form on the ribbon; it exists but has to be added to the Quick Access Toolbar first.
- Running the form on a plain range rather than a Table, so new records do not extend the formatting or formulas.
- Building a form for more than 32 columns, which the built-in dialog cannot show.
- Using a form where data validation on the sheet would have been simpler and visible to everyone.
- Sending an .xlsm form to external respondents, when Microsoft Forms collects the same data without an Excel install.
Frequently asked questions
How do I create a data entry form in Excel?
Make the headings a Table with Ctrl+T, then add the hidden Form command via File ▸ Options ▸ Quick Access Toolbar ▸ All Commands ▸ Form. Click a cell in the table and press it.
Why can I not find the Form button?
It is not on any ribbon tab by default. Add it to the Quick Access Toolbar from File ▸ Options ▸ Quick Access Toolbar, choosing All Commands.
How do I make a fillable form with checkboxes?
Turn on the Developer tab in File ▸ Options ▸ Customize Ribbon, then use Developer ▸ Insert ▸ Form Controls and set a linked cell for each control.
Can people fill it in without Excel?
Use Microsoft Forms. It collects responses in a browser and writes them into an Excel workbook, which is the right choice for respondents outside your organisation.