A trader in Tokyo found himself in a hopelessly embarrassing situation after it was found that he had keyed in some wrong numbers, which ended up in an error that caused 617 billion US dollars’ worth of orders to be cancelled on the stock exchange. These sorts of errors have earned the name “fat finger errors“, and we thought it was a good opportunity to give you some tips on how to avoid errors in Excel.
Avoid Errors in Excel using Data Validation
Excel has a built in system you can use to avoid errors in Excel where you specify what sort of data should be entered in a specific range of cells and then either warn the user or totally ban any non-conforming data to be entered.
Step 1 – basic settings
First select the cell or range of cells you want to use the data validation feature on, and then click the Data tab and select Data Validation.
You then need to choose a setting that is appropriate to the type of data that will be entered in this cell – if you will be entering text, such as a product or customer name, then possibly the List option is best for you. If the data is a number, then use the whole number or decimal number option.
Avoid Errors in Excel: Settings for Text
If the cells on your sheet will have text typed into them, you can control this in two ways – either by limiting the amount of characters allowed to be entered – for example, you may have a product code that is always exactly 5 characters long – then you could select the “Text Length” setting and set it allow only entries that are exactly 5 characters long.
Or you might want to limit the words that are entered in a cell to a specific list – for example, a user may need to enter the name of a country in a cell, and it’s important the country name is spelt the correct way. To do this, you need to follow these steps:
You have now set up Excel to only allow words that are in your list to be entered in a specific cell or range of cells. The added benefit with this is that users will actually be able to select a word from a drop down cell when they select this cell.
Avoid Errors in Excel: Settings for Numbers
If you need to control numbers, then you can select Whole Number or Decimal number from the list and choose several settings to control a max or min number allowed, or specify a range the number needs to be within. See image below:
Avoid Errors in Excel: Other settings
You can experiment with the other settings as well – such as validation for dates and even more advanced validation using formulas.
Avoid Errors in Excel: How Data Validation should react
Once you have set up the validation type, you can then set up how Excel Data Validation should work. You can set it up to stop users from entering in the information, or warn them, but allow non-compliant information to be entered.
This behaviour is set on the Error Alert tab. Open the Data Validation window and click on the Error Alert tab.
Set the Style to Stop if you would like to enforce full validation – users will not be able to enter any other value that those specified by you.
If you set it to Warning, users will get a warning message with a prompt to proceed with the value they have entered or try again.
If you set it to Information, users will get a message telling them the value entered is not within the allowed values, but it won’t prompt them to change it.
While this might not have saved the Tokyo trader, it will certainly help you avoid errors in Excel in your business.
Data Validation, and Conditional Formatting, which is another excellent tool you can use if avoid errors in Excel is gone over in more detail in our Interactive Excel Courses – information can be found about them here: Online Training for Excel 2010, Online Training for Excel 2013, and Online Training for Office 2010 or Online Training for Office 2013.
