I have a spreadsheet containing the names of cricket players who have scored more than 9999 runs in Test Cricket. I’m using this sheet as my example sheet to explain to you how to use MAX function in Google Sheets.

In addition to the above, you can learn how to use MAX function in a Filtered data in Google Sheets and also how to exclude hidden rows in MAX.

I’ve just imported the above-said sample data from a Wikipedia page with an IMPORTHTML formula. Below is that awesome Google Doc Spreadsheet Formula.

IMPORTHTML("https://en.wikipedia.org/wiki/List_of_players_who_have_scored_10,000_or_more_runs_in_Test_cricket","table",1)

This imported data has so many columns. I have limited the number of columns by additionally using a Query as below. So you can use the below formula for this test.

=query(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_players_who_have_scored_10,000_or_more_runs_in_Test_cricket","table",1),"Select Col2, Col3, Col8")

The below is that sample data which we were talking about.

Max Function Syntax

MAX(value1, [value2, …])

How to Use MAX Function in Google Sheets

In the above example, you can use a Max formula in Column C as below to return the highest run scorer in the list.

=Max(C2:C)

It’s that much easy. You can use the arguments in MAX function as comma separated as below which would return the result 12 as it’s the highest value in the example below.

=MAX(10,5,12,4,1,5)

Finally, let us see the use of MAX in filtered data range in Google Sheets.

How to Use MAX Formula in Filtered Data in Google Sheets

When you want to find the maximum value in a filtered data you can use the Max Function number 104 in Subtotal as below.

=subtotal(104,C2:C14)

This’s applicable to hidden rows also.

I suggest all should learn Google Sheets Subtotal function with function numbers. I’ve already detailed how to tame hidden rows or filtered data with the SUBTOTAL function. See the link to that tutorial.

Must Read: What is Function Numbers in Google Sheets

That’s all for now. Enjoy.