Reversing an array, no matter whether it’s 2d, 3d, NxN, is tricky in Google Sheets. What you want to do is to use TRANSPOSE, SORT, and a virtual column together. Learn how to reverse an array in Google Sheets.

I know I should first relate it to a real-life example. Then only you can digest what is the so-called reversing an array in Google Spreadsheet means.

Suppose I have 12 column data set for the 12 months starting from January to December. I want to reverse the column order. I mean, I want the month column in the order December, November, October…January.

I know, the below screenshot can better convey my idea.

Reverse Multiple Columns/Array in Google Sheets

In the above example, I have the below formula in Cell A8. That means this formula reverses an array.

=transpose(sort(transpose(A1:L6),row(A1:A12),0))

As you can see, this is not a flexible formula. It works for the selected range only, right? How to use this in a growing data range then?

It’s possible to reverse columns in a growing array and I will come to that. Before that, here is the above formula explanation that can convey the logic.

Reversing Columns Formula Logic/Explanation

Leave the outer Transpose formula. I am talking about the SORT formula first. The transposed array A1:L6 would look like as below.

This range sorted by the sequential numbers 1 to 12 in descending order. The Row formula populates the sequential numbers (called sort column).

SORT(range, sort_column, is_ascending)

That means, the SORT formula, in that the sort column is the row formula, reverses the rows due to the descending sort.

The outer transpose formula then changes the data orientation back to its original state. But by then, the columns are reversed.

How to make the above formula dynamic?

Dynamic Formula to Reverse an Array in Google Sheets

Dynamic virtual column with sequential numbers and infinite data range can make the above formula dynamic. In such case, the formula must be entered in a new tab to avoid circular dependency related issues.

If you are going to use the below dynamic formula (the last formula in this post) to reverse an array in Google Sheets, do one thing. Delete the unwanted columns and rows on the sheet that contain the source data. This can enhance the performance of your file.

Instead of giving you the formula first, see the changes (in comparison with the above non-dynamic formula).

Dynamic Virtual Column with Sequential Numbers

This is Changes # 1:

The above data has 12 columns to reverse. What we want is a virtual column, that contains the sequential numbers 1 to 12.

To populate a virtual column with sequential numbers, we can depend on the ROW or COLUMN function. I have used the ROW function since the COLUMN function returns a virtual row, not a column.

In the above non-dynamic formula (formula with fixed data range) I have used the ROW formula as below.

row(A1:A12)

In the dynamic formula this should be replaced by the below formula.

row(INDIRECT("A1:A"&counta(Sheet1!A1:1)))

What’s the difference?

At present my source data has only 12 columns. The first formula is customized for 12 columns. I mean for returning sequential numbers 1 to 12.

The second formula is dynamic. It counts the values in the column and uses that count to return sequential numbers.

If you add 1 more column, the formula would return the numbers 1 to 13. That’s all about that dynamic virtual column.

Infinite Range in SORT and Associated Issues

This is Changes # 2:

We want the formula to consider the rows and columns that may get added to the source in the future. So instead of the below formula as the sort range;

transpose(A1:L6)

I would prefer;

transpose(indirect("Sheet1!A1:"&left(address(1,counta(Sheet1!A1:1),2))))

The Address function finds the column letter of the last column in the array and thus helps to transpose A1:L. Here the last column letter “L” is dynamic.

Final Formula (Dynamic) to Reverse Columns in Google Sheets

=transpose(sort(transpose(indirect("Sheet1!A1:"&left(address(1,counta(Sheet1!A1:1),2)))),row(INDIRECT("A1:A"&counta(Sheet1!A1:1))),0))

Use any of the two formulas to reverse an array in Google Sheets.

Similar:

1. Formula to Reverse Text and Numbers in Google Sheets.
2. How to Flip a Column in Google Sheets – Finite and Infinite Columns.