How to Merge Rows in Excel (8 Useful Ways)

Certainly, Microsoft Excel excels at arranging and manipulating data. Oftentimes, we may need to combine several rows in a spreadsheet, fortunately, there are numerous ways to do this, and, in this article, we’ll explore 8 ways of how to merge rows in Excel. In addition, we’ll also learn to merge columns in Excel.


Download Practice Workbook


8 Ways to Merge Rows in Excel

First of all, let’s consider the List Employee information dataset shown in the B4:C19 cells containing the “Name” and “Salary” columns respectively. Here, we’ll discuss how to merge rows in Excel and merge the top rows that contain the “Department” where each staff member is employed.

Dataset for how to merge rows in excel

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


1. Using Merge & Center Option

First and foremost, let’s start with the simplest and most obvious way to merge rows in Excel, that is to say, using the Merge & Center option from the Home Ribbon.

📌 Steps:

  • In the first place, hold down the CTRL key to select multiple arrays, for example, the B4:C4, B10:C10, and B15:C15 cells >> click the Merge & Center option.

Using Merge & Center Option

Voila! That is how simple it is to merge rows in Excel, as shown below.

how to merge rows in excel using merge & center option


2. Utilizing Format Cells Feature

Alternatively, we can apply the Format Cells option to merge rows in Excel, so just follow along.

📌 Steps:

  • Initially, choose multiple B4:C4, B10:C10, and B15:C15 cells by holding down the CTRL key >> press CTRL + 1 keys to open the Format Cells window.

Utilizing Format Cells Feature

  • Next, move to the Alignment tab >> choose Center >> check the Merge cells option >> hit the OK button.

Format cells dialog box

Boom! The chosen cells are merged and centered as shown below.

how to merge rows in excel with format cells option


3. Applying Keyboard Shortcut

Now, we know what you’re thinking, are there any shortcut keys? Guess what, the answer is a resounding yes! 😀 Better still, the following method answers this exact question.

📌 Steps:

  • Similar to the prior methods, choose the cells to merge >> press ALT, H, M, and C in quick succession.

ALT + H + M + C

Applying Keyboard Shortcut

Eventually, your results should resemble the image given below.

how to merge rows in excel with keyboard shortcut


4. Merging Rows Through Quick Access Toolbar

Besides, if you often need to merge rows in Excel, you can add this feature to the Quick Access Toolbar so that you can merge cells with a single click.

📌 Steps:

  • To begin with, right-click on the Merge & Center option >> press Add to Quick Access Toolbar.

Merging Rows Through Quick Access Toolbar

  • Afterward, select the cells >> click the Merge icon at the top.

Using merge option from quick access toolbar

Lastly, this method returns the same output as the previous methods.

how to merge rows in excel Quick Access Toolbar

Read more: Excel Merge Rows with Same Value


5. Employing Excel Functions to Merge Rows

Unquestionably, the heart and soul of an Excel spreadsheet are its functions. Therefore, let’s see how to merge rows in Excel with the help of functions. In this situation, consider the List of Best Sellers dataset in the B4:C12 cells containing the “Book” and “Author” names.

Dataset for Employing Excel Functions to Merge Rows


5.1 Using TEXTJOIN Function

First and foremost, we’ll employ the TEXTJOIN function, which joins strings of text with the specified delimiter.

📌 Steps:

  • To start with, enter the formula given below into the D5 cell >> drag the Fill Handle tool to copy the formula to the cells below.

=TEXTJOIN(", ",TRUE,B5:C5)

Formula Breakdown
  • TEXTJOIN(“, “,TRUE,B5:C5) → concatenates a range of text string with a delimiter. Here, “, ” is the delimiter argument which is the Comma character. Next, TRUE is the ignore_empty argument which ignores empty cells. Lastly, the B5:C5 is the text1 argument that points to Row 5.
    • Output → Poor Economics, A. Banarjee

how to merge rows in excel with TEXTJOIN function


5.2 Employing CONCATENATE Function

Conversely, another way to merge rows in Excel involves using the CONCATENATE function which joins the text together.

📌 Steps:

  • At the very beginning, enter the D5 cell and insert the following expression into Formula Bar.

=CONCATENATE(B5,", ",C5)

Here, the B5 and C5 cells represent the “Book Name Poor Economics” and the “Author A. Banarjee”.

In the above formula, the CONCATENATE(B5,”, “,C5) syntax combines the texts Poor Economics and A. Banarjee. And the output is “Poor Economics, A. Banarjee“.

how to merge rows in excel with CONCATENATE function


6. Merging Rows with Same Value

Further, we can merge rows with the same value using Excel functions. For instance, let’s suppose the Sales List dataset in the B4:D14 cells depicting the “Name”, “Item”, and “Price” respectively.

Dataset for Merging Rows with Same Value

📌 Steps:

  • In the first place, proceed to the E5 cell >> copy and paste the equation into the Formula Bar.

=IF(B5=B4,E4&", "&C5,C5)

Formula Breakdown
  • IF(B5=B4,E4&”, “&C5,C5) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, B5=B4 is the logical_test argument that checks if the value in the B5 cell is equal to the B4 cell. If the condition is true, then the function joins the values of the E4 and C5 cells with the Ampersand operator (value_if_true argument) else it returns the value of the C5 cell. (value_if_false argument).
    • Output → Formal wear

how to merge rows in excel with same value

In truth, we’ve skipped some relevant examples of merging rows with the same values, which you may explore if you wish.


7. Merging Rows Based on Criteria

Furthermore, we can merge rows based on criteria in Excel by applying the Consolidate tool. For example, we want to obtain the “Total Sales” in USD spent by each customer when he/she purchased various “Items”. Now allow us to demonstrate the process in the steps below.

📌 Steps:

  • Initially, observe and perform the steps as shown in the animated GIF below.

Steps for merging rows with same value

Consequently, the final output should appear in the figure below.

how to merge rows in excel based on criteria

In addition, you can learn about other techniques of merging rows based on criteria by following the linked article.


8. Applying VBA Code

For one thing, VBA Macros make our lives easier by automating mundane and repetitive tasks. Seeing that, let’s explore how to merge rows in Excel with VBA code.

📌 Steps:

  • First, navigate to the Developer tab >> click the Visual Basic button.

Applying VBA Code

Now, this opens the Visual Basic Editor in a new window.

  • Second, go to the Insert tab >> select Module.

Inserting Module

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Sub Merge_Rows()
    Range("B4:C4").Merge
    Range("B10:C10").Merge
    Range("B15:C15").Merge
End Sub

VBA code for how to merge rows in excel

Code Breakdown

On this occasion, we’ll explain the VBA code used to merge rows in Excel.

  • In the first portion, the sub-routine is given a name, here it is Merge_Rows().
  • Next, use the Range.Merge method to merge the rows B4:C4, B10:C10, and B15:C15.

Code breakdown

  • Third, click the Run button to execute the VBA macro.

Running VBA code

Eventually, the results should look like the picture given below.

how to merge rows in excel with VBA code


How to Merge Columns in Excel

Last but not least, we can also merge columns in Excel using the Ampersand (&) operator, so let’s see it in action.

📌 Steps:

  • At the start, enter the formula into the D5 cell as shown in the image below.

=B5:B12 & ", " & C5:C12

In this case, the B5:B12 and C5:C12 arrays refer to the “Book Name” and “Author” columns.

📃 Note: This is an array formula that may not be compatible with older versions of Excel. In case, you are using an older version then apply the expression below and drag the Fill Handle to copy the formula.

=B5 & ", " & C5

How to Merge Columns in Excel


Things to Remember

Notably, here are a few things to remember when merging rows in Excel.

For instance, the Merge & Center option may be greyed out, to fix this follow the screenshots below.

Merge & Center greyed out

  • At this point, navigate to the Review tab >> click on Unprotect Sheet to enable the Merge & Center option.

Unprotect worksheet

As a note, we can also unmerge cells by following the steps below.

  • Initially, choose the cell >> click the drop-down arrow >> select the Unmerge Cells option.

Unmerging rows in excel

Consequently, this unmerges the selected cells, as shown below.

Results of unmerging rows


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section for how to merge rows in excel


Conclusion

In short, this tutorial explores all the ins and outs of how to merge rows in Excel. Now, we hope all the methods mentioned above will prompt you to apply them in your Excel spreadsheets more effectively. Furthermore, if you have any questions or feedback, please let me know in the comment section. Or, you can check out our other articles related to Excel functions on ExcelDemy.


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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo