How to split cell values vertically in Excel?
When working in Excel, you may encounter situations where multiple values in a single row or multiple rows need to be converted into a vertical list. This is especially useful when data is stored in a horizontal format but needs to be processed or analyzed in a column format.
This guide will cover three efficient methods to convert cell values vertically:
Split cell values into rows vertically in Excel
- Using a Formula (Excel 365) – Best for dynamic and automatic transformations
- Using Kutools for Excel – A quick and easy solution with a specialized tool for All Excel versions
- Using VBA Code – Ideal for automating the process for All Excel versions
Comparison of Methods for Splitting Cell Values Vertically in Excel
When multiple values are stored in a single cell or across multiple cells, you may need to convert them into a vertical list for easier processing and analysis. The following table compares the three methods introduced in this article.
| Feature | Excel 365 Formula | Kutools Split Data to Rows | VBA Code |
|---|---|---|---|
| Split cell values into a vertical list | ✔ | ✔ | ✔ |
| Process multiple source cells | ✔ TEXTJOIN combines the source range | ✔ | ✔ |
| Support custom delimiters | ✔ Change the delimiter in the formula | ✔ Wide range of delimiters | ✔ Modify the delimiter in the code |
| Dynamic results when source data changes | ✔ Automatic | ✘ Re-run required | ✘ Re-run required |
| Requires formulas | ✔ | ✘ | ✘ |
| Requires VBA / coding | ✘ | ✘ | ✔ |
| Requires macro execution | ✘ | ✘ | ✔ |
| Choose a separate output location | ✔ Enter formula in a blank cell | ✘ Splits directly in the source area | ✔ Select an output cell |
| Keeps original source data unchanged | ✔ | ✘ Operates directly on source data | ✔ Outputs results to another location |
| Manual effort after setup | Low | Low | Moderate |
| Excel version requirement | Excel 365 | Excel 2010, 2013, 2016, 2019, 2021, and 365 with Kutools | Excel versions with VBA support |
| Setup process | Enter =TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",TRUE,A1:A4),";")) in a blank cell and press Enter. | Select the cells, choose Kutools > Merge & Split > Split Data to Rows, select the delimiter, and click OK. | Open the VBA Editor, insert a module, paste the code, run it, select the source range, and then select an output cell. |
| Best for | Excel 365 users who need dynamically updating results | Users who want a quick no-code method across different Excel versions | Advanced users who want an automated and customizable solution |
| Ease of use | Easy to Moderate | Very easy | Advanced |
Split cell values into rows vertically by a Formula (Excel 365)
If you have Excel 365, you can use the TEXTSPLIT, TEXTJOIN, and TRANSPOSE functions to convert cell values vertically without manual effort.
Please copy and paste the following formula into a blank cell, and press "Enter" key, Excel will automatically spill the data into multiple rows, see screenshot:
=TRANSPOSE(TEXTSPLIT(TEXTJOIN(";", TRUE, A1:A4), ";"))
- TEXTJOIN(";", TRUE, A1:A4 ): Joins the text in cells A1 to A4 into a single string, separated by semicolons (;).
- TEXTSPLIT(...): Splits the combined string back into separate values using the semicolon as the delimiter.
- TRANSPOSE(...): Converts the resulting vertical list of values into a horizontal row (or vice versa, depending on the original orientation).
✅ Pros:
- ✔ Dynamic Updates: If the source data changes, the formula automatically updates the results.
- ✔No Manual Effort: Eliminates the need for repetitive manual splitting.
- ✔Customizable Delimiters: Replace the semicolon (;) with any delimiter (e.g., comma, space) to suit your data.
❌ Cons:
- ✘ Only available in Excel 365 , does not work in older Excel versions.
Split cell values into rows vertically by Kutools for Excel
If you use Excel 2019 or earlier, you can use "Kutools for Excel" – a powerful add-in with specialized tools to transform and manage data easily. With its "Split Data to Rows" feature, you can seamlessly convert cell values vertically based on any delimiter you need.
After installing "Kutools for Excel", please do as this:
- Click "Kutools" > "Merge & Split" > "Split Data to Rows", see screenshot:
- In the "Split Data to Rows" dialog box, specify the following operations:
- Select the cells that you want to split;
- In the "Delimiter" section, select the delimiter to define how the cell content should be split. Here, I will select "semicolon".
- Finally, click "OK" button.
Result: The data will be converted into a vertical column.
✅ Pros:
- ✔ Works in all Excel versions (2010, 2013, 2016, 2019, 2021, 365)
- ✔No need for complex formulas or coding.
- ✔Supports a wide range of delimiters.
- ✔Kutools offers over 300 tools for data manipulation, saving time on other tasks.
❌ Cons:
- ✘ Requires Kutools installation
Split cell values into rows vertically by VBA code
For advanced users, VBA provides a robust solution to automate the vertical splitting of cell values. The VBA code below enables you to effortlessly split cell contents into rows using any custom separator.
1. Hold down the "ALT" + "F11" keys to open the "Microsoft Visual Basic for Applications" window.
2. Click "Insert" > "Module", and paste the following code in the "Module" Window.
VBA code: Split cell values into rows vertically by specific separator
Sub splitvertically()
'updatebyExtendoffice
Dim xRg As Range
Dim xOutRg As Range
Dim xCell As Range
Dim xTxt As String
Dim xStr As String
Dim xOutArr As Variant
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xOutRg = Application.InputBox("please select output cell:", "Kutools for Excel", , , , , , 8)
If xOutRg Is Nothing Then Exit Sub
For Each xCell In xRg
If xStr = "" Then
xStr = xCell.Value
Else
xStr = xStr & ";" & xCell.Value
End If
Next
xOutArr = VBA.Split(xStr, ";")
xOutRg.Range("A1").Resize(UBound(xOutArr) + 1, 1) = Application.WorksheetFunction.Transpose(xOutArr)
End Sub
3. Then press "F5" key to run this code, and a prompt box will pop out to remind you select the cell values that you want to split vertically, see screenshot:
4. And then click "OK" button, another box will pop out to remind you select a cell to put the result, see screenshot:
5. Click "OK" button, and the cell values have been split into a list by a separator, see screenshot:
✅ Pros:
- ✔Automates the process, saving time
- ✔Works in all Excel versions
- ✔Can be customized for different delimiters
❌ Cons:
- ✘ Requires basic knowledge of VBA programming
- ✘ Slightly complex for beginners
Converting cell values vertically in Excel can be achieved through multiple methods, each with its own strengths. By understanding these methods, you can choose the best approach for your specific needs and optimize your workflow in Excel. Whether you’re a beginner or an advanced user, these tools and techniques will help you handle data more effectively and efficiently. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master 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