Excel Forms

By Author: Ed Bolton
Total Articles: 17
Comment this article

If you've ever seen Microsoft Excel, you would describe it as a criss-cross of boxes into which you can enter data. A spreadsheet is a table. One advantage of the simple design is it allows you to analyze your data e.g. one short formula allows you to sum all the data in a single column. This simplicity is Excel’s greatest strength but there are two common situations in which it becomes a weakness.



Firstly, if you are trying to design spreadsheets for publication, the table format can become very limiting. You can mess around with column widths to place text on a page but the columns have to be consistent down the entire document. So you end up having to merge lots of cells to create an area of text. In this case, the solution is to create your documents in Word or Powerpoint and insert your tables/charts as required. The Microsoft Office suite is sufficiently integrated to make this relatively straightforward.



The other weakness is with collecting data. If you want a number of users to add information to your spreadsheet, you don’t want them to leave out any relevant fields ...
... of information. The most widely used solution is to shade the cells that require user entry. Unfortunately, this still relies on them spotting all the shaded cells and taking action.



The next option is to protect the Excel spreadsheet, and unlock all the cells that require user entry. This prevents a user editing and potentially damaging the rest of the sheet. It also means they can tab between the cells into which inputs are required. So it speeds up their navigation of the spreadsheet and means they systematically consider every cell requiring an input.



Protecting cells doesn't ensure the integrity of the entries they make. So then you can add data validation to cells to ensure e.g. a user enters a date where one is required. For many applications, this will be sufficient to prevent errors in data entry. However, it is not possible with a simple spreadsheet to force a user to complete the sheet in full. The only way to look for incomplete entries is to add Visual Basic code to your file. For that, you may require the help of an Excel expert.



Of course, if you require Excel consulting and help, you may as well go a stage further and dispense with entering data directly into the spreadsheet. Instead, you should request that data get entered into a form, and only placed on the spreadsheet when validated. A form is what you complete when you go to a website. You have a list of fields e.g. name, phone, address to fill in and, if you've entered an invalid e-mail address, it won't let you complete it. Forms are clearly the easiest way to collect data, otherwise every website in the world would ask you to add your details to a table.



They also have another advantage. Let's say you have to select your state and city on a form. If you have 1,000 cities, it could be somewhat tedious for the user to identify their own location. Whereas with a form, the list of cities can be filtered based on their selection of state. Again, this improves the user experience and reduces the risk of errors. Dropdowns are one way of ensuring the user enters valid data. Another advantage is that you can provide a visible calendar from which they can select a date.



Forms are an excellent way of ensuring that, if anyone else is going to enter data into your spreadsheet, then they're going to do it in exactly the way you intended. By giving them lists from which to select data, you can also reduce the number of questions they may have in the process.



About the Author: Ed Bolton is the founder of Excel4Business, and specialist in Excel consulting.