-1

Usually, I create tables and use the columns as my sorting/filter points.

It's well known that people hate scrolling horizontally. For one large project I am working on, I would like to flip the axes, so that the columns become the rows and the rows become the columns. I can wrap text if necessary to make the rows larger when need be. I would then need to be able to sort by row, not by column.

enter image description here

So my data is currently in the top format, and I would like to go to the bottom format, where A, B, and C are the new "titles". These would still be what you would sort by.

Is this possible? Search results have turned up nothing.

To clarify, I don't want to sort Excel by row instead of column, like some have suggested this question may be a duplicate of. I would like to only sort the table in question by row, and leave everything else unaffected.

A PivotTable, or something that displays the table in an alternate view, might work. I'd prefer not to have to edit it in a different place, but if I must, that would work as well.

InterLinked
  • 2,406
  • 6
  • 29
  • 64
  • It could be done using macros. If wouldn't be an autofilter though. You'd have to get the user to provide the filter criteria (either on a user form or a seperate worksheet, or even a [combo box](https://support.office.com/en-us/article/Add-a-list-box-or-combo-box-to-a-worksheet-in-Excel-579e1958-f7f6-41ae-ba0c-c83cc6e40878) in the first row), then the macro would hide all the non-matching columns. It actually would work OK if properly implemented, but it would be a lot more work for you than just toggling the Autofilter button. If you're interested in a solution like this, let me know. – Nate Jun 16 '17 at 23:15
  • @Nate Really, it's impossible to do it natively? I'm not too familiar with macros but I hear they're a big security concern and are frowned upon. I was looking for something like how you can reverse axes in a chart or graph – InterLinked Jun 16 '17 at 23:39
  • 1
    Copy and paste special transpose. – Scott Craner Jun 17 '17 at 00:10
  • You don't copy? You can't copy and paste the data or you do not understand? – Scott Craner Jun 17 '17 at 00:16
  • @InterLinked yes you can transpose a table, simply copy and select [paste special > transpose](https://support.office.com/en-us/article/Transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744), sort it by column then transpose it again. VBA is indeed a security concern if you download from non-trusted places. It's completely perfect for internal use. Many things can't be done without VBA – phuclv Jun 17 '17 at 05:43
  • I saw the result right in the first Google page – phuclv Jun 17 '17 at 05:55
  • @LưuVĩnhPhúc, I don't think it's an exact duplicate to the other question, he's looking for sorting and filtering, not just sorting. It is a good solution for the sorting, though. – Nate Jun 17 '17 at 16:44
  • @InterLinked: If your question is how to get from the top format (A B C / 2 3 3 / 23 34 5) to the bottom format (A 2 23 / B 3 34 / C 3 3), then [the other Scott](/users/567231/scott-craner "Scott Craner") has [given you the answer](/q/1220127/150988#comment1783210_1220127).  Please acknowledge that it is the answer (or ask more follow-up questions, if necessary). If that’s not your question, but it’s not how to sort by row instead of column, then please [edit] your question to explain what you are asking. Note that your question *title* asks “How do you sort by row instead of column in Excel”. – Scott - Слава Україні Jun 17 '17 at 22:31
  • @Scott My title is "How do you sort by row instead of column in Excel TABLES". Big difference. The question that this is supposedly a "duplicate" of does NOT deal with tables at all. Sorting a column/row sorts the ENTIRE column/row. That's not what I would like to do, and I would like to do it only in the context of the table. – InterLinked Jun 18 '17 at 13:04
  • 2
    @InterLinked: “Sorting a column/row sorts the ENTIRE column/row.” Well of course if you sort an (entire) column or row, Excel sorts the entire column or row. What else would you expect it to do? “That’s not what I would like to do, and I would like to do it only in the context of the table.” So [***select*** the table](https://i.stack.imgur.com/gQGRw.png) before clicking on “Sort”. – Scott - Слава Україні Jun 19 '17 at 04:00

1 Answers1

0

I don't think there is a way to sort the columns in the way that you suggest. If you just need a view that is rotated from the original data, you might be able to use a pivot table on your original table, but you would still need to edit it in its original location.

churchid
  • 1