How to transpose / convert columns and rows into single column?
When you use Excel worksheet, sometimes, you will meet this problem: how could you convert or transpose a range of data into a single column? (See the following screenshots:) Now, I introduce three quick tricks for you to solve this problem. SOLUTION The following long formula can help you quickly transpose a range of data into a column, please do as this: 1. First, define a range name for your range of data, select the range data that you want to convert, right click and choose Define Name form the context menu. In the New Name dialog box, enter the range name you want. Then click OK . See screenshot: 2. After specify the range name, then click a blank cell, in this example, I will click cell E1, and then input this formula: =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1) . Note : MyData is the range name of the selected data, you can change it as you need. 3. Then drag the formula down to the cel