To merge columns in Excel with Power Query, load your data into the Power Query Editor, select the columns, then choose Transform > Merge Columns. Pick a separator, enter a name for the result, and select OK; when you close and load the query, Excel returns the combined column and can rebuild it when the source data changes.
What merging columns does
Power Query’s Merge Columns command combines values from two or more columns in the same row into one text value. For example, you can combine First Name and Last Name to create a Full Name column, or join a product code and size with a hyphen. The result is created inside the query, so the same transformation can be applied again when you refresh the data.
There is an important distinction between merging columns and merging queries. Merge Columns combines fields across a row. Merge Queries matches rows between separate tables using one or more corresponding fields, much like a database join. If your goal is to append a first and last name, use Merge Columns. If your goal is to bring a customer’s region from another table into a sales table, use Merge Queries.
Prepare the source table
For a reliable result, put the source data in a structured Excel table with a header row. Select a cell in the range and choose Data > From Table/Range. If Excel asks whether the table has headers, confirm the correct setting. The Power Query Editor opens with the table’s columns and a sequence of recorded transformation steps.
Suppose a table has First Name, Last Name, and Department columns. The aim is to combine the first two into Full Name while leaving Department unchanged. Before merging, check that the values are in the expected columns, that the headers are clear, and that the source does not contain unexpected title rows or totals. Those issues are easier to correct before building additional steps.
Merge columns step by step
- In the Power Query Editor, select the first column header you want to combine.
- Hold Ctrl on Windows and select each additional column header. For adjacent columns, you can also select the first header and Shift-click the last header. Confirm the columns are in the desired left-to-right order in the table.
- Choose Transform > Merge Columns. The command is available when the selected columns can be combined.
- In the Merge Columns dialog, choose a separator such as Space, Comma, Semicolon, Dash, or Custom. For a custom separator, type the exact characters to insert between values.
- Enter a useful name in the New column name box, such as Full Name or Item Code.
- Select OK. Power Query replaces the selected columns with the combined column.
- Choose Home > Close & Load to return the result to Excel. If you want to choose the destination or load as a connection, use Home > Close & Load To.
For the example, selecting First Name and Last Name and choosing a space separator turns the row containing Maya and Chen into Maya Chen. The column order matters: if Last Name comes before First Name in the source, the combined result follows that order. Reorder the columns before merging if necessary.
Choose a separator that preserves meaning
The separator is part of the output, not merely a display setting. A space suits names, a comma and space can suit a city-and-state label, and a hyphen may suit a product identifier. Select None only when the values are meant to run together, as with some codes. Consider whether the combined text will later be read by people, split back into fields, or used as a key for matching records.
- Space: useful for names such as Maya Chen.
- Comma and space: useful for labels such as Portland, Oregon.
- Hyphen: useful for codes such as AB-204.
- Custom separator: useful when the required character is not listed, such as a slash or a pipe.
- None: appropriate only when adjoining values cannot create ambiguity.
A separator does not validate the combined value. If a name field already contains leading or trailing spaces, adding another space can produce an untidy result. Clean source values first, or use a later transformation to remove unwanted spaces.
Data types, blanks, and cleanup
Merging columns produces text, so treat the result as a text label rather than a number or date. This matters for identifiers with leading zeros: a value such as 007 may be changed or displayed differently if it has been interpreted as a number before the merge. Set identifier columns to the Text data type before combining them when the zeros are meaningful.
Blank and null values deserve attention. A null is not the same thing as an empty string, and the exact result can depend on the input values and transformation. Preview several representative rows, including rows where one or more fields are blank. If the output must always contain a particular marker, such as Unknown, replace or otherwise handle missing values before merging rather than assuming the separator will behave as a substitute.
For inconsistent text, select the relevant column and use Transform > Format > Trim to remove leading and trailing spaces. Transform > Format > Clean removes nonprinting characters. Apply cleanup selectively: changing the source fields may be inappropriate if the original formatting is meaningful. You can also set each column’s data type from the data-type icon in its header.
Keep the original columns when needed
The Merge Columns command replaces the selected columns with the result. If you need to retain First Name and Last Name for other analysis, duplicate them before merging. Select a column and use Add Column > Duplicate Column, repeating for each field you want to preserve; then merge the working copies. Another option is to keep the source table unchanged and create a separate query for the combined output.
This choice affects how useful the query remains later. A single Full Name field may be convenient for a report, but separate first and last names are often better for sorting, filtering, or matching records. Keep both forms when users need the original components or when downstream steps depend on them.
Refresh and load the result
Power Query records the merge as a step in the query rather than making a one-time edit to the source cells. When new rows are added to the source Excel table, use Data > Refresh All to update the loaded result. The query reapplies its steps to the current source data, including the merge. If the source columns are renamed or removed, however, the step may fail because it refers to the original column names.
By default, Close & Load commonly places the query output on a worksheet. Close & Load To lets you choose a table destination, a new or existing worksheet, or a connection-only load where available. Loading as a table is useful when people need to inspect the combined values; a connection-only query can be suitable when another query uses the result.
On Excel for Mac, Power Query is available in Microsoft 365, but the available sources and parts of the interface can differ from Windows and may depend on the Excel build. Look under Data for the Power Query import options and use the editor’s Merge Columns command once the query is open. In Excel for the web, query creation and editing options are more limited than in the Windows desktop app, so use desktop Excel if the editor or required command is not available.
Merge Columns compared with other methods
Power Query is a good choice when the output needs to be repeatable, when data is imported from files or systems, or when the same cleanup must run each time new data arrives. The transformation stays in the query and can be refreshed without manually filling a formula down again.
A worksheet formula may be more convenient for a small, live calculation that should update immediately as users edit cells. For example, a formula can join two cells with a separator, but that creates a worksheet calculation rather than a Power Query transformation. Excel also has a worksheet Merge & Center command; it combines cell layout for display and does not concatenate the values of several cells. It is not the tool for joining text.
Use Merge Queries when you need to match records across tables, not when you simply want to join fields in one row. Use Append Queries when you need to stack rows from similar tables. These commands address different shapes of data, even though their names can sound similar.
Common problems and fixes
- The result is in the wrong order: arrange the source columns in the desired order before selecting Merge Columns, then inspect the preview.
- The output has missing or doubled spaces: trim the source fields and check blank values before choosing a space separator.
- A leading zero disappears: set the identifier column to Text before the merge, then verify the preview.
- The command is unavailable: make sure the query is open in Power Query Editor and that at least two compatible column headers are selected.
- Refresh reports a missing column: check whether the source header changed, and update the relevant step or restore the expected header.
- The combined field cannot be split reliably: the separator may also occur inside the original values. Choose a less common delimiter or retain the original columns.
Always inspect the preview after merging and again after refreshing with new data. A transformation can be syntactically valid while still producing unsuitable labels if new source values have different spacing, missing fields, or unexpected characters.
Frequently Asked Questions
Does Merge Columns delete the original columns?
Yes. It replaces the selected columns with the new combined column. Duplicate the source columns first if you need to keep them.
Can I merge more than two columns?
Yes. Select two or more column headers in the Power Query Editor, choose Transform > Merge Columns, and select the separator and output name.
Will the merged column update when I add rows?
It will update when you refresh the query, provided the new rows are included in the source range or Excel table and the expected columns remain available.
Is Merge Columns the same as Merge Queries?
No. Merge Columns combines fields within rows. Merge Queries matches rows from separate tables using selected key columns.
Can I use a custom separator?
Yes. In the Merge Columns dialog, choose Custom and enter the character or text to place between the values. Check that it will not make the result ambiguous if you later need to split it.
Related
- Is Excel on iPad? Features, Limits, and Costs
- Invoice and Packing List Template in Excel: What to Include
- Invoice Order Template Excel: Setup and Key Details
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Page Break Dotted Lines: How to Turn Them OffTo hide page-break dotted lines in Excel for Microsoft 365 on Windows, select the affected worksheet, then go…
- Remove a Word in an Excel Cell With Find & ReplaceTo remove a word from an Excel cell, select the cell or range, press Ctrl+H, enter the word…
- Can You Make Columns in Word? A Quick GuideYes. In Microsoft Word for Microsoft 365 on Windows, select the text you want to format, then choose…
- Lock Track Changes in Word Online: What You Can DoYou can’t password-lock Track Changes directly in Word for the web. To make sure collaborators can’t turn tracking…
- Microsoft Excel Scenario Manager: Setup, Uses, and LimitsMicrosoft Excel Scenario Manager is a What-If Analysis tool for saving different sets of input values and comparing…
- Excel Named Range Scope Greyed Out? How to Change ItIn Excel for Microsoft 365 on Windows, a named range’s scope is fixed when the name is created,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.