5 Essential Factors about Data Validation and Error Alert in Excel

The feature of data validation in Excel has also provided us with the function of error alert. This article will talk about the error alert in Excel cells.

If you use Excel frequently, you will not be unfamiliar with the feature “Data Validation”. Then how about setting error alert for cells? There are 3 types of error alert for data validation. So the sentences below will discuss the error alert in detail.

An Example of Inputting Invalid Contents

In the image below, the cells in B2:B15 have all set the data validation. And only whole numbers are acceptable in those cells. Suppose now you manually enter a decimal number into cell B2 inadvertently.Input Invalid Contents

When you press the button “Enter”, you will see the window of error alert. And the different styles will show in the following part. In addition, make sure that you manually input the contents into cells.

Factor1: Stop Style

The default style of error alert is “Stop”. If in this example, you set the style as Stop, you will see this window like the image show below:Stop Style

You will get different result if you click the different button in this error alert window.

  1. If you click “Retry” in this window, the error alert will disappear and cell B2 will still be in the edit mode.Edit Mode
  2. If you click “Cancel”, cell B2 will be a blank cell.
  3. Click “Help”, the window of Excel help will pop up. And here you can find more information about data validation.
  4. Last, if you click the “Close” button, cell B2 will be a blank cell. This result is the same as clicking “Cancel”.

Factor2: Warning Style

The image below shows the warning style if the content is invalid.Warning Style

  1. If you click “Yes”, the invalid number will remain in B2. Besides, the cursor will be positioned at cell B3.The Result of Click Yes
  2. If you click “No”, cell B2 will still be in the edit mode.
  3. If you click “Cancel”, cell B2 will be blank.

And click “Help” and “Close” will get the same result as the “Stop” style.

Here if you click “Yes”, and then you find the mistake immediately. Instead of deleting the content, you double click the cell and edit it. However, if you still input a wrong number, such as 123.5, and press “Enter”, you will see the alert window again. Here if you click “Cancel” or “Close”, the B2 cell will keep the wrong number 123.4 in it.

Factor3: Information Style

The last style is “Information”.Information Style

  1. If you click “OK”, the wrong number will remain in cell B2. And the cursor will position at cell B3.
  2. Click “Cancel”, B2 will be blank.

And in this style, click “Help” and “Close” produce the same result.

In this style, there also exists a different condition, which is the same as the condition in the “Warning” style. If you change the number into 123.5, and then press “Enter, you will see the error alert window again. Click “Cancel” or “Close”, the number “123.4” will remain in B2.

The above three conditions are all happen when you manually input the invalid content. Sometimes, you will copy some values from other sources and paste it into cells. And you will find that there is no error alert. You should also pay attention to this condition.

Error Alert doesn’t Appear

  1. If you copy a number from a webpage and paste it into the cell directly, you will not see error alert. Even though the number is invalid, it will remain in cell.No Error Alert
  2. In addition, if you copy the number from another cell in Excel and paste into B2, you cannot see the error alert, either.Copy from Another Cell

If you paste the cell with format, then data validation in cell B2 will also disappear. On the other hand, if you only paste the value into the cell, the data validation will remain. But whatever the types of paste it is, the error alert will not show up. Thus, you need to be much careful about the copy and paste process.

A Method to Solve Problems of Error Alert

In the above part, we have figured out the different styles of error alert. And you know that in some circumstance, the invalid value can remain in cells. Therefore, to handle this problem, you can use an additional feature to check for the errors.

  1. Click the tab “Data” in the ribbon.
  2. And then click the small arrow next to the button “Data Validation”.
  3. In the drop-down menu, click the option “Circle Invalid Data”.Circle Invalid Data
  4. Next you will see a new window. If you want to get more accurate result, you can click “Yes” in the window.Click Yes to Get Accurate Result

Thus, all the cells with invalid contents will be circled in a red oval.Red Oval

But if you copy the format into B2, the cell is actual an ordinary cell. Therefore, even if the content is invalid, it will not be highlighted with a red oval. Hence, you may pay more attention when paste values from other cells.

Excel may Produce Some Unexpected Errors

If you find that Excel will produce some unexpected errors, you are most probably meet with an Excel corruption. And sooner or later, Excel will go on strike. In order to save your data and information in Excel, you must try to recover Excel as soon as possible. With our cutting edge software, you can get back your precious information soon.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.