Excel 2003: Swap Rows and Colunms

Microsoft Office excel 2003 - licence
January 21, 2011 at 16:14:45
Specs: Windows Vista, 2g
I use Excel 2003 and I'm trying to flip the rows and columns.
I have this: Columns A B C Rows 1 2 3
I need this: Columns 1 2 3 Rows A B C

I need this for A-Z and 1-31. I don't have any data in the worksheet yet. (I'm trying to set it up before entering the data.)

I selected A1-Z31 and copied it, moved outside the selected cells, clicked Edit/Paste Special/Transpose/Ok and nothing happens. I even tried opening a new worksheet, selecting A1 Edit/Paste Special/Transpose/Ok but nothing changed.


See More: Excel 2003: Swap Rows and Colunms

Report •


#1
January 22, 2011 at 08:34:38
Paste Special...Transpose is only for transposing data as shown here:


     A      B      C
1   One    Two   Three

Copy A1:C1

Select A1 (or any cell)

Paste Special...Transpose

      A
1   One
2   Two
3   Three

Paste Special...Transpose can also be used to transpose Rows of data in Columns of data.

In other words, Paste Special...Transpose has nothing to do with Row or Column headers.

Excel will allow you change the Column letters to numbers, but it will not allow you to change the Row numbers to letters.

Use Tools...Options...General Tab, check R1C1 Reference Style to get Column Numbers instead of Letters.

If you really want Row letters and Column numbers, the only suggestion I can offer is to use Tools...Options...View and uncheck the Row & Column Headers options. This will hide the Row and Column headers in your sheet. You can then insert your own letters and numbers in Column A and Row 1.

Obviously, Excel is not going to honor your Row and Column headers, so if you use Column A and Row 1 for your headers, all formulas are still going to refer to the original Excel cell.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 22, 2011 at 10:14:17
<snip>If you really want Row letters and Column numbers, the only suggestion I can offer is to use Tools...Options...View and uncheck the Row & Column Headers options. This will hide the Row and Column headers in your sheet. You can then insert your own letters and numbers in Column A and Row 1.

Obviously, Excel is not going to honor your Row and Column headers, so if you use Column A and Row 1 for your headers, all formulas are still going to refer to the original Excel cell.
<snip>

Ok Thanks! Now, is there a way to save Column A and Row 1 as headers so it doesn't sort?


Report •

#3
January 22, 2011 at 11:50:40
Since I can't see your spreadsheet from where I'm sitting, I'm not sure what you are sorting on, so it's hard to give a definitive answer.

However, you might try this:

Assuming you want something like this...


    A   B   C   D   E
1       1   2   3   4
2   A  
3   B
4   C
5   D

You can use formulas based on the actual Rows and Columns. The results of the formulas won't sort.

To get A, B, C, D, etc. in Column A, place this in A2 and drag it down:

=CHAR(ROW()+63)

To get 1, 2, 3, 4, etc. in B1:Bx, enter this in B1 and drag it across:

=COLUMN()-1

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 22, 2011 at 16:36:04
Thanks... I'm rollin' now! :)

Report •

Ask Question