Excel – Using the Data Form
If you find you are working a lot with long lists of data, you probably find after some time that distinguishing one line from another becomes increasingly difficult…a form of snow blindness if you like. You can format the list as a table and shows lines in alternating colours which might help but wouldn’t it be nice sometimes to be able to see only one line at a time.
Well…you can, using a FORM. You can go to the length of creating a form in VBA, and adding all kinds of buttons and clever functionality, but there is actually a built in FORM creator which will build a form when required on any data as long as it is in a list format i.e. column headings only, no row headings.
In 2003 or earlier versions of Excel, the FORM was available from the standard menu. Since 2007, it has been relegated to just another button in the CUSTOMIZE THE QUICK ACCESS TOOLBAR (2007+) or CUSTOMIZE THE RIBBON (2010+) options.
You will find the FORM button under COMMANDS NOT IN THE RIBBON or ALL COMMANDS.
Add this to either the QAT or RIBBON and you are ready to go!
So now you have access to the FORM, what does it do and how does it work?
Click anywhere in your data table and click the FORM button.
And without any further intervention by you, or working your way through multiple steps of a wizard, Excel will create a user FORM for you.
Using the column headings, there is a separate line for each heading and a text box displaying the information held in that column for a single record.
In the top right hand corner you will see the total number of records in your list. Using the scroll bar between the data and the buttons you can move from one row to another using the arrows at the top and bottom of the scroll bar or click and drag the scroll bar itself to move quickly through the records.
Not only does the FORM allow you view records, but you can also edit the information directly in the FORM which will update the spreadsheet. There is extra functionality available using the buttons on the right hand side.
New: To add new records to your list click here and start typing the information directly into the text box next to the heading. Sometimes, you will see some values/data in the form that does not appear in a white text box. This will be a calculated field or perhaps a look up function based on something you enter in another box. Because these cells in the worksheet are reliant on a formula you cannot enter information directly here.
One major downside to using the FORM is that if your data table has drop down lists to select values from, these are not transferred to the FORM, so you need to type values in manually.
One advantage of using a FORM to add new records, is that if your data is stored as a NAMED RANGE, any new records you add are automatically added to the NAMED RANGE so you don’t have to manually adjust the REFERS TO bit in the NAME MANAGER. If you are unsure about NAMED RANGES then refer to my blog http://wp.me/p2EAVc-99 to find out more. There is however, one caveat to this. A FORM will only add to an existing NAMED RANGE if it is called “database”. Call it anything else and the NAMED RANGE is fixed and it will not automatically expand to include new records.
Delete: Does exactly what you would imagine…it deletes the record from your table/list – permanently. Note that I said it deletes the record, not just the highlighted piece of information in the FORM.
Restore: If you make a change to anything in the FORM other than delete a record, this button becomes active and allows you restore the data back to its original value.
Criteria: I’ll jump to CRITERIA next because the FIND PREV and FIND NEXT buttons are generally used in conjunction with this button. When you click on CRITERIA it clears the FORM. This now becomes a search FORM. Type search criteria in one or more of the text boxes. You can enter whole values or you can use wildcards. Using FIND PREV and FIND NEXT then allows you to scroll through records that match your search criteria. When you click on CRITERIA you will see a CLEAR button that will clear the form of any criteria you have entered, and a FORM button that will return you the standard form.
So what wildcards can I use?
*(star/asterisk) denotes any number of characters.
H* will search for any value that begins with H followed by any number of characters.
*h will search for any value that ends in h.
? (question mark) denotes any single character
H?p will search for Hip, Hop, Hup, H3p etc.
You can then combine these together or use them in conjunction with < or >.
>h* searches for any names (for example) that begin with the letter h, I, j, k etc. and beyond.
<>h* will search for any values that don’t start with h.
So use any operators you are familiar with (<, >, <=, >=, <>) either on their own or with wildcards and you should be able to find just about anything in your list. Use the FIND PREV and FIND NEXT to scroll through the records that meet your criteria.
And finally Close which unsurprisingly closes the FORM.
So next time you are working with a list of data, give the FORM a go, it might be of use…might not.