MIN and MAX Functions in MS Excel: Calculate the smallest or largest number in a range

MIN and MAX Functions in MS Excel: Calculate the smallest or largest number in a range

5 mins read6.1K Views Comment
Rashmi
Rashmi Karan
Manager - Content
Updated on Feb 6, 2025 16:27 IST

It is very common for MS  Excel users to find information in different cells of the spreadsheet and within that information, we want to obtain the minimum or maximum value quickly. The MIN and MAX functions are very interesting functions that allow us to obtain or detect the maximum and minimum value of information from a given data set. In this blog, we will cover how to use MIN and MAX functions in MS Excel.

Min and Max Functions in Excel

Content

MIN Function

Excel’s MIN function belongs to the category of statistical functions, and the official definition is that it returns the minimum value from a list of values. Min function ignores empty cells, logical values ​​, and text. 

150+ Keyboard Shortcuts In MS Excel
How to Use Excel Filter Shortcut?

MIN Syntax

=MIN(number1, [number2], …)

How to use Excel’s MIN function?

We take the below spreadsheet as an example. This is a list of some of the sub-verticals of some startups and their valuation. Here we have to find the lowest amount.

How to Create a MIN Formula?

  • Click on the cell where you want to add the formula
  • Click the AutoSum button list arrow
  • Select MIN
  • You will get the minimum value from the specified range when you click on MIN.
  1. The MIN formula =MIN(B2:B18) – suggesting the range of cell numbers B2 to B18
  2. The minimum value in the data set
  3. The resultant value from the specified range after you apply the formula
What Are Excel Errors and How To Fix Them?
Introduction to MS Excel – A Beginner’s Guide
Recommended online courses

Best-suited MS Excel courses for you

Learn MS Excel with these high-rated online courses

12 K
2 months
– / –
3 months
5.5 K
18 hours
60.55 K
10 months
3 K
10 hours
3.2 K
28 hours
1 L
6 months
– / –
45 hours
1.34 L
300 hours

MAX Function

The Excel MAX Formula finds the maximum value from a given data/array/database set. MAX function returns the highest value from that set. It counts numbers and ignores empty cells, text, logical, and text values. 

MAX Syntax

=MAX(number1, [number2], …)

How to Create a MAX Formula?

  • Click on the cell where you want to add the formula
  • Click the AutoSum button list arrow
  • Select MAX

Again we refer to the same set of data. We select the data, go to Autosum, and hover to the max function.

  • After you click on MAX, you will get the maximum value from the specified range.
  1. The MIN formula =MAX(B2:B18) – suggesting the range of cell numbers B2 to B18
  2. The minimum value in the data set
  3. The resultant value from the specified range after you apply the formula

What MIN and MAX Ignore

These functions only work with numbers and ignore the following:

  1. Empty Cells – A blank cell is skipped in the calculation.
  2. Text Values – Any cell containing text (even if it looks like a number) is ignored by the MIN and MAX functions in Excel.
  3. Logical Values (TRUE/FALSE) – These values are excluded unless entered directly in the formula.
  4. Errors – If a cell contains an error like
#VALUE!
Copy code
or
#DIV/0!
Copy code
, it is ignored, but the function may return an error if the entire range consists of errors.

How to Optimize the Performance of MIN and MAX Functions in Excel

To optimize the performance of your spreadsheets when using MIN and MAX with large datasets, consider these pointers:

  • Use MINIFS and MAXIFS: When looking for minimum and maximum values with specific conditions, use the MINIFS and MAXIFS functions. These are faster than array formulas.
  • Use INDEX and MATCH instead of VLOOKUP: For searching specific values, INDEX and MATCH are more efficient than VLOOKUP because VLOOKUP examines the entire lookup range, which is resource-intensive. INDEX and MATCH only look at the necessary columns.
  • Minimize the Used Range: Excel stores information about the used area on a worksheet. If the used range extends significantly beyond what is actually used, it can cause performance issues. Delete all the rows and columns below and to the right of your real last-used cell, then save the workbook.
  • Minimize Links Between Worksheets: It is generally slower to calculate references to other worksheets than references within worksheets. Try using fewer or, if possible, minimal links between worksheets.
  • Pick Excel Formulas Smartly: Formula efficiency plays a major role when dealing with large datasets.
  • Use Advanced Data Management Tools: You can opt for advanced data management tools like Power Pivot to work with massive datasets efficiently. You can perform calculations, aggregations, and lookups faster than traditional Excel formulas.
  • Sort Data: If possible, sort the data first and use an approximate match.
  • Remove Duplicates: Having a lot of duplicate data can slow down your spreadsheets. Clean the duplicate values for effective results with MIN and MAX functions.
  • Minimize Volatile Functions: Volatile functions recalculate with every change and can slow down Excel if overused.

I hope you learn how to use the MAX and MIN functions in MS Excel. Next, we will cover other essential MS Excel functions like SUM, AVERAGE, MEDIAN, COUNT, etc. 


Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel

FAQs

How to use MAX and MIN functions in Excel

Click in the cell where you want to add the formula. Click the arrow on the Sum button. Select MAX. If necessary click on the desired range of cells. Click the Enter button or press Enter

What is the function of MIN in Excel?

MIN function in Excel returns the minimum value of a set of values.

What is the Max function in Excel?

Max function in Excel returns the maximum value of a set of values.

Why do we use Min and Max in Excel?

The MAX function allows you to find the highest number in a given range. The MIN function does the opposite, providing you with the lowest number in a defined range.

How do you remove the max and min function in Excel?

To remove any formula, be it max or min functions in Excel - Select the cell or range of cells that contain the max or min formula. Press Delete.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio