How to Move Every Other Row to Columns in Excel?
When working with data in Excel, especially imported or unstructured datasets, you may often find names and associated values arranged vertically in a single column. To make this data more readable and usable, it’s often necessary to convert it into a tabular format — moving every other row into columns.
Move every other row to column with formulas
This is a straightforward method that uses Excel's INDEX function to extract alternating rows and display them as columns. If you want to convert the names into one column and the ages into the next column. Please apply the following formulas:
1. In Cell C2, Enter the following formula into a blank cell, then drag the formula down as needed. This formula retrieves the value from every second row starting from row 2 (names).
=INDEX($A:$A, (ROW(A1)-1)*2 + 2)
2. In Cell D2, enter the below formula to extract the alternative data, drag the formula down to fill the cells.
=INDEX($A:$A, (ROW(A1)-1)*2 + 3)
- INDEX($A:$A, ...): Looks in Column A and returns the value from a specific row number.
- ROW(A1): Returns the current row number. In A1, it returns 1. When you drag the formula down, it becomes A2 (row 2), A3 (row 3), etc.
- (ROW(A1)-1)*2 + 2: This calculates the row number to pull from Column A.
So the formula pulls values from A2, A4, A6, A8…, which means every second row (starting from row 2).
Move every other row to column with Kutools for Excel
If you prefer a more user-friendly, non-formula solution, Kutools for Excel offers a feature that allows you to transpose rows into columns effortlessly.
After installing Kutools for Excel, please do with the following steps:
- Click Kutools > Range > Transform Range.
- In the Transform Range dialog, set the following operations:
- Select the range you want to move every other row
- Check Single column to range in Transform Type section,
- And then, check Fixed value then select or enter 2 in the list in Rows per record section.
- Finally, click OK button.
- In the following prompt box, select a single cell to put out the result, see screenshot:
- Click OK, Kutools will automatically move every other row into separate columns.
Move every other row to column with VBA code
If you often need to perform this task, a VBA macro can automate the process, making it faster and more efficient.
1. Press F11 + Alt keys together on the keyboard to open the Microsoft Visual Basic for Applications window.
2. Then click Insert > Module to insert a new module window. And then copy the follow VBA code to the window.
VBA: Move every other row to column.
Sub MoveRange()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set InputRng = InputRng.Columns(1)
For i = 1 To InputRng.Rows.Count Step 2
OutRng.Resize(1, 2).Value = Array(InputRng.Cells(i, 1).Value, InputRng.Cells(i + 1, 1).Value)
Set OutRng = OutRng.Offset(1, 0)
Next
End Sub
3. Click Run button or F5 key on the keyboard, and a dialog pops out for you to select a range to move. See screenshot:
4. Then click OK to show the other dialog to select a cell to output the result. See screenshot:
5. Click OK, and you can see the result shown as below:
🎯 Conclusion:
Each method for moving every other row to columns in Excel has its own advantages, depending on your needs:
- Formula Method: Best for quick, one-time tasks with small datasets. It requires no additional tools but may slow down with large data ranges.
- Kutools for Excel: Ideal for users who prefer a simple, click-based solution without complex formulas or coding. Kutools provides a user-friendly interface for quick transformations.
- VBA Macro: The best choice for automation and repetitive tasks, especially with large datasets. Requires basic coding knowledge but offers maximum flexibility.
Choose the method that suits your needs, whether you're working with a small set of data or need a more scalable solution for larger datasets. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.
Relative 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