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.
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:
- On a blank worksheet, create a list of items for the drop down list.
- 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.
- On the data entry sheet, select the cells where you want the drop down list.
- On the Excel Ribbon, click the Data tab, then click Data Validation.
- In the Data Validation dialog box, for Allow, select List.
- In the Source box, type an equal sign, then the name of your list: =Product_List
- 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:
- Select the cells that you want to highlight (to select non-adjacent cells, press the Ctrl key, then click on cells)
- On the Excel Ribbon, click the Home tab, click Conditional Formatting, and click New Rule
- In the Select a Rule Type section, click Use a Formula to Determine Which Cells to Format
- 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=""
- Click Format, on the Fill tab select a color, then click OK, twice.