- July 9th, 2012
Note: this article was written specifically for Excel 2010 and its Ribbon interface, however the concepts reviewed will apply for the most part to earlier versions.
In our last article we discussed how Excel is a fantastic tool for creating forms, and reviewed the native Data Form, which is built into Excel (but you need to work to find it in Excel 2007+). It’s a simple tool for entering transactional data, like check register or vehicle service information, but isn’t a great tool for one-off purpose-built forms that generally only need to capture your information one time, like an application form, employee expense report or time-off request. The next form type we’ll discuss is the worksheet form, in which you model a worksheet to mimic what you would see with a printed form. In fact, many companies make forms like this in Excel so they can replace pre-printed forms, and it saves quite a bit of expense, both from not having to send something to a commercial printer, as well as being able to capture data electronically (although tragically, most companies that employ digital forms – in Excel or specific form building software applications – never do anything more with that digital information). Another key point about this article is that we’ll be talking about how to design an intelligent user-entry form for one-time use, not being able to actually capture the data in a transactional sense, like keeping a record of invoices you generate. That is easy enough to do, but it requires VBA (Visual Basic for Applications) code, which is outside of the scope of this article. However, there will be a link to a Company Invoice template form that can capture transactional data that you’re free to modify for your own use.
As mentioned in the last article, the key to intelligent form design is making it as easy as possible for your users to give you the information that you want. If you make it hard for them to give it to you, they will make it hard for you to get it out. It’s really as simple as that. This isn’t necessarily by any conscious decision on their part, it’s just a fact that if you make it difficult for someone to give you information, they generally won’t be inclined to spend a whole lot of time error checking their entries. We’ve all had negative experiences filling out one form or another, so that leads to two primary principles in design: first, make it easy and intuitive for people to enter information (data), and second, structure your form in such a way that it flows from point to point in a natural progression. Here’s a simple example for entering phone numbers: most people will enter “(212) 555-1212”, and waste time with the parentheses and hyphen, when Excel has a Telephone number format that will accept “2125551212” and automatically format it as “(212) 555-1212”. Do you have to educate your users to a certain extent, sure, but they’ll thank you once you do, and that can cascade down to how they interact with other intelligently designed forms & applications in the future. In fact, intelligent design extends far beyond just filling out forms, but how people interact with applications in the first place.
With that in mind we’ll be covering various elements native in Excel to be able to facilitate efficient design and user input:
- Worksheet Protection – Using some of Excel’s internal functionality to direct data entry, and prevent them from overwriting important formulas or other information.
- Data Validation (to limit or direct user entry) – For instance in a Time-Off request you wouldn’t want to let a user enter a date before today. Or using lists to limit what a user can select, like all of the US states, instead of giving them the opportunity to misspell Mississippi (or in one case I saw not too long ago: “Missasagua”, and that’s probably misspelled).
- Conditional Formatting – You can use this to indicate to a user that a cell needs to have an entry.
- Cell Formatting – Just like the telephone number formatting example.
- Worksheet Formulas (to prefill entries based on a Data Validation selection) – Let’s say you have a customer list in a worksheet, you can use VLOOKUP to pre-fill the customer address information as soon as the user selects Customer Name from a Data Validation list.
- Worksheet Controls – Check Boxes, Option Buttons, and other web-type features that make it easier for your users to fill out a form faster. An example would be a “Tax Required” button on an Invoice form that could then be used to calculate the appropriate tax.
None of the methods we’ll be discussing are infallible, the data/input that you receive is only as good as the users giving it, and there will always be mistakes. But if you can limit that on the front end, then you greatly simplify both your life and theirs, and you can all subsequently spend more time on more important things.
Worksheet Protection – What is that anyway? Well, by default all cells in an Excel worksheet (over 1 billion now) are protected. Which means that if you protect a worksheet, then user entry into any cell is restricted. But you can use that to your advantage, so let’s say that you want to allow a user to enter information into certain cells, but not alter others, then you simply unlock those cells and protect the worksheet. Once you do that users can only enter information into those unprotected cells.
NOTE: you can select multiple, non-contiguous cells with CTRL+Left-Click, then CTRL+1 launch the Format Cells dialog. From there goto the Protection tab and uncheck the “Locked” check box. When you protect the worksheet afterwards, then just those cells will be open for entry. The Hidden option is for formulas; when you select that then users will only see the results of formulas, as opposed to the actual formulas themselves. This can be very handy if you have novice users who might get confused when they see formulas (or if you have a very complex formula that you don’t want to share).
An added bonus to Worksheet Protection is that it introduces a natural Tab order (meaning that your users can use the Tab key to move from each unprotected cell to the next). A key note here with regards to design is that the natural Tab order goes from Left-to-Right, then back down to the next row and does not deviate, so you need to keep that in mind with your “intelligent” design. By way of example, think of any Internet form you’ve filled out where you tabbed to different fields in the wrong order, requiring you to redirect to go back to the right place. What’s that do? Wastes time right? While it might not seem consequential, think of a company like Verizon with 200,000 employees. If they have a poorly designed form that even only 20% of the staff use, and they spend an extra x minutes per week/month working with an inefficiently designed process then you can only imagine the cascading effect that can have on their internal efficiency, and subsequently why our rates go up each year. Another thing that taking advantage of the natural Tab order does is keep your user’s fingers on the keyboard instead of transitioning to the mouse to move from cell to cell. Anytime you can eliminate wasted motion like that is a good thing and again, it might seem inconsequential, but it adds up over time. And time goes two ways, you waste it or save it, but you can’t get it back.
Once you’ve selected the cells you want users to be able to enter data into you can goto the Review Tab and select Protect Sheet. Or in the vein of efficiency, use the keyboard shortcut ALT+T+P+P, both of which will display the Protection dialog:
Beginning with Excel 2003 Microsoft added significantly more protection options, some of which you might find helpful beyond just locking/unlocking cells. Deciding which options to allow is entirely up to you, and it may well very change from form to form, so it’s well worth experimenting with the options to see how they all work.
Standard worksheet protection with unlocked cells should suffice for most forms, but it does take some thought when you plan the form to ensure that you can follow the Tab Order directionality. There are other ways to allow tabbed behavior using Defined Name Ranges and/or VBA (Visual Basic for Applications) code, but that’s another discussion. You’ll find more details on worksheet protection in this article: Automating Data Entry and Protecting your Data.
Data Validation – This is one of the most powerful tools you have when it comes to building powerful, user-friendly and intelligently designed forms. Data Validation allows you to direct a user to only input specific information, like select from a list of States, input a certain range of numbers, or dates. For example, if you have an Employee Time-Off request, you wouldn’t want someone to be able to enter a day before today. Or you might have an Annual Employee Performance Appraisal and want to limit a recommended annual merit increase to a certain percentage. For more information on how to set up Data Validation, see our previous article here: Automating Data Entry with Data Validation Lists.
The example workbook for this article is a very simple customer information form. Here is an example of using Data Validation to allow a user to select from a list of Titles:
Notice how the cells requiring are shaded orange, but the Data Validation cell where “Mr.” has been selected is white? That brings us to our next topic, which is Conditional Formatting.
Note: Excel internally recognizes Data Validation input on a worksheet, and it’s captured with the VBA Worksheet_Change event, which can be a very powerful tool for directing workflow based on a user’s selection.
Conditional Formatting - Once you’ve set up your form, and for this article we’re using a very simple example, you can use Conditional Formatting to focus the user’s attention on where they need to enter data. This won’t stop someone from submitting your form with incomplete information (you can do that with VBA), but it serves as a visual clue that they need to fill in certain fields. To employ Conditional Formatting, simply select the cells that you want shaded (HINT: this can be combined with the steps to unlock cells, as you have already selected the cells, so why repeat it?), goto the Home tab and select Conditional Formatting, or use the keyboard shortcut ALT+O+D. In this case we applied a very simple condition that simply checks to see if each cell has a value of nothing (“”) or not. If the condition is true, then Excel applies the format, otherwise it won’t. There are a lot of features available in Conditional Formatting, far too many to review here, so please spend some time getting used to it. Beginning in Excel 2007 Microsoft greatly enhanced your capabilities with this tool, so it’s well worth getting to know. For instance, you were previously limited to 3 conditions, now you have 64. Although please think conservatively, as actually having that many conditions could cause just as much confusion as not.
Note on Quotes: when you initially enter =”” in the Conditional Formatting Rule criteria and confirm it with OK/Apply you probably won’t see anything happen, which might lead you to believe that you did something wrong. You didn’t, but Excel will initially change the formula to =””””””, so first hit Apply, then go in and Edit the rule and remove the additional quotes that Excel added. Unfortunately, this is by design as Excel has a hard time with quotes (and apostrophes), as they have internal functions as well as what you’re trying to do with them.
Cell Formatting – The next element of form design is using custom cell formats to your advantage. It’s all too common to see users fill out forms using literal formats, like entering “(212) 555-1212”, which is unnecessary, as you can tell Excel to do it for you. For forms especially, there’s even a series of “Special” options that have been pre-built for you. In this case we’ll select Phone Number. Now all your users need to do is enter “2125551212” and Excel will automatically format it for you. Cell formatting also preserves the integrity of a value, whereas user entered punctuation can render it nearly useless as Excel will then read it as text. It is possible to get to the value with the use of some formulas, but why go to that extent if you can avoid it up front? The key is to use Excel’s tools so that it works for you, as opposed to you working for Excel (which happens a lot). Fortunately, Excel’s pretty smart, so it knows a lot of things, like “$123.45” is a number even though the $ has been added by hand, but you should never count on it, just like you shouldn’t anticipate that users won’t make mistakes (especially since we all do).
Worksheet Formulas – You can use formulas to your advantage in Excel forms, and it is one of its most powerful features. While we’re not going to get into a full-blown formula discussion here, as there have been entire books devoted to the subject, we’ll cover a few concepts to give you an idea of what can be done, which is primarily having Excel perform calculations so your users don’t have to do it themselves (can you say error management?). Let’s go back to the Employee Performance Appraisal example. Imagine that you have a list of all employees on another worksheet (Employee ID, Name, Hire Date, Hourly Salary, etc.), you can use formulas to populate all of that information simply by selecting the Employee ID from a Data Validation list. Here’s an article on IF statements & LOOKUPS, which allow you to do that. You can also use a method called Concatenation to combine information from certain cells and create unique text strings. For instance, let’s say that your employee e-mail naming convention is “LastName.FirstName@Company.com”, and you have First Name in cell A1 and Last Name in cell B1. You can use:
Which would create “Jones.Bob@XYZco.com”. The key here is the Ampersand (&) operator, which lets you link both cell references and values. Since that formula creates a text string you can coerce it to be a hyperlink by using the following:
This brings up an interesting point that if you try to do this with numeric references you’ll lose any number formatting you might have applied as Excel now sees that data as text, but you can use the TEXT function to coerce it back within your new text string. The following example lets you concatenate text and today’s date all within one cell:
=”Report Printed on: ”&TEXT(TODAY(),”mm/dd/yyyy”)
Which would read: “Report Printed on: 07/09/2012”. Otherwise you’d see: “Report Printed on: 41099” (41099 is Excel’s way of storing the date value). Pretty slick, huh? I liken this to Excel knows that I’m on Windows 7, it knows my system’s regional preferences, but it doesn’t know my intention with the value I’m referencing, even if I’ve already formatted the a referenced cell properly, so it runs home and converts the value into its basest element, in this case the system date value.
Worksheet Controls - The final element to worksheet forms is using worksheet controls, of which there are two distinct categories: Form Controls & ActiveX Controls. These are items like Check Boxes, Option Buttons, List & ComboBox controls, etc., all of which can be used to simplify form use. For example, you could add a Check Box to a form for “Tax Required?”. Once checked it would trigger a formula that will calculate the Tax due on a purchase order. For the purpose of this article, we’re only going to discuss Form Controls, as they can be formatted and controlled within Excel itself. ActiveX controls on the other hand are VBA components and you generally need to write code against them in order to expose their full capabilities. While they are more flexible and robust than their Form control counterparts, they are more difficult to master.
The first thing you need to do is expose the Controls themselves, which are located on the Developer tab in the Ribbon. As this also exposes VBA elements, Microsoft chose to hide this Ribbon element by default, since unfortunately most Excel users will never use it. In Excel 2010 goto FileàOptionsàCustomize Ribbon and check the “Developer” check box in the right-hand pane. In Excel 2007 this is a check box under the General options.
Once you confirm this you’ll now see the Developer tab appear on the right side of the Ribbon. From there you’ll see a Ribbon group called Controls, and clicking on the Insert button will display the controls that you have available:
Like Conditional Formatting and Formulas, there are so many options here, that we’re just going to briefly touch on the topic with a general example. But you should experiment with as many of the controls as you can. Once you do you’ll probably find yourself using them all the time, even for your own work. One of the most common controls to use is the Button. Buttons are great because you can assign macros to them to perform certain tasks, like print a form once it’s been filled out. What’s really cool is that you can assign a macro to almost any object. Form Controls are objects, but so are any of the shapes you’ll find from the InsertàShapes Ribbon group. I prefer to use shapes to assign macros to since they’re much more visually appealing, and while it may seem inconsequential, people will actually fill out a well-designed and appealing form much faster and with less errors than one that‘s poorly designed.. Which would you rather see:
Building a good form not only takes functionality into consideration, but needs to appeal to the end user as well. Just think about how much easier your tax forms would be to fill out if they were thoughtfully designed.
Note: in the example workbook both buttons have a macro assigned to them to display the worksheet form in Print Preview mode, so you’ll need to enable Macros in order for them to work. You’ll find an Introduction to Macros article here: Automating Tasks with the Macro Recorder – First Steps.
For our working example we’ll add a Check Box to our form to indicate whether our contact wants to receive opt-in e-mails. From the Form Control dialog simply select the Check Box control (the Form control, not ActiveX), then draw it on your worksheet with the mouse.
From here you can right-click the Check Box to gain access to its internal controls and formatting. Obviously you want to edit the text to suit your needs, but you can also do things like linking the control to a particular cell. When checked the control has a value of “TRUE”, and when unchecked its value is “FALSE”, so you can perform calculations against that. Most of the time you’ll put the linked cell in either a location away from the form itself, or format its text color to match the form background so it doesn’t display. If a user doesn’t need to see the TRUE/FALSE value, then don’t show it to them. There’s no need to confuse people by displaying something that they don’t need to see.
In this case the Check Box is linked to cell B11, which will toggle/display TRUE/FALSE depending on whether it’s checked or not. As you see in the Value options, you can decide whether the Check Box’s initial state should be Checked or Unchecked. A quick note on formatting is that you can’t change the font size with Form controls. That’s controlled strictly by the active worksheet’s Zoom setting. On the other hand, you can manipulate font attributes with ActiveX controls.
In this example the formula in cell A15 simply reads:
=”This customer “&IF(B11=TRUE,”does”,”does not”)&” want to receive e-mail communication.”
This is another example of using concatenation to create custom messages for your users, or perform calculations that they might otherwise need to do manually. Granted, these are simple examples, but they should give you an idea of what you can do. If anything it will help you realize how you can set things up in a time-saving and well thought out fashion to make it easier to collect information from people. The other thing it does is greatly reduce errors, which directly equates to bottom line savings.
Review – in this article we discussed the various elements that you can employ to streamline data collection processes without having to purchase an expensive purpose-built form creation software application simply by using tools readily available to you in Excel. You’ll find loads of great example forms on the internet that are free for you to use, modify and distribute as you see fit. Most notably is the Microsoft Template Gallery, which you’ll find here: http://office.microsoft.com/en-us/templates/. The key points to remember about building effective forms in Excel are relatively simple:
- If you make it hard for people to give you data, then they’ll make it hard for you to get it, so be creative, and design forms with the end user in mind. If you focus on HOW the user interacts with your form, then the data flow will be easy. If you focus on just getting the information with little or no regard to their interaction/process then you’re all in for a miserable experience.
- A well-built and intelligently designed form can greatly reduce errors, and allow your users to give you information much faster than if they had to write it out by hand, or perform manual calculations on their own.
- Use the tools that you have available to you, Worksheet Protection, Data Validation, Conditional and Custom Cell Formatting, and Worksheet Formulas and Controls.
Here’s a link to the sample workbook: http://sdrv.ms/PHXUm1
Chris “Smitty” Smith is Director of Training and a Developer for the boutique consulting firm www.excelandaccess.com