How to reverse and transpose Excel data with this powerful but simple solution

Often, a solution is simpler than you first imagine if you break the problem down a bit and apply built-in features in a creative way. For instance, in this article, I’ll show you how to move and reverse a data set. Neither task is difficult, but doing both seems to complicate things, and therefore, you overthink the solution. With that simple task quickly solved, we’ll move on to another seemingly complicated problem–reversing and transposing!

I’m using Excel 2016 on a Windows 10 64-bit system, but you can use older versions. There’s no downloadable demonstration file–you can easily create the simple example data range or use your own data.

Reverse the order

Reversing a data set isn’t difficult although I’ve seen a lot of convoluted solutions for doing so. The simplest solution is to add a column of consecutive values adjacent to the data set and sort by the new column. It doesn’t hurt a bit. If you want to keep the original order, copy the data and sort the copy. You don’t have to copy and sort at the same time, although, you will see online solutions that do so using arrays and other complex solutions–nobody really needs to work that hard.

Now, let’s assume you want to keep the original data’s order but you also need it reversed. First, copy the data to the new location, as shown in Figure A. Then, add a column of consecutive values to the copied data set by entering 1, 2, and 3 (I4:I6) adjacent to the copied dataset. With such a small data set, you can easily enter the values manually. If the range is larger, use the fill handle to complete your list of consecutive values. First, enter 1 in I4. Then, double-click the fill handle and choose Fill Series from the AutoFill options at the bottom of the range. Doing so will change your column of 1s to a consecutive list.

Figure A

Copy the data set and enter a list of consecutive values adjacent to it.

The next step is to sort the copied data set by the new column of values. To do so, click any cell in the new column (column I) and choose Sort Largest To Smallest from the Sort & Filter dropdown in the Editing group, as shown in Figure B. If Excel asks you to expand the range, click Sort to confirm. As you can see in Figure C, you have a reversed data set in a new location, and the original data exists in its original order. At this point, you can delete the consecutive values (or not).

Figure B

Choose a descending sort.

Figure C

Sorting the consecutive values reserves the order of the data.

Transpose the data set

Reversing the rows was easy. Now, let’s complicate the problem a bit by reversing and transposing the data. Again, achieving the results is much easier than you might initially think. First, let’s look at a quick transposing example:

Select the data set (B4:D6) and press Ctrl + C to copy it to the Clipboard.
Select F4 and choose Transpose from the Paste dropdown in the Clipboard group (Figure D). The results are shown in Figure E.

Figure D

Choose Transpose (T).

Figure E

Here are the transposed results.

Transpose the reversed set

The values in row 4 are now in column F. The values in row 5 are in column G, and the values in row 6 are in column H. But the results aren’t reversed and while the process to transpose and reverse is still simple, knowing when to do which complicates things a bit. But don’t worry.

If you want the transposed values reversed, simply add the adjacent column of consecutive values and sort the new data set, as shown in Figure F. On the other hand, if you want the reversed data set transposed, approach the problem in that order, as shown in Figure G. First, copy the data set and reverse it, then transpose it. If you compare the two resulting data sets, you’ll see that the final data sets aren’t the same.

Figure F

Reverse the transposed results.

Figure G

Transposing the reversed data set takes an additional step.

Easier than you thought

You want this data to look like what? Sometimes a problem only seems big, as I’ve shown in this article. Considering when to reverse and when to transpose is the hardest part of this easy-to-implement solution. I’m not suggesting that every request to reorganize your data will be so easily achieved. But with a little creative use of built-in tools, you might achieve what you need more easily than you initially anticipate.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

How to reverse and transpose Excel data with this powerful but simple solution