10 Tips to Avoid Data Entry Disasters

Ten tips can save our professional reputation. Professionals create solutions for customers to use. Our customers expect perfection. Our products must not break. Our products must produce accurate results. But how do we guarantee these when our customers can enter 'garbage'.

Garbage In, Garbage Out (GIGO)

This is not a new problem. The phrase Garbage in, garbage out has been around since the late 1950's. Fortunately for us some very smart people have wrestled with this problem and found solutions. Here are ten of them that build quality into our Excel productions, prevent data entry disasters, and help preserve our professional reputations.

Basic Tips

1. Segregate User Inputs

We will frustrate our customers if they have to hunt for where they can make entries. To minimize their frustration, keep all inputs in one prominent and easily accessible place so users know where entries are expected and can see ALL that is expected. This also minimizes errors of omission.

2. Format User Inputs

We will frustrate our customers if we do not make it obvious which cells accept input. The solution is to use a unique cell fill and font color to highlight input cells. To ensure standardization, use Excel's Input style (change its horrible color). Applying styles is easy and styles can also unlock data entry cells (More on this in tip 5).

3. Label User Inputs

Add a clear and concise label to the cell left of or above the input cell to make it clear what is being requested. Include units of measure for numeric values.

4. Add Data Validation

Customer's won't forgive us for letting them making stupid entries. They expect us to protect them from fat-finger events or brain-lapse moments. To meet this expectation use Excel's data validation to restrict dates to dates, restrict quantities to positive values, restrict item counts to whole numbers, restrict selections to a list, etc. If Excel's data validation is insufficient - use VBA.

5. Worksheet Protection

Our model or application can be ruined if users change our formulas. And once again, customers will blame us for letting them destroy our work. The solution is to lock all cells that are not data entry cells so users can't make entries where they shouldn't. And once again, Excel's Styles make this easy. By default all cells in a new worksheet have the Normal style applied and by default, the Normal style is set to Locked. This has no effect until will apply worksheet protection at which time no changes can be made to locked cells. So before we apply worksheet protection we should make sure Excel's Input style is set to Unlocked and all data entry cells have the Input style applied to them.

Advanced Tips

6. Use Integrity Checks

When entering things like percentages for allocations, make sure they add up to 100%. This type of check cannot be performed in data validation. It requires calculations performed outside data entry cells. Calculated checks are also known as Integrity Checks. Integrity checks can prevent data entry errors and modeling errors. For more on integrity check best practices and how-tos see Beyond Excel: Integrity Checks.

7. Error Messages

When our solution detects an error, whether found by data validation or when an integrity check fails, display the problem and recommend a solution. Customers appreciate guidance. Here is an example: "Problem: Date is in the past. Fix: Enter a date greater than today"

8. Conditional Formatting

When integrity checks fail, apply conditional formatting to make failed checks glow red (like the Excel's Bad style) so they are obvious. Optionally, confirm checks that pass with a green fill (like Excel's Good style). People like little rewards that affirm all is well.

9. Visibility

If a problem exists anywhere, it must be visible everywhere. Display integrity check error messages prominently. I recommend placing them at the top of every worksheet just below the worksheet's heading and above the freeze pane line. 

10. Hyperlinks

Add hyperlinks to integrity check error messages so we can take ourselves or our users to where the problem is so we can resolve it as quickly as possible

For more on Integrity Checks, including how to add hyperlinks see Beyond Excel: Integrity Checks.

Shameless Promotion

That which I preach, I do. I use tools to help me. I offer some tools free on Beyond Excel. I offer a few tools for nominal fees on Eloquens.

To manage styles and apply corporate branding see: Eloquens: BXL Format Add-in

To facilitate creating and managing integrity checks see: Eloquens: Integrity Checks Add-in