How to use Decimal option in Data Validation in Excel

Data Validation command is used to prevent invalid data being entered into cell. use decimal option in Data Validation in Excel.

Hi guys, we know very well about the MS Excel. MS Excel is an electronic application software which is used for data analysis, modeling, representation data in charts. Today we have to discuss an important topic how to use decimal option in Data Validation in Excel. It is in an important command of MS Excel. This command is used to prevent invalid data being entered into the cell by applying some restrictions on the single cell or range of the cells in any worksheet. If you desire, you can permit any users to being entered any wrong data. But notify them by giving an alert message during typing the contents in the cell.

If you want to set any input message on selected cell or range of cells then type the “Title” and “Input Message” in the relevant box. You can also set any error message from “Error Alert” tab, where you can set “Style”, “Title” and “Error Message” in the relevant box.

Example  If you are working in a company on which you have to set a maximum limit for commission’s amount up to 6%. The  6% will be calculated on salary of all sales executive’s. The range of incentive is C2:C6. With the help of Data Validation command in the different version of Microsoft Excel, then you have to take following steps given below.

Must Read: What-If Analysis and Excel’s Two Input Data Table

Steps of Data Validation in MS Excel:

Step 1: Prepare the following sheet as per given below:


Step 2: Select the range C2:C6 on which you want to apply restriction to calculate the commission amount. This commission amount will be “Less than Or equal to” (<=) 6% of sales executive salary.

Step 3: Either you have to Press ALT+D+L or you can also Click on the Data Validation command from the Data Tab. Select “Less than Or equal to” (<=) option in the Data box and enter the formula, =B2*6%, in the Maximum box then click OK button. how-to-use-decimal-option-in-data-validation-in-excel

Note: Now, if you are trying to feed formula =B2*7% then excel displays an error message on the screen. Because you already set the criteria “Less than Or equal to” (<=) 6%.

Step 4: Type the following formula in Cell C2: =B2*6%.

Step 5: Copy this formula with data validation rule in given range c3:c5, calculate total salary in cell B7 and total incentive in cell c7, finally your sheet displays the given result.


Must Read: 5 Ways How to use SUM Function in MS Excel

I hope you like this article how to use decimal option in Data Validation in Excel. Share this article with your friends, colleagues and your other staff members, to show your knowledge and skill. Sent your important suggestion in comment box. Thanks.

Leave a Reply

Thanks to leave a comment. Please note that all comments are moderated according to our comment policy and your email address will NOT be published. Please Do NOT use keywords in the name field. Lets have a meaningful conversation.