Microsoft Excel as we know it provides a lot of options and functions. While using it, we might come across the need to accomplish multiple tasks. And among them, one is to merge rows. In this article, we’re about to cover just that.
Our purpose here is to cover everything there is concerning the “how to merge two rows in Excel” topic. I won’t say that it’s a one click solution. However, it can be done in some really fast and easy ways. Not to worry, we’re about to cover them all in this one.
Users often lose data while trying to merge rows in Excel. Well, we’re aware of that and we’re about to talk about the procedures that’ll help you avoid that from happening. So, without further ado, let’s begin.
Contents
The Common Problem
You might have noticed this before, Microsoft Excel really doesn’t have a reliable tool to execute the task easily. What I’m trying to say is, if you use the integrated Merge & Center button, you’ll just see the error shown below.
If you click OK, the rows will be merged. However, it’ll only keep the data of the first cell. The rest of the data will disappear. Wondering how to fix the problem? Worry not, there are more than one ways to do it.
How to Merge Two Rows in Excel
Let’s get this straight. Merging rows is different from merging columns and cells. As we’ve shown before, there’s a possibility of losing data. We’re going to explain a few methods below that’ll help you do it right without any errors.
Merge Two Rows in Excel Without Losing Data
If you don’t want to lose data while merging rows, then are two possible ways to go about it. Let me explain:
Merge Two Rows in Excel Using CONCATENATE and Concentration Functions
In this case, your primary target should be to join the values of multiple cells into one. Here, you can choose between using the CONCATENATE function, CONCAT function (Excel 2016 or higher), or the concentration operator (&).
Either way, you’ll have to put in cells as references and type in the desired delimiters in-between. Let’s talk in detail.
- In the worksheet we’re working on, there are two books in separate rows written by the same author.
- Now, our task is to merge the data in the cells, B2 ans B3.
- To do this, we’ll be using this formula: =CONCATENATE(B2,” “,B3)
- Once applied, the results should appear like this:
Similarly, if we are to use the Concentration Operator, the formula should be =B2&”, “&B3
As we can see, the results are the same.
Merge Two Rows in Excel Using Concatenate and TRANSPOSE Functions
Let’s try yet another procedure now. This involves using the CONCATENATE function along with the TRANSPOSE function. Using this, you can easily merge two values without losing any data at all. Let’s see how it works:
- Within the cell where you want to merge the rows, enter the CONCATENATE formula & then select the TRANSPOSE function as shown in the image.
- Then pressing F9 will show the actual reference values.
- Press Enter to see the desired results.
Merge Two Rows in Excel Using Clipboard
When we consider the basic copy (Ctrl C) and paste (Ctrl V) functions in excel, even if you copy two cells and paste it elsewhere, the cells don’t get merged. So, how do we make this happen? Let’s find out.
- The first thing you want to do here is, under the Home tab, go to the clipboard Icon.
- Then selectboth the rows you want to merge.
- After that, press Ctrl+C to copy them.
- Then you’ll want to select any empty cell and double click on it.
- After that, click on the available Item to Paste. (In the Clipboard command sequence)
- The results should come out like this:
- And here you have it. The task is complete.
Merge Rows in Excel with Merge Cells add-in
This tool called Merge Cells add-in is a real lifesaver. It keeps all the data even if the cells contain multiple values. So, let’s see how it works:
- First of all, you’ll need to find the Ablebits Data Tab after installing the add in.
- There, under the Merge group, click on the Merge Cells dropdown menu.
- There, you’ll find the Merge Rows into One option.
- Click on it after you’ve selected the range of data that you want to merge.
- This will basicallyopen the Merge Cells dialogue box with the default settings that usually work fine. In this example, we need only to change the separator from the default space criteria to line break.
- Lastly, click on merge and that’s pretty much it.
Final Words
Well, this pretty much wraps it up. I would recommend you use the simple CONCATENATE formulas. But still, there are other ways to do it if that’s not to your liking. I hope all the info helped to clear out all the doubts you had regarding the topic.
So, the next time anyone asks you, how to merge two rows in Excel, I believe you’ll have the answer. Now it’s time to bring this ride to a close. So, hope we’ll catch you in the next one.
Similar Post: