How to Return the Maximum Value from Each Excel Row with One Formula
Question details
The user needs a dynamic Excel formula that returns the largest value from every row of a two-dimensional range, spills the results automatically down the column, and resizes dynamically when new rows or columns are added.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting row-wise maximum values from a dynamic two-dimensional dataset without manually dragging formulas down.
- Observed behavior
- The goal is to achieve an automatically spilling, resizable array of maximum values using a single modern array formula.
Ensure you are using a version of Excel (such as Microsoft 365) or WPS Office that fully supports Dynamic Arrays and functions like BYROW and LAMBDA.
Use the BYROW and LAMBDA Functions
This is the most modern and efficient approach. It applies a custom LAMBDA function across every row of a specified array, returning the maximum value for each row and spilling the results automatically.
By including an extra row and column in your range references (e.g., A1:P16 for a 15x15 dataset), the formula can automatically expand when new rows or columns are inserted.
Click on the top cell of the column where you want the maximum values to appear.
Type the formula =BYROW(A1:P16,LAMBDA(a,IF(COUNT(a)=0,"",MAX(a)))). Adjust the range A1:P16 to fit your data.
Press Enter. The formula will automatically calculate and spill the maximum values down the column for each corresponding row.
Combine BYROW with OFFSET for Highly Variable Ranges
If your dataset size frequently changes and you want the range to calculate its own dimensions dynamically, you can nest an OFFSET function inside BYROW.
Use INDEX and SEQUENCE for Older Dynamic Environments
For spreadsheet versions that support dynamic arrays but lack the newer BYROW function, an INDEX and SEQUENCE combination can simulate a row-by-row spill calculation.
Use WPS Spreadsheets for Powerful Array Calculations
WPS Spreadsheets provides comprehensive support for modern array formulas, allowing you to easily perform complex data analysis and dynamic spilling without complicated workarounds.
- 1. Open Your Workbook: Launch WPS Office and open your data file in WPS Spreadsheets.
- 2. Select the Output Area: Click the topmost cell of your target output column where you want the maximum values to start.
- 3. Enter the Formula: Type your dynamic array formula (such as the BYROW function) and press Enter to instantly spill the results.
Frequently Asked Questions
What does 'spill' mean in Excel dynamic arrays?
Spilling occurs when a single formula produces multiple results, and those results automatically fill neighboring blank cells downwards or across, without needing to manually copy and paste the formula.
Why does my BYROW formula return a #SPILL! error?
This happens when the destination cells required for the formula's output are not empty. Clear any existing data or spaces in the column below your formula to allow the array to expand successfully.
Can I use these formulas in older versions of Excel?
Functions like BYROW, LAMBDA, and dynamic SEQUENCE require modern versions of Excel (like Microsoft 365 or Excel 2021) or the latest version of WPS Office. Older versions will not recognize these functions and may require traditional Ctrl+Shift+Enter array formulas.
