3 Ways to Prevent Excel Data Entry Errors

Garbage in, garbage out, am I right?

Valid Data is Crucial

You depend on Excel spreadsheets to show accurate data and analysis.

Enter the wrong data and the results will be incorrect, and that could have a negative impact on your business or reputation.

Safeguard Data Entry

Here are 3 ways to help safeguard your data entry, and I’m sure you use a few other methods in your worksheets too.

Here’s a screen shot of a data entry worksheet, where I’ve used each of those technique, to help people enter valid data.

But remember, these techniques help make your Excel files idiot resistant, not idiot proof!

You’ve met people, right? They’re very creative at finding ways around your helpful Excel data entry safeguards.

Data Validation Drop Down and colour coding
Data Validation Drop Down and colour coding

1) Use Keyboard Shortcuts

Keyboard shortcuts save you time and can prevent errors. Quickly enter the current date and time, or copy the cell above, instead of re-typing.

  • To enter the current date: Ctrl + ;
  • To enter the current time: Ctrl + :
  • To copy from the cell above: Ctrl + D

2) Use Drop Down Lists

Instead of allowing free-form data entry, you can add drop down lists to the worksheet using the Excel data validation feature.

For example, create a list of product names, and select from that list.

This prevents typos, and only valid entries are allowed. Then, if a product is discontinued, remove it from the list, and it won’t appear in the drop down for new entries.

To create a drop down list, follow these steps:

  1. On a blank worksheet, create a list of items for the drop down list.
  2. To name the list, select all the items, then click in the Name Box, at the left of the formula bar. Type a one word name for the list, such as Product_List then press the Enter key.
  3. On the data entry sheet, select the cells where you want the drop down list.
  4. On the Excel Ribbon, click the Data tab, then click Data Validation.
  5. In the Data Validation dialog box, for Allow, select List.
  6. In the Source box, type an equal sign, then the name of your list: =Product_List
  7. Click OK.

3) Highlight Missing Data With Colour

Use a bright colour to highlight data entry cells that are empty. This makes it easy to spot the cells that have to be filled in.

To highlight an empty cell:

  1. Select the cells that you want to highlight (to select non-adjacent cells, press the Ctrl key, then click on cells)
  2. On the Excel Ribbon, click the Home tab, click Conditional Formatting, and click New Rule
  3. In the Select a Rule Type section, click Use a Formula to Determine Which Cells to Format
  4. In the Format Values Where This Formula is True box, type a formula that checks the active cell (Name Box shows active cell address), using double quote marks (“”) to test for an empty string. For example: =A3=””
  5. Click Format, on the Fill tab select a color, then click OK, twice.

What Are Your Data Entry Safeguards?

What techniques do you use in your Excel files, to help people enter valid data?

Do you use the 3 techniques that I listed?

___________

2 thoughts on “3 Ways to Prevent Excel Data Entry Errors”

  1. I’m a regular visitor to your blog and website. Your explanations on Excel topics like data validation is very clear and concise. The examples that you provide are easy to follow. I’ve enjoyed and benefited from your web pages.

    Of course, I’ve added your website address in the ‘about’ page of our website familycomputerclub.com.

    Thank you very much.

  2. Hello,
    My name is Mercy and I am a current student at UMA. My instructor shared this link with me as we are currently learning about Excel. I have taken notes on these useful tips you shared of how to safeguard data entry. I am still learning therefore, I thank you for sharing these simple directions or instructions and I am looking forward in following these easy steps. I have also saved this to my reading list.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.