Jul
8
Creating Effective Forms with Excel – The Simple Data Form (Part 1 of 3)
- July 8th, 2012
Have you ever gotten an online form from a company or government organization and received a PDF that you had to print out and fill out by hand (and you’ve probably even seen one that looks like a scan of a scan of a poorly built form on an old IBM typewriter)? What’s the point? If the point is to be able to use an electronic medium, then what purpose is served by making you print something and fill it out by hand? Not only does that process waste your time, think about what happens on the other end: you print out the form, fill it out, scan/fax/mail it, then when it gets back to the other side what do they do? Put all of that information back into a computer. What a monumental, no colossal, waste of time (not to mention the time that needs to be spent trying to error check your/my lousy handwriting)! So why do people make it so hard to get information from each other? The answer isn’t simple, but the solution is:
Build a fillable form!
There are many software applications that are purpose built for form building, but unless you build forms for a living you need not go to the expense. If you have Microsoft Office, which most of us do, you can easily build a form in Excel, which is ubiquitously seen in the business world as a form tool (oh, and some of us use it for financial analysis). But it’s an amazingly effective form building tool, and allows us to keep information in the electronic/digital environment, after all, once information is put into a computer, there’s no need for that work to be redone by hand!
So what is a form? Simply put, a form is a medium into which you enter information. We all have to fill them out, DMV registration, passport applications, opening bank accounts, online flight bookings, etc. But what’s the biggest problem with doing we generally encounter with any of these processes? The lack of intelligent design and structure is the problem. Printed/non-digital forms notwithstanding, have you ever gone to an online form to buy something, or book a flight and the tab order is out of sequence, or not logical at all? (For those of you who navigate forms with the mouse, here’s a hint: use the TAB key) We all have, and it’s frustrating, but it can all be avoided if you simply understand that the easiest way to get concise information from people is to make it easy for them to give it to you. If you make it hard for them to do that, then they will make it hard for you. So which would you rather do? Save time and make it easy, or make the experience painful for everyone? Unfortunately, too many forms are seemingly built with the latter in mind.
Like it or not, data collection and management is central to almost everything we do, be it keeping track of personal finances, managing aspects of a business, or even booking a vacation. Unfortunately, most organizations make it difficult for you to give and get information (big corporations and governments most notably), so we’re going to discuss how to keep it simple with one tool that most of us have at our fingertips. Excel.
There are two primary types of forms and the way in which you use them. The first is a form that allows you to capture continuous information, like keeping track of a check register or vehicle service history. This type of information is stored with columns holding relevant transactional details, like Date, Check Number, Payee, Amount, etc., while rows store individual records, or transactions. The second is a single use form, like an employment application, invoice or little league sign-up. This type of form usually requires input in various places on a worksheet. Note that a single-use form can be created in such a way as to capture transactional details, but doing so requires VBA (Visual Basic for Applications) programming, which we’re not going to discuss as part of this series.
In this article we’re going to discuss the simplest way to create a continuous form in Excel, which is to use the DataàForm method (a built-in functionality in Excel). However, it’s not very robust, and only supports a limited number of data fields, but it can be a great tool for allowing people to quickly enter data. Unfortunately, it’s one of those features that Microsoft chose to hide in Excel 2010, so you have to find it. We’ll show you that in a minute, first here’s an example of the Data Form:
That’s about as easy as it gets, but it’s not robust in that there’s no way to prevent users from entering incorrect information, so this should only be used for very simple data collection needs. Otherwise you can spend as much time validating what your users entered as you would entering it by hand, knowing what needs to go where, and how. Note in the example how the data is clearly structured with each column storing a different type of information, while each row represents a unique record. One nice thing about this structure is that it readily lends itself to being represented as a Pivot Table or Chart, which are great ways to analyze your data, and display it graphically.
Note: good data at the end is only as good as the data going in!
To expose the Data Form tool in Excel 2010 goto the Quick Action Toolbar above the Ribbon, select the drop-down for more options, then the More Commands option.
QAT
Once you do that the Customize Ribbon dialog will appear, in the left-hand pane select All Commands, then find “Form” and drag it to the right hand pane like you see here:
Customize the QAT - More Commands
Add the Data Form to the QAT
From here on out, anytime you’re in a data set that Excel can recognize when you click on the Form button the Data Form will appear. But there are some simple rules for using this tool: first, your data needs to be structured in such a way that Excel can read and understand it (in much the same way that your users need to understand what’s expected of them). So your worksheet needs to be set up in a contiguous manner, meaning that if you have empty columns or rows in your worksheet, then don’t expect Excel to pick it up (then again, don’t expect your users to either). Second, you need to have column headers for the form to be able to identify, and as you saw in the Data Form example, Excel uses the first row in your worksheet to define the entry fields. Third, if you have more data fields then the form can accept, this won’t be the appropriate tool for you (but we’ll discuss that in the next article). Fourth, and key to intelligent form design is to layout your worksheet in such a way that makes sense for data collection and use once you get it, which means that individual records should ideally be stored in a series of rows going down the worksheet, not columns going across, as columns should be used for individual components of each overall record (row). Also think about Excel’s capabilities: while you now have over 1 million rows of data, you “only” have 16,000 columns. If you were to collect data in columns as opposed to rows, you could run out of room. Unfortunately, we see this type of structure all too often, so you can avoid the pain of having to rebuild a workbook just by building it right in the first place. Here’s an example of the simple Data Form in use:
Data Form Example
Notice that your column headers do have Accelerators attached to them. Those are the underlines beneath the S in Store, a in Date, etc. To quickly acces any of those fields directly from the keyboard, all you need to do it press ALT+ the corresponding letter for that field. I’ve never tried to push one of these forms to its field limit, but I’d imagine that you’re going to run out of Accelerators when you get beyond 26 fields (corresponding with 26 letters in the alphabet).
If you do like the Data Form, but need a more robust version, longtime Excel MVP John Walkenbach has a free add-in he created called the Enhanced Data Form. You can find it here: http://spreadsheetpage.com/index.php/dataform/home.
John Walkenbach's Enhanced Data Form
You can find a copy of the Simple Data Form workbook here: http://sdrv.ms/OLHFSh. Note that it contains a simple macro attached to a button to display the form in case you haven’t exposed the Data Form button on the QAT, which will require you to enable macros. In versions of Excel prior to Excel 2007, the Data Form is available from the Data menu.
In the next article we’ll discuss building single-use forms, like an invoice, or employee expense report.
Visit My Website
Great post Chris. Nice graphics as well.
Christopher