# Thread: Inverting columns of data

1. ## Inverting columns of data

I have a set of data columns that starts at the top as the most recent and at the bottom is the oldest. I want to reverse that so the oldest is at the top and the newest/youngest is at the bottom. To illustrate...

Now > After
A>>>>> Z
B>>>>> Y
C>>>>> X
. .
. .
. .
X>>>>> C
Y>>>>> B
Z>>>>> A

I know that you can take a set of data and turn the columns into rows, but I really want to turn columns into columns in the reverse order. I want to know if there is an Excel Function or process that will do that easily. Any insight or help that anyone would care to provide will be greatly appreciated. Thanks.

Ron M

2. Hi Ron

It is not clear what you are asking.
Data rows can be sorted from top to bottom in ascending or descending order.
Data columns can be sorted left to right in ascending or descending order.

What version of Excel are you using?
The more recent Excel versions have more sorting options.

zeddy

3. Are the cells in each row related to the adjacent cells? Once sorted, must they remain on the same row?

4. ## Using the RefEdit control

1. Click Start Sort button. Mirror Sorting form will appear.
2. Drag a box around range to be sorted.
3. Click Ascending or Descending option button.
4. Click Sort Button. Form will close. Range will be mirror sorted (reverse horizontal, reverse verticle).
5. Can select any range. Works on any sheet if you copy the sort button to it.

HTH,
Maud

Sort1.jpg Sort2.jpg

5. Ron,

If Maubie's solution doesn't work because your data is not in numerical order but rather just by age (newest to oldest) you can try this on for size.

1. Insert a column before column A.
2. In the 1st data row (exclude header rows) enter this formula in column A: =Row().
3. Copy the formula down the rows.
4. Select the numbers in Column A and hit copy.
5. Select Paste Special then Values.
6. Select the range of columns/rows to be sorted including column A.
7. Sort Decending.
8. Delete Column A.

HTH

6. Zeddy, the problem is that it is not a matter of sorting the colums in numeric order, I simply want to flip the columns as they are now in a relative "age" order, oldest at the bottom and youngest at the top. I want to reverse that order and have oldest at the top and youngest at the bottom. I am using Excel 2010.

Maudibe, yes the cells in each row are related to each other, which is why I simply want to "flip" the entire set of columns without changing any of the data in each row. I don't think it is a matter of "sorting" anything in ascending or descending order. I guess maybe I want to actually flip the rows, so the one at the top is now at the bottom, etc. Thanks for that insight. I want to flip the data in the columns, but what I actually want to flip are the rows. It is sorting like inverting a table - values in each row do not change, only the position of the rows relative to each other.

RetiredGeek, I think that your solution is the one that will work - put in a temporary variable to give a "real sense of order" to the existing data and then sort on that variable. That should do. I was thinking about this approach, but I thought that maybe someone would have a more "elegant" approach using some of the "functionality" in Excel that I do not know about.

My thanks to all of you.

Ron M

7. Hi Ron M

Assuming you have your list of ages in say A1:A20 , try the following INDEX formula, in B1 and copy down.

=INDEX(\$A\$1:\$A\$20,ROWS(1:\$20))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•