Use the Data Entry Form in Excel
by Steve Giesel
If you’re like me, occasionally you need to create a Comma Delimited
(.CSV) file. You know the type, these are files typically created
in Microsoft Office Excel and then saved in .csv format. The
first row is usually used for column header titles such as FirstName, LastName,
email address, etc. and subsequent rows are filled out with the actual data,
one dataset per line. We use this type of file to create and populate
students into course management software such as BlackBoard or Moodle.
You can also import data into Excel via the .csv file.
Data entry into Excel cells can get tedious, but Microsoft includes a
form entry tool that speeds up this process. It’s been around a long
time but doesn’t receive much notice. The command button for launching
this data entry form is not available on the Ribbon, so you’ll need to add
it to your Quick Access Toolbar. For a review of the
Quick Access Toolbar and how to customize it, please see the Quick Tips
I’ll briefly describe and illustrate the steps below so you can get started.
Be sure to watch for a Quick Tips movie in the near future.
PLACE THE FORM BUTTON ON YOUR QUICK ACCESS TOOLBAR
- Click the Customize Quick Access Toolbar drop-down to open
the menu. This arrow is just
to the right of the Quick Access Toolbar found in the upper left corner
of the Excel Window as shown here.
- From the menu, select the More Commands… link towards the
bottom of the list. The Excel Options dialog window opens.
- The drop-down list at the top left of the window ("Choose
commands from:") provides access
to groups of commands. Click the down arrow and select, Commands
not in the Ribbon.
- Scroll 1/3 of the way down and highlight the Form…
- Click the Add > > button between the two main columns on
the page. The Form…
command will appear in the right-hand column now as a part of the Quick
- Click OK.
The image in #1 above shows the Form… command button already
in my Quick Access Toolbar.
- You are now ready to begin.
SET UP YOUR COLUMNS AND START ENTERING DATA
- Open a new, blank workbook in Excel.
- Enter the column headers as needed in the first row as shown here.
- Highlight any cell you just enter data into.
- Click the Form… button you just added to your Quick Access
- A dialog asking if you wish to use the first row as labels appears,
- The Data Entry form appears. Notice how the three data fields
are labeled with the first-row data.
- Click in the first field and begin entering data.
- Press Tab to go to the next field and enter data.
- When finished entering data in the last field, press Enter or click
the New button to enter that data into the spreadsheet and go to a new
- You’ll be ready to enter data in the first field on a new line,
repeat steps 8 & 9.
- Click Close when finished.
- Click the Microsoft Office Button.
- Mouse down to Save As, then click Other Formats.
- Give the file an appropriate name.
- In the Save as type: drop-down choose,
CSV (Comma delimited) (*.csv).
- Click Save.
- You’ll receive a dialog telling you that some formatting can’t be
saved in the .csv format. Just click OK and you’re finished.
You now have a comma delimited file you created in Excel. If you
need to enter more data later, just open the file in Excel and click the
Form… button on the Quick Access Toolbar. The data entry form
will open ready to add additional data.