When you are designing a template or entry form, you want it to be easy to use. And that starts with the initial impression.

In general:

• Do not show 1,000 columns or 1,000 rows in a spreadsheet.
• Hide fields and use filters to simplify the workbook. In the right templates, users will only see what they need to see.
• Mark fields clearly. (More on this in a few sections.
• Do not use a lot of colours. Instead, try a technique like zebra striping where every other line is a slightly darker colour.
• Create space by manipulating the height and width of cells so your spreadsheet does not look too cramped.
• Make your worksheet look like a form. You can turn off the formula bar, column and row borders, gridlines, and sheet tabs.

A clean design is a good start to helping your users make sense of your template or entry form. From there, they can start inputting information.

Like the design, you want the actual data entry to be as foolproof as possible. You might have multiple people entering information from different locations.

You want users to just be able to type in their numbers or data, not struggle with complicated formulas and confusing cell connections. That work should all happen behind the scenes, so that when they get the sheet it is as simple as possible.

Some best practices:

1. Protect the entire sheet and only unprotect the cells you want users to be able to change.
2. Keep formulas in protected cells so users cannot change them.
3. Create a list and restrict users to list items. For example, you can create a list identifying all the possible categories for the worksheet so that the category names stay the same. This will eliminate duplicate errors, like “hotel costs” vs. “accommodation.”
4. Clearly label where you want users to input data. This can go back to design.
5. Use AutoComplete for applicable fields.
6. If you want data to appear in multiple sheets, use the [Group] function to make it so users only have to enter the data once.
7. Create a macro to guide users through the process by telling them exactly what they need to enter and letting Excel do the placement.

Which one of these practices will you implement at your organization?