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.

array formula in merged cells - error message

This is not true, they are allowed in merged cells. Here are a few suggestions to solve this problem.

Unmerge cells

  1. Select the merged cell
  2. Click "Merge & Center" button to unmerge cells
  3. Select first cell
  4. Enter array formula
  5. Select cells
  6. Click "Merge & Center" button to merge cells

array formulas merged cells2

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

array formulas merged cells

Excel defined named ranges

  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 in formula bar
  8. Press enter

array formula in merged cells - named range1

Recommended posts

If you want to learn more about array formulas join Advanced excel course.