'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Reference the Last Nonblank Cell in an Excel Formula Range
Question details
The user needs to create a dynamic formula range that extends to the last non-blank cell in a column, even if the starting cell or intermediate cells are blank.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Building dynamic array formulas, such as WRAPROWS, where the data range must automatically adjust to include all data up to the very last populated cell.
- Observed behavior
- When using simpler referencing methods on data that starts below row 1 or contains gaps, the formula drops the final cells of the dataset.
Ensure your spreadsheet software is updated to a version that supports dynamic array functions if you plan to reshape the referenced data using functions like WRAPROWS.
Use LOOKUP and INDEX to Find the Last Nonblank Cell
This method is the most robust solution. It correctly identifies the last populated cell even if the column contains scattered blank cells or the data starts below row 1.
The LOOKUP function can be used to scan an entire column and locate the row number of the last non-empty cell. By combining this row number with the INDEX function, you can construct a dynamic range that captures your complete dataset perfectly.
Click on the cell where you want to output your dynamic array or calculated result.
Type the formula integrating LOOKUP and INDEX. For example, to wrap data in column M into 6 columns, use: =WRAPROWS(M1:INDEX(M:M,LOOKUP(2,1/(M:M<>""),ROW(M:M))),6,"")
Press the Enter key. The range will dynamically adjust from cell M1 down to the absolute last non-blank row in column M.
Use COUNTA and INDEX for Contiguous Data
If your data is strictly contiguous with no gaps and begins exactly in row 1, you can use a simpler COUNTA formula to determine the end of the range.
Easily Manage Dynamic Ranges with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including LOOKUP, INDEX, and modern dynamic array handling. You can seamlessly manipulate, reference, and reshape vast amounts of data using familiar syntax.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your column data.
- 2. Input the dynamic formula: Select an empty cell and type your INDEX and LOOKUP formula to define the dynamic range.
- 3. Calculate and evaluate: Press Enter to instantly process the data. The range will auto-adjust based on your latest inputs.
- 4. Debug if necessary: Go to the Formulas tab and use the Evaluate Formula tool if you need to trace how the dynamic range is calculating.
Frequently Asked Questions
Why does the COUNTA method fail if there are blank cells in the column?
The COUNTA function strictly counts non-blank cells. If you have 10 rows of data but 2 are blank, COUNTA returns 8. The INDEX function will then point to row 8 instead of row 10, chopping off the bottom of your dataset.
Can I use the OFFSET function to find the last non-blank cell?
Yes, OFFSET combined with COUNTA (e.g., =OFFSET(M1,0,0,COUNTA(M:M),1)) can create dynamic ranges. However, OFFSET is a volatile function that recalculates every time any change is made on the sheet, which can slow down large files. The INDEX and LOOKUP method is non-volatile and much more efficient.
Why is my WRAPROWS function returning a #NAME? error?
WRAPROWS is a newer dynamic array function. If you receive a #NAME? error, it means your current spreadsheet version does not support this function. Upgrading to the latest version of your spreadsheet software will resolve this compatibility issue.
