Editor choice

# How to Set a Minimum and Maximum Value in Excel – 4 Easy Methods

This is an overview of the methods.

Data Validation provides instant restriction from entering data out of the range. MAX, MIN, and IF Functions show the referenced data considering a maximum and minimum value. RANDBETWEEN generates random data within a bottom and top value to make sample datasets.

The dataset below showcases the Sizes and Prices of several shirt samples.

### Method 1 – Using the Data Validation Feature

To fill the Size and Price column use the Data Validation feature.

#### 1.1. Using Value Range

Steps:

• Select the range Size. Go to the Data Tab>Â Data Tools.
• Click Data Validation.

• A window is displayed. Choose Settings.
• In Allow,Â select Whole Number >> In Data, betweenÂ >> 22 in MinimumÂ >> 30 in Maximum

• In Style, select WarningÂ >>InÂ Title, chooseÂ Out_of_Range Â >>Â InÂ Error message, Please enter a value between 22-30.

• Click OK. Enter values. If values are entered out of the range (22-30), the Warning Message will be displyed.

• Click OK. This is the output.

#### 1.2. Using a Data Validation Custom Formula

Steps:

• Select the range Size: Data Tab >>Â DataÂ Tools >>Data Validation
• In the Data Validation window: in Allow,choose Custom Â >> Enter the formula below.
`=AND(C5>=22,C5<=30)`

Repeat the procedure in 1.1 for Error Message.

• Select the range Price and apply the following formula.

`=AND(D5>=100,D5<=200)`

• Click OK.

• This is the output.

This is the final output.

### Method 2- Applying the RANDBETWEEN Function

the RANDBETWEEN function generates random data from top and bottom values.

Steps:Â

• Click C8. Enter the Formula
`=RANDBETWEEN(\$F\$5,\$E\$5)`

Here,

• F5 = Bottom Value
• E5 = Top Value

Make sure you lock the references by pressing F4.

• Press ENTER. Drag the Fill HandleÂ down to Autofill the formula. You will get random values between 22-30.

### Method 3- Using the MAX and MIN Functions

The MAX function returns the largest value and the MIN function returns the smallest value in a set of values ignoring logical values and texts.

Steps:

• Select F5. Enter the Formula.
`=MAX(D5:D13)`

It extracts the largest value in D5:D13.

• Click G5. Enter the Formula

`=MIN(D5:D13)`

It extracts the minimum value in D5:D13.

### Method 4 – Utilizing the SMALL and LARGE Functions

The LARGE function returns the k-th largest value. The SMALL function returns the k-th smallest value. To find the first largest and the first smallest value, use k => 1.

Steps:

To extract the maximum and minimum values, select F5. Enter the Formula.

`=LARGE(D5:D13,1)`

It extracts the largest value in D5:D13 (the second argument k => 1 is used)

To find the smallest value, enter the formula.

`=SMALL(D5:D13,1)`

It extracts the smallest value in D5:D13.

## Related Articles

<< Go Back toÂ Excel MAX Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

1. Hi, I enjoyed your explanations and thank you for that. But what I’m trying to do is a bit different. I want to give my students grades that must not exceed 100. Each student has an exam grade and some extra credits. So the main formula is ExamGrade+(ExtraCredit*20%100) but some do get more than 100. How can I prevent this or correct this? Thank you

• You can simply do that using any of the above methods that use the MIN function or the IF function.

1. MIN Function: The formula will be …

2. IF Function: The formula will be …

Assume the Exam_Grade is in cell B2 and the Extra_Credit is in cell C2. Then apply any of the following formulas in cell D2 to get the Total_Grade with a maximum of 100.
`=MIN(B2+C2*0.2,100)`
`=IF(B2+C2*0.2>100,100,B2+C2*0.2)`

I have also emailed you an Excel document for this. Please check.

Thanks for reaching out to us.

Regards,
Md. Shamim Reza (ExcelDemy Team)

Advanced Excel Exercises with Solutions PDF