Excel Tutorials · Lesson 74
Excel has had a built-in data entry form for decades, hidden because Microsoft dropped it from the ribbon without removing the feature. Once it's added back to the Quick Access Toolbar, it turns any table into a form you can add to, search, and edit from, with no VBA involved. This lesson covers the setup and where it can and can't help.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
The Form command is a built-in pop-up data entry screen: point it at a table, and Excel generates one labeled field per column automatically. You can add a new record, search for existing ones, edit a field, or delete a row, all from one small window instead of scrolling and typing directly into cells.
It has existed in Excel since well before the ribbon interface, and it still works exactly the same way. What changed is visibility: Microsoft stopped putting a button for it on the ribbon years ago, though the feature itself was never removed. It has to be added to the Quick Access Toolbar manually, which is why most people who learned Excel recently have never seen it.
It works against a Table or a clean range with a single header row, and it caps out at 32 fields. Formula columns show up read-only, since editing a calculated value through the form would break the formula behind it.
The honest framing: it is not a form builder in the way a modern app would mean it, there is no dropdown control, no validation rule, and no custom layout. What it offers instead is a genuinely fast way to add and search rows in a plain table with zero setup and zero code, which is exactly why it has survived, quietly, for this long.
Step-by-step, matching the video above.
Click any cell in your data and press Ctrl+T. The Form command needs a proper header row and a defined range to build its fields from.
Go to File > Options > Quick Access Toolbar, set "Choose commands from" to All Commands, find Form, click Add, then OK.
Click any cell inside your table, then click the Form icon now sitting in the Quick Access Toolbar. Excel builds one labeled field per column automatically.
Click New, fill in each field, and press Enter or click New again to commit the record as a new row in the table.
Click Criteria, type a value into any field, then use Find Next and Find Prev to step through matching rows.
With a record showing, change any field to update that row directly, or click Delete to remove the row entirely from the table.
Click Close once finished. Every committed record is already saved in the table; nothing further needs to happen.
| Name | Status | |
|---|---|---|
| Jordan Reyes | jordan@example.com | Active |
| Priya Nair | priya@example.com | Pending |
Convert this range to a Table (Ctrl+T), then open Form. Excel builds a Name field, an Email field, and a Status field automatically, with no setup beyond having a header row. Clicking New opens a blank version of the same three fields ready for the next contact.
Five buttons, and what each one actually does.
Clears the fields for a blank entry. Fill them in and click New again, or press Enter, to commit the record as a new row at the bottom of the table.
Permanently deletes the row currently shown in the form. There is no confirmation prompt and no separate undo inside the form itself, so double-check before clicking.
Reverts changes made to the current record's fields, but only before you move to another record. Once you navigate away, Restore no longer applies to that row.
Step one record at a time through the table, in the order rows appear on the sheet, without needing to close the form and scroll manually.
Switches the form into search mode: type a value into any field, press Enter, and Find Next/Find Prev then jump only to records matching what you typed.
The one-time setup that makes everything above possible.
Microsoft dropped Form from the default ribbon layout, but never removed the underlying feature, so it still has to be added back manually through the Quick Access Toolbar.
File > Options > Quick Access Toolbar, change "Choose commands from" to All Commands, scroll to Form, click Add, then OK. It now appears as a small icon at the very top of the window.
Once added to the Quick Access Toolbar, Form stays available in every workbook you open on that machine, not just the one you added it from.
Form only builds fields for the first 32 columns of a table. Wider tables need a different approach, such as a custom UserForm built with VBA.
| Feature | Built-in Form | VBA UserForm |
|---|---|---|
| Setup time | One-time, no code | Requires VBA |
| Dropdown fields | No | Yes |
| Field limit | 32 columns | None |
The built-in Form wins on speed for a simple table. A custom UserForm wins the moment you need dropdowns, validation, conditional fields, or more than 32 columns, at the cost of building it in VBA first.
Four things that go wrong on the first attempt.
Click into an actual cell inside your data range first. Form needs an active selection inside a table or contiguous range to know what to build fields from.
That column contains a formula. Form displays calculated columns as read-only by design, since typing into them would overwrite the formula.
You likely clicked Close instead of New/Enter after filling in the fields. Nothing is saved until the record is explicitly committed.
The range likely has more than 32 columns, or is missing a proper single header row. Check both before assuming the command itself is broken.
Where the built-in form earns its place, and where it doesn't.
For a table without dropdowns or complex validation, Form is genuinely the fastest way to add and search records without touching the grid directly.
Form's own Criteria search is basic. For a friendlier, always-visible search bar, a macro or FILTER-based approach gives more control over how results display.
Once a form needs dropdown lists, conditional fields, input validation, or more than 32 columns, the built-in Form command runs out of road and a custom UserForm becomes the right tool.
Sheets has no built-in in-sheet form. The closest equivalent is Google Forms, a separate tool whose responses feed into a linked sheet, which behaves differently from an in-cell form.
Form works against a plain range too, but a blank row anywhere in it, or a missing header, is enough to make Excel misread where the data starts and stops.
Data validation dropdowns applied to the table don't carry over into Form; every field appears as a plain text box, so bad entries can slip through.
Filled-in fields aren't saved until New (or Enter) is clicked. Clicking Close first discards whatever was typed.
Those fields show read-only for a reason. Editing a calculated value there would silently overwrite the formula it depends on.
The limit is fixed. A wider table needs either fewer visible fields in the form's underlying range, or a custom UserForm instead.
There's no confirmation dialog. Delete removes the currently shown row immediately, so confirm you're looking at the right record first.
A form shouldn't top out at 32 columns with no dropdowns.
Free 14-day trial. No credit card required.
A built-in pop-up data entry form that generates one field per column in a table, letting you add, find, edit and delete records without scrolling through rows and columns.
Microsoft removed it from the ribbon years ago but never removed the feature itself. It has to be added manually to the Quick Access Toolbar to use it.
Go to File > Options > Quick Access Toolbar, change "Choose commands from" to All Commands, find Form, click Add, then OK.
Yes, or at minimum a range with a single header row and no blank rows. Select any cell in it and press Ctrl+T to convert it to a Table before opening Form.
No. The Form command is a native Excel feature with no code involved, unlike a custom UserForm, which does require VBA to build.
Up to 32 columns. Beyond that, the Form command won't open, and a custom UserForm or a different layout becomes necessary.
Yes. Click Criteria, type a value into any field, then use Find Next and Find Prev to step through matching records.
No. Columns containing formulas appear greyed out and read-only in the form, since editing them there could break the formula.
Whatever is currently typed into the form's fields is discarded. A record is only added to the table once New (or Enter) is clicked to commit it.
Not a native one. Sheets pairs with Google Forms instead, a separate tool that submits responses into a sheet, which works differently from Excel's in-sheet Form command.
No. The Form command displays dropdown-validated columns as plain text boxes, so invalid entries can slip through unless you also add a check elsewhere.
A VBA UserForm, built through the Developer tab, supports dropdowns, buttons, validation and custom layouts, at the cost of needing macros and more setup time.
Every business starts with a spreadsheet. Updoot is where you scale past it, with data already connected to where it belongs.
Start Your Free Trial →