Editor choice

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

This is an overview of the 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.


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

Expected Sample of Data

Method 1 – Using the Data Validation Feature

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

Dataset Overview

1.1. Using Value Range


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

Use Data Validation

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

Data Validation pops-up

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

How to Set a Minimum and Maximum Value in Excel

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

Error Alert

How to Set a Minimum and Maximum Value in Excel

  • Click OK. This is the output.

How to Set a Minimum and Maximum Value in Excel

1.2. Using a Data Validation Custom Formula


  • Select the range Size: Data Tab >> Data Tools >>Data Validation
  • In the Data Validation window: in Allow,choose Custom  >> Enter the formula below.

Repeat the procedure in 1.1 for Error Message.

Set a Minimum and Maximum Value in Excel with Custom Formula

  • Select the range Price and apply the following formula.


  • Click OK.

How to Set a Minimum and Maximum Value in Excel

  • This is the output.

Error Message

This is the final output.

How to Set a Minimum and Maximum Value in Excel

Method 2- Applying the RANDBETWEEN Function

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


  • Click C8. Enter the Formula


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


  • Select F5. Enter the Formula.

It extracts the largest value in D5:D13.

MAX function

  • Click G5. Enter the Formula


It extracts the minimum value in D5:D13.

MIN function

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.


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


LARGE Function

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

To find the smallest value, enter the formula.


SMALL Function

It extracts the smallest value in D5:D13.

Download Practice Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

  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.

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

      Thanks for reaching out to us.

      Md. Shamim Reza (ExcelDemy Team)

Leave a reply

Advanced Excel Exercises with Solutions PDF