Share via

Transpose Function

Anonymous
2020-02-26T17:29:35+00:00

Hi

I have been using the transpose function in a workbook on an ongoing basis. I periodically update the workbook transposing columns into rows with no issues. I recently went to update the workbook and the transpose function does not work correctly. Instead of copying the highlighted column, it copies the rows to the right of the first cell at the top of the highlighted column. The transpose function in other workbooks does not do this. Also, after hitting the ctrl & shift & enter buttons together there are no curly brackets around the transpose formula.  Can anyone help with this? Thanks (office 365)

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-02-27T22:39:59+00:00

    Hi John,

    Thanks for the updates. Yes, we still need a screenshot of the Product Information Page(File->Account), since the behavior you described is a little different from our version, and this function varies among several recent versions as well as different subscriptions, so we need those information to build the test environment. 

    PM means private message, and you could enter it via this link. You could share both files and screenshots in PM.

    Have a nice day: )

    Best regards,

    Oliver

    0 comments
  2. Anonymous
    2020-02-26T23:45:52+00:00

    Hi Oliver

    Quote from your last response is correct.

    To confirm, you meant after you type the formula in B38, the contents in C38,D38 are not same as B38? Assuming I type =TRANSPOSE(A1:A10) in B38, then when I click C38 or D38, it will display =TRANSPOSE(A1:A10) as well.

    I did not receive a spill error.

    Do you still require a screenshot of the Product Information Page?

    What is a PM Invitation.

    I can provide you with a copy of the workbook, however it contains sensitive data that would have to be removed. Even without data the transpose functions was working fine.

    Thanks,

    John

    0 comments
  3. Anonymous
    2020-02-26T23:17:12+00:00

    Hi John,

    Thanks for the updates. I think if you have entered the formula in B38, the cells on the right side like C38, D38 cannot be entered into other contents, otherwise the Spill! error message will appear. To confirm, you meant after you type the formula in B38, the contents in C38,D38 are not same as B38?

    Assuming I type =TRANSPOSE(A1:A10) in B38, then when I click C38 or D38, it will display =TRANSPOSE(A1:A10) as well. Did you meet a different situation?

    If so, please go to File->Account and post a screenshot of the product information page, and we will collect the info to do more tests. Also, we have initiated PM invitation for you and you could share us a sample file via this link. We would like to figure out what type of source data you used, then help you find the issue cause.

    Best regards,

    Oliver

    0 comments
  4. Anonymous
    2020-02-26T19:19:48+00:00

    Hi Oliver,

    Thanks for your help. Hope this makes senses.

    John

    0 comments
  5. Anonymous
    2020-02-26T18:24:54+00:00

    Hi John,

    Thanks for posting here. The behaviour you met is called dynamic array formula, which is an update released recently for Office 365 subscribers. When you press Enter to finish, Excel will dynamically size the output range for you, and place the results into each cell within that range. You could learn more on this page: Dynamic array formulas and spilled array behavior

    Also, I did a search about the TRANSPOSE function and found the new introduction on this page: TRANSPOSE Function

    Since you mentioned you are transposing multiple columns, the result will appear automatically, instead of letting you select a range. If that article cannot help you solve the problem, it would be good if you could share some details or a screenshot of the problem, and we could do more tests to narrow down the issue cause.

    We appreciate your understanding and patience. Have a nice day: )

    Best regards,

    Oliver

    0 comments