# MAX and MIN Values from a Filtered List in Excel

Author:

How to get the MAX and MIN values from a filtered data set. This method returns the value from only the visible rows after data has been filtered.

To do this, we use the SUBTOTAL() function.

### Sections:

Syntax

Example - Filtered Data

Example - Exclude Manually Hidden Rows

Notes

## Syntax

### MAX

``````=SUBTOTAL(4, range)
``````

4 tells the function to return the max value.

range is the range from which you get the max.

### MIN

``````=SUBTOTAL(5, range)
``````

5 tells the function to return the min value.

range is the range from which you get the value.

## Example - Filtered Data

``````=SUBTOTAL(4,B5:B9)
``````

This gets the max value from the range in a filtered data set. B5:B9 is the range.

Use 4 or 5 for the first argument to get the MAX or MIN value. Other than that, the syntax is exactly the same. Result: This works if the data is filtered or unfiltered. ### MIN or MAX

Change the first argument to return a min or a max.

4 means to return the MAX.

``````=SUBTOTAL(4,B5:B9)
``````

5 means to return the MIN.

``````=SUBTOTAL(5,B5:B9)
``````

## Example - Exclude Manually Hidden Rows

The above example will not work on data that is manually hidden (right-click a row and click Hide).

To make the SUBTOTAL function also ignore manually hidden cells, we must put 10 in front of the number for each argument.

### Max

``````=SUBTOTAL(104,B5:B9)
``````

### Min

``````=SUBTOTAL(105,B5:B9)
``````

104 replaced the 4 to return the Max.

105 replaced the 5 to return the Min.

Nothing else changed.

## Question? Ask it in our Excel Forum

Excel Function: SUBTOTAL()

Similar Content on TeachExcel
Sum the Visible Rows from a Filtered List in Excel
Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...
Average the Visible Rows in a Filtered List in Excel
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...
SUBTOTAL Function - Work on Filtered Data in Excel
Tutorial: Perform basic functions on a filtered dataset in excel, including SUM, AVERAGE, COUNT, COU...
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
Macro: This Excel macro filters data in Excel in order to display the top 10 items from the data ...
Count the Visible Rows in a Filtered List in Excel
Tutorial: How to use the COUNT function on a filtered list of data so that hidden rows are not incl...
Extract a Word from a Sentence / Cell in Excel with this UDF - Allows for a User-Defined Delimiter
Macro: Extract whole words from a cell or sentence in Excel with this UDF. This allows you to spe...
Tutorial Details
Excel Function: SUBTOTAL()