Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
MAX and MIN Values from a Filtered List in Excel
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:
Example - Exclude Manually Hidden Rows
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.
Notes
This is a great function to use to return the Min and Max value from a list of data that you are analyzing. In fact, even if you are not performing a filter right now, but you think you might in the future, it can be a good idea to use the SUBTOTAL function instead of the MIN and MAX functions just so that you can later filter the data and still have accurate results.
The SUBTOTAL function allows you to apply a number of regular functions to filtered data, for a full list and explanation, view our tutorial on the SUBTOTAL function in Excel.
Make sure to download the file for this tutorial so you can work with the above examples in Excel.
Question? Ask it in our Excel Forum
Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...
Tutorial: Perform basic functions on a filtered dataset in excel, including SUM, AVERAGE, COUNT, COU...
Macro: This Excel macro filters data in Excel in order to display the top 10 items from the data ...
Tutorial: How to use the COUNT function on a filtered list of data so that hidden rows are not incl...
Macro: Extract whole words from a cell or sentence in Excel with this UDF. This allows you to spe...