Excel Error “You can’t enter an array formula over merged cells”

You created a cool array formula and then merged it across several cells. Later, you realize that you need to modify this array formula. When you try to press Control Shift Enter you get this horrible error:

You can’t enter an array formula over merged Cells






How Did This Happen?

You had an array formula in a merged cell but now you can’t change it? Correct.

The trick is that you have to enter your array in a single cell and then merge the cells.


Steps To Fix Your Merged Cell Array

  • Press ‘Esc’ (escape key) or press ‘Enter’
  • Unmerge the cells
  • In cell with formula made required modifications
  • Press Control Shift Enter
  • Highlight formula and cells to be merged
  • Merge cells


Alternative Solution

Every time you update your array formula you’ll have to follow the tedious steps above.

Instead, I would suggest using the “Center Across Selection” formatting option. You can quickly edit your array and keep the centered look.


Center Across Selection Steps

  • Write your array (press Control Shift Enter)
  • Highlight array and other cells
  • Go to ‘Format Cells’ (shortcut ‘Ctrl’  ‘1’)
  • Alignment / Horizontal / Center Across Selection


See My Excel File

Download my Excel file here to see examples.


Are Merged Cells Evil?

Most of the time they are pure evil. In your raw data you should never have merged cells.  However, I have used them sparingly in an output or dashboard sheet so I’m not one to say to never ever use them. Just know when and where.


Learn More About ARRAY FORMULAS !

Mike Girvin is the array formula master! Here are two YouTube playlists of Mike’s array formula videos!



About Me

My name is Kevin Lehrbass. I’m a Data Analyst.

I’ve been working with databases and spreadsheets since 2001.

Check out my recommended Excel Training and my YouTube channel.


Leave a Reply

Your email address will not be published. Required fields are marked *