Function Problems

Reverse a Range in Excel While Ignoring Blank Cells

Jason MillerJason Miller Nov 20, 2026 868 views

Question details

Reverse the nonblank values in a horizontal Excel range and place the results in another range.

Reverse a Range in Excel While Ignoring Blank Cells
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.
Before you start

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.

Solution 1Recommended

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.

1
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))),"").

2
Confirm the formula

Press Enter in a newer Excel version. In older Excel versions, confirm the array formula with Ctrl+Shift+Enter instead.

3
Fill the destination

Copy AQ2 across through AZ2, then copy the filled cells down for the remaining source rows.

Use an array formula to reverse nonblank values
How blanks are handled: The formula skips cells that are empty or contain a formula returning an empty string. After all nonblank values are returned, the remaining result cells display an empty string.
WPS Spreadsheet

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. 1. Open your workbook: Open the Excel workbook in WPS Spreadsheet and locate the source range AG2:AP2.
  2. 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. 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. 4. Save the workbook: Use File > Save As to save the completed workbook in Excel format.
Reverse nonblank values across each row with a reusable formulaOpen and edit Microsoft Excel XLS and XLSX filesWork with a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.