How to Find the Last Nonempty Column Number in a WPS Office Excel Row
Question details
The user needs an Excel formula to return the worksheet column number or relative position of the last cell that contains data in a row.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Analyzing worksheet rows to locate the last populated cell for reporting, dynamic ranges, or data validation.
- Observed behavior
- The expected result is a column number or relative position for the last cell containing data in the target row.
Before writing the formula, confirm whether you need the worksheet column number or the relative position within a selected range, and check whether your version of WPS Office Excel supports dynamic array functions such as XMATCH.
Use LOOKUP to Return the Last Nonempty Worksheet Column Number
Best when you need the absolute worksheet column number of the last populated cell in a specific row, such as row 1.
This formula combines COLUMN(1:1) with IF to keep column numbers only where cells are not empty, then LOOKUP with a very large number returns the last numeric value in the row.
Select the cell where you want the column number result to appear, for example B1 or a separate summary cell.
Type =LOOKUP(1E+307,COLUMN(1:1)*IF(1:1="","",1)) and replace 1:1 with the target row if needed.
If your version of WPS Office Excel uses legacy array behavior, press Ctrl+Shift+Enter instead of Enter to confirm it as an array formula.
Use XMATCH or BYROW to Return the Relative Position
Use this when you only need the position inside a defined array or table, or when your version supports dynamic array functions.
Find the Last Non-Empty Column in a Row with WPS Office Excel
Use the LOOKUP formula in WPS Office Excel to return the worksheet column number of the last populated cell in a row. If you need a relative position in a range, use XMATCH or BYROW in supported versions.
- 1. Open your workbook in WPS Office: Launch WPS Office and open the Excel workbook that contains the row you want to analyze.
- 2. Select the formula output cell: Click the cell where the last nonempty column number should appear.
- 3. Enter the LOOKUP formula: Type =LOOKUP(1E+307,COLUMN(1:1)*IF(1:1="","",1)) and adjust the row reference to your target row.
- 4. Press Enter or Ctrl+Shift+Enter: Press Enter to confirm. If your version uses legacy array behavior, press Ctrl+Shift+Enter instead.
- 5. Verify the column number: Check that the result matches the last column containing data in the selected row.
Frequently Asked Questions
What does the LOOKUP formula return if the entire row is empty?
The formula may return an error or an unintended result because there is no non-empty cell to match. Wrap the formula with IFERROR or first check that =COUNTA(1:1)>0 before using it.
How do I change the formula from row 1 to another row?
Replace both 1:1 references in the formula with the desired row reference, for example 5:5 for row 5.
What is the difference between a worksheet column number and a relative position?
The worksheet column number is the fixed column reference, such as 7 for column G. A relative position is counted from the start of the selected array and may differ if the array does not begin in column A.
Do I need Ctrl+Shift+Enter in WPS Office Excel?
In older versions that use legacy array formulas, yes. In newer versions that support dynamic arrays, pressing Enter is usually sufficient.
Can this formula ignore text and return the last numeric cell only?
The LOOKUP version considers any non-empty cell. To find the last numeric value only, use a formula built around ISNUMBER and XMATCH or MATCH.
