How to transpose or convert a single column to multiple columns in Excel?
Suppose you have a single column of data, as shown below, and need to rearrange it into multiple columns. This tutorial introduces two ways to convert a single column into a range in Excel.
- Convert a single column to a range from row to row
- Convert a single column to a range from column to column
| Method | Best for | How the layout is controlled | Main consideration |
|---|---|---|---|
| Formulas | Users comfortable with formulas who need a linked result | Edit the number in the formula to set the number of cells in each row or column | The formulas must be filled across and down, then pasted as values if a static result is needed |
| Kutools for Excel | Quickly converting a row or column without building formulas | Set a fixed number of cells per row or use blank cells to separate records | Requires Kutools for Excel |
Transpose a single column to multiple columns with formulas
Formulas can rearrange a single column either from row to row or from column to column, depending on how you want the output data organized.
Convert a single column to a range from row to row
In Excel, the OFFSET function can help you arrange the data from row to row. Follow these steps:
- In the blank cell C1, enter the following formula, and then drag the fill handle from C1 to G1:
=OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*5,0)
📝 Note:
A1 is the first cell of the source data, and 5 is the number of cells to place in each row.
- Continue dragging the fill handle down as far as needed. The data from the single column is then arranged into multiple rows, as shown below:
- Because the results are formulas, copy them and paste them as values if you need a static result.
Easily convert a single row or column into a range of cells
Kutools for Excel's Transform Range feature lets you quickly convert data in a single row or column into a range of cells.
Convert a single column to a range from column to column
The formula above arranges the source data from row to row. To arrange it from column to column, use the following formula:
- In the blank cell C1, enter the following formula, and then drag the fill handle from C1 to C5:
=INDEX($A$1:$A$20,ROW(C1)+(5*(COLUMNS($C$1:C$1)-1)))
📝 Note:
A1:A20 is the source data, and 5 is the number of cells to place in each output column.
- Drag the fill handle across from column C to column F. The data in the single column is then arranged into multiple columns, as shown below:
Transpose a single column to multiple columns with Kutools for Excel
The formulas require you to build the desired layout by filling formulas across and down. With Kutools for Excel's Transform Range feature, you can convert data in a single row or column into a range by specifying the desired layout in a dialog box. Click to download Kutools for Excel!
After installing Kutools for Excel, follow these steps:
- Select the column that you want to convert.
- Click Kutools > Range > Transform Range.
- In the Transform Range dialog box, specify the following settings:
- Under Transform type, select Single column to range.
- To begin a new row at each blank cell in the source column, select Blank cell delimits records. Alternatively, select Fixed value and enter the number of cells to place in each row.
- Click OK. In the Transform Range prompt box, select a destination cell for the result. The output range can be placed in a different worksheet or workbook.
- Click OK. The single column is transformed into multiple columns beginning at the selected cell.
Kutools for Excel's Transform Range feature can convert a single column or row into a range, or combine a range into a single row or column. Learn more about Transform Range.
Download Kutools for Excel – Free 30-day trial
Frequently Asked Questions
How do I change the number of items placed in each row?
In the OFFSET formula, replace 5 with the number of items you want in each row. For example, use 4 to place four source values in each output row.
Why do zeros appear after the source data ends?
The formula may be referencing blank cells beyond the end of the source data. Limit the filled output area to the required number of cells, or wrap the formula in an appropriate blank-handling formula if blank results are needed.
Will the formula results update when the source column changes?
Yes. The formula results remain linked to the source cells and update when the source data changes. To keep the current results unchanged, copy them and paste them as values.
Can Kutools start a new row whenever it encounters a blank cell?
Yes. In the Transform Range dialog box, select Blank cell delimits records. Each blank cell in the source column will mark the beginning of a new output row.
Can the transformed result be placed in another worksheet or workbook?
Yes. When Kutools prompts you to select the output location, you can choose a destination cell in a different worksheet or workbook.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in