How to Merge Rows in Excel (4 Methods)

Excel is a platform where all the databases can be saved and utilized for not only calculations related to the database but also helps to get all kinds of arrangements or management facilities. Many times, it is required to merge various rows in Excel. This article is about explaining the important methods available in Excel to merge rows in Excel.

4 Methods of How to Merge Rows in Excel

There are various types of datasets that are made to keep records of the past, updating it with the present information and utilizing it for future planning.

Let us consider, that a company works with clients to provide the support to complete their projects and deals are made based on this. The below dataset contains the list of clients the company is working with presently and contains the number of deals and the deal name accordingly.

The dataset to explain the methods of how to merge row in Excel

You can download the worksheet from here.

Now it might be required to merge the rows to remove duplicates of clients’ names and see the name of the client only. Again, it might be necessary to see all the arguments inside clients, business deals no. and deal names altogether in a single row. So, we will see 4 different methods through which the mentioned works can be done.

1.Merging Rows to Show the Top-Left Value Only

As mentioned, we might require merging rows having the same client names to see only the client’s name and remove all other arguments. There are 4 different ways to complete this task.

a. Merging Row Directly from the Home Tab

For merging row from the Home tab, you must follow the following steps:

  • Select the rows you want to merge.

Let’s say we want to merge the first three rows from the given dataset.

Selecting rows to merge

  • Next, go to the Home tab and select Merge & Center.

Finding Merge & centers from home tab

  • A small menu bar will appear, where you must click OK.

Clicking ok for merging rows

Repeating the same steps for other rows the result will look like:

Result of using Merge & Centers

Note: You can also select from the drop-down menu of Merge & Center. In that case, you must select Merge Cells and it will provide the same result as shown above.

Selecting merge cells from merge & center

b. Merging Row Using the Format Option

You will also find the Format option in the Home Tab.

Finding format from home tab

Remember to select the rows you want to merge before selecting the Format option.

 A drop-down menu will appear and from there you must select .

Selecting format cell from format option

Then, a new drop-down menu will appear. Choose Merge Cells inside the Alignment toolbar and click OK.

Selecting Merge cells from Alignment of format option

Repeating the similar steps for other rows to be merged will give the result as follows:

Result of using format cell

You can notice by comparing with the above result that the results are shown at the left whereas in (a) the results were shown at the middle which is because the option there itself says Merge & Center.

c. Merging Rows Using Keyboard Shortcut

Again, similar results can be found using Keyboard Shortcuts. For this you can follow the steps mentioned below:

  • Select the rows you want to merge.
  • Press ALT+H+M+C. The result will be the same as method (a).

Result using keyboard shortcut

The keys H, M, C of the keyboard represent the Home tab, Merge & Center option respectively.

ALT+H+M+C

d. Merging Through the Quick Access Toolbar

If you need to do row merging very frequently, you can customize your Quick Access Toolbar and include Merge & Center in that. You can do this by:

  • From the Home tab right-click on Merge & Center. a drop-down menu will appear. Select Add to Quick Access Toolbar.

Customizing Merge & center to Quick Access Toolbar

  • Then, merging of rows can be done easily by selecting the rows to be merged and clicking on the Quick Access Toolbar.

You can find the Quick Access Toolbar from the top left of the Excel file.

You will notice that clicking on the Quick Access Toolbar automatically clicks the Merge & Center option.

Clicking Quick Access Toolbar auto clicks merge & center

The result will be the same as the above methods.

Result of using Quick Access Toolbar as merge & center

Read more: Excel Merge Rows with Same Value

2.Merging Rows with Macro Using VBA

You can use simple code to merge rows in Excel. Let’s have a look at them.

The steps of writing a VBA code to merge rows in Excel are as follows:

  • Press ALT + F11. This will take you to the VBA window.
  • Your worksheets will be shown at the left of the window. Right-click on the worksheet for which you want to write the code. Then click on Module.

Selecting Module in VBA window

  • Write the Code in General Window as below:

Sub MergeCells()

    Range("B4:D6").Merge

    Range("B7:D8").Merge

    Range("B9:D10").Merge

End Sub

Code for VBA Macro

  • Save the file as Excel Macro-Enabled Workbook.
  • Then in your Excel Worksheet select the range of rows you want to merge and press ALT+F8 on your keyboard. This will open a menu box.

Running the code in Excel

  • Select your saved Excel Macro-Enabled Workbook and click on Run.

This will give the below result:

Result of running the VBA Macro

ALT+F11
ALT+F8

3.Merging Rows Using Function

Functions may vary based on them the Microsoft Office version you are using.

a. Using TEXTJOIN

If you are using Microsoft 365 or Microsoft Office Version 2019 then you can use the formula TEXTJOIN. As a function, it takes a comma to keep the arguments at distance from each other for ease of understanding. Then it takes an argument whether to ignore blank cells or not. Finally, it takes the array of data to merged.

Writing the function in the rows where merged rows will show up and array is selected accordingly  which will provide the desired result as follows:

Result of using TEXTJOIN function

Here, we have written the formula for merging rows 4,5,6 together. Similarly, for writing formula for merging 7,8 and 9,10 and the result looks like the shown picture.

=TEXTJOIN(“,”, TRUE, B4:D6)

b. Using Nested CONCATENATE and TRANSPOSE Function

If you are using any of the previous versions of Microsoft Office, then you can use the Nested function of CONCATENATE and TRANSPOSE.

Here, you just must nest TRANSPOSE inside CONCATENATE and provide the array range of the rows you want to merge.Result of using nested CONCATENATE and TRANSPOSE

 Note: You might need to add rows to see the exact result.

=CONCATENATE(TRANSPOSE(B4:D6)

4.Merging Rows Using Pivot Table

You can merge rows in Excel using Pivot Table. Follow the below steps to get the result using the Pivot table:

  • Select the range of the dataset.
  • From the INSERT tab, select Pivot Table.

Selecting PIVOT from INSERT

  •  Along with a box of the pivot table, a drop-down menu at the right of the file will appear.

Pivot box

Pivot options to be selected

  • Tick on the Clients, Deal Name, and Business deal no. This will show them in Rows and Values.

Selecting the options to create the pivot table

  • Go to Design and from Report layout select Show in tabular form.

Creating tabular form from design tab

  • From Pivot Analyze, select Options and that will open a drop-down menu. There, from Layout and Format, tick Merge and center cells with labels.

Option is selected from Pivot Analysis

Selecting merge cells from option of Pivot Analyze tab

The result will look like this:

Pivot table result

Note: You can either keep the total values or omit it from the Pivot Analyze Tab according to your need.

Things to Remember

Moreover, while applying the above methods, you need to remember two things .

  1. If you are going to use Merger & Centers and found it greyed like below:

Unenabled merge & center option

The reason behind this is that your workbook is on a Protected Sheet. To omit this, you need to go to review and select Unprotected Sheet.

Selecting unprotected sheet from review tab

Doing this will enable the Merge & Centers for use.

Process of unmerging the merged rows

  1. If you want, you can unmerge by selecting on the Row you want to unmerge and click on Merge & Center.

Conclusion

The article explains 4 methods of how to merge rows in Excel. You can follow any one of them whichever you find easier.


Further Readings

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo