How to quickly stack multiple columns into one column in Excel?
In Excel, the Text to Columns feature is designed to split data in a single column into multiple columns using a specified delimiter. However, what if you need to do the opposite—combine or stack the values from multiple columns into a single column, as shown in the example below? This scenario frequently arises when consolidating datasets, preparing information for analysis, or formatting reports for further processing. Unfortunately, there isn't a built-in Excel function that directly stacks columns vertically, but there are several practical solutions you can use to achieve this task efficiently.
Stack multiple columns into one with formula
Stack multiple columns into one with VBA
Stack multiple columns into one with Transform Range 
Stack multiple columns into one with Power Query
Stack multiple columns into one with formula
If you prefer not to use macros or add-ins, you can stack multiple columns into a single column with an array formula using the INDEX function. This approach is suitable for dynamic datasets or situations where you want to avoid manual operations. One advantage is that the formula will update results automatically if your source data changes, but you need to be precise with your range names and avoid inserting or deleting cells within the referenced range.
1. Select the data range you wish to stack (for example, A1:C4), then click in the Name Box (located to the left of the formula bar), type a meaningful name such as MyData, and press Enter. This names your range for easy reference.
2. Click a blank cell where you want the single stacked column to begin—usually below your data or in another sheet. Paste this formula into the selected cell:
=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)3. Press Enter to confirm, then drag the fill handle down until you see a #REF! or similar error, indicating all data have been listed. Delete the error cell if needed.
In this formula, MyData refers to the range you defined in step1. COLUMNS(MyData) automatically adjusts for the number of columns in your data. Make sure not to insert or delete columns inside MyData after entering the formula, as that can affect the results. If your data contains blank cells, those blanks will also be stacked—you can filter them out afterwards if necessary. For large ranges, dragging the fill handle can take time; consider double-clicking the fill handle if the column to the left has data all the way down.
If your version of Excel supports dynamic arrays (Excel365 or Excel2021 and later), you can try:
=TOCOL(MyData,1)This formula is only available in newer Excel versions and instantly stacks the columns in a single column.
Stack multiple columns into one with VBA
If you frequently need to stack columns or want greater control, a VBA macro can automate this process in just a few clicks. VBA is ideal for advanced users handling large datasets or repetitive tasks. Keep in mind, however, that enabling macros can pose a security risk if you aren't sure of the macro's source. Always save your file beforehand and be cautious when running unfamiliar code.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. In the VBA window, click Insert > Module. Copy and paste the following code into the new module:
VBA: Stack columns to one
Sub ConvertRangeToColumn()
'UpdatebyExtendoffice
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
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
3. To run the macro, press F5, or click the Run button. After running, a prompt will appear—select the data range you want to stack and click OK.
4. You will then be prompted to select a destination cell for the result. Click OK again. The columns will be stacked in a single column starting from the destination cell.
If you encounter an error, double-check the selected range and ensure there are no protected sheets or merged cells. For more advanced customization, such as skipping blanks or stacking only certain columns, the code can be further modified. Remember to save your work before running VBA code to prevent unwanted data loss.
Stack multiple columns into one with Transform Range
If you prefer a fast and user-friendly solution without having to write formulas or code, you can use the Transform Range feature of Kutools for Excel. This utility is especially helpful for users who want to process data quickly via an intuitive interface, such as office administrators or those who routinely process multi-dimensional data. Using Kutools minimizes the risk of formula mistakes and saves significant time, although it requires installing the add-in.
After free installing Kutools for Excel, proceed as follows:
1. Select the columns or range of data you want to stack into one column. Then click Kutools > Range > Transform Range to open the dialog.
2. In the Transform Range dialog box, choose the Range to single column option, and click OK. Next, select a cell where you want the result to appear.
3. Finally, click OK. The selected columns will be immediately stacked into one column in your chosen location.
This method is straightforward and error-free. Remember that you can easily undo the change (Ctrl + Z) if something doesn’t look right, or rerun Transform Range as needed for different data sets. If your data contains merged cells, it is best to unmerge them before using this feature to avoid unexpected results.
Stack multiple columns into one with Power Query
Power Query (also called Get & Transform in some Excel versions) is another practical way to stack multiple columns into one, especially for users dealing with large volumes of data or those needing to automate recurring data transformation tasks. Power Query is built into Excel 2016 and later, and is available as a free add-in for Excel 2010 and 2013. It is most useful for advanced data preparation and can easily handle complex reshaping operations with a simple user interface. However, there is a short learning curve for first-time users.
Load your source table into Power Query. Select your data in Excel. Go to Data > From Table/Range to open Power Query Editor.
2. Select all columns in the query preview. Go to the Transform tab, find the Table group, and click Transpose. This flips the rows and columns.
3. Again, select all of the transposed columns. In the Transform tab, under the Any Column group, click the Unpivot Columns.
4. Remove the "Attribute" column if it's not needed. Right-click the "Attribute" column header > Select Remove.
5. Click Close & Load to send the result to Excel as a new worksheet or table. At any time, you can refresh or reapply the transformation if your source data changes.
Power Query allows you to save this stacking process as a reusable query and quickly update results if your data changes in the future.
Note: This approach might not preserve the original column header information from the source data.
In summary, each of these methods has its strengths: formulas are suitable for dynamically updating data, VBA is excellent for automating repetitive tasks, Kutools for Excel enables point-and-click operations with little technical knowledge required, and Power Query is ideal for advanced or recurring transformations. Always review your stacked data for remaining blanks, merged cells, or formatting errors after processing. If errors or unexpected results appear, double-check your defined ranges or step selections. Using a combination of these methods will help you efficiently manage and transform your Excel data for reporting and analysis needs.
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