Author: Oscar Cronquist Article last updated on April 05, 2021

array formula in merged cells - error message

Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: Array formulas are not valid in merged cells.

This is not true, they can be entered in merged cells.

I recommend that you try to avoid merged cells as much as possible, they may cause weird errors and may not play well with other features in Excel.

Here are a few suggestions to solve this problem.

1. Unmerge cells

array formulas merged cells2

  1. Select the merged cell.
  2. Go to tab "Home" on the ribbon.
  3. Click the "Merge & Center" button to unmerge cells.
  4. Select the first cell of the unmerged cell.
  5. Enter the array formula.
  6. Select the cells you want to merge.
  7. Click the "Merge & Center" button to merge cells.

Tip! You don't need to repeat these actions for all cells below, just click and hold on the black dot on the lower right corner of the cell, then drag down as far as needed. See animated image below.

array formulas merged cells

Back to top

2. Excel defined named ranges

This workaround requires you to create a named range containing the array formula you want to use. Here are the steps.

  1. Go to tab "Formula" on the ribbon.
  2. Click "Name Manager".
  3. Type a name, I named it arrayformula.
  4. Enter the array formula in "Refers to:" field.
    array formula in merged cells - named range
  5. Click OK button.
  6. Select the merged cell.
  7. Type =arrayformula
  8. Press enter.

array formula in merged cells - named range1

Back to top

3. Excel 365 dynamic array formulas and merged cells?

Merged cells excel 365 dynamic array formulas 1

The image above shows what happens when you try to enter a dynamic array formula in a merged cell in Excel 365.

Merged cells excel 365 spill range has merged cell

Merged cells don't work with Excel 365 dynamic array formulas, the #SPILL error is what you get if you try to enter the formula in a merged cell or if a merged cell is in the spilling range.

Merged cells excel 365 spill range has merged cell1

Back to top