2 Useful Tips to Avoid the Problems Caused by Merged Cells in Your Excel Worksheet

Merged cells have a wide range of use in Excel. However, sometimes it will also cause some problems. Now we will show you how to avoid those problems.

The feature of merging cells is always used in many different conditions. For example, you will merge cells for the header of a range. However, sometimes those merged cells will cause a lot of troubles to your work. Below are the most frequently appeared problems. And we have also provided two methods to avoid the problems caused by merged cells.

The Problems that may Happen

Actually, there are many unexpected problems that will happen in an Excel file. Here we will list some of them.

Problem 1: Count the Number of Cells

Counting cells is a common task in your work. However, when there are merged cells in a range, the result will be different. The image below shows an example of a range. And there are several merged cells.Example

  1. Click a blank cell in the worksheet. Here we will click cell B15.
  2. And then input this formula into the cell:


  1. After that, input another cell into another cell C15:


  1. There is a merged cell in cell B10. And you will find that the results of the two formulas are different.Different Result

This is because this merged cell is counted in column B instead of column C. Therefore, when you need to count cells in a range with merged cells, you need to pay more attention.

Problem 2: COPY and Paste

Copy and paste are also the most frequently used features in Excel. But when you need to copy merged cells, you will also meet with problems.

  1. Suppose now you need to copy the header of the range into another cell. You will click this cell and press the shortcut keys “Ctrl +C” on the keyboard.
  2. And then you will click the target cell and press the button “Ctrl +V”.

You will find that the target cell will also merge with other cells.Merged Cell

But actually you only need the contents itself. Therefore, you need to copy the item again. This problem is also frequently appeared in your work.

Those problems will cause trouble to you. And in the coming parts, we will show you two tips to avoid the problem.

Method 1: Count the Merged cells

The first method is to count the number of merged cells in the range. You can use a VBA macro to count the merged cell. Thus, when you need to count the worksheet, you will not make mistakes.

  1. Press the shortcut keys “Alt +F11” on the keyboard.
  2. And then insert a new module.Insert Module
  3. Now copy the following codes into the module:
Sub CountMergedCells()
  Dim objRange As Range, strMergedCellAddress As String, objMergedCell As Object
  Set objMergedCell = CreateObject("Scripting.Dictionary")
  For Each objRange In Range("A1:F14")
    If objRange.MergeCells Then
      strMergedCellAddress = objRange.MergeArea.Address
      objMergedCell(strMergedCellAddress) = ""
    End If
  Next objRange

  MsgBox objMergedCell.Count
End Sub

In this VBA macro, you can change the range into your target range.

  1. Next you can run this macro. And a pop-up window will show you the number of merged cells.New Window

There are 3 merged cells in the worksheet.

On the other hand, sometimes you need to find the exact position of your target. You can refer to our previous article 2 Practical Methods to Find Merged Cells in Your Excel. You can use the two methods in this article to find them.

Method 2: Change Cells Alignment

Instead of counting the merged cells, you can also avoid using this tip. Here we will use the header as an example. And the content is in cell A1.

  1. Click the target cells that you need to merge. Here we will click A1:F1.
  2. And then press the shortcut keys “Ctrl +1” on the keyboard.
  3. In the “Format Cells” window, click the tab “Alignment”.
  4. And then click the small button in the text box of the “Horizontal”.
  5. After that, choose the option “Center Across Selection” in the drop-down list.Alignment
  6. Next click the button “OK” in the window. Now you will find that the content is in the middle of the first row.Result

But actually there is no merged cell. Therefore, the problems caused by merged cells will not happen again.

Don’t Repair Excel Files by Yourself

Not everyone can repair corrupted Excel files by themselves. When you meet with Excel corruption, you may search on the Internet. Although some symptoms are similar, the reasons may be different. Therefore, the methods on the Internet may not fix you problem. You can use a powerful tool to repair corrupt Excel data. This tool can analyze the problems and find the best way to solve Excel errors.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word docx document damage and outlook repair software products. For more information visit www.datanumen.com