CODEWITHSUNDEEP
excelms-officevba
Quan Nguyen
Quan Nguyen

Reputation: 560

Excel rotate (NOT transpose) a table 90 degrees clockwise

I have a table in Excel. How can I rotate (NOT transpose) a table 90 degrees clockwise?

Example

cell1  |  cell2  | cell3
cell4  |  cell5  | cell6

Expected

cell3  |  cell6
cell2  |  cell5
cell1  |  cell4

P/S: If I use transpose option when paste, I will have unexpected result

cell1  |  cell4
cell2  |  cell5
cell3  |  cell6

Thanks

Upvotes: 5

Views: 14568

Answers (3)

raymkchow
raymkchow

Reputation: 949

If you can use the new feature of SORTBY in excel, assuming your table sits in A1:C2, you just need to =SORTBY(TRANSPOSE(A1:C2), TRANSPOSE(COLUMN(A1:C2)),-1) to allow the data sorted by the natural reverse order provided by COLUMN after you transpose it.

Upvotes: 3

rohrl77
rohrl77

Reputation: 3337

Assuming you want to do this manually, and that it doesn't need to be done via functions:

  1. First transpose your range using the regular copy transpose paste function
  2. Next, create an artificial sorting key (simply number is 1 to n) infront of the first column and sort in a descending order
  3. Delete the sorting column you created in front of the first row

Upvotes: 6

L.Dutch
L.Dutch

Reputation: 966

You have to go in a two steps process:

  1. transpose your table (you have already done it)
  2. "mirror" your table

To do this last step, consider that you have a table M rows x N columns, which is transposed into a N rows x M columns. The cell located at row i and column j has to be moved to the row i-1 + N and column j.

In your example, after transposition cell 1 is in (1,1) with N = 3, and it will go to (1-1+3,1)=(3,1) and so on.

You need to use a short macro to do the transformation.

Upvotes: 1

Related Questions