How to transpose data from a column into a single cell in Excel?
When working with Excel, you may often encounter situations where you need to take a vertical list of data from a column and combine it into one single cell. This task is common when preparing result summaries, exporting data, creating reports, or needing to send aggregated values to another system or tool. If the list to be merged is short, you might consider using the Concatenate or TEXTJOIN functions, but these become cumbersome and less efficient when you have a large number of cells to combine. In this article, we share some practical and efficient methods for transposing and joining data from a column into a single cell in Excel, including VBA code solutions and the specialized Kutools for Excel add-in. These solutions can help reduce manual errors and save significant time, especially when dealing with extensive datasets.
Transpose data from a column into a single cell with an Excel Formula
Transpose data from a column into a single cell with Kutools for Excel
Transpose data from a column into a single cell with a User Defined Function (VBA)
Transpose data from a column into a single cell with an Excel Formula
If you prefer not to use macros or add-ins, you can also achieve the result with an Excel formula. The following solutions utilize the TEXTJOIN function, which is available in Excel 2016 and later versions. Alternatively, you may use CONCATENATE or CONCAT for smaller ranges, but TEXTJOIN is highly recommended for its simplicity and ability to ignore blank cells.
1. Click on the cell where you want your merged result to display (for example, B1), and enter:
=TEXTJOIN(",",TRUE,A1:A10)2. Press Enter. The values from A1 to A10 will be displayed in B1, joined into one cell and separated by commas. You may adjust the separator by changing the comma inside quotation marks (e.g., use ";" for semicolons or " " for spaces).
If you wish to apply this to a different range, replace A1:A10 with your desired cell references. The second parameter (TRUE) causes Excel to ignore blank cells in the list. If your Excel version is earlier than 2016 and TEXTJOIN is not available, you will need to use VBA or a combination of legacy formulas.
This formula-based approach is suitable in environments that restrict macros and works instantly as data is updated. However, note that for very large ranges, formulas may become slow or less responsive.
Transpose data from a column into a single cell with Kutools for Excel
If you have Kutools for Excel installed, its dedicated Combine feature allows you to efficiently merge data from columns, rows, or custom ranges into a single cell, all while offering an intuitive interface and flexible options for customizing delimiters and output location. This method suits users who need frequent data aggregation and want a straightforward, non-formula solution—no coding required.
Once you have Kutools for Excel installed and active, follow these steps:
1. Select the data column you wish to aggregate into one cell.
2. On the Excel ribbon, go to Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data. Refer to the screenshot for interface guidance:
3. In the pop-up dialog box, select Combine into single cell under To combine selected cells according to the following options. You may then choose a separator for the combined results (comma, space, semicolon, etc.), or enter a custom delimiter that suits your needs. See screenshot for reference:
4. Click Ok or Apply. Kutools will immediately merge your selected column values into one cell according to your specified settings. The process is quick, even for large datasets, and your original data remains unchanged. Example result:
 |  |  |
Download and free trial of Kutools for Excel Now!
Kutools for Excel makes this type of data handling much easier, especially for users who regularly combine, reformat, or clean up tables. You can experiment with different separator settings to match your report requirements, and the tool works reliably even when processing thousands of cells. If any problems arise (such as the tool not activating), ensure Kutools is properly installed and updated, and that you have selected the correct range before running the action.
While Kutools streamlines merging tasks and preserves source data integrity, be aware that the merged result is static: if you update your original data later, the combined cell will not synchronize unless you repeat the combine action.
Transpose data from a column into a single cell with a User Defined Function
Aside from combining list values with Excel's Concatenate function, you can utilize a User Defined Function (UDF) written in VBA to quickly merge and transpose a column's values into one cell. This method is especially efficient and flexible if your list is long or you need custom separators between items. It is a practical choice for users who are comfortable with enabling macros and want a repeatable solution for future tasks.
1. Press ALT + F11 together to open the Microsoft Visual Basic for Applications editor window.
2. In the VBA window, click Insert > Module to create a new module, and then copy and paste the following code into the blank module:
VBA code: Transpose data from a column into a single cell
Function transposeRange(Rg As Range)
'update by Extendoffice
Dim xCell As Range
Dim xStr As String
For Each xCell In Rg
If Not IsEmpty(xCell.Value) Then
xStr = xStr & xCell.Value & ","
End If
Next
transposeRange = Left(xStr, Len(xStr) - 1)
End Function
3. Save your changes and close the VBA editor. Return to your worksheet. In your target cell, enter the formula: =transposerange(A1:A10). Press Enter to apply the formula. This will join the values from A1 to A10 into one single cell, separated by commas (or any delimiter you choose). Refer to the screenshot below for an example of the result:
Note: In this code, A1:A10 refers to the desired column range. You can adjust the range as needed. If you prefer a separator other than a comma—for example, a semicolon, space, or dash—modify the portion xStr = xStr & xCell.Value & ",". Simply replace the comma with your preferred character(s). When using VBA UDFs, ensure that macros are enabled for your workbook. If your list contains blank cells, those will still be included; if you wish to exclude blanks, further changes to the code may be required.
If you encounter a #NAME? error after entering the formula, check that you copied the code correctly, saved the file as a macro-enabled workbook, and the macro is available in the current workbook. Also, you may need to accept macro security alerts when opening/writing the VBA code.
This VBA approach offers flexibility in formatting the results, but note that it is not suitable for workbooks shared online or with macro restrictions and, in some cases, may not update automatically if data changes in the source range unless you force a recalculation. For one-off or automated tasks, this remains an efficient solution.
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