Can't enter an array 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.

The site is about Microsoft Excel.

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