Reverse data in a Google Spreadsheet array

Mon, Oct 20, 2014 - 4:50am -- Isaac Sukin

I have a spreadsheet of metrics I track every week. I add a new column to the left every week for that week's data, so that I don't have to scroll forever to the right to see recent information. (Yes, it would be easier to add rows, but that's another story.) All was well until I wanted to add sparklines; since the newer data was on the left, it looked like the sparklines were trending down, when actually the data was just backwards. I needed to find a way to reverse an array in Google Spreadsheets. It took me awhile to figure out and I couldn't find a solution online so I decided it is worth putting up.

Here's the trick:

=SPARKLINE(TRANSPOSE(SORT(TRANSPOSE(C2:F2),TRANSPOSE(COLUMN(C$1:F$1)),FALSE)))

Basically, this sorts the array based on the column it's in, in descending order so that the array is flipped. The data is transposed (and then transposed back) because the SORT function only works on columns.

Here it is in action:

Spreadsheet with correct sparklines