Yes. To combine values across columns on the same row, enter a formula such as =TEXTJOIN(" ",TRUE,A2:C2) in a blank cell and fill it down. To stack separate columns into one vertical list, use =VSTACK(A2:A10,B2:B10,C2:C10) in Microsoft 365 or Excel 2024. The right method depends on whether you want to join row values or append lists.
First decide what “consolidate” means
People use “consolidate columns” to describe two different tasks. One is joining the contents of several cells on each row—for example, combining first, middle, and last names into one full name. The other is placing values from several columns one after another in a single column—for example, turning three monthly lists into one longer list. These produce different results, so decide which arrangement you need before choosing a tool.
There is also an Excel command named Consolidate, but it generally summarizes matching data with calculations such as Sum or Average. It is not a general-purpose command for joining text or stacking lists. That distinction matters: choosing Data > Consolidate when you want one text value per row will not produce the result you expect.
- Join across each row: combine cells from A2, B2, and C2 into one result in row 2.
- Stack down one column: put all entries from A2:A10 first, then B2:B10, then C2:C10.
- Summarize matching ranges: use the Consolidate command when you need an aggregate such as a total.
Join values from columns on the same row
For a quick formula, select a blank cell in the first result row. With source values in A2:C2, type =TEXTJOIN(" ",TRUE,A2:C2). If A2 contains “Mira,” B2 is blank, and C2 contains “Chen,” the result is “Mira Chen.” The space inside the first pair of quotation marks is the delimiter; TRUE tells Excel to ignore empty cells.
Copy the formula down to apply it to the other rows. You can drag the fill handle at the lower-right corner of the selected cell, or select the destination range and use Home > Fill > Down. Relative references adjust as the formula is copied: the next row refers to A3:C3. Keep the formula in a separate column so it does not overwrite the source data.
Change the delimiter to suit the output. With city, state, and postal code in A2:C2, a comma and space can separate the values. With product codes or initials, you may want no delimiter at all.
With values in A2:C2, this formula joins nonblank entries with a comma and space: =TEXTJOIN(", ",TRUE,A2:C2). If the cells contain North, East, and West, the result is “North, East, West.”
TEXTJOIN is available in modern Excel versions, including Microsoft 365. If you have an older version without it, use the ampersand operator. With values in A2 and B2, the formula =A2&" "&B2 joins them with a space. Unlike TEXTJOIN with TRUE, this simpler formula does not automatically skip a blank cell cleanly: if B2 is empty, the result can contain a trailing space. You can use CONCAT as another option, but it does not add delimiters or ignore blanks in the same way as TEXTJOIN.
Choose a delimiter and handle blanks
A delimiter is the character or text Excel places between joined values. Common choices include a space, comma-space, hyphen, slash, or line break. The delimiter is part of the formula, so check what the receiving system expects before combining data. A comma-separated result may be convenient to read, but a comma inside a source value can make later splitting or importing more complicated.
TEXTJOIN’s second argument controls how empty cells are handled. TRUE skips cells that are blank; FALSE includes them, which can leave repeated delimiters when there are gaps. “Ignore empty cells” means truly empty cells are skipped. A cell containing a formula that returns an empty string may also behave as empty for this purpose, but a cell containing a space is not empty. If results seem to have unexplained gaps, check whether source cells contain spaces or formulas.
For an address, you might join street, city, and region with a comma, while a person’s name might use spaces. If a cell can contain a delimiter itself, consider whether the final output is meant for display or for data exchange. For data that must be reliably parsed later, keeping fields separate is often safer than joining them into a text string.
Stack several columns into one vertical list
To append separate column ranges into one continuous list, use VSTACK. With entries in A2:A4, B2:B4, and C2:C4, enter =VSTACK(A2:A4,B2:B4,C2:C4) in a blank cell. If column A contains Red, Blue, and Green; B contains Small, Medium, and Large; and C contains Round, Square, and Oval, the result is a single column in this order: Red, Blue, Green, Small, Medium, Large, Round, Square, Oval.
VSTACK spills its results into the cells below the formula. The formula goes in only the top cell of the output; do not copy it down. Make sure the cells where the results need to appear are empty. If they are not, Excel can show a #SPILL! error, and it will identify a blockage that you need to clear.
VSTACK is available in Microsoft 365 and Excel 2024. To omit blanks from the combined result, you can filter the stacked array. With the same source ranges, this formula stacks them and returns only nonblank entries: =FILTER(VSTACK(A2:A4,B2:B4,C2:C4),VSTACK(A2:A4,B2:B4,C2:C4)<>""). FILTER is available in Microsoft 365 and Excel 2021; VSTACK is still required for this particular formula, so the combined formula requires a version that includes VSTACK.
When the columns have different numbers of entries, VSTACK can still combine the ranges, but shorter arrays are padded with #N/A values to match the tallest array’s width. For single-column ranges of different lengths, check the output for padding and decide whether to adjust the ranges or filter out unwanted results. If your columns are all part of one rectangular range and you want to read it in a particular order, confirm the order with a small sample before using the result elsewhere.
Use Power Query for repeatable consolidation
Power Query is useful when you regularly receive updated files or need to repeat the same cleanup. It can append tables vertically, or merge columns within a row, and then load the result back into Excel. Unlike a cell formula, a query records transformation steps that you can refresh when the source data changes.
To append separate tables in Excel for Microsoft 365 on Windows, first format each source range as an Excel table using Insert > Table, and give the tables clear names. Then select Data > Get Data > Combine Queries > Append. Choose two tables or three or more tables, select the tables to append, and confirm. The appended tables should have matching column headings if you want corresponding fields to line up; Power Query matches columns by header name, not simply by their left-to-right position. Load the result to a worksheet, then use Data > Refresh All after updating the source tables.
For combining fields across a row in Power Query, load the source table with Data > From Table/Range. In the Power Query Editor, select the columns to join, then choose Transform > Merge Columns. Choose a separator and a new column name, and confirm. This creates a combined column in the query output. The exact availability or wording of Power Query commands can vary by Excel platform and build; Excel for the web and Mac may not offer the same editing experience as Excel for Windows.
Power Query is a good fit when you want a refreshable process, need to append many tables, or want to clean and reshape data as part of consolidation. For a one-time task involving a few cells, a formula is usually faster. Before removing or replacing source columns, keep a copy of the original data so you can verify the transformation.
What Excel’s Consolidate command actually does
The command under Data > Consolidate is designed to combine figures from multiple ranges using a function, such as Sum, Count, Average, Max, or Min. It can be useful when separate sheets contain similarly arranged numeric reports and you want a summary. It does not concatenate text strings and is not the right choice for making one long list from several columns.
For example, if several worksheets contain sales totals with matching row and column labels, Consolidate can summarize those values. If you need to combine names from three columns, use TEXTJOIN instead. If you need to append monthly records into one table, use VSTACK or Power Query. The word “consolidate” can refer broadly to any of these jobs, but Excel’s named command has a more specific summarizing purpose.
Common problems and how to fix them
- The formula appears as text: check that the cell is not formatted as Text, and that the formula begins with an equals sign. Change the format to General and re-enter the formula if needed.
- The output has extra spaces or separators: inspect the source cells for spaces, and set TEXTJOIN’s ignore-empty argument to TRUE when appropriate.
- You see #SPILL!: clear cells in the intended output area. Dynamic-array formulas need room to return their full result.
- You see #NAME? for VSTACK: your Excel version may not include the function. Use Power Query or a manual copy-and-paste approach, or use a version with VSTACK.
- Rows no longer match after sorting: sort the source table as a whole, not a single source column, or you may separate related values from their original records.
- The combined list contains duplicates: joining or stacking does not automatically remove duplicates. Remove them separately with Data > Remove Duplicates, or use UNIQUE in a compatible version if a dynamic result is appropriate.
Also consider whether you need a live result or a fixed value. Formula results update when their source cells change. To preserve a finished result as text, copy it and use Home > Paste > Paste Values. Keep the formula or source data elsewhere if you may need to revise the output later.
How to choose the right method
For a small, one-time job, use TEXTJOIN to combine values across each row, or VSTACK to stack ranges vertically if your Excel version supports it. Choose Power Query for recurring imports, many tables, or repeatable cleanup. Choose Data > Consolidate only when you need to calculate a summary from corresponding ranges.
Before consolidating, check that the data is structured consistently. Remove unintended blank rows, make sure headings are clear, and decide whether empty values and duplicates should remain. If you are combining lists from different worksheets, keep a source or category column when you need to know where each record came from. Once values have been flattened into one column, their original column identity may otherwise be lost.
Frequently Asked Questions
How do I combine multiple columns into one column in Excel?
To join cells on each row, enter TEXTJOIN in a new column, such as =TEXTJOIN(" ",TRUE,A2:C2), and fill the formula down. To append whole columns into one vertical list, use VSTACK in Microsoft 365 or Excel 2024.
How do I combine columns without spaces?
With values in A2:C2, use =TEXTJOIN("",TRUE,A2:C2). The empty delimiter adds nothing between values, and TRUE skips blank cells. Check that removing separators will not make the combined text hard to interpret.
Can I combine columns and keep the original data?
Yes. Put the formula in a separate, empty column or create a Power Query output. Neither approach requires you to delete the source columns. If you later copy and paste values, retain the original data until you have checked the result.
Does Data > Consolidate combine text columns?
No. Excel’s Consolidate command is intended to summarize corresponding ranges with functions such as Sum or Average. Use TEXTJOIN for text across a row, or VSTACK and Power Query to append lists vertically.
How do I combine columns and remove duplicates?
First combine or stack the values, then use Data > Remove Duplicates on a copied result if you want a fixed list. In versions with dynamic-array functions, UNIQUE can return distinct values from a range; review the result because removing duplicates may discard records that are meaningful in context.
Related
- Data Validation in Excel: Lists, Rules, and Fixes
- Excel Data Validation: Lists, Rules, and Setup
- Remove Data Validation Restrictions in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Double Space Text in Microsoft Word Step by StepTo double-space text in Microsoft Word, select the text—or press Ctrl+A on Windows or Command+A on Mac to…
- Excel Date Settings: Formats, Systems, and Common FixesExcel date settings control how dates are displayed, interpreted, and calculated. In Microsoft Excel for Microsoft 365 on…
- Why Can’t I Remove a Section Break in Word?You usually can remove a section break in Word, but it may be hard to see, select, or…
- Freeze and Unfreeze Panes in Excel: A Step-by-Step GuideTo keep headings or identifying columns visible while you scroll in Excel, select the cell just below the…
- How to Set Out of Office in New Outlook on WindowsIn New Outlook for Windows, select Settings > Accounts > Automatic replies, turn on automatic replies, write your…
- How to Embed a Video With Sound in PowerPoint SlidesTo embed a video with sound in PowerPoint, choose Insert > Video > This Device on Windows, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.