Skip to main content

Kutools for Office β€” One Suite. Five Tools. Get More Done.

How to transpose / convert a single column to multiple columns in Excel?

Author Kelly Last modified

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.

a screenshot showing a column being converted to multiple columns

Transpose a single column to multiple columns with formulas

Transpose a single column to multiple columns with Kutools for Excel


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:

a screenshot of using formula to convert a single column to a range of data from row to row

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:

a screenshot of all results in columns

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

Easily convert / transform a single row or column to a range of cells:

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

a screenshot of using Kutools for Excel to easily convert a single column to multiple columns

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


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:

a screenshot of using formula to convert a single column to a range of data from column to column

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:

a screenshot showing all results


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. With Kutools for Excel's Transform Range feature, you can quickly convert a single row or column data to a range of cells as you need. Click to download Kutools for Excel!

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:

a screenshot of enabling the Transform Range feature of Kutools for Excel

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.

a screenshot of configuring the Transform Range dialog box

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:

a screenshot of selecting a destination cell

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

a screenshot showing the original column and the converted columns

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!


Best Office Productivity Tools

πŸ€– Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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.

Excel Word Outlook Tabs PowerPoint
  • 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