How to enter array formulas in merged cells
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.
Here are a few suggestions to solve this problem.
Table of Contents
1. Unmerge cells
- Select the merged cell.
- Go to tab "Home" on the ribbon.
- Press with left mouse button on the "Merge & Center" button to unmerge cells.
- Select the first cell of the unmerged cell.
- Enter the array formula.
- Select the cells you want to merge.
- Press with left mouse button on the "Merge & Center" button to merge cells.
Tip! You don't need to repeat these actions for all cells below, just press 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.
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.
- Go to tab "Formula" on the ribbon.
- Press with left mouse button on "Name Manager".
- Type a name, I named it arrayformula.
- Enter the array formula in "Refers to:" field.
- Press with left mouse button on OK button.
- Select the merged cell.
- Type =arrayformula
- Press enter.
3. Excel 365 dynamic array formulas and merged cells?
The image above shows what happens when you try to enter a dynamic array formula in a merged cell in Excel 365.
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.
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Highlight a column in a stacked column chart
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
List all unique distinct rows in a given month
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Easily select data using hyperlinks
The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]
Rearrange values using formulas
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
This post will teach you how to work with Excel arrays in visual basic for applications (VBA). Why do you […]
Array formulas allows you to do advanced calculations not possible with regular formulas.
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
2 Responses to “How to enter array formulas in merged cells”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar,
Greetings. I was trying using the ARRAY Formula in the merged Cell, but facing an issue.
For a simple formula in the merged cell, the formula as explained on the website works perfectly but when I try to use it in another merged cell which contains HYPERLINK, I get #REF. I have pasted the code here for your reference. if you could please let me know what is wrong in this code.
=HYPERLINK(INDEX('01-08-2011'!$C$4:$L$129,MATCH(SEARCH!$C$2,'01-08-2011'!$C$4:$C$1159,0),11))
Thanks and regards,
Ali.
Done... Its Resolved :D
Thanks and regards,
Ali.