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

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


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.
- The MIN formula =MIN(B2:B18) – suggesting the range of cell numbers B2 to B18
- The minimum value in the data set
- The resultant value from the specified range after you apply the formula


Best-suited MS Excel courses for you
Learn MS Excel with these high-rated online courses
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.
- The MIN formula =MAX(B2:B18) – suggesting the range of cell numbers B2 to B18
- The minimum value in the data set
- 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:
- Empty Cells – A blank cell is skipped in the calculation.
- Text Values – Any cell containing text (even if it looks like a number) is ignored by the MIN and MAX functions in Excel.
- Logical Values (TRUE/FALSE) – These values are excluded unless entered directly in the formula.
- Errors – If a cell contains an error like
#VALUE!
#DIV/0!
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.

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