avoid errors in ExcelA 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.

avoid errors in excel_1

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_2

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.

avoid errors in excel_3

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:

Create a list of the allowed words on a seperate sheet and then select this entire list.
1) Create a list of the allowed words on a separate sheet and then select this entire list.
2) In the formula box (above cell A1), enter a name to describe your list, then press enter.  This has now named this range.
2) In the formula box (above cell A1), enter a name to describe your list, then press enter. This has now named this range.
3) Select the cell you want to add this validation to and open the Data Validation window again.  Select List from the "Allow" drop down list, and then in the "Source" box, type in = followed by the name of the range you set in the previous step.
3) Select the cell you want to add this validation to and open the Data Validation window again. Select List from the “Allow” drop down list, and then in the “Source” box, type in = followed by the name of the range you set in the previous step.  Then click OK

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_7

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_8

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.

avoid errors in excel_9

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.

avoid errors in excel_10

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.

avoid errors in excel_11

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.

avoid errors in excel_12

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.