Excel/Turning columns and raws round
QUESTION: Dear Tom
Hope my message find you well
Is there any equation which can turn the raw to the column place and verses?
I have a very large data, and by mistake I have entered the column variable in the raw and the raw in the column
the builtin TRANSPOSE worksheet function will switch row and column. However, it you have very large data, I don't think that would work foryou.
Say your data is on a sheet named Data and you data starts in row 2 with headers in row 1
on the second sheet put in this formula
then drag fill it down and across. It will transpose your data.
when you are done, you can select all the cells, do a copy , then paste values to replace the formulas with the values they display.
---------- FOLLOW-UP ----------
QUESTION: Thank Tom for your instant response
Yes, I did understand your answer
But I am struggling a bit in the formula
My sheet called sheet1
headings located in A1 (heading) end on BC1
data starts in column A2 down to A13
The whole file starts in A1 and end in BC13
What shall I put in between the COLUMN( ),ROW( ) brackets?
I tried number of column and row, but this ends by wrong formula
An example of the wrong formulas was;
You don't need anything in the parentheses. When you don't supply any argument (nothing in the parenthese), the column() and row() return the column and row of the cell in which they are located as a number. So if you put =column() in cell C5 it would return 3 since that is column 3. Likewise if you put =row() in cell BC11 it would return 11 since that is the row.
I have supplied the column() function in the row position of Address and the row() function in the column position of the Address function. This is what allows it to transpose the data.
to summarize, you don't need to alter this function
except to use the proper sheet name (and perhaps rename the functions to match whatever the function name is in your regional version of Excel if they are different).