How to Alternate Values from Two Columns with an Excel Formula
Question details
The user wants to populate a single column by automatically alternating values taken from two distinct columns, row by row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating data or populating invoice rows by interlacing values from columns D and E into column B without manually copying and pasting.
- Observed behavior
- The user needs a dynamic array formula or a drag-down function to transform a 2D range of two columns into a single, alternating continuous column.
Ensure your source data in the two columns is properly aligned and free of merged cells, as merged cells can cause alignment issues and calculation errors when applying array or row-based formulas.
Use the TOCOL Function to Spill Values Automatically
The TOCOL function is the most efficient and modern method to flatten a 2D array into a single column, automatically reading values row by row.
This function is available in modern spreadsheet versions (like Microsoft 365 and the latest WPS Office updates). It eliminates the need to drag formulas down because it naturally 'spills' the results into adjacent empty cells.
Click on the first cell in your target column (for example, B2) where you want the alternating data to begin.
Type the formula =TOCOL(D2:E5) into the formula bar. Adjust the range D2:E5 to match the exact boundaries of your source data.
Press Enter. The values from columns D and E will instantly spill down column B in an alternating row-by-row sequence.
Apply the INDEX Formula for Fill-Down Scenarios
If you are using an older spreadsheet version that lacks array-spilling capabilities, combining INDEX with QUOTIENT and MOD is a robust alternative.
Use the INDIRECT Function to Alternate Row References
You can also use the INDIRECT function combined with basic division to construct dynamic cell references as text and convert them into actual cell values.
Consolidate and Alternate Data Effortlessly in WPS Office
WPS Spreadsheet provides robust support for advanced array functions and mathematical formulas, making it easy to alternate, consolidate, and analyze data from multiple columns seamlessly.
- 1. Download and Install WPS Office: Get the free version of WPS Office from the official website and open the WPS Spreadsheet application.
- 2. Input Your Source Data: Enter or paste your original two-column data into columns D and E of a blank worksheet.
- 3. Type the Formula: Select the top cell of your destination column (e.g., B2) and input the flattening formula =TOCOL(D2:E5).
- 4. Verify Alternating Data: Hit Enter and watch as WPS Spreadsheet instantly evaluates the array and populates the column with your alternated values.
- 5. Save in Excel Format: Go to Menu > Save As, and choose the standard .xlsx format so your array formulas remain perfectly compatible across platforms.
Frequently Asked Questions
Why is my TOCOL function returning a #NAME? error?
The #NAME? error typically occurs if you are using an older version of your spreadsheet software that does not support modern dynamic array functions like TOCOL. If you encounter this, switch to the INDEX and MOD formula solution provided above.
Can I alternate values from three columns instead of two?
Yes. If you are using the TOCOL function, simply expand your highlighted range to cover three columns (e.g., =TOCOL(C2:E5)). If using the INDEX method, you will need to adjust the divisor in your QUOTIENT and MOD functions from 2 to 3 to account for the extra column.
How do I remove empty cells when alternating column data?
You can utilize the second argument of the TOCOL function to ignore blanks. By typing =TOCOL(D2:E5, 1), the formula will skip any empty cells in the range and only stack cells that contain actual data.
Why does my INDIRECT formula break when I insert new rows above my data?
The INDIRECT method relies on hardcoded math based on the current row number (like ROW()/2+1). Inserting or deleting rows above your formula changes the row number of your target cells, which breaks the mathematical alignment. Using the TOCOL or INDEX methods offers more stability.
