How to merge cells in Excel with no data loss

Normally, you often use Merge & Center directly to merge cells in Excel, but cells with data, you will lose data in the back cells because Merge Cell only retains data in the first cell. If you want to merge cells in Excel without losing data, please join us to learn the ways that the tutorial tutorial below.

How to merge cells in Excel with no data loss Picture 1

Here are instructions 3 ways to merge cells in Excel without losing data, please follow along.

Method 1: Merge cells in columns using Justify and Merge & Center

If you want to combine cells in columns, then you use this way, if the cells you need to combine in multiple columns, this way does not work, please refer to the following methods. To combine cells in columns using Justify and Merge & Center, do the following:

Step 1 : Select the cells in the column that you want to merge without losing data and then resize the column width so that the cell contains all the data in the cell.

How to merge cells in Excel with no data loss Picture 2

Step 2 : Select the Home tab -> Fill -> Justify.

How to merge cells in Excel with no data loss Picture 3

Step 3 : After selecting Justify , all data in the cells will be transferred to the first cell. Next, you select the cells you want to merge and select Merge & Center if you want to merge cells and align data between cells or you choose Merge Cells if you want to merge cells and not align the data position after merging.

How to merge cells in Excel with no data loss Picture 4

So you've merged the cell without losing data.

How to merge cells in Excel with no data loss Picture 5

Method 2: Merge cells using Concatenate function and Merge & Center feature

Use the Concatenate function to combine data in cells to merge, then you use Merge & Center to merge cells. You do the following:

Step 1 : Select any cell then enter the function = CONCATENATE (C7; ''; D7; ''; E7) where C7, D7, E7 are the cells to be combined. Thus the data in the cells to be combined are displayed in the cell that you use the Concatenate function.

If you do not understand Concatenate function, you can find out here http://TipsMake.vn/cach-noi-chuoi-trong-excel/

How to merge cells in Excel with no data loss Picture 6

Step 2 : Copy the data in the cell using functions and paste data into the first cell in the cells to merge.

Note: you only paste the data ( Value ) but not the Concatenate function formula by copying the data in the cell containing the function, then you right-click the first cell in the cells to combine and select Value in the Paste Options .

How to merge cells in Excel with no data loss Picture 7

So the data of the cells to be merged is displayed in the first cell of the cells to be merged, next you delete the cell containing the Concatenate function .

How to merge cells in Excel with no data loss Picture 8

Step 3 : Select the cells to merge data and select Merge & Center or Merge Cell option to combine cells in Excel.

How to merge cells in Excel with no data loss Picture 9

A small message appears and select OK so the cells will be merged without data loss.

How to merge cells in Excel with no data loss Picture 10

Method 3: Use VBA code

Here's how to help you to merge cells in Excel without losing data quickly, you can do the following:

Step 1 : First, you select the cells to merge without losing data, next you choose View -> Macros (combination Alt + F8 ) to open the Macro window .

How to merge cells in Excel with no data loss Picture 11

Step 2 : In the Macro dialog box, enter a name for the unmarked macro and select Create to open the Visual Basic for Applications window .

How to merge cells in Excel with no data loss Picture 12

Step 3 : Visual Basic for Applications window appears, copy the code below paste into the Sub (named macro just) . End Sub.

Dim Cll As Range, Temp As String On Error Resume Next If Selection.MergeCells = False Then For Each Cll In Selection If Cll <> "" Then Temp = Temp + Cll.Text + "" Next Cll Selection.Merge Selection.Value = Left (Temp, Len (Temp) - 1) Else Selection.UnMerge End If Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter 

Then click the Run icon to run the lumped macro without data loss.

How to merge cells in Excel with no data loss Picture 13

A small message appears and click OK , so the cells you select will be merged quickly.

How to merge cells in Excel with no data loss Picture 14

If in the same Excel file you want to merge other cells, you just need to select the cells to merge and then press Alt + F8 (or choose View -> Macros ).

How to merge cells in Excel with no data loss Picture 15

The Macro dialog box appears, double click the name of the macro you just created to run the macro.

How to merge cells in Excel with no data loss Picture 16

Thus, the cells you have selected are quickly merged without losing data.

How to merge cells in Excel with no data loss Picture 17

Above the article has detailed instructions on how to merge cells in Excel without losing data, hopefully through this article you will understand and can easily apply the ways to combine cells in Excel to keep the data. Good luck.

4 ★ | 1 Vote

May be interested

  • How to Sort Cells Alphabetically in ExcelHow to Sort Cells Alphabetically in Excel
    excel is a powerful spreadsheet tool used to store and manage text or figures. alphabetical sorting is one of excel's useful features with the ability to help you sort, access and reference data quickly. to sort cells in excel alphabetically, simply double-click by highlighting the range of cells to sort, then click the 'az sort' or 'za sort' icon in the bar. standard tool. to sort cells alphabetically in excel using the advanced sort option, highlight the entire worksheet, click the 'sort' option from the 'data' menu. , then select the column and order you want to sort in the dialog box that appears.
  • How to merge and split cells in WordHow to merge and split cells in Word
    how to merge and split cells in word. working with tables in word, sometimes you need to split a cell into multiple cells, or merge multiple cells into one cell to fit the requirements. if you do not know how to combine and split cells in word, then please refer to the article d
  • How to cross cells in ExcelHow to cross cells in Excel
    you're making a table in excel and have a blank data cell and you want to cross that box but don't know how to do it. let tipsmake.com help you through the article how to cross cells in excel offline!
  • How to calculate and color blank cells in ExcelHow to calculate and color blank cells in Excel
    counting and coloring blank cells in excel will rely on excel functions and conditions to perform.
  • How to split 1 cell into 2 cells in Excel - Split cells in ExcelHow to split 1 cell into 2 cells in Excel - Split cells in Excel
    how to split 1 cell into 2 cells in excel - split cells in excel. depending on different purposes, you want to split a cell into two cells to enter two different data in one cell and still be able to calculate normally as other cells.
  • How to Alphabetize Cells in ExcelHow to Alphabetize Cells in Excel
    excel is a powerful spreadsheet tool used to store and manage text and numbers, and alphabetizing is one of the many benefits of using excel since it allows you to quickly organize, access, and reference data. to alphabetize cells in excel...
  • How to indent the line in ExcelHow to indent the line in Excel
    when you enter data into a spreadsheet, excel automatically aligns with cells containing numbers to the right of the cell, and text input cells are automatically located to the left of the cell.
  • Instructions on how to split and merge cells in WordInstructions on how to split and merge cells in Word
    in word, when you work with tables and want to split cells or merge cells, how to do?
  • How to name, comment and protect cells in ExcelHow to name, comment and protect cells in Excel
    instructions on how to name, note and protect cells in excel. 1. name the data cell. - normally, the data cell with the default name is the combination of the column and row order that makes up the cell. for example, cell b5 is named b5. - to change the name for a cell, do as follows: + click to select
  • How to format data based on other cell conditions in ExcelHow to format data based on other cell conditions in Excel
    conditional formatting in excel makes it easier to categorize or highlight data, based on the conditions you set.