7 Easy Ways to Merge and Center Cells in Excel

In some cases, there is a lot of data in a cell or the text is so big that normal cell size width is insufficient. Perhaps, sometime there is no opportunity to fit the cell with respect to its text size. In these cases merge and center cells can be a very good way. Merge and center cells in Excel are one of the most important features in Excel to fit texts or data suitably. In this article, we will learn to merge and center cells in Excel.

📁 Download Excel File

To practice, please download the Excel file from here:

Learn to Merge and Center Cells in Excel with 7 Examples

Considering a dataset named Greatest Performers List 2022 that consists of the First name, Last Name, Category, and Age of each performer. In the dataset, there are 13 rows and 5 columns. We are going to use CONCATENATE functions, and VBA code to generate merge cells. Let’s get started with the approaches.

Suppose you have a cell containing long text, it also overlaps across the other cell. Here we need to merge and center cells to get text accurately. Please follow the steps where we are going to use the Merge and Center Options from Alignment.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, Select the cells that we are going to merge and center.
• Secondly, Click on Merge from the Alignment menu.
• Thirdly, select Merge & Center from Merge.

• Finally, we get the merged and centered cell containing Ricky Ponting, and Don Bradman.

Also, Using the keyboard shortcut you can merge cells within a second. Follow the procedure.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, Select the cells that we are going to merge and center.
• Secondly, Press Alt; You will see some Keys from the Top Ribbon.
• Now press H for Home Tab.
• Then, Press M to select Merge & Center from the Alignment menu.

• Lastly, you will get the merged and centered cell named Cricketer’s Name.

From the Home option, if you try to merge cells, you will get only the first value in the merged cell. Suppose you are likely to merge the cells of Lionel and Messi; Here you will get only Lionel in the merged cell and Messi will be missing. But following the below procedure will get the merged cell without losing data in Excel.

⬇️⬇️ STEPS ⬇️⬇️

• First, select a blank cell i.e. D6.
• Then, Type the following formula in D6.

• =B6 & ” ” & B6 denotes, taking values from the B6 and C6; And in between two words there is space.
• Finally, we get our output, Lionel Messi, at D6.

• Now pull the Fill Handle option to get values automatically.

• Finally, by auto-filling the table, we obtain our final output.

You can merge cells using a formula containing CONCATENATE function. The general syntax of CONCATENATE function is:

Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• First, select a blank cell i.e. E6.
• Insert the following formula having a CONCATENATE function in E6.

• CONCATENATE function joints several cells into another cell.
• Finally, We get the output at E6 and using the auto-fill option, we get the final result.

With a single action, you can merge cells within a second. Follow the procedure.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, Select the cells that we are going to merge and center.
• Secondly, Press Alt; You will see some Keys from the Top Ribbon.
• Now press H for Home Tab.
• Then, Press M to select Merge & Center from the Alignment menu.

• Lastly, you will get the merged and centered cell named Cricketer’s Name.

We can use VBA code to merge cells in any position. With the help of the Developer option, you can generate a program of your own. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, Check first if your Developer option is available or not.
• Then, To enable the Developer option, right-click on the mouse at the top Ribbon and select Customize the Ribbon.

• After that, Click on Developer and click on OK.

• Now Developer mode is on.
• Next, Click on the Visual Basic option.
• Then to insert the VBA code, click on Module from the Insert option.

Code

Sub MergeCells()
Range("B6:C6").Merge
End Sub

• Finally, inserting the code, click on Run to execute.

• Therefore there we get our merged cell having a text containing Lionel Messi.

How to Find Merged Cells in Excel

If you are on a mission to find out the merged cell in a set of data. You can find it on your own using Find & Select option. Let’s follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, Click on the Home menu from Top Ribbon.
• Secondly, From Editing click on Find & Select.
• Thirdly, click on Find.

• Therefore a Find and Replace box appears.
• Next, From Find click on the Format option.
• Again, another box named Find Format will appear.
• Then Click on the Alignment tab.
• Also, Click on the Merge Cells option.
• Then click on the OK button.
• Finally, click on Find All option.

• After all, 4 cells of merged cells were highlighted in the Find and Replace window.
• If we click each of them like Cristiano the cell gets highlighted which is in $B$7.

📄 Important Notes

🖊️  Be careful during merging and centering cells. Only the data of the first cell will show in the merged cell if you don’t use any formula. Therefore, you will lose your data.

🖊️  During working with VBA code, Check first whether your Developer tool is available or not.

📝 Takeaway from This Article

📌  Now you will be able to merge and center cells correctly in Excel.

📌  Use of CONCATENATE function demonstrated in this article.

Conclusion

We demonstrated every possible way to merge and center cells in Excel. Queries, as well as suggestions, are appreciated. Leave your thoughts in the comment box. For better understanding and new learning don’t forget to visit www.ExcelDen.com.