Another way to enter lists in Excel, why Microsoft’s effort sucks and an alternative.
Excel has two ways of typing in data, there’s the usual direct entry into the worksheet or you can make a form with a single click of a button.
For example here’s a ‘typical’ Excel list:
And here’s the same list, displayed as an Excel data form.
Normally making a form is a messy business but the above form is made with a single click in Excel. It takes the column headings as field labels and makes editable fields for each column.
There are buttons:
New – makes a new entry (aka row in the worksheet)
Delete – removes the current entry or row.
Restore – reverts to the last used details for that entry
Find Prev – searches the entries / rows for specified text working back (or up the rows) from the current position.
Find Next – searches the entries / rows for specified text working forward (or down the rows) from the current position.
Criteria – filters the list / form to show only certain entries.
Close – takes you back to the worksheet view.
Getting the button
Microsoft is embarrassed about the Excel data form feature (rightly so) and so they hide it away where customers can’t easily find it.
Excel 2007 and Excel 2010
In recent versions of Excel, the data form feature is hidden away. The easiest workaround is to put it in the Quick Access Toolbar (QAT). Click on the wedge at the right of the QAT and choose ‘More Commands’.
Select the ‘Commands Not in the Ribbon’ list and scroll down to ‘Form …’ then add that button to the QAT list on the right.
Now the Excel data form is on the Quick Access Toolbar. You can click on it within any Excel worksheet with a data list and headings to make the pre-populated form appear.
In Excel 2003 and Excel 2002 right-click on the toolbar and choose ‘Customize …’ at the bottom. Click on the Commands tab then select the category ‘Data’. Click on ‘Form …’ on the right and drag it to wherever you like on the toolbar or menu.
Now we’ve told you about it, now we have to tell you why this Excel feature sucks – really sucks – sucks like a heavy duty vacuum cleaner.
The Excel Data Form could be a major feature, the sort that gets applause in demonstrations, instead Microsoft has given it little attention or effort. It appears no development time or money has been given to it for years.
Where do we begin?
Limitations: the form has a limit of 32 fields / columns. That’s a pity because this feature becomes more useful as the number of columns grows.
Navigation: none of the navigation tools available to Excel have been added. There’s no ‘VCR’ like forward/back buttons, no way to jump to a particular row by number. The vertical scroll bar makes it easy to accidentally jump from one record/row to another without realizing.
Formatting: no cells formatting appears in the form. For example entering a date in a data formatted cell is changed to a date as soon as you leave the cell (confirming that you typed correctly) but in the form there’s no change until you leave that row/record.
Wrapping: have a long text field with wrapping set in the cell for easy viewing? Well forget that. In the Excel data form – you’re stuck with a single text line showing less than 40 characters regardless of the cell formatting.
Validation: all those nifty data validation rules you made are ignored by the Excel form until you move away from that record/row, instead of alerting you in the cell / field as it should.
Input message: any input message/tooltip disappears entirely from the data form.
Drop down list: if you’ve created a drop-down list of options for each cell in a column, the Microsoft made data form will ignore it. You have to type in each entry and it will be accepted, unless you have validations rules as well as the drop-down list.
No synchronization with the worksheet: when you close the form the cursor where you were when the form started. It should be positioned in the row and column last used in the form. This would let you switch between the worksheet and form views.
Modal: the Excel data form is ‘modal’ meaning you’re locked into using that form and can’t switch to any other part of the worksheet unless you close the form first.
Fixed Size: the data form can’t be resized, which is a pity since it would be very useful in this case. The width of the form is based on the widest data column.
And while we’re in complaining mode, how about giving the command a decent name in the Command List’? No wonder many people skip over this feature when it’s listed merely as ‘Form …’ which is almost Delphic in its clarity.
A better option
John Walkenbach has made an excellent Enhanced Data Form which fixes some of the lapses in Microsoft’s effort.
You can do beyond 32 fields/columns, the dialog is resizable and you can have drop-down lists too.
Best of all its free with separate versions for Excel 97 – 2003 and another for Excel 2007-2010. VBA source code is available for $15.
If this is what can be done in VBA by a non-Microsoft staffer, imagine what Microsoft could do to make a really good data form feature with their access to the Excel source code and immense resources? All they are lacking is the will.