To stack values from several Excel columns into one vertical list, use =TOCOL(A2:C100,1,TRUE) in Microsoft 365 or Excel 2024; it reads the selected columns in order and skips blank cells. If you mean joining cells across each row into a single text value, use TEXTJOIN instead—the right method depends on what “one column” should contain.
What “consolidate columns” can mean
People use “consolidate” for two different spreadsheet tasks. The first is stacking: take the entries in several columns and place them one below another in a single column. For example, separate lists of products in columns A, B, and C become one longer product list in column E. This is usually what someone means when they want to consolidate columns into one column.
The second task is combining across a row: join the contents of cells A2, B2, and C2 into one text value in D2, such as a full name or a formatted address. That creates one combined value per row, not one long list. Excel’s Data > Consolidate command is a third, different feature: it summarizes numeric data from multiple ranges. It does not simply stack columns. Identifying the desired result first avoids choosing a tool that produces the wrong shape of data.
Stack columns with TOCOL
For adjacent columns, TOCOL is a concise way to create a dynamic list. It is available in Microsoft 365 and Excel 2024. Enter the formula in an empty cell outside the source data, such as E2, and Excel spills the results downward into as many cells as needed.
With values in A2:C100 and headers in row 1, enter =TOCOL(A2:C100,1,TRUE). The second argument, 1, tells Excel to ignore blanks. The third argument, TRUE, tells it to scan by column. So it returns the nonblank entries in A2:A100 first, then those in B2:B100, then those in C2:C100. For example, if A2:A3 contains “Pear” and “Plum,” B2 contains “Apple,” and C2 contains “Kiwi,” the result is Pear, Plum, Apple, Kiwi, each in its own output row.
Check the range before using the formula: include the data rows, not the header row, and avoid selecting entire columns unless you have a reason to. A large range can make a workbook slower or make it harder to see where the results end. Leave the cells below the formula clear; if another value blocks the spill area, Excel displays a #SPILL! error. The formula itself remains in the top cell, and the results update when source values change.
Use VSTACK for selected ranges
If the source columns are not next to each other, or you want to choose a particular order, use VSTACK. It places one range beneath another. For example, if the first list is in A2:A5 and the second is in C2:C5, enter =VSTACK(A2:A5,C2:C5) in an empty cell. The result contains the four values from column A followed by the four values from column C. This formula is available in Microsoft 365 and Excel 2024.
VSTACK is useful when the lists are separated by unrelated fields, or when you want to append only specific columns. You can put the ranges in any order in the formula to control which list comes first. Keep in mind that VSTACK does not remove duplicates: if “Pear” occurs in both source ranges, it appears twice in the output. It also does not, by itself, filter out blank rows. If blank entries matter, use TOCOL with its ignore-blanks option for a suitable rectangular range, or remove the blanks in Power Query.
When stacking ranges with different numbers of columns, VSTACK fills the extra positions in shorter rows with #N/A. That issue does not usually arise when stacking single-column ranges, but it matters if you later use the same function to append tables with different widths. For a one-column result, select single-column source ranges and inspect the output for unexpected errors or blank entries.
Join cells across each row instead
If your goal is one combined text value per record, use TEXTJOIN. It can insert a separator and optionally ignore empty cells. For example, with a first name in A2 and a surname in C2, enter =TEXTJOIN(" ",TRUE,A2,C2) in D2. If A2 contains “Ava” and C2 contains “Chen,” the result is “Ava Chen.” Copy the formula down to combine the corresponding cells on each row.
You can use a comma and a space as the separator for address parts, or join a larger row range with a separator such as a space. Decide whether blanks should be ignored: with the TRUE argument, empty cells do not create extra separators. TEXTJOIN combines values into a text string; it does not preserve each source value as a separate row. If you need to sort, filter, or count the original items individually, stack them rather than joining them.
For a permanent combined value rather than a live formula, copy the formula results and use Home > Paste > Paste Values. On Windows, Ctrl+C copies the selection; on Mac, use Command+C. Paste Values is available from the Home ribbon’s Paste menu on both platforms, though the keyboard and menu presentation can differ. Keep a copy of the source data if you may need to revise the result later.
Consolidate columns with Power Query
Power Query is a good choice for repeatable cleanup, larger datasets, or a process you will refresh when new data arrives. To reshape several columns from one table into a single list, first make sure the range has clear headers. Select a cell in the data and choose Data > From Table/Range. If Excel prompts you to create a table, confirm that the table has headers when that is accurate.
- In the Power Query Editor, select the columns whose values you want to collect.
- Choose Transform > Unpivot Columns. Power Query creates an Attribute column containing the former headers and a Value column containing the entries.
- If you need only the list, remove Attribute by right-clicking its header and choosing Remove. Rename Value if a clearer output heading is useful.
- Choose Home > Close & Load to return the result to Excel.
Unpivoting is especially helpful when the original column headers identify categories or periods and you want a normalized list. If you remove Attribute, those labels are discarded, so keep that column when you need to know which source column each item came from. Power Query can also filter null or unwanted rows in the editor. After the source table changes, refresh the query to rebuild the output; it is not the same as a formula that recalculates immediately in each cell.
Why Data > Consolidate is different
Excel’s Data > Consolidate feature combines information from ranges by applying a summary function, such as Sum or Average. It is intended for tasks such as adding corresponding monthly totals from several worksheets, not turning several lists into one longer list. The ranges generally need a meaningful relationship by position or labels for the summary to make sense.
For example, if three departments each have a quarterly sales total in the same labeled layout, Consolidate can summarize corresponding figures. If you instead have names in three columns and want all names in one list, use TOCOL, VSTACK, or Power Query. The word “consolidate” in the command name can make this confusing, but the result and purpose are different.
Choose the right method and check the result
- Use TOCOL for a quick, dynamic list from a rectangular range, especially when you want to skip blanks and preserve column-by-column order.
- Use VSTACK when combining selected ranges in a chosen sequence or when the source columns are not adjacent.
- Use TEXTJOIN when values from the same row should become one text string, such as a full name.
- Use Power Query when the transformation is part of a repeatable import or cleanup process that you will refresh.
- Use Data > Consolidate for summaries of related numeric ranges, not for making a simple vertical list.
Before relying on the output, compare its item count with the nonblank source entries and check that the order is appropriate. Look for duplicates, values that appear blank but contain spaces, and numbers or dates that have changed display. If blank-looking cells contain spaces, Excel may treat them as text rather than empty cells. Clean those entries first if they should be omitted. Also decide whether duplicates are meaningful: stacking preserves them, which is often right for transaction records but may not be right for a unique customer list.
Common problems and version notes
A #NAME? error can mean the Excel version does not recognize TOCOL or VSTACK. These functions are available in Microsoft 365 and Excel 2024, but not in every older release. In an older version, use Power Query, or copy each source column and paste it below the previous one. Manual stacking works for a one-time task, but it will not update automatically when the source lists change.
A #SPILL! error usually means cells in the intended output area are occupied, merged, or otherwise preventing the dynamic result from expanding. Clear the obstructing cells or move the formula. If the formula returns values in an unexpected order, check the scan direction: TOCOL’s default scans by row, while the third argument TRUE scans by column. To put every entry from column A first, then every entry from column B, use column scanning.
On Excel for the web, dynamic-array formulas such as TOCOL and VSTACK are supported in Microsoft 365, but the exact menus and Power Query options can differ from the Windows desktop app. On Mac, formulas use the same function names and comma separators in typical English-language settings; keyboard shortcuts differ, as noted above. Regional settings can use a different formula argument separator, so follow the separator Excel inserts in your installation if commas are rejected.
Frequently Asked Questions
How do I combine two columns into one list in Excel?
In Microsoft 365 or Excel 2024, enter =TOCOL(A2:B100,1,TRUE) in an empty cell to stack the two columns and ignore blanks. Adjust the range to match your data and leave enough clear cells below for the spilled results.
How do I combine columns without losing the original data?
Put a formula in a separate, empty output column or use Power Query to load the result to a new worksheet. Both approaches leave the source columns intact. Avoid deleting the source until you have checked that the output contains the items you need.
Can I combine columns and remove duplicates?
TOCOL and VSTACK retain repeated entries. If you have Microsoft 365 or Excel 2021 or later, you can wrap a list formula in UNIQUE; for example, =UNIQUE(TOCOL(A2:C100,1,TRUE)) returns distinct values from the stacked columns. Check whether repeated entries represent errors or valid separate records before removing them.
How do I put several cells into one cell on each row?
Use TEXTJOIN, such as =TEXTJOIN(" ",TRUE,A2:C2), to combine the row’s values with spaces while ignoring empty cells. Fill the formula down for additional rows. This produces one text value per row, not a vertical list of individual values.
Why does Excel say #SPILL! when I use TOCOL?
The formula needs to place its results in cells below the formula cell. If any of those cells contain data or are merged, Excel cannot create the full output. Clear the blocked area or move the formula to a location with enough room.
Related
- Excel Circular Reference Greyed Out? Find the Cause
- Excel Named Range Scope Greyed Out? How to Change It
- Excel Cannot Insert Row: Causes and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Budget Tutorial: Build a Monthly PlanTo make a monthly budget in Google Sheets, enter your take-home income, list spending categories, and add columns…
- Remove Duplicates in Excel: A Practical Step-by-Step GuideTo remove duplicate rows in Excel for Microsoft 365 on Windows, select your data, choose Data > Remove…
- Import CSV to Outlook: Contacts, Steps, and TroubleshootingYes. To import a CSV contact list into Outlook, open People or Contacts and choose Manage contacts >…
- How to Copy an Excel File to WordTo copy Excel content into Word, select the cells in Excel, press Ctrl+C, switch to Word, and press…
- Copying Text from Excel to Word: Formatting, Links and TablesTo copy cells from Excel into Word, select the cells, press Ctrl+C in Excel, place the cursor in…
- Excel Solver: Find Numbers That Sum to a Value?Yes. Excel Solver can choose numbers from a list that add up to a target: give each number…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.