To combine the contents of two Excel columns side by side, enter =A2&" "&B2 in a new column, then fill the formula down. For example, if A2 contains Avery and B2 contains Chen, the result is Avery Chen; use Paste Values afterward if you need a fixed result rather than a formula.
What “consolidate two columns” can mean
People use “consolidate” to describe several different spreadsheet tasks. Most often, they want to join the contents of corresponding cells in two columns—for example, first name and last name, city and state, or a product code and description. In that case, the values from row 2 are combined with each other, the values from row 3 are combined with each other, and so on.
Another possible meaning is stacking the contents of both columns into one longer list. For instance, putting all entries in column A first and all entries from column B beneath them is different from joining A2 with B2. A third meaning is combining records that match on a key, such as bringing a customer name into a sales table. Those tasks call for different tools, so identify the desired arrangement before changing the worksheet.
- Join side by side: combine cells on the same row, such as first name and last name.
- Stack vertically: place the values from both columns in a single longer column.
- Match records: use a shared identifier to bring related information together.
The steps below focus first on joining cells from two columns, the usual interpretation. Later sections cover stacking and explain why Excel’s Consolidate command is not generally the right tool for either task.
Join two cells with a formula
For a quick combination, add a blank destination column and enter a concatenation formula in its first data row. With the first value in A2 and the second in B2, use =A2&" "&B2. The space inside the quotation marks becomes the separator, so Avery in A2 and Chen in B2 produce Avery Chen. Replace the space with another delimiter if the data calls for it.
For example, with street name in A2 and apartment number in B2, you might use a comma and a space as the separator: =A2&", "&B2. With a hyphen between a department code and an item number, use =A2&"-"&B2. In each case, the formula joins the displayed cell values in the order written; it does not change the source cells.
After entering the formula, press Enter. Select the result cell and use its fill handle—the small square at the lower-right corner—to fill the formula down through the remaining rows. Excel adjusts relative references automatically, changing A2 and B2 to A3 and B3 on the next row. Check the final rows before relying on the results, especially if the source columns have different lengths or contain gaps.
Formulas are useful when source data may change: the combined result updates when either source cell changes. If your worksheet uses semicolons instead of commas in function arguments because of regional settings, that matters for functions such as TEXTJOIN; the ampersand formula above has no function-argument separator to change. Formula separators and number/date display can vary with regional settings.
Handle blank cells and extra spaces
The simple ampersand formula always inserts its separator. If A2 contains Avery and B2 is blank, =A2&" "&B2 leaves a trailing space after Avery. That invisible character can cause awkward sorting, matching, or exports. One way to remove leading and trailing spaces from the combined result is TRIM.
With the two source values in A2 and B2, enter =TRIM(A2&" "&B2). If A2 contains Avery and B2 is blank, the result is Avery without a trailing space; if the cells contain Avery and Chen, the result is Avery Chen. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. It does not remove every kind of invisible character, such as a nonbreaking space copied from a web page.
In Microsoft 365, TEXTJOIN offers a more direct option when empty cells should be skipped. With values in A2 and B2, enter =TEXTJOIN(" ",TRUE,A2,B2). If A2 is Avery and B2 is blank, the result is Avery; if both contain Avery and Chen, it is Avery Chen. The TRUE argument tells TEXTJOIN to ignore empty cells. This function is available in Microsoft 365 and newer Excel releases, including Excel 2019 and later.
“Empty” has a practical limitation: a cell containing a formula that returns an empty string or a cell containing spaces is not necessarily treated like a genuinely blank cell. If results still contain odd gaps, inspect the source values and clean them as needed. For a large, messy dataset, a deliberate cleanup step is often safer than assuming a join formula will repair inconsistent source text.
Fill the results and keep or remove the formula
Once the formula works in the first row, fill it down to the last record. You can drag the fill handle, or copy the formula cell and paste it into the destination range. Verify a few rows in the middle and at the bottom to make sure the references moved correctly and that no records were skipped.
Decide whether the destination should remain connected to the source. Keep the formulas if the original columns will continue to be updated and the combined text should stay current. If the combined column is a finished export or you plan to delete the source columns, replace the formulas with their current results:
- Select and copy the completed formula results.
- Choose the destination cells, then use Home > Paste > Paste Values.
- Check that the displayed text remains correct before deleting or moving the original columns.
Paste Values leaves the visible results as text or values rather than formulas that refer to the old columns. It is a useful safeguard before rearranging a worksheet, but it also disconnects the output from future source edits. Keep a copy of the original data until you have checked the converted column.
Stack two columns into one list
If you mean “put every value from both columns into one vertical list,” do not use a row-by-row join formula. For a short list, copy the first column’s values, paste them into the destination, and paste the second column below the last value. This preserves the entries as separate items rather than combining pairs.
In Microsoft 365 or Excel 2024, VSTACK can create a stacked result dynamically. With the first list in A2:A4 and the second in B2:B4, enter =VSTACK(A2:A4,B2:B4) in an empty cell. If A2:A4 contains Red, Blue, and Green and B2:B4 contains Gold, Silver, and Bronze, the formula spills six rows: Red, Blue, Green, Gold, Silver, Bronze. Make sure the cells beneath the formula are empty so the result has room to spill. VSTACK is available in Microsoft 365 and Excel 2024, not all older Excel versions.
For recurring work or larger tables, Power Query can reshape columns into a single list. In Excel for Microsoft 365 on Windows, select the source range and choose Data > From Table/Range. In Power Query Editor, select the two columns, choose Transform > Unpivot Columns, then remove the Attribute column if you only need the values. Choose Home > Close & Load to return the result to Excel. Unpivoting turns the selected columns into rows; review the query output for blank values and headers before using it. On Mac and in Excel for the web, Power Query availability and available commands can differ by version, so a formula or copy-and-paste method may be simpler there.
When matching records needs a different method
If the two columns belong to separate tables and should be combined only when an ID matches, joining cells by row position can attach the wrong information. For example, a customer list and an order list may be sorted differently. First match each record using a stable identifier, such as a customer ID, then combine the fields you need. Do not assume row 2 in one table corresponds to row 2 in another.
For straightforward row-by-row pairing, confirm that both columns describe the same records in the same order. If one list has been sorted independently, contains inserted rows, or has missing entries, align the records before combining them. A formula cannot determine that two unrelated rows refer to the same person merely because they occupy the same row number.
Why Excel’s Consolidate command is different
Excel has a feature named Consolidate, found on the Data tab, but its name can be misleading for this question. It is designed to summarize or combine corresponding data from ranges, commonly using operations such as Sum or Average. It is not a general text-joining command for putting the contents of two columns into one cell.
For joining text, use a formula such as the ampersand expression or TEXTJOIN. For stacking lists, use copy and paste, VSTACK in supported versions, or a Power Query reshape. Choosing the tool based on the data’s shape helps prevent a numeric summary from being mistaken for a combined text column.
Common problems to check
- Unexpected spaces: A blank source cell may still leave the delimiter behind. Try TRIM or TEXTJOIN, then inspect for spaces already present in the source.
- Values appear as dates or numbers: Concatenation combines displayed cell content, and formatting can affect what you see. If a number needs leading zeros or a date needs a particular format, make that representation explicit before joining rather than assuming the original cell format will carry over.
- Formula shows instead of a result: Check that the cell is not formatted as Text, that the formula begins with an equals sign, and that Show Formulas is not enabled. Re-entering the formula after changing a cell from Text to General may be necessary.
- Results stop updating: If you pasted values, the output is no longer linked to the source. If formulas remain but do not recalculate, check Excel’s calculation settings.
- Some rows are missing: Check the fill range and look for blank rows or uneven source lists. A formula filled only partway down will not create results for later records.
Before deleting either source column, inspect representative outputs, including rows with blanks, punctuation, long text, and unusual number formats. Combining columns is easy to undo while the originals remain, but deleting source data can make mistakes harder to recover.
Frequently Asked Questions
How do I combine two columns in Excel without a space?
With values in A2 and B2, use =A2&B2. It joins the cell contents directly, so check whether the end of the first value and start of the second need punctuation or a separator.
Can I combine two columns and delete the originals?
Yes. Create and check the combined results, copy them, and choose Home > Paste > Paste Values in the destination. Confirm the pasted output before removing the source columns.
How do I combine columns while ignoring blanks?
In Microsoft 365, with the values in A2 and B2, use =TEXTJOIN(" ",TRUE,A2,B2). It skips empty cells and inserts a space between nonempty values. Cells containing spaces are not the same as truly empty cells.
Can I combine two columns into one list instead of joining each row?
Yes. Copy one list below the other, use Power Query to unpivot the columns, or use VSTACK in Microsoft 365 or Excel 2024. These methods stack values vertically rather than joining pairs from the same row.
Does Data > Consolidate join text from two columns?
No. Excel’s Consolidate feature is intended to summarize corresponding ranges, often with a calculation such as Sum. Use concatenation for text in matching rows or a stacking method for one longer list.
Related
- Excel Cannot Insert Row: Causes and Fixes
- Excel Blue Line Print Area: What It Means
- Excel Automatically Splitting Text to Columns? Here’s Why
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- SUMIF Checkbox in Google Sheets: Sum Checked RowsIf your checkboxes are in A2:A20 and the values to add are in B2:B20, use =SUMIF(A2:A20,TRUE,B2:B20) to total…
- SUMIF Negative Values in Google Sheets: Formula & TipsTo add only negative numbers in Google Sheets, use =SUMIF(B2:B100,"<0"), replacing B2:B100 with the range containing your numbers.…
- How to Hide Page Breaks in Excel and Keep Print LayoutTo hide page-break lines in Excel for Microsoft 365 on Windows, open File > Options > Advanced, find…
- Insert a Section Into a Table of Contents in Microsoft WordTo insert a section into a table of contents in Microsoft Word, apply a built-in heading style to…
- Creating a Brochure in Word: A Practical GuideTo create a brochure in Microsoft Word, start with File > New and search for a brochure template,…
- Weekly Timesheet in Google Sheets: Setup and TipsYes. You can make a weekly timesheet in Google Sheets by listing each workday, entering start and end…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.