In Microsoft Excel, you can easily detect the transaction error by setting up a validation rule to make sure that data recording is accurate and as per your standards.
By following below mentioned steps, you can easily create validation rules:
Step 1: First Select the Ranges of Cells.
Step 2: First Click on Data tab, In the Data Tools group, Click on Data Validation button to open the Data Validation dialog box.
Now Data validation dialog box would get open as mentioned below:
Note: Data validation dialog box will help you to define the type and condition of data that you would like to allow in the cells. Depending on your data type that you have chosen, Microsoft excel only allow the selective range of data in cells range. To set up, please see below Step 3.
Step 3: Now select Whole number in Allow drop down list.
Step 4: Set your desired data limits and condition that you want to allow in cells range. For example, you can set whole number value between 2000 and 5000 and click OK to close the Data Validation dialog box.
Step 5: For Verification, enter Retail price less than 2000 or more than 5000.
Step 6: Now press Enter to get popup window. Excel not accepting your over limit price or under under limit (e.g. 1500) as mentioned below:
You can also clear this validation limit by following below mentioned steps:
Step 1: Again select the Ranges of Cells.
Step 2: Click on Data tab, In the Data Tools group, Click on Data Validation button to open the Data Validation dialog box.
Step 3: Now Press Clear All button.
Step 4: Now Click OK to clear the validation rules for ranges of cells as mentioned below:
In Microsoft Excel, You can easily create rules for those range of cells in which the data (Retail Price) have already available. In Bulk data, this will not be an easy task to identify the wrong data that violates your rules.
But in Microsoft Excel, you can easily find out wrong entries that violates the rule by using Excel Circle command on Data Validation group by following below mentioned steps:
Step 1: Select the Ranges of Cells.
Step 2: Click on Data tab, In the Data Tools group, Click on Data Validation button to open the Data Validation dialog box.
Step 3: Set your desired data limits and condition that you want to allow in cells range. For example, you can set whole number value between 2000 and 5000 and click OK to close the Data Validation dialog box.
Step 4: Now again click on Data tab, In the Data Tools group, Click on Data Validation arrow. On the menu, Click on the Circle Invalid Data to circle the cells with wrong entries.
Now you can see the result as mentioned below:
To hide circles, Again Select Cells range. Click on Data Validation arrow, in the Data Validation list, Click on Clear Validation Circle as mentioned below:
Step 5: For Verification, enter Retail price less than 2000 or more than 5000.
Step 6: Now press Enter to get popup window. Excel not accepting your over limit price or under under limit (e.g. 1500) as mentioned below:
You can also clear this validation limit by following below mentioned steps:
Step 1: Again select the Ranges of Cells.
Step 2: Click on Data tab, In the Data Tools group, Click on Data Validation button to open the Data Validation dialog box.
Step 3: Now Press Clear All button.
Step 4: Now Click OK to clear the validation rules for ranges of cells as mentioned below:
In Microsoft Excel, You can easily create rules for those range of cells in which the data (Retail Price) have already available. In Bulk data, this will not be an easy task to identify the wrong data that violates your rules.
But in Microsoft Excel, you can easily find out wrong entries that violates the rule by using Excel Circle command on Data Validation group by following below mentioned steps:
Step 1: Select the Ranges of Cells.
Step 2: Click on Data tab, In the Data Tools group, Click on Data Validation button to open the Data Validation dialog box.
Step 3: Set your desired data limits and condition that you want to allow in cells range. For example, you can set whole number value between 2000 and 5000 and click OK to close the Data Validation dialog box.
Step 4: Now again click on Data tab, In the Data Tools group, Click on Data Validation arrow. On the menu, Click on the Circle Invalid Data to circle the cells with wrong entries.
Now you can see the result as mentioned below:
To hide circles, Again Select Cells range. Click on Data Validation arrow, in the Data Validation list, Click on Clear Validation Circle as mentioned below:
---Thank You---
4 Comments
Good Job Sir
ReplyDeleteThank you
DeleteWhat are the most important data formats seen in Excel? Can you please tell me sir
ReplyDeleteYour Answer mentioned below:
Delete1)Numbers:
Numbers are one of the most frequently-seen data types in Excel. They can be formatted with a customized number of decimal places, and appear with or without commas separating the thousands digits. Numbers can be added, subtracted, divided, multiplied, or included in formulas and functions that accept numerical inputs.
2)Dates:
Excel can display dates in any number of ways, including the classic US-style MM/DD/YYYY format. Dates can be added or subtracted using standard addition and subtraction, and can also be manipulated using a slew of date-based functions. Interestingly, dates in Excel are technically also stores as numbers, with each date represented as the number of days elapsed since January 1, 1900. For example, the date May 6, 2019 is stored in Excel as the number 42,129, because there are 42,129 days between January 1, 1900, and May 6, 2019.
3)Percentages:
Numbers can also be formatted as percentages, which multiplies the given number by 100 and adds a percentage sign at the end. For example, the number 0.08 is equivalent to the percentage 8%.
4)Strings:
Text is stored in Excel in a format called a string. Strings of text can contain standard characters such as letters, numbers, and punctuation; strings can be manipulated via text-manipulation functions like MID and RIGHT.