How to merge cells in Excel without losing data

The tutorial demonstrates different techniques to quickly merge two cells in Excel and combine multiple cells row by row or column by column without losing data in Excel 365, Excel 2021, 2019, 2016, 2013, 2010 and lower.

In your Excel worksheets, you may often need to merge two or more cells into one large cell. For example, you may want to combine several cells for a better data presentation or structure. In other cases, there may be too much content to be displayed in one cell, and you decide to merge it with adjacent blank cells.

Whatever the reason, combining cells in Excel is not as straightforward as it may seem. If at least two cells you are trying to join contain data, the standard Excel Merge Cells feature will only keep the upper-left cell value and discard values in other cells.

But is there a way to merge cells in Excel without losing data? Of course there is. And further on in this tutorial, you will find a few solutions that work in all versions of Excel 2016, Excel 2013, Excel 2010 and lower.

Combine cells using Excel's Merge and Center feature

The fastest and easiest way to combine two or more cells in Excel is to use the built-in Merge and Center option. The whole process takes only 2 quick steps:

  1. Select the contiguous cells you want to combine.
  2. On the Home tab > Alignment group, click the Merge & Center

In this example, we have a list of fruits in cell A1 and we want to merge it with a couple of empty cells to the right (B2 and C2) to create a large cell that fits the entire list.
Combine cells in Excel using the Merge and Center feature

Once you click Merge and Center, the selected cells will be combined into one cell and the text is centered like in the following screenshot:
Merged and centered cells

Other merging options in Excel

To access a couple more merge options provided by Excel, click the little drop-down arrow next to the Merge & Center button and choose the option you want from the drop-down menu:
A few more merging options available in Excel

Merge Across - combine the selected cells in each row individually:
Merge Across - combine the selected cells in each row individually

Merge Cells - join the selected cells into a single cell without centering the text:
Merge Cells - join the selected cells into a single cell without centering the text

Tip. To change the text alignment after merging, simply select the merged cell and click the desired alignment in the Alignment group on the Home tab.

Excel's merging features - limitations and specificities

When using Excel's built-in features to combine cells, there are a few things to keep in mind:

  1. Make sure that all the data you want to include in a merged cell is entered in the left-most cell of the selected range because only the content of the upper-left cell will survive after merging, data in all other cells will be deleted. If you are looking to combine two or more cells with data in them, check out How to merge cells without losing data.
  2. If the Merge and Center button is greyed out, most likely the selected cells are in Edit mode. Press the Enter key to cancel the Edit mode, and then try to merge cells.
  3. None of standard Excel merging options works for the cells inside an Excel table. You have to convert a table to a usual range first (right click the table and select Table > Convert to Range from the context menu), and then combine the cells.
  4. It's not possible to sort a range containing both merged and unmerged cells.

How to merge cells in Excel without losing data

As already mentioned, the standard Excel merge features keep the content of the top-left cell only. And although Microsoft has made quite a lot of improvements in the recent versions of Excel, the Merge Cells functionality seems to have slipped out of their attention and this critical limitation persists even in Excel 2013 and Excel 2016. Well, where there is no obvious way, there is a workaround :)

Method 1. Combine cells within one column (Justify feature)

This is a quick and easy method of merging cells keeping all their content. However, it requires that all the cells to be merged reside in one area in one column.

  1. Select all the cells you want to combine.
  2. Make the column wide enough to fit the contents of all cells.
    Make the column wide enough to fit the contents of all cells to be merged.
  3. On the Home tab, in the Editing group, click Fill > Justify. This will move the contents of the selected cells to the top-most cell.
    Justify cells.
  4. Click Merge and Center or Merge Cells, depending on whether you want the merged text to be centered or not.

If the combined values spread across two or more rows, make the column a bit wider and repeat the process.

This merging technique is easy to use, however it does have a number of limitations:

  • Using Justify you can only join cells in a single column.
  • It works for text only, numerical values or formulas cannot be merged in this way.
  • It does not work if there are any blank cells in between the cells to be merged.

Method 2. Merge multiple cells with data in any range (Merge Cells add-in)

To be able to merge two or more cells in Excel without losing data and without extra "tricks", we created a special tool - Merge Cells for Excel.
The Merge Cells add-in for Excel

Using this add-in, you can quickly combine multiple cells containing any data types including text, numbers, dates and special symbols. Also, you can separate the values with any delimiter of your choosing such as a comma, space, slash or line break.

To join cells exactly the way you want them, configure the following options:

  • Choose Cells into one under "What to merge".
  • Select the delimiter under "Separate values with".
  • Specify the cell where you want to place the result: top-left, top-right, bottom-left or bottom-right.
  • Make sure the Merge all areas in the selection option is selected. If this box is not checked, the add-in will work like the Excel CONCATENATE function, i.e. combine the values without merging the cells.

Combine two or more cells without losing data with the Merge Cells add-in

Apart from joining all cells in the selected range, this tool can also merge rows and combine columns, you just have to select the corresponding option in the "What to merge" drop-down list.
Merging rows or columns in Excel

To give the Merge Cells add-in a try, you are welcome to download the evaluation version for Excel 2016 - 365.

Method 3. Use CONCATENATE or CONCAT function to combine two or multiple cells

Users who feel more comfortable with Excel formulas, may like this way to combine cells in Excel. You can employ the CONCATENATE function or the & operator to join the cells' values first, and then merge the cells if needed. In Excel 2016 - Excel 365, you can also use the CONCAT function for the same purpose. The detailed steps follow below.

Supposing you want to combine two cells in your Excel sheet, A2 and B2, and both cells have data in them. Not to lose the value in the second cell during merging, concatenate the two cells by using either of the following formulas:

=CONCATENATE(A2,", ",B2)

=A2&", "&B2
Use the CONCATENATE function to combine the cells' values

The formula, however, inserts the concatenated values in another cell. If you do need to merge two cells with the original data, A2 and B2 in this example, then a few extra steps are required:

  • Copy the cell with the CONCATENATE formula (D2).
  • Paste the copied value in the top-left cell of the range you want to merge (A2). To do this, right click the cell and select Paste Special > Values from the context menu.
  • Select the cells that you want to join (A2 and B2) and click Merge and Center.

In a similar manner, you can merge multiple cells in Excel, the CONCATENATE formula will be just a little bit longer in this case. An advantage of this approach is that you can separate values with different delimiters within a single formula, for example:

=CONCATENATE(A2, ": ", B2, ", ", C2)
Combine the values of multiple cells using the CONCATENATE function.

You can find more formula examples in the following tutorials:

Shortcut for merging cells in Excel

If you merge cells in your Excel worksheets on a regular basis, you may find useful the following Merge Cells shortcut.

  1. Select the cells you want to merge.
  2. Press the Alt key that provides access the commands on the Excel ribbon and hold it until an overlay appears.
  3. Hit H to select the Home tab.
  4. Press M to switch to Merge & Center.
  5. Press one of the following keys:
    • C to merge and center the selected cells
    • A to merge cells in each individual row
    • M to merge cells without centering

Shortcut to merge cells in Excel

At first sight, the merge shortcut seems a bit long-winded, but with a little practice you may find this way to combine cells faster than clicking the Merge and Center button with the mouse.

How to quickly find merged cells

To find merged cells in your Excel sheet, perform the following steps:

  1. Press Ctrl + F to open the Find and Replace dialog, or click Find & Select > Find.
  2. On the Find tab, click Options > Format.
    In the Find and Replace dialog, click Options > Format.
  3. On the Alignment tab, select the Merge cells box under Text control, and click OK.
    Finding merged cells in Excel
  4. Finally, click either Find Next to select the next merged cell, or Find All to find all merged cells on the sheet. If you choose the latter, Microsoft Excel will display a list of all found merged cells and let you navigate between them by selecting one of the merged cells in this list:
    Finding all merged cells in a sheet

How to unmerge cells in Excel

If you changed your mind immediately after merging cells, you can quickly unmerge them by pressing the shortcut Ctrl + Z or clicking the Undo button on the Quick Access Toolbar.

To split the previously merged cell, select that cell and click Merge & Center, or click the little arrow next to Merge & Center, and select Unmerge Cells:
Unmerging cells in Excel

After unmerging the cells, the entire contents will appear in the top-left cell.

For more information on how to quickly unmerge cells in Excel, read this article.

Alternatives to merging cells in Excel

It goes without saying that merged cells can help present the information in your Excel worksheets in a better and more meaningful way… but they spawn numerous side-effects that you may not be even aware of. Here are just a few examples:

  • You can't sort a column with merged cells.
  • Neither AutoFill nor Fill Flash feature works if a range of cells to be filled contains merged cells.
  • You cannot turn a range containing at least one merged cell into a full-fledged Excel table, let alone a pivot table.

So, my advice would be to think twice before merging cells in Excel and do this only when really needed for presentation or similar purposes, e.g. to center the table title across the table.

If you want to combine cells somewhere in the middle of your Excel sheet, you may consider using the Center Across Selection feature as an alternative:

  • Select the cells that you'd like to join, B4 and C4 in this example.
  • Press Ctrl + 1 to open the Format Cells
  • Switch to the Alignment tab and select the Center Across Selection option from the Horizontal drop-down list, and then click OK.

Center Across Selection instead of merging cells

In terms of look, the result is indistinguishable from the merged cell:
The Center Across Selection feature in action

To prove that we did not really merge two cells, we can select each one individually:
The text is displayed across two cells, but the cells are not merged.

This is how you can combine two cells in Excel or merge multiple cells without losing data. Hopefully, this information has proved useful for your day-to-day tasks. I thank you for reading and hope to see on our blog next week.

You may also be interested in

Table of contents

40 comments

  1. I have two cells and want to combine data into one cell with separate as Pipe [|] without any space. APpreciate your response.

  2. Can you merge 2 cells without spacing or ; to breakup the values in ech cell? Would rather they combine into a single value.

  3. My excel file is constantly hanging with only size of 296 KB, and data at Sr. No. 1, which have multiple merged rows, has been intermingled into data at Sr. No. 2's merged rows. Kindly guide me in this regard. Thanks in advance.

  4. Se me bloqueo el Excel. Sale el siguiente mensaje: merging cells only keeps the upper-left value and discards other values Qúe debo hacer?
    Agradezo su ayuda

  5. can anybody tell me how to copy text from single cell and paste it into merged cell for window 11

    • Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  6. Or, you can just copy BOTH cells, then paste into a note pad

  7. I have two sequence cells filled with text, I want two merge them in the first one.

    • Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

      • Hi! i merge rows but i want to know how i will see the right rows values when i filtered it. for example,

        A1(country) B1(name)
        B2(name

        when i filterd for A1(merge cells) i want to see both B1 and B2 in the right row

      • i have to combine 2 columns into 1 but the format is a bit werd
        currently i have
        apple| 5
        apple| 4
        apple| 7
        orange| 4
        orange| 2

        and i need a formula to be able to get
        apple
        5
        4
        7
        orange
        4
        2

        This list is about 800 long please assist!

        • Hi!
          Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. I'm really sorry, we cannot help you with this issue.

        • Creating a pivot table with that range is the first step to get the final result, the steps in between is up to your skills.

  8. Hi there
    I have two cells with data as follows

    Coloum A Coloum B

    SELL BTC/USDT

    I need both cells to merge without loosing the data so only one cell shows SELL BTC/USDT
    Is this easy to do ?

    • Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  9. When merging cells which are contain more data, it showing #############################
    How to solve the problem

  10. How do I merge values without losing the format, for example, I have:

    A1: 12,000
    B2: 24,000

    When I merge with & in another cell it comes:

    C3: 12000 - 24000 (the coma is missing)

    I wish to have

    C3: 12,000 - 24,000

  11. How would i Merge Date and time into one cell without losing value of either, for example:

    02/11/2020 09:29

    As it currently stands, Date is in Cell A and Time is in Cell B, but i'd like to merge them both into Cell A without losing the data.

    Thank you!

    • Hello!
      Date and time are stored in Excel as numbers. Therefore, you can simply sum these cells. Set the cell format "mm/dd/yyyy hh:mm"
      I hope my advice will help you solve your task.

  12. Method 3 saved my life!! I loooove this website sooo much! I ad to mere over 5,000 first and last names so THANK YOU!!!

  13. 16-1101-EN-S-000-SD-CL2-
    0003
    convert pdf into excel get value showing in excel in two row i need this two values together with top row if this possible with merge command or any other option is there

  14. Trying to merge 2 rows on a Mac Excel 2011 but your software seems to only work for a PC. Do you have a Mac version?

  15. Hi people
    I have a the following work and I need help. I have A column which has several codes "427, 540,etc" random codes like that. Each code has a list of skills associated to it in column B distributed in different cells along several rows on column B. I need to be able to merge all the skills that belongs to one code for example 427 which goes from A:2 to A:20 into one cell in column C without loosing data and then repeat the same process for the rest of the codes in A. so I will have Column A - header : Key Column B Header:Skills Column C: concatenated list of skills by code.
    A:2 to A:19 (under the "key" header) I have code 427, for code 427 from B:2 to B:19 I have listed skills, on C:2 I need the listed skills from B:2 to B:19 into that cell (C:2). And repeat the process for "key" 540. Note that the number of cells occupied by skills will differ by each code. Also I need using the code below be able no not having to input myself the cell where I want the merged skills, I want them on the same row that I have the New "key"

    I have this code currently
    Sub JoinCells()

    Set xJoinRange = Application.InputBox(prompt:="Highlight source cells to merge", Type:=8)
    xSource = 0
    xSource = xJoinRange.Rows.Count
    xType = "rows"
    If xSource = 1 Then
    xSource = xJoinRange.Columns.Count
    xType = "columns"
    End If
    Set xDestination = Application.InputBox(prompt:="Highlight destination cell", Type:=8)
    If xType = "rows" Then
    temp = xJoinRange.Rows(1).Value
    For i = 2 To xSource
    temp = temp & " " & xJoinRange.Rows(i).Value
    Next i
    Else
    temp = xJoinRange.Columns(1).Value
    For i = 2 To xSource
    temp = temp & " " & xJoinRange.Columns(i).Value
    Next i
    End If

    xDestination.Value = temp

    End Sub

    Thanks!

  16. Hi,
    I want to merge multiple cells based on other cells.of other cells are merged, target cells automatically will merge and sum two values.
    For example:
    If I merge A1 & A2, then D1 & D2 will merge automatically and will sum C1 & C2.

    Please help me.

  17. i have below data in 2 column and i need data same data against Each code in one Cell.

    Code Country
    1234 America
    1234 USA
    1234 Pakistan
    1234 England
    1234 UK
    1234 London
    5000 Canada
    5000 Itlay
    5000 UAE
    5000 Germany
    5464 India
    5464 Egypt
    5464 Iraq
    5464 UK
    5464 South Africa
    5464 China
    5464 Japan
    5464 Scotland

    1234 ?
    5000 ?
    5464 ?

    I need all countries names in one cell against each code

    thanks

  18. Hi, I am using an advanced filter on a column of data that contains country headings. Each country takes up three rows of data e.g.
    Australia
    Australia
    Australia
    Bahrain
    Bahrain
    Bahrain

    And so on. I want to merge the countries but keep the data in each cell. I know I can use the format painter to do this but this makes further updating of the spreadsheet difficult for users who do not know what I have done. Is there a way I can format the cells to fix this problem?

    Further to this, when merged, the data cannot all be sorted alphabetically, so at the moment I have to run a macro to unmerge the cells, sort, and then remerge them using a format painter. If there is a way to avoid using the merge button itself, and achieve the same result that would be perfect.

  19. Thank you for your help with this.

  20. Hello There,

    I have to merge the cells again & again for my business packing list but i don`t know what is the short key of merging cells or if there is no such key then how to set a formula which would merge alternate cells by applying one short key instead of clicking the MERGE & CENTER button with mouse. i have tried aforementioned formula but it is not working after once. can someone help that how it would work continuous?

    Stay happy.

  21. Hello! ma'm!
    i am facing the problem of splitting the data of such type
    100.008400,0.125700
    99.983900,0.130800
    99.910600,0.146100
    99.788500,0.171500
    99.617700,0.207000
    99.398400,0.252400
    into two different columns without commas. Is this possible? If yes than how?

    • Very easy w/o using VBA
      Go to Data Tab, then Text to Columns
      Keep it on Delimited (click next)
      Unclick the default and click the Comma (click next)
      This page will show you how it will look (see Data preview). If it looks good then click Finish. If you click "next" you can format the cells if need be (such as number, text, etc).

  22. How to merge cells with two different cells with some cells containing the first, last name and the other cell containing first, last middle initial?
    Exp.
    Susan, Smith
    Susan, Smith A.

    Thanks in advance

    • Hello Susan,

      You can take the following macro as a template and modify, if necessary.

      Sub MergeSimilarNames()

      Dim sel As Range, sameCells As Range
      Dim rowsCount As Integer
      Dim priorRowIndex As Integer

      Set sel = Application.Selection
      rowsCount = sel.Rows.Count
      priorRowIndex = 1
      Application.DisplayAlerts = False

      Set sameCells = sel.Cells(1, 1)
      For i = 1 To rowsCount
      Dim pos1, pos2 As Integer
      Dim SimilarNames As Boolean

      pos1 = InStr(sel.Cells(i, 1).Value, sel.Cells(priorRowIndex, 1).Value)
      pos2 = InStr(sel.Cells(priorRowIndex, 1).Value, sel.Cells(i, 1).Value)
      SimilarNames = (pos1 > 0) Or (pos2 > 0)
      If Not SimilarNames Then
      If (sameCells.Rows.Count > 1) Then
      sameCells.Merge
      End If
      Set sameCells = sel.Cells(i, 1)
      Else
      Set sameCells = Application.Union(sameCells, sel.Cells(i, 1))
      End If
      priorRowIndex = i
      Next

      If (sameCells.Rows.Count > 1) Then
      sameCells.Merge
      End If

      Application.DisplayAlerts = True
      End Sub

  23. Hi,

    I have a one of the columns with sequentially repeated values. I am looking for a shortcut or the easiest way to merge the cells with same values with in the column without impacting any other columns/Rows.

    Please let me know if you have any simple ways.

    Cheers

    • Hello Sri,

      You can use the macro below to merge values in the first column of the selected range. But please create a backup copy of the book.

      Sub MergeSameCells()

      Dim sel As Range, sameCells As Range
      Dim rowsCount As Integer
      Dim priorRowIndex As Integer

      Set sel = Application.Selection
      rowsCount = sel.Rows.Count
      priorRowIndex = 1
      Application.DisplayAlerts = False

      Set sameCells = sel.Cells(1, 1)
      For i = 1 To rowsCount
      If sel.Cells(i, 1).Value sel.Cells(priorRowIndex, 1).Value Then
      If (sameCells.Rows.Count > 1) Then
      sameCells.Merge
      End If
      Set sameCells = sel.Cells(i, 1)
      Else
      Set sameCells = Application.Union(sameCells, sel.Cells(i, 1))
      End If
      priorRowIndex = i
      Next

      If (sameCells.Rows.Count > 1) Then
      sameCells.Merge
      End If

      Application.DisplayAlerts = True
      End Sub

      Also, you can use our Duplicate Remover add-in to select duplicates. Then you can delete them. In this case cells will not merged.
      https://www.ablebits.com/excel-suite/find-remove-duplicates.php

  24. Thank you. Useful

    Requesting to present VBA Looping

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)