Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

doc-convert-column-to-rows-1

Transpose a single column to multiple columns with formulas

Transpose a single column to multiple columns with Kutools for Excel


Convert / Transform a single row or column to a range of cells:

Kutools for Excel's Transform Range feature can help you to convert a single row or column data to a range of cells as you need.

doc-convert-column-to-rows-10

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Transpose a single column to multiple columns with formulas


Convert a single column to a range of data from row to row:

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)*5,0), and then drag the fill handle from C1 to G1, see screenshot:

doc-convert-column-to-rows-2

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

2. Then go on dragging the fill handle down to the range as far as you need. And the single column data has been converted into multiple rows as following screenshot shown:

doc-convert-column-to-rows-3

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


Convert a single column to a range of data from column to column:

The above formula can help you to transpose the single column to range from row to row, if you need to transpose column to range from column to column, the follwoing formula also can do you a favor.

1. In a blank cell C1, please enter this formula:=INDEX($A$1:$A$20,ROW(C1)+(5*(COLUMNS($C$1:C$1)-1))), and then drag the fill handle from C1 to C5, see screenshot:

doc-convert-column-to-rows-4

Note: A1:A20 is the column data that you want to convert, 5* stands for the number of cells that you want to have in each column.

2. Then drag the fill handle across C column to F column, and the data in single column has been transposed to a range from column to column. see screenshot:

doc-convert-column-to-rows-5


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

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

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

After installing Kutools for Excel, please do as this:

1. Select the column that you want to convert.

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

doc-convert-column-to-rows-6

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 cells per row from the Fixed value that you need.

doc-convert-column-to-rows-7

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-rows-8

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

doc-convert-column-to-rows-9

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…

Click Download and free trial Kutools for Excel Now!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    YT · 1 months ago
    Thank you so much for this article. The formula you described worked perfectly!
    Thank you!
  • To post as a guest, your comment is unpublished.
    Dip · 2 months ago
    shall we convert single word into multiple column ???

    Like "WORD" converted to W O R D in column
  • To post as a guest, your comment is unpublished.
    Todd · 2 years ago
    Thank you!
  • To post as a guest, your comment is unpublished.
    Glenn Watkins · 2 years ago
    I have data imported to excel, a list, some lines begin with a [ and these I want the cell moved to column A, same line;, some lines have a space then a [ these I want to move to column B, same line; some columns have two spaces then data, and these I want to move to Column C, same line. Is there any way I can do this???????
  • To post as a guest, your comment is unpublished.
    RAMYA · 2 years ago
    I have a single name in several columns also had a amount list in several columns. Now how to total the whole amount and how to make a several name columns into single
  • To post as a guest, your comment is unpublished.
    Deborah · 4 years ago
    Absolutely totally brilliant! I downloaded the trial version to see if this program will do what it said (I had my doubts). I completed a project I has been messing around with ALL DAY in less than 60 seconds. BRILLIANT is all I can say. I will now spend all night playing with this program to see what other exceptionally cool things it does. FYI – I was exporting a large amount of data from a very frustrating accounting program that does not play nice with any other program. I need the data in excel so that I can manipulate it. This program was a life saver!
  • To post as a guest, your comment is unpublished.
    Surya · 5 years ago
    Hi I have a 3 columns but the data is same in the first two

    Material No.1 : Color : Gray
    Material No.1 : Color : Black
    Material No.1 : Color : Blue

    This runs into 100s of thousands. When I transpose, the first row gets transpose. But I lose the rest of the data.

    Please suggest any solutions. Much appreciated.
    • To post as a guest, your comment is unpublished.
      Ankit · 3 years ago
      You need to select three to four cell and =transpose(yourrange) then hit Alt+Shift+Enter you will got you cell in cloumns