how do I make a data that look like below picture and change it to "to be"

What i'm trying to do

The picture will explain. i have a very long row of data that need to move in to 1 row, is there a easy way todo without copying and transpose row by row

2 Answers 2


You can use Power Query to get the result:

  1. Select Range and go to Data- From Table/Range (uncheck "My table has a header"): enter image description here

  2. Select all Columns- Go to Transform- Unpivot Columns- Remove column Atribute: enter image description here

  3. Close and load data: enter image description here

  • 3
    This is the best answer I have seen on SE ever! gifs? Really? You are making the rest of us look bad! Keep up the good work!
    – Randomhero
    Apr 17, 2019 at 8:15
  • This saved me so much time! Thank you, no way I'd find a way to do this my self.
    – jonas
    Dec 11, 2023 at 11:57

Certainly! If you have a very long row of data that you want to consolidate into one row without copying and transposing row by row, you can use the "TRANSPOSE" function along with some array manipulation. Here's a step-by-step guide:

Suppose your long row of data is in cells A1 to ZZ1, and you want to transpose it into a single row starting from cell A2.

Transpose the Data:

In cell A2, enter the following formula:


Press Ctrl + Shift + Enter to enter the formula as an array formula. You should see curly braces {} appear around the formula in the formula bar. Adjust Formatting (Optional):

If needed, you might want to adjust the formatting, such as column width, to ensure that the transposed data fits well. This formula transposes the data from a row to a column. The $A$1:$ZZ$1 references your original row, and the TRANSPOSE function flips it into a single column. The curly braces indicate that it's an array formula.

Now, you have your data in a single column in column A, and you can move it or copy it to another location if needed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .