Reverse a Range in Excel While Ignoring Blank Cells
Question details
Reverse the nonblank values in a horizontal Excel range and place the results in another range.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Source values are in AG:AP, and the user wants each row's nonblank values reversed into AQ:AZ.
- Observed behavior
- The desired output lists nonblank source values in reverse order, with any remaining destination cells left visually blank.
Check that your source data starts in AG2:AP2 and that AQ:AZ is available for the results. Adjust the row and range references if your data starts elsewhere.
Use an array formula to reverse nonblank values
Use this formula to reverse each row's nonblank values and fill the destination across and down.
Select AQ2 and enter =IFERROR(INDEX($AG2:$AP2,LARGE(IF($AG2:$AP2<>"",{1,2,3,4,5,6,7,8,9,10}),COLUMN(A1))),"").
Press Enter in a newer Excel version. In older Excel versions, confirm the array formula with Ctrl+Shift+Enter instead.
Copy AQ2 across through AZ2, then copy the filled cells down for the remaining source rows.

Reverse nonblank values in WPS Spreadsheet
Use the same array formula in WPS Spreadsheet to reverse values from a horizontal range while skipping blank cells.
- 1. Open your workbook: Open the Excel workbook in WPS Spreadsheet and locate the source range AG2:AP2.
- 2. Enter the formula: Select AQ2 and enter =IFERROR(INDEX($AG2:$AP2,LARGE(IF($AG2:$AP2<>"",{1,2,3,4,5,6,7,8,9,10}),COLUMN(A1))),"").
- 3. Confirm and fill: Press Enter, or use Ctrl+Shift+Enter if your WPS version requires array-formula confirmation. Copy AQ2 across to AZ2 and then down for the other rows.
- 4. Save the workbook: Use File > Save As to save the completed workbook in Excel format.

Frequently Asked Questions
Why are some result cells blank?
The formula returns an empty string once it has listed every nonblank value in AG2:AP2. The remaining cells in AQ2:AZ2 therefore appear blank.
Does the formula skip cells containing formulas that return an empty string?
Yes. The condition AG2:AP2<>"" excludes both empty cells and cells whose formulas return an empty string.
How do I use the formula for a different source range?
Replace both references to $AG2:$AP2 with your source range. If the range is not ten columns wide, also change the array constant {1,2,3,4,5,6,7,8,9,10} to match the number of source columns.
Do I need Ctrl+Shift+Enter to enter the formula?
Older Excel versions require Ctrl+Shift+Enter to confirm this array formula. Newer versions can generally evaluate it when you press Enter.




