How to Find the Maximum Value Only in Filtered Rows in WPS Office Spreadsheets
Question details
The user needs to calculate the highest value from only the rows currently visible after applying a filter, ignoring hidden or filtered-out rows.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- After filtering a dataset, the user wants to find the highest number among only the visible records rather than the entire range.
- Observed behavior
- The standard MAX function includes hidden and filtered rows, which produces an incorrect result for filtered data.
Open the worksheet in WPS Office Spreadsheets and identify the numeric column range you want to evaluate, such as B2:B1000. If you need to distinguish manually hidden rows from filtered rows, note which rows are hidden manually before choosing function number 4 or 104.
Use SUBTOTAL Function 104 to Ignore Filtered and Manually Hidden Rows
Use this solution when you want the maximum of only the currently visible cells and need manually hidden rows excluded as well.
Click the empty cell where you want the maximum visible value to appear, such as C2.
Type =SUBTOTAL(104,B2:B1000) and press Enter. Replace B2:B1000 with your actual numeric data range.
Apply or change a filter and confirm that the result updates to show only the highest visible value.
Use SUBTOTAL Function 4 to Include Manually Hidden Rows
Use this solution when the maximum should reflect all rows except those removed by filtering, but still include rows you hid manually.
Find the Maximum Visible Value in Filtered Rows with WPS Office Spreadsheets
Use WPS Office Spreadsheets to replace the standard MAX function with SUBTOTAL or AGGREGATE so the result reflects only the filtered visible rows. The same formulas work with Excel files and the familiar ribbon interface requires no new workflow.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the filtered worksheet in Spreadsheets.
- 2. Select the result cell: Click an empty cell where the maximum visible value should appear.
- 3. Enter a visible-only formula: Type =SUBTOTAL(104,B2:B1000) and press Enter, replacing B2:B1000 with your numeric range.
- 4. Adjust the filter and confirm: Use Data > Filter to change the filtered view and confirm the result updates to the visible maximum.
Frequently Asked Questions
Why does the MAX function return a value from a row that is hidden by a filter?
The standard MAX function evaluates all cells in the referenced range regardless of filter status. Use =SUBTOTAL(104,range) or AGGREGATE to limit the calculation to visible rows.
What is the difference between SUBTOTAL 4 and SUBTOTAL 104?
Function number 4 excludes rows hidden by a filter but includes manually hidden rows. Function number 104 excludes both filtered rows and manually hidden rows.
Can AGGREGATE return the maximum of visible rows and ignore errors?
Yes. AGGREGATE can perform MAX on visible rows and can also ignore error values. For example, =AGGREGATE(4,5,B2:B1000) returns the maximum ignoring hidden rows; use option 7 to ignore hidden rows and error values.
Will SUBTOTAL update automatically when I change the filter?
Yes. SUBTOTAL recalculates based on the current visible range when you apply or change a filter, so the maximum adjusts immediately.
Can I use this formula in a WPS Office spreadsheet saved as .xlsx?
Yes. WPS Office Spreadsheets supports SUBTOTAL formulas and .xlsx files, so the same formula works when the file is opened in Microsoft Excel.
