Forum Discussion

wendy20's avatar
wendy20
Copper Contributor
Sep 27, 2020

RESOLVED issue: TRANSPOSE function of Excel is not working (try the alternative C+S of the keyboard)

 

Hi there,

 

I have encountered an issue with an excel array function using a Windows computer.

I am sure the formula and items in the function are all used correctly. I can replicate the same order when doing the exact same thing using my Macbook.

 

The excel always show #value (error value) when I hit C+S+E. I don't know why this shortcut key just doesn't work in excel of this computer.

 

Please help!! 

 

 

 

 

 

THANK SO MUCH EVERYONE!!

 

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Steel Contributor

    The formula in cell b123 is not an array ( CSE ) formula, otherwise Excel must enclose formula with { } braces/curly brackets. Please upload the file you are using and let us examine it. Once move to b123 press F2 and hit Ctrl+Shift+Enter simultaneously. 

    • wendy20's avatar
      wendy20
      Copper Contributor

      Rajesh_Sinha 

       

      Hi Rajesh,

       

      I have figured that the issue is not about my input, rather it is my excel's functionality on TRANSPOSE.

      It simply wouldn't allow me to transpose anything. The excel just left hanging whenever I hit C+S+E.

       

      Thanks for asking!

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    wendy20 Not sure if this helps, but I replicated your schedule and used both CSE and without (current MS365 version), without problems. Tested on both PC (Windows 10) and Macbook (Catalina).

     

    As per the help pages, a #VALUE! error will occur when any of the cells are empty or contain text, or when the number of columns in array 1 is different from the number of row in array 2. You don't seem to have empty cells and the arrays are sized correctly. So, are you sure that all cells in both arrays are numbers?

     

    Attached my file. See if it works on either of your machines.

    • wendy20's avatar
      wendy20
      Copper Contributor

      Riny_van_Eekelen 

      Hi Riny,

       

      Thank you so much for helping me out with those efforts!

       

      Yes, I am sure that all the cells in both arrays are numbers. 

      I have downloaded the file you've attached and tried to redo your working.

      1. Cleared the existing content

      2. Selected the right cells

      3. Typed in the formula required 

      4. Tried to C+S+E      *********The excel seems to be frozen every time when I hit these buttons. No response at all.

       

      So I went back to trace the error. As shown in the evaluation of calculation, the first step on the TRANSPOSE(B10:G11) has already resulted in an error value.

       

       

      I am suspecting that there are some issues with my Excel setting. What do you think?

      Anyways, I really do appreciate you for taking your time and energy to help me.

       

       

      Cheers! 

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        wendy20 But did it work OK before you started redoing my formulae? Not sure why you get the value error. According to the help screens that will occur if you do NOT enter the formula with C-S-E where your Excel version requires it. You tagged your original post with "Office365". If that means that you are a MS365 subscriber using a current version of Excel, you wouldn't have to worry about C-S-E.

Resources