Editor choice

How to Set a Minimum and Maximum Value in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In a dataset sometimes we need to input entries between ranges. In that case, entries between the minimum and maximum values represent a whole dataset. This article will demonstrate how to set a minimum and maximum value in Excel.

Before starting the article. Let’s have a look at the methods we are using and the outputs from the corresponding methods.

How to Set a Minimum and Maximum Value in Excel

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. So, let’s start.


How to Set a Minimum and Maximum Value in Excel: 4 Easy Ways

Let’s say, we have a data set depicting the Sizes and Prices of various samples of shirts. The dataset looks mostly like the picture below.

Expected Sample of Data

We can manage the data set in a certain way so that we can not put any entries out of the range. In this article, we use Data Validation feature, MAX, MIN, SMALL, LARGE, and RANDBETWEEN functions to set maximum and minimum values.


Method 1: Using Data Validation Feature

Let’s say, we need to fill the Size and Price column of our dataset.

Check how you can fill this range with the Data Validation feature.

Dataset Overview


1.1. Using Value Range

Data Validation feature offers to insert a certain value between a specific range (Minimum & Maximum) in a cell or a range of cells. We can make a custom Warning window pop up in case the user does not comply.

Steps:

  • Select the range Size. Go to Data Tab> Data Validation drop-down menu (In Data Tools Section).
  • Click Data Validation.

Use Data Validation

  • A window pops up. In the Settings section, select Whole Number (In Validation Criteria Allow ) >> between (In Data) >> 22 (Minimum) >> 30(Maximum)

Data Validation pops-up

  • In Error Alert section, select Warning (in Style drop-down menu) >> Out_of_Range (In Title) >> Please Enter a value between 22-30 (In Error message)

How to Set a Minimum and Maximum Value in Excel

  • Click OK. Begin entering values. Check out if we input values out of range (22-30), the Warning Message we set pops up.

Error Alert

  • Repeat these steps for range Price & Set the values between 100-200.

How to Set a Minimum and Maximum Value in Excel

  • Click OK. Inputting the values we will come up with an image similar to the image below.

How to Set a Minimum and Maximum Value in Excel


1.2. Using Data Validation Custom Formula

Data Validation Feature offers Custom formulas to maintain a certain range of values. ADD combines two arguments and results as one. In Validation Criteria Custom, we use ADD to insert two arguments; the first one is minimum the other one is maximum.

Steps:

  • Select the range Size. Follow the sequence, Data Tab >> Data Validation drop-down menu (In the Data Tools Section), and open the Data Validation dialog box.
  • Select Data Validation. A window pops up. Below the Settings Section, Select Custom (In Allow drop-down menu) >> Type the formula below in the Formula box:
=AND(C5>=22,C5<=30)

Now, repeat the Error Message setting from the previous method.

Set a Minimum and Maximum Value in Excel with Custom Formula

  • Again select Range Price. Repeat the same step and apply the following formula

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

  • Click OK.

How to Set a Minimum and Maximum Value in Excel

  • You can check the workability of the Formula by inputting any values out of the range, and the warning window pops up.

Error Message

And after entering all the entries, the dataset looks like the below picture.

How to Set a Minimum and Maximum Value in Excel


Method 2: Applying RANDBETWEEN Function

For generating values randomly between a maximum and minimum value, we can use the RANDBETWEEN function. RANDBETWEEN generates random data from top and bottom values. For this case, let’s say we want random sizes of our shirts between a top and bottom value(22-30).

Steps: 

  • Click on any cell C8. Type the Formula
=RANDBETWEEN($F$5,$E$5)

Here,

  • F5 = Bottom Value
  • E5 = Top Value

Make sure you lock the references by pressing F4.

RANDBETWEEN Function

  • Press ENTER. Drag the Fill Handle as down to Autofill the formula. You will get random values between 22-30 like in the picture below.

This value will change every time we trigger an operation. Though it provides the luxury to create a dummy dataset quickly.


Method 3: Using 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.

To extract the maximum and minimum value from our dataset, let’s follow the steps below

Steps:

  • Select any cell F5. Type the Formula
=MAX(D5:D13)

This formula will extract the largest value in the range D5:D13.

MAX function

  • Click on any cell G5. Insert the Formula

=MIN(D5:D13)

This formula will extract the minimum value in the range D5:D13.

MIN function


Method 4: Utilizing SMALL and LARGE Functions

The LARGE function returns the k-th largest value (i.e. 5th largest) and the SMALL function returns the k-th smallest value in a dataset. So, if we want to find the first largest and the first smallest value, we have to use the value of k => 1.

Steps:

To extract the maximum and minimum values from our dataset, let’s follow the steps below: Select any cell F5. Type the Formula

=LARGE(D5:D13,1)

LARGE Function

This formula will extract the largest value in the range D5:D13 as we have used the second argument k => 1

For finding the smallest value, insert the Formula

=SMALL(D5:D13,1)

SMALL Function

This formula will extract the smallest value in the range D5:D13.


Download Practice Workbook


Conclusion

So, these are the methods you can follow to set a minimum and maximum value in Excel. In many cases, maintaining entries between a maximum and minimum value is necessary. As shown in the above article we can achieve these criteria using functions like MAX, MIN, and IF also features like Data validation. 

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. Hope you find the sequence of the above steps lucid to understand and super easy to follow. Comment if this article helps you and saves your time.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
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

2 Comments
  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 …
      Total Grade = MIN(Exam_Grade + Extra_Credit*0.2,100)

      2. IF Function: The formula will be …
      Total Grade = IF(Exam_Grade + Extra_Credit*0.2>100,100,Exam_Grade + Extra_Credit*0.2)

      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)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo