One of the problems that occur when merging is loosing of data. Regardless of the excel version in use, when you merge columns of data in excel, only the left column data is kept and the data in the other columns will be deleted. The guide below is going to help you know how you are going to merge data in excel without losing it.
Merging of data using clipboard
Using clipboard in excel to merge data may be of great importance in retaining the merged data. Below are steps you can follow to merge data using clipboard;
1. The first thing to do is to enable the clipboard, on the Home tab, click the anchor button
at the bottom right corner of the clipboard group.
2. Then select the range of column you intend to merge and press Ctrl + C to copy or go to home click copy.
The columns will be copied and listed on the clipboard.
3. Double click in blank cells that you intend to paste the copied columns and click the copied items in the clipboard. The items will be filled in the active cells. This will place all the selected columns merged without any loss.
Merging of data using Formula
Another good alternative that you can use is the use of a formula. This can easily be done as shown below;
Formula 1: =A2&B2&C2&…
Taking the example of the table
Enter the formula =A2&B2&C2 in the D2, drag the formula all through using the auto-fill handle to apply the formula to the other cells. This will merge the columns of data into one column.
Formula 2: =A2&" "&B2&" "&C2&…
Enter the formula =A2&" "&B2&" "&C2 in the D2, drag the formula all through using the autofill handle to apply the formula to the other cells. This will merge the columns of data into one column.
In both of the formulas A2, B2 and C2 are cells in the first row that are merged in one cell and you can change them as you desire.
Merging of data using Concatenate
Using the formula to merge data may be tedious especially when we are dealing with multiple cells. The use of concatenating will help deal with the formula. Concatenate is used in merging as follows;
1. Enter the Concatenate formula = Concatenate, and select the range of cells. In our case =Concatenate(A2:C2& ",")
A2:C2 is the range in the first row intended to be merged.
"," separate the cell content
2. To convert the highlighted part of the formula to value, press F9
3. The part of the formula that has been selected will be replaced with the content in the cell automatically.
4. Press the Enter key to get the merged cells.
Merging column using Kutools
Kutool in excel can also be used to merge cells without losing data.
1. Once you have kutool installed on your computer, select the column range that you want to merge and the go-to Kutool and click on combine
2. Specify the option in the combined column and rows dialog box as follows;
- Choose the combined columns and under To combine selected cells according to the following options
- Make specific the separator for the combined data by selecting the space option.
- Choose the cell where you want to place the combined results.
- Decide how you want to deal with the combined results. You may choose whether you want to keep or delete content from the combined cells.
3. Click okay to combine the data without losing it into one cell.