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.
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.
Step 2 : Select the Home tab -> Fill -> Justify.
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.
So you've merged the cell without losing data.
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/
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 .
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 .
Step 3 : Select the cells to merge data and select Merge & Center or Merge Cell option to combine cells in Excel.
A small message appears and select OK so the cells will be merged without data loss.
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 .
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 .
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.
A small message appears and click OK , so the cells you select will be merged quickly.
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 ).
The Macro dialog box appears, double click the name of the macro you just created to run the macro.
Thus, the cells you have selected are quickly merged without losing data.
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.
You should read it
- How to merge cells in Excel - Instructions to merge cells in Excel 2010, 2013, 2016
- How to merge cells in Excel 2003 2007
- How to merge cells in Google Sheets
- How to combine 2 or more cells in Excel without losing data
- Merge keyboard shortcuts in Excel
- How to color formula cells in Excel automatically
- Complete tutorial of Excel 2016 (Part 5): Basics of cells and ranges
- How to Lock Cells in Excel
May be interested
- How to Sort Cells Alphabetically in Excelexcel 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. 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 Excelyou'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 Excelcounting 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. 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 Excelexcel 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 Excelwhen 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 Wordin 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 Excelinstructions 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 Excelconditional formatting in excel makes it easier to categorize or highlight data, based on the conditions you set.