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.

Data list formatted as a table with banded rows

Data list formatted as a table with banded rows

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.

Finding the FORM button to add to the QAT or RIBBON

Finding the FORM button to add to the QAT or RIBBON

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.

The FORM button added to the QAT

The FORM button added to the QAT

 

 

 

 

 

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.

Viewing a single record using the data form

Viewing a single record using the data form

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.

Buttons on the data form

Buttons on the data form

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.

A form showing both data entry boxes and un-editable formulas

A form showing both data entry boxes and un-editable formulas

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.

Example showing multiple search criteria

Example showing multiple search criteria

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.

Advertisements

Posted on April 9, 2015, in General, Info and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: