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
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!
- Excel Array Formulas Beginner to Advanced
- Ctrl+Shift+Enter: Excel Array Formulas #00: Intro To DVD and Video Series
My name is Kevin Lehrbass. I’m a Data Analyst at McKinsey & Company in Toronto.
I’ve been working with databases and spreadsheets since 2001.