Macro to Open the Data Enrty Form

Excel’s Data Form shows one record at a time in a simple dialog, making it easy to add, find and edit rows in a long list. It’s hidden from the ribbon, but this one-line macro opens it.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Open the Data Form

Your data should start in A1, with headers in row 1:

Sub OpenDataForm()
    ActiveSheet.Range("A1").Select
    ActiveSheet.ShowDataForm
End Sub

Open it for a table anywhere on the sheet

Click any cell in the data first, then run:

Sub OpenDataFormHere()
    ActiveCell.CurrentRegion.Name = "Database"
    ActiveSheet.ShowDataForm
End Sub

Naming the range Database tells Excel exactly which data the form should use.

What you can do in the form

Button Does
New Adds a record at the bottom of the list
Find Next / Find Prev Moves through the records
Criteria Finds records that match, such as Region = South
Delete Removes the current record

Without a macro

  1. Go to File → Options → Quick Access Toolbar and choose Commands Not in the Ribbon.
  2. Add Form…, then click OK.
  3. Click a cell in the data and click the Form button.

Things to know

  • The form shows up to 32 columns.
  • Columns with formulas appear as read-only text.
  • For a modern alternative, see Excel Tables or Microsoft Forms for collecting data from others.

Related macros