How to split one column into multiple columns in Excel?
Suppose you have a list stored in a single column and want to rearrange it into multiple columns and rows so that the data is easier to view. This article introduces a formula and a Kutools for Excel feature that can quickly reshape the list.
Split one column into multiple columns with a formula
Split one column into multiple columns with Kutools for Excel
| Method | Best for | How the layout is controlled | Main consideration |
|---|---|---|---|
| Formula | Users comfortable editing cell references and formula parameters | Adjust the formula variables to match the starting cell and the number of rows in each record | The formula must be filled across and down, and zeros may appear after the source data ends |
| Kutools for Excel | Quickly reshaping a single-column list without building a formula | Enter the required number of rows per record in the dialog box | Requires Kutools for Excel |
Split one column into multiple columns with a formula
You can use an OFFSET formula to rearrange values from a single column into multiple columns and rows.
- Select the blank cell where you want the first value of the result to appear. In this example, select C2.
- Enter the following formula and press Enter:
=OFFSET($A$1,(ROW()-2)*3+INT((COLUMN()-3)),MOD(COLUMN()-3,1)) - Drag the fill handle to the right for the required number of columns, and then drag it down until all source values have been included.
📝 Note:
The general structure of the formula is:
OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set),MOD(COLUMN()-f_col,col_in_set))- f_row is the row number of the first formula cell.
- f_col is the column number of the first formula cell.
- rows_in_set is the number of source rows that make up one record.
- col_in_set is the number of columns in the original data.
Adjust these values to match the layout of your source data and output range.
Split one column into multiple columns with Kutools for Excel
If editing the formula is inconvenient, Kutools for Excel's Transform Range feature lets you reshape a single-column list by specifying the number of rows in each record.
After installing Kutools for Excel, follow these steps:
- Select the column list, and click Kutools > Range > Transform Range.
- In the Transform Range dialog box, select Single column to range under Transform type. Under Rows per record, select Fixed value and enter the number of source rows to place in each output row.
- Click OK, and then select a cell where you want to place the result.
- Click OK. The single-column list is converted into multiple columns and rows.
Demo: Convert rows to columns and rows
Frequently Asked Questions
How do I change the number of values placed in each output row?
In the formula method, change rows_in_set to the number of source rows that make up one record. With Kutools, enter that number as the Fixed value under Rows per record.
Why does the formula return zeros after the source data ends?
OFFSET returns zero when the formula reaches blank source cells. Stop filling the formula after all source values have been included, or clear the extra output cells.
Can I start the formula in a cell other than C2?
Yes. Update f_row and f_col in the formula so that they match the row and column numbers of the new starting cell.
Will the formula results update when the source data changes?
Yes. Formula results remain linked to the source data. If you need a fixed result, copy the output and paste it as values.
Can Kutools place the output on another worksheet?
Yes. When prompted to select the output location, switch to the desired worksheet and select the starting cell.
Related articles
- How to mass convert text to date in Excel?
- How to convert lowercase to proper or sentence case in Excel?
- How to convert time to decimal hours/minutes/seconds in Excel?
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