ChatterBank0 min ago
Excel Columns And Rows
5 Answers
On one worksheet I have several columns, each one is a list of info about one file.
On a second worksheet I need that column to be copied as a row.
I used the 'copy, then 'paste special/transpose' function.
But doing one column at a time is tedious.
Is there a way to use '=' to copy a whole column as a row in the second worksheet, and to drag down that function so Excel will understand it need to copy the next column and so on from the forst w/s to the second w/s?
Not sure if I've explained this clearly. Any help much appreciated.
On a second worksheet I need that column to be copied as a row.
I used the 'copy, then 'paste special/transpose' function.
But doing one column at a time is tedious.
Is there a way to use '=' to copy a whole column as a row in the second worksheet, and to drag down that function so Excel will understand it need to copy the next column and so on from the forst w/s to the second w/s?
Not sure if I've explained this clearly. Any help much appreciated.
Answers
You can copy and transpose more than one column at a time. Highlight 10 columns, copy, then paste special transpose and all 10 columns will be pasted as rows. Or, in sheet2, assuming your data starts in cell A1 on sheet1, enter the formula "= INDEX( Sheet1!$ A:$ Z, COLUMN(), ROW())" without the quotes, then you can drag the formula down and across as you...
12:15 Fri 10th May 2013
Not a lot of help I'm afraid but it does seem as if you need to do the tedious copy/transpose for each. If there is a way to format cells beforehand I can't see it, for you would not be formatting individual cells, you would be asking for processing and data shifting before the paste, which is different.
Maybe someone can create a macro for you ?
Maybe someone can create a macro for you ?
You can copy and transpose more than one column at a time. Highlight 10 columns, copy, then paste special transpose and all 10 columns will be pasted as rows.
Or, in sheet2, assuming your data starts in cell A1 on sheet1, enter the formula "=INDEX(Sheet1!$A:$Z,COLUMN(),ROW())" without the quotes, then you can drag the formula down and across as you suggested. Check out the INDEX formula in excel's help to see what it does. This assumes that your origianl data is in columns A-Z of sheet1 (hence the $A:$Z in the formula which you can change if necessary)
Or, in sheet2, assuming your data starts in cell A1 on sheet1, enter the formula "=INDEX(Sheet1!$A:$Z,COLUMN(),ROW())" without the quotes, then you can drag the formula down and across as you suggested. Check out the INDEX formula in excel's help to see what it does. This assumes that your origianl data is in columns A-Z of sheet1 (hence the $A:$Z in the formula which you can change if necessary)