You may need to use the same values in some cells. In that case, you don’t need to insert the same cell value repeatedly, simply you can merge and center those in Excel. So, you may guess what I’m going to explain today. Today, in this article, I will demonstrate how to merge , and center selected cells in Excel.
Basically, I will talk about 4 different ways of merging and centering selected cells. In addition, three of them will merge the selected cells, which will act like a single cell considering only the cell value of the 1st cell.
On the other hand, the 4th one won’t remove any cell value. Instead of that, the 4th method will join different cell values into a single cell.
Below, I have attached an animation as an overview of this article.
Download Practice Workbook
You can download the practice workbook from here:
4 Ways to Merge and Center Selected Cells in Excel
Here, I will demonstrate 4 suitable methods with detailed steps on how to merge and center selected cells in Excel. For your better understanding, I am going to use the following dataset, which contains some information about a company within four columns. Those are ID, Full Name, Job Title, and Department of some particular employees. The dataset is given below.
Furthermore, for conducting this session, I’m going to use Microsoft 365 version.
1. Use of Merge & Center Feature
Here, I will use the Merge & Center feature for combining selected cells. This is the most common and easiest way for merging cells.
Basically, merging cells is behaving like a single cell. So, you can store only one value in that cell. If you have different values in different cells, then using this Merge & Center feature will remove all the cell values except keeping only the cell value of the 1st cell.
However, you can merge selected cells in both horizontal and vertical ways. Actually, the direction will depend on the selection of cells.
Below, to enrich your understanding, I will show you both scenarios.
1.1 Merging Selected Cells within a Single Column
Here, I will merge selected cells within a particular column. Basically, the cell selection will be column-wise. Moreover, you may think of it as merging cells vertically.
Suppose I want to merge the same department situated in the Department column of my dataset.
Now, let’s see the steps.
Steps:
Firstly, select cells that you want to merge. Here, I have selected E5:E7 cells. Additionally, all those cells have the same value “Sales”.
Secondly, from the Home tab >> go to the Alignment group >> chose Merge & Center along with Center and Middle alignment.
Actually, by default in my dataset the alignment of the cells was in Center and Middle alignment. Though, this is not an important issue. By clicking only Merge& Center feature from the Alignment group, you will still see the cells were merging and storing the merged cell value in the center position.
After clicking the Merge & Center feature, you will get a warning from Microsoft Excel. Here, if you are trying to merge the blank cells then you won’t get any warning like that.
Now, press the OK button on the warning. By pressing OK, you are going to agree with the statement. Where you are giving permission to Excel for removing your data that are stored in the selected cells except 1st cell of the selection.
As a result, you will see the following output where the selected cells are merged and centered. Here, it kept only the E5 cell value. Though all the cell values from E5 to E7 are the same, still it considered only the cell value of E5 (the 1st cell of selection).
Then, do the same for all other departments. Here, I have selected E8:E10 for the Engineering department, E11:E12 for the Finance department, and E13:E16 for the IT department, and as the department of Human Resources is a single one so I don’t have to do anything with this.
Lastly, you will get the following result.
Read More: How to Merge Cells Vertically Without Losing Data in Excel
1.2 Merging Selected Cells within a Single Row
Here, I will show you how to combine selected cells row-wise. This method is just for your better understanding. Additionally, I will follow the same procedure. Here, the cell selection will be horizontal.
Suppose you have the same information as the previous company but now the information is given row oriented. At this time, you want to merge the same department for your convenience.
Now, let’s see the steps.
Steps:
Firstly, select cells that you want to merge. Here, I have selected C7:D7 cells. Additionally, all those cells have the same value “Sales”.
Secondly, go to the Home tab >> from the Alignment group >> select Merge & Center.
After clicking the Merge & Center feature, you will get a warning from Microsoft Excel. Here, if you are trying to merge the blank cells then you won’t get any warning like that.
So, press the OK button on the warning. Here, by pressing OK, you are going to give permission to Excel for removing the D7 cell value and keeping only the cell value of C7.
Lastly, you will see the following merged and centered cells.
Actually, by default in my dataset the alignment of the cells was in Center and Middle alignment. That is why you will see the merged cell value is not only in the center position but also in the middle alignment.
Similarly, do the same for the Engineering department where I have selected cells E7:F7.
As a result, you will get the following merged cell.
However, you can also merge the cells situated at more than one column and row. Below, I have attached an image of this kind of selection. Where I have merged and centered E6:F7 cells.
Read More: How to Merge and Center Cells in Excel (4 Easy Methods)
2. Employing Keyboard Shortcuts for Merging Selected Cells
In this section, I will use a keyboard shortcut for merging and centering cells. If you are handy with keyboards then you can use this method. Moreover, this procedure will save time. Let’s see the steps below.
Steps:
Firstly, select cells that you want to merge. Here, I have selected E5:E7 cells.
Secondly, press ALT+H+M keys.
Then, you will see the following list of shortcuts. For merging and centering, you should press the C key. On the other hand, after cell selection, you can press ALT+H+M+C keys together and you will get the expected result.
After using the keyboard technique, you will get a warning from Microsoft Excel. On the other hand, if you are trying to merge the blank cells then you won’t get any warning like that.
Here, for merging cells you have to press the OK button on the warning. By pressing OK, you are giving permission to Excel for removing your data that are stored in the E6 and E7 cells. You will see only the E5 cell value in the merged cell.
Actually, merging cells behaves like a single cell. Not only this fact, but it also will remove all the cell values except keeping the 1st cell value of the selection.
Finally, you will get your merged and centered cells.
Here, the cell of my dataset was in the Center and Middle alignment. That is why you will see the merged cell value is not only in the center position but also in the middle alignment.
In the same way, merge all other departments. Here, I have selected E8:E10 for the Engineering department, E11:E12 for the Finance department, and E13:E16 for the IT department, and as the department of Human Resources is a single one so I don’t have to do anything with this.
Lastly, you will get the following output.
Read More: How to Merge Multiple Cells in Excel at Once (3 Quick Ways)
Similar Readings
How to Unmerge Cells in Excel (7 Easy Methods)
Merge Text Cells in Excel (9 Simple Methods)
How to Merge Text from Two or More Cells into One Cell in Excel
Merge Cells in Excel with Data (7 Quick Ways)
How to Merge Two Cells in Excel Without Losing Data (2 Ways)
3. Applying Format Cells Feature to Merge & Center Specified Cells
The interesting part is you can use the Format Cells feature to merge and center selected cells. This is also an easy method. Now, let’s check the steps below.
Steps:
First, select cells that you want to merge. Here, I have selected E5:E7 cells. Additionally, all those cells have the same value “Sales”.
Then, you need to press the CTRL+1 keys to open the Format Cells dialog box directly.
Also, you can use the Context Menu Bar or the Custom Ribbon to go to the Format Cells command. In the case of, using the Context Menu Bar, select the data range >> Right-Click on the data >> choose the Format Cells option.
While using Custom Ribbon, select the data range >> from the Home tab >> go to the Format feature >> choose the Format Cells command.
As a result, a dialog box named Format Cells will appear.
Now, from that dialog box, you have to go to the Alignment section.
Then, choose Center Across Selection in the Horizontal box.
After that, select Center in the Vertical box.
Consequently, mark the Merge cells option which is under the Text control segment.
Finally, you have to press OK to get the changes.
Then, you will get a warning from Microsoft Excel. Here, if you are trying to merge the blank cells then you won’t get any warning like that.
So, press the OK button on the warning. Here, by pressing OK, you are giving permission to Excel for removing the E6:E7 cell’s value.
At this time, you will get the merged and centered cells.
As in my dataset, the alignment of the cells was in the Center and Middle alignment. Thus, you will see the merged cell value is not only in the center position but also in the middle alignment.
Similarly, merge all other departments. Here, I have selected E8:E10 for the Engineering department, E11:E12 for the Finance department, and E13:E16 for the IT department, and as the department of Human Resources is a single one so I don’t have to do anything with this.
In the end, you will get the following output.
Read More: How to Merge Multiple Cells in Excel (5 Suitable Ways)
4. Using CONCATENATE Function to Merge Selected Cells Value in Excel
Here, I will use the CONCATENATE function for merging cell values in Excel. Basically, by merging cells you can keep only one cell’s value and remove the other’s value. Therefore, I will use this function to keep all the cell values. Most likely, you can say, I’m going to join cells value by applying this CONCATENATE function.
Suppose you want to merge the Job Title and Department.
Now, follow the steps given below.
Steps:
First, select the cells where you want to combine specified cell values.
Then, from the Home tab >> go to Alignment group >> choose Center and Middle alignment.
Subsequently, write the following formula in the selected cells.
=CONCATENATE(C5,”-“,D5)
Here, the CONCATENATE function will join the C5 cell value, hyphen (–), and D5 cell value.
Lastly, press CTRL+ENTER, and you will get all the joined or merged cells value at a glance.
Furthermore, you can use the TEXTJOIN function. In that case the formula will be as below:
=TEXTJOIN(“-“,TRUE,C5,D5)
Where hyphen (–) is the delimiter. This function will join the text of C5 and D5 cells.
Moreover, you can use another function for same purpose. Which is known as CONCAT function. Below, I have added the formula for this CONCAT function.
=CONCAT(C5,”-“,D5)
Where the CONCAT function will merge C5 and D5 cells value along with a hyphen (–).
Read More: How to Merge Text from Two Cells in Excel (7 Methods)
Practice Section
Now, you can practice the explained methods by yourself.
Conclusion
I hope you found this article helpful. I have described how to Merge and Center selected cells in Excel here. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
How to Merge Datasets in Excel (5 Easy Methods)
Merge Multiple Cells without Losing Data in Excel (6 Methods)
[Fix:] Excel Unable to Merge Cells in Table
How to Merge Data from Multiple Workbooks in Excel (5 Methods)
Merge Data in Excel from Multiple Worksheets (3 Methods)
VBA to Merge Cells in Excel (9 Methods)
The post How to Merge and Center Selected Cells in Excel (4 Ways) appeared first on ExcelDemy.
