Excel spreadsheet - one column into two columns

[tiagrindle]

I'm trying to transpose one column in excel to at least two columns.
It is set up (all in one column, separate rows)

name
email
name
email
name
email
name
email

you get the point, how can I do this? I tried to transpose but it's not working like I want. I would like it laid out in two columns

name email
name email
name email
etc.

Thanks in advance!

 

[Patty Winter]

I haven't used Excel macros myself, but I imagine it would be
fairly easy to create one that would copy the even-numbered
rows into one new worksheet and the odd-numbered rows into
another worksheet. Then you could copy the contents of one
sheet into a new column on the other one.


Patty

 

[tmtanvir]

How to transpose / convert a single column to multiple columns in Excel?
Supposing you have a table as showing below, and you need to change one column to a range. Here we will show you some tricky things about how to change a single column into multiple columns.

Transpose a single column to multiple columns with formula

Transpose a single column to multiple columns with Kutools for Excel

doc-convert-column-to-rows1 arrow-big doc-convert-column-to-rows2
Kutools for Excel: Add 120 New Powerful Features to Excel.
Classic Menu for Office: Bring Back Old Look to Office 2010 and 2013 (includes Office 365).
Office Tab: Bring Tabbed Editing and Browsing Features to Office, Just Like Chrome, Firefox, Internet Explorer 8/9/10.
arrow blue right bubble Transpose a single column to multiple columns with formula

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!


In excel, the OFFSET function can help you, you can use this formula as following steps:

1. In a blank cell C1, please enter this formula: =OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*6,0), see screenshot:

doc-convert-column-to-rows3

Note: A1 is the started cell, *6 stands for the number of cells that you want to have in each row. Meanwhile you must start this formula in the columnC.

2. Press Enter key, and select cell C1, drag the fill handle from C1 acrossto H1, while C1:H1 are selected and then copy down as far as you need. Seescreenshot:

doc-convert-column-to-rows4

As they are formulas, when you copy them, you must copy and paste them as values.

There are some limitations of this formula, maybe, it is difficult for beginners to apply. Here I will continue to introduce you an easy way to deal with this problem.

arrow blue right bubble Transpose a single column to multiple columns with Kutools for Excel

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

After installing Kutools for Excel, please do as this:

1. Select the column that you want to convert.

2. Click Kutools > Range Converter > Transform Range, see screenshot:

doc-convert-column-to-rows5

3. In the Transform Range dialog box, specify the settings as follows:

Choose Single column to range from Transform type;
Specify the cells per row, if you have selected a column with blank cells as separator, you can check Blank cell delimits records, and the data will start a new row at each blank cell. You can also specify the number of cellsper row from the Fixed value that you need.
doc-convert-column-to-rows6

4. Then click OK, a Transform Range prompt box will pop out, and click a cell to put the result. The Output range can be specified in different worksheets and workbooks. See screenshot:

doc-convert-column-to-rows7

5. Click OK, you will see the single column has been transformed to multiple columns in selected cell. See screenshot:

doc-convert-column-to-rows2

Kutools for Excel's Transform Range tool can help you easily transform a single column to a range, transform a single row to a range, transform a range to a single row and transform a range to a single column. Click to know more…


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.
screen shot

btn read more btn download btn purchase