How to do this

Occasional Contributor

Please Help Me,to change this


data 1 data 2 data 3
data 4 data 5 data 6
data 7 data 8 data 9


into this ;

data 1
data 2
data 3
data 4
data 5
data 6
data 7
data 8
data 9

14 Replies

@adeliasuharly I suspect that your example may be a simplified representation of your real data, but perhaps the attached example can help you work out a solution that works for you. Note, I introduced two helper columns to aid in making a 9x1 range out of the 3x3 range.


woww,thank you so much..but i think my data is too much and too complex . thank ypu so much for your answerr. GBU @Riny_van_Eekelen 


@adeliasuharly I suspected that. But how complex is it? Excel can handle huge amounts of data and solve pretty complex problems. Would be helpful if you could provide a more realistic example of your data (exclude private and confidential information) and indicate which Excel version you are working with and on what platform (Windows, Mac, Web).


so i want to make this blocked data (pict 1) turn into single column (pict 2)

Screenshot (17).png

Screenshot (18).png



@adeliasuharly That should not be much of a problem with "Get & Transform Data" a.k.a. Power Query. Are you familiar with that?


to be honest, No Sir @Riny_van_Eekelen 

Best Response confirmed by adeliasuharly (Occasional Contributor)

@adeliasuharly Would you be willing to learn? It's not very difficult for the kind of job you need it to do. Attached a PowerQuery example, just to demonstrate that it's possible. But, difficult to offer a solution to your specific situation, based on a picture alone.


The blue table could represent your data and the green one is the PQ result.


woww Thank youu,your example is what i want.but ive tried 7 times to import my file into query feature but it doesnt work. actually i could do my work manually.but it takes a lot of time.meanwhile i need it very quick.your tutorial make my life easier.but i think i still need more time to learn...@Riny_van_Eekelen.

so this are my data in excel. i want to change the tabel format in " COMPLETE DATA"  just like in " TESTING FILE".


@adeliasuharly Like in the attached file? Sheet3.


YESS, how to do it? i wish i know how to do it step by step @Riny_van_Eekelen 


@adeliasuharly Well, I started by selecting R1:V164. Then Data, From Table/Range.

Screenshot 2020-07-01 at 19.34.43.png

Confirm "my table has headers".

Then the PQ Editor opens and it looks like this:

Screenshot 2020-07-01 at 19.37.18.png

Select the first column, press Shift and the last column. All columns turn green. 

Then, right-click in any of the column headers and select "Unpivot columns".

Then, right-click the Attribute column and "Remove column"

Screenshot 2020-07-01 at 19.44.04.png


Now you will be left with a list of values as in the example file.

Save your changes via "Close & Load to"

Screenshot 2020-07-01 at 19.46.28.png

... a Table and into a new Worksheet.

Screenshot 2020-07-01 at 19.48.10.png

This should create your list as in the example file I uploaded earlier.

Now, having said all of this I realise that this is not at all straight-forward if you have never used this feature before. But it is not as difficult as it seems. Try and start over if you don't succeed. Don't give up!


Most of what I know today about PQ, I have learned here a couple of weeks ago. 

Good luck!



thank youu,for your helpp and your support. i've tried the step you told me..but this is what happened. so I have to fix this problem first. but so far it seems like the way you taught me, is the easiest to understand.@Riny_van_Eekelen 


@adeliasuharly How annoying. Such error messages aren't really helpful. Don't have any clue what causes your problem. Does it work OK in the file that I sent earlier? 


Yes, it works for the file you  sent earlier. but if you make it from scratch, it becomes an error@Riny_van_Eekelen. but i will find out the solution