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.

**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.

**Table of Contents**hide

**Download Practice Workbook**

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

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.

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.

**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**.

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

- 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**)

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

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

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

**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.

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

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

- Click
**OK**.

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

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

**Read More: How to Find Minimum Value Based on Multiple Criteria 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**.

- 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.

**Read More: How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)**

**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**.

- Click on any cell
**G5.**Insert the Formula

`=MIN(D5:D13)`

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

**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)`

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)`

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

**Read More: How to VLOOKUP Minimum Value in Excel (Easiest 8 Ways)**

**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.

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 functionor theIF 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_Gradeis in cellB2and theExtra_Creditis in cellC2. Then apply any of the following formulas in cellD2to get theTotal_Gradewith a maximum of100.`=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)