Why do you need to merge cells in excel?
There are several reasons you might need to merge cells in excel. It could be for table heading, improve formatting or to combine data in single cell from different cells.
In this article, I will be sharing few easy and fast methods to merge cells in excel.
Easy way to merge cells in excel
Mostly we merge cells when it comes to table headings. To bring heading in center of table we merge multiple cells. Lets suppose you have below table and want to bring heading in center of table.
- Select all the cells you want to merge and click on Merge & Center or press “ALT + H + M + C”.
If you have two different text in different cells and want to combine in one cell, in this case this method won’t work. By using this method you can keep only one value. Let’s suppose you have first name in cell D3 and last name in E3, when you will merge these cells it will keep only the upper-left value (Which is first name) and will discards other value (Which is last name).
Merge Cells Without Losing Data in Excel
In order to merge cells without losing data, you can use CONCATENATE formula to combine values in different column. For instance, you have below data where you have to combine cell D3 and E3 and between these two text will add space so the formula will be entered as =CONCATENATE(D3,” “,E3) in cell F3.
Alternatively, you can use ampersand symbol as well to combine text.. For example, instead of CONCATENATE formula you can use =D3&” “&E3.
Quick Ways to Merge Cells in Excel
Sometimes you may have a large amount of data which needs to be merged, so doing it manually will be time consuming. For example you have data as shown in below image and want to merge all the duplicate values showing in first two columns like Region East is repeated three time and Sold by jones as well. So by merging these duplicate values would help to read data easily.
The following are two different methods which you can use to quickly merge large data in excel:
Merge Duplicate Values Using Kutools
Kutools is an excel add-in which can be used to perform different complicated tasks easily in excel. The following are steps to merge cells quickly by using Kutools.
- Select the data range you want to merge
- Go to Kutools tab
- Select Merge & Split and click on Merge Same Cells
After these steps, all the duplicate values will be merged and you will see the result as shown in below image.
Merge Duplicate Values With VBA
You may also use VBA (Visual Basic for Applications) to merge duplicates values quickly in excel. Let’s see how to do this with VBA.
- Go to Macros and click on Record Macro
- Store Macro in: Select Personnel Macro Workbook and click ok
- Go back to Macros and click on Stop Recording
- Now go to Developer Tab and open Visual Basic
- Double click on VBA Project (Personnel), click on Modules and open Module 1
- Now you need a VBA Code which you can copy from below table.
- Replace existing code with below code and go back to data sheet.
Option Explicit
Sub Merge_Same_Cells()
Application.DisplayAlerts = False
Dim rng As Range
MergeCells:
For Each rng In Selection
If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then
Range(rng, rng.Offset(1, 0)).Merge
Range(rng, rng.Offset(1, 0)).HorizontalAlignment = xlCenter
Range(rng, rng.Offset(1, 0)).VerticalAlignment = xlCenter
GoTo MergeCells
End If
Next
End Sub
- Now select the data range you want to merge and go to macros.
- Open View Macros and click on Run
So as you can see how easy and quickly cells have been merged with this method. You can use any of these methods to merge duplicate cells quickly in excel.
I hope you will find this article useful, if still you have any question regarding the same, do let me know in below comments section. I would love to assist you any further.
Thank you for being here….!
You might be interested to learn more:
How to Generate Barcodes in Excel – Complete Guide
