Reversing an array in Google Sheets, whether it’s a 2d, 3d, or NxN array, can be quite tricky. But fear not, my friends! I have a secret formula that will allow you to reverse any array with ease. Say goodbye to complicated workarounds and hello to a simple solution using Google Sheets’ TRANSPOSE, SORT, and a virtual column. Are you ready to learn how to reverse an array in Google Sheets? Let’s dive in!
Imagine you have a 12-column dataset representing the 12 months of the year from January to December. Now, let’s say you want to reverse the order of these columns. In other words, you want the month column to be in the order of December, November, October… January. Check out the screenshot below to better understand what I mean.
To achieve this reverse order, you can use the following formula in cell A8:
=transpose(sort(transpose(A1:L6),row(A1:A12),0))As you can see, this formula is not very flexible. It only works for the selected range. But what if you want to reverse columns in a growing array? Don’t worry, my friends! I have a solution for that too.
Let me explain the logic behind the formula we just used. First, forget about the outer TRANSPOSE formula and focus on the SORT formula. When we transpose the array A1:L6, it will look something like this:
[Visual representation of the transposed array]
Now, when we sort this range using the sequential numbers 1 to 12 in descending order (which we generate using the ROW formula), the rows get reversed due to the descending sort. Finally, the outer TRANSPOSE formula changes the data orientation back to its original state, but by then, the columns are reversed. It’s like magic!
But how can we make this formula dynamic? Let’s find out!
To make the formula dynamic, we need to introduce a virtual column with sequential numbers and account for an infinite data range. To avoid circular dependency issues, we’ll enter the formula in a new tab.
Before using the dynamic formula (the last formula in this post) to reverse an array in Google Sheets, make sure to delete any unwanted columns and rows in the sheet that contains the source data. This will improve the performance of your file.
Now, let’s talk about the changes we need to make to the formula for it to become dynamic.
- Dynamic Virtual Column with Sequential Numbers
In the previous example, we had 12 columns to reverse, so we needed a virtual column that contained the sequential numbers 1 to 12. We achieved this by using the ROW formula as follows:
row(A1:A12)In the dynamic formula, however, this should be replaced by the following formula:
row(INDIRECT("A1:A"&counta(Sheet1!A1:1)))What’s the difference, you ask? Well, in the previous formula, we customized it for 12 columns specifically. It returned sequential numbers 1 to 12. But in the dynamic formula, we count the values in the column and use that count to return the sequential numbers. So if you add one more column, the formula will update accordingly and return the numbers 1 to 13. It’s a clever way of keeping our formula dynamic!
- Infinite Range in SORT and Associated Issues
To make sure our formula considers any future rows or columns that may be added to the source, we need to modify the sort range. Instead of just transposing the fixed range A1:L6, we’ll use the following formula:
transpose(indirect("Sheet1!A1:"&left(address(1,counta(Sheet1!A1:1),2))))In this formula, the ADDRESS function helps us find the column letter of the last column in the array, making it dynamic. So if you add more columns in the future, the formula will automatically adapt. How cool is that?
Finally, here’s the dynamic formula 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))Feel free to use either of the two formulas I’ve shared to reverse an array in Google Sheets.
And there you have it! Now you’re equipped with the knowledge to reverse any array in Google Sheets, whether it’s fixed or dynamic. So go ahead and impress your colleagues with your newfound skills!
If you’re interested in learning more tips and tricks for Google Sheets and other powerful tools, make sure to check out Crawlan.com. It’s your go-to source for all things data and productivity.
Happy reversing, my friends!
