How to convert a range into a single column in Excel?
When data is spread across multiple rows and columns, you may need to combine all the values into one continuous column for filtering, analysis, or further processing. This tutorial introduces three ways to convert a range into a single column in Excel.
| Method | Best for | Result |
|---|---|---|
| Formula | Users who need results linked to the source range | Returns values from the named range in row-by-row order |
| Kutools for Excel | Quickly converting a selected range without formulas or code | Transforms the entire selected range into one column |
| VBA | Repeatable conversions and users comfortable running macros | Copies the selected range into a single column in row-by-row order |
Convert multiple rows and columns into a single column with a formula
The following formula can convert a named range into a single column while preserving the values in row-by-row order.
- Select the range that you want to convert, right-click it, and select Define Name. In the New Name dialog box, enter a name for the range, such as MyData, and click OK.
- Select a blank cell where you want the single-column result to begin. In this example, select E1 and enter the following formula:
=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1) - Drag the formula down until all values from the source range have been returned. Stop when the formula begins displaying an error.
📝 Note:
MyData is the name assigned to the source range. If you use a different range name, replace MyData in the formula accordingly.
Convert multiple rows and columns into a single column with Kutools for Excel
The formula can be difficult to remember and requires a named range. With Kutools for Excel's Transform Range feature, you can convert a selected range into a single column through a dialog box.
After installing Kutools for Excel, follow these steps:
- Select the range that you want to convert.
- Click Kutools > Range > Transform Range.
- In the Transform Range dialog box, select Range to single column.
- Click OK, and then select a destination cell for the result.
- Click OK. The selected range is converted into a single column.
The Transform Range feature can also convert a single column into a range with a fixed number of rows.
Convert multiple rows and columns into a single column with VBA
You can also use the following VBA code to combine values from multiple rows and columns into one column.
- Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
- Click Insert > Module, and paste the following code into the module:
Sub ConvertRangeToColumn() 'Updateby20260916 Dim Range1 As Range, Range2 As Range, Rng As Range Dim rowIndex As Long Dim xTitleId As String On Error Resume Next xTitleId = "Kutools for Excel" Set Range1 = Application.Selection Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8) If Range1 Is Nothing Then Exit Sub Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8) If Range2 Is Nothing Then Exit Sub On Error GoTo 0 rowIndex = 0 Application.ScreenUpdating = False For Each Rng In Range1.Rows Rng.Copy Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True rowIndex = rowIndex + Rng.Columns.Count Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub - Press F5 to run the code. In the first dialog box, select the range that you want to convert.
- Click OK. In the next dialog box, select a single destination cell.
- Click OK. The values in the selected range are converted into a single column.
📝 Notes:
- Select a blank destination area that does not overlap the source range.
- Save the workbook before running the macro because VBA actions cannot be undone.
- Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the code.
Frequently Asked Questions
In what order are the values placed in the single column?
The methods shown here process the source range row by row, moving from left to right across each row before continuing to the next row.
Why does the formula eventually return an error?
The formula has reached a position beyond the named source range. Stop filling the formula after the last source value, or use IFERROR if you prefer blank cells instead of errors.
Will the single-column result update when the source data changes?
The formula result updates automatically. Kutools and VBA create separate results, so you need to run the conversion again after changing the source data.
Can blank cells in the source range be retained?
Blank source cells are included as positions in the output sequence. If you want a compact list without blanks, you will need to filter or remove the blank results afterward.
Can I place the result on another worksheet?
Yes. With Kutools or VBA, switch to the desired worksheet when selecting the destination cell. For the formula method, enter the formula in the worksheet where you want the output.
Related articles
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