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

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

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:


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 column C.

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


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:


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.


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:


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


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…


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase


Permalink 0 Surya
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.
2014-07-03 15:49 Reply Reply with quote Quote
Permalink 0 Ankit
You need to select three to four cell and =transpose(your range) then hit Alt+Shift+Enter you will got you cell in cloumns
2016-01-07 09:06 Reply Reply with quote Quote
Permalink +1 Deborah
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!
2015-07-22 23:26 Reply Reply with quote Quote
Permalink 0 RAMYA
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
2017-05-16 03:26 Reply Reply with quote Quote

Add comment

Security code