3 Ways to Prevent Data Entry Errors in Excel

Garbage in, garbage out. 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.

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

Data Validation Drop Down

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

Use Drop Down Lists

Instead of allowing freeform data entry, you can add drop down lists to the worksheet. 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:

  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.

Highlight Missing Data With Color

Use a bright color 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.

___________

You may also like...

1 Response

  1. Dinesh says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *