Merge Cells in Excel: 4 Easy Ways and Tips

Contents:

• Effective Use of the Combine Button in Excel
• Effective Use of the CONCATENATE Function in Excel
• The CONCATENATE Function: Effectively Combining Cells in Excel
• A Complete Guide to the CONCATENATE Function in Excel

Efficient use of the "Merge" button in Excel

This function is ideal for formatting tables, allowing you to improve them appearance. For example, you can use it to create a common header for several columns.
It is important to remember that when merging cells, only the value of the first cell is preserved, and the data from other cells is deleted.
Let's consider a practical example: you have a table with a catalog of cars, and you want to format the header for the columns with characteristics, removing unnecessary empty cells.

First, select the cells above the columns containing the car characteristics: year of manufacture, engine size, gearbox, drive, steering wheel.

On the Home tab, select the "Merge Cells" or "Merge and Center" button, depending on the desired visual result.

As a result, you will get one cell instead of five. Enter a title and change the design as desired.
Now you have a title "Car Specifications".

Similarly, we will remove the extra empty cell above the "Make, model" cell. To do this, select them simultaneously and click the "Merge and Center" button.

Now the table looks like this:

To unmerge cells, select them and click the "Unmerge Cells" button. You can select the entire table, and Excel will automatically find the merged cells and unmerge them.

Now the table looks like this. You can make changes and merge the cells again.

In addition, Excel has a "Merge by Rows" button. It allows you to merge cells row by row. For example, if you have a range of three columns and seven rows, you can combine them into a single column while maintaining the same number of rows.

The result is a table with one column, while the number of rows remains the same.

There are several additional methods for merging cells while preserving all the data in them. These functions collect the values ​​from the selected cells into a single blank cell.
For example, you might want to combine all the car specifications into one string to use to create a receipt. Let's look at how to do this using three functions.

Efficient Use of the CONCATENATE Function in Excel

The CONCATENATE function is ideal for combining a small group of cells while preserving all the data. This can be useful when you need to quickly gather information from different sources in one place.
The main feature of using the CONCATENATE function is that each cell must be specified separately as an argument. Unfortunately, it is not possible to select a large range of cells at once, which can slow down the process, especially when working with large tables.
The function combines data from the selected cells into a resulting cell. In this case, it is often necessary to additionally format the result - add spaces, punctuation marks, or text labels. These changes will have to be made manually in the formula bar.
To start using it, select the cell in which the function will collect values. For example, create a new column "Receipt Data" and select the first cell to enter the formula.

Open the window for building the function. To do this, you can use one of two methods: go to the Formulas tab and click Insert Function, or click the fx icon in the formula bar in any table tab.

In the Formula Builder window that opens, use the search bar to find the CONCATENATE function and click "Insert function".

After that, a window for entering the function arguments will appear. Specify "Text 1," "Text 2," and so on for the cells you want to combine. For example, if you have five cells, add three additional arguments using the + button.

Fill in the function arguments one by one, selecting values ​​from the table. For example, for "Text1", select the cell with the year of manufacture (B4).
The order of actions for specifying values ​​is as follows:
The selected value will automatically be transferred and displayed in the link bar as fx=CONCATENATE(B4). Repeat the process for the remaining arguments: "Text2" is the value of the "Volume" cell, "Text3" is "Gearbox", "Text4" is "Drive", "Text5" is "Steering Wheel".
As a result, your function will look like this: fx=CONCATENATE(B4; C4; D4; E4; F4). Click "Done."

The function successfully merged the data from all cells, but spaces and punctuation were not added automatically. You will need to enter them manually in the formula bar.
Add commas and spaces between argument values ​​using the following format: ‘, ‘;.

As a result, the function will look like this: fx=CONCATENATE(B4; «, «; C4; «, «; D4; «, «; E4; «, «; F4). Press Enter.

In the same way, you can add not only spaces and punctuation marks, but also entire phrases or words for more informative output.
For our example, you can change the function like this: fx=CONCATENATE(«year of manufacture «; B4; «, volume «; C4; «, gearbox «; D4; «, drive «; E4; «, steering wheel «; F4). Don't forget to include all the necessary spaces between the data.

After that, you need to drag the resulting formula down the column to automatically combine the characteristics of all other cars, ensuring data consistency.

CONCAT Function: Efficiently Combine Cells in Excel

The CONCAT function is ideal for combining large ranges of cells, keeping all the data in a single cell. This is especially useful when working with large amounts of information when a compact presentation of data is required.
The function allows you to select an entire range of cells, however, as with the CONCATENATE function, the combined values ​​will be stuck together without any separators.
There are two ways to add separators to the resulting cell. The first method is to specify separators directly in the formula window after each cell. The second method is to manually enter them in the reference bar.
To use the CONCAT function, similar to the CONCATENATE function, select the cell where the combined value will be placed. Then open the formula window and find the CONCAT function.

In the opened formula builder window, the argument "Text 1" will appear, where you need to specify the range of cells to combine. For example, select all the cells with car characteristics and click "Done".
The selected range will appear in the formula reference line: fx=CONCATENATE(B4:F4).

As in the previous case, the function combined the data from all the cells, but they were glued together. To add spaces and punctuation marks in the final result, you will need to enter each argument separately, combining them with separators.
Return to the function building window and change the arguments as follows: "Text1" is the value of the "Year of Production" cell, "Text2" is ‘, ‘, "Text3" is the value of the "Volume" cell, "Text4" is ‘, ‘. Continue to the last cell, alternating values ​​​​and separators.
After that, click "Done". The final formula will look like this: fx=CONCATENATE(B4; ‘, ‘; C4; ‘, ‘; D4; ‘, ‘; E4; ‘, ‘; F4), and the data will be separated by commas and spaces.

To add not only commas and spaces, but also text elements, you need to specify them in the intermediate arguments of the function. For example, let's change the data to: "Text2" — ', volume', "Text4" — ', gearbox', "Text6" — ', drive', "Text8" — ', steering wheel'.

As a result, the function will return a value where all data will be separated by additional words. The formula will look like this: fx=CONCATEN(B4; ', volume '; C4; ', gearbox '; D4; ', drive '; E4; ', steering wheel '; F4).

As in the previous method, changes to the formula can be made directly in the link bar, but using the formula building window is more convenient - this reduces the likelihood of errors.
Drag the resulting value down to the end of the table - the function will combine the characteristics of all other cars according to the same principle.

A Complete Guide to the CONCATENATE Function in Excel

The CONCATENATE function is ideal for combining large ranges of cells, allowing you to keep all the data in one place. This is especially useful when you need to process large amounts of information and present it in a more readable format.
To use this function, select the cell where all the values ​​will be collected. Open the function builder and find the CONCATENATE function.
A formula builder window will appear, where you will need to specify several parameters. Let's take a closer look at each of them.

The first parameter is "Separator." This is the value that will be inserted between the merged cells. It will be the same for all cells. For example, you can use a comma and a space: «, «.

The next parameter, "Skip_empty", determines how the function will handle empty cells. Possible options: enter 1 (TRUE) to ignore empty cells and 0 (FALSE) to include them.

• 1 (TRUE) — empty cells are ignored, separators are not duplicated.
• 0 (FALSE) — empty cells are included, separators are inserted.

In our case, there are no empty cells, so we can set the value to 1. This will avoid unnecessary separators.

The last parameter is "Text1". Here you specify the values ​​of the cells you want to combine. You can select one range or enter each value separately. In our example, we will select an entire range.

The final function in the link line will look like this: fx=CONCATENATE(«, «;1;B4:F4). After that, click "Done" and stretch the resulting cell to the remaining rows.

As a result, the function will combine the data from all cells, separating them with commas and spaces. This is not only convenient but also saves time. However, if you need to add various additional text to each value, you will have to do this manually using the formula builder. Details can be found in the instructions for the CONCAT function.
It is worth noting that Google Sheets also has a function for merging cells. You can use the "Merge" button, which works similarly to Excel, or the CONCATENATE function. However, in the latter case, you will have to enter the values ​​manually. If you want to use a separator, such as a comma and a space, the function would look like this: fx=CONCATENATE(C4; «, «; D4).