Hello team,
I have entered an array function, the function is only displayed black on the first cell that I entered it, every else is greyed out. I entered the same function in an other sheet, the function font is black not only in first cell but also in other cells. How can I make the function looks grey out in all the cells other than the first cell?
How can I control this behavior in Excel?
Regards,
Bita
Array formula is greyed out

 Administrator
 Posts: 72233
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Array formula is greyed out
I don't have Microsoft 365 so I cannot help you with this.
Regards,
Hans
Hans

 BronzeLounger
 Posts: 1427
 Joined: 01 Mar 2015, 02:03
Re: Array formula is greyed out
Hi Hans,
Thanks for it!
BittenApple
Thanks for it!
BittenApple

 UraniumLounger
 Posts: 6698
 Joined: 25 Jan 2010, 09:09
 Location: up North (but it's not that grim)
Re: Array formula is greyed out
I do have Excel 365 and as far as I can make out this difference in behaviour is down to how you enter the array formula.
If you use the 'traditional' method of entering an array formulae. i.e. you use 'Ctrl Shift Enter', then as expected, in the cell holding the formula, the formula is surrounded by the array braces { } and the cells covered by the array formula similarly display the same thing. Each cell displays the formula surrounded by { }. All of these cells are displayed in 'black', they are not greyed out.
If though if your version of Excel is 'dynamic array aware' then you do not need to use Ctrl Shift Enter to commit an array formula, you can simply use Enter. If you do that, then the cell holding the array formula is not surrounded by the array braces { } and neither are the formulae in the cells covered by the array. HOWEVER, while the first cell is shown in black, the dependant cells are shown as greyed out.
Does this help?
Ken
If you use the 'traditional' method of entering an array formulae. i.e. you use 'Ctrl Shift Enter', then as expected, in the cell holding the formula, the formula is surrounded by the array braces { } and the cells covered by the array formula similarly display the same thing. Each cell displays the formula surrounded by { }. All of these cells are displayed in 'black', they are not greyed out.
If though if your version of Excel is 'dynamic array aware' then you do not need to use Ctrl Shift Enter to commit an array formula, you can simply use Enter. If you do that, then the cell holding the array formula is not surrounded by the array braces { } and neither are the formulae in the cells covered by the array. HOWEVER, while the first cell is shown in black, the dependant cells are shown as greyed out.
Does this help?
Ken

 BronzeLounger
 Posts: 1427
 Joined: 01 Mar 2015, 02:03
Re: Array formula is greyed out
Hello ken,
It is exactly as you say. Thanks for it.
My Excel 365 and I think I just entered without taking array ctrl+shift+enter the first time, I did the function.
Is there anyway that we can check if our Excel is dynamic array aware?
Regards,
Bita
It is exactly as you say. Thanks for it.
My Excel 365 and I think I just entered without taking array ctrl+shift+enter the first time, I did the function.
Is there anyway that we can check if our Excel is dynamic array aware?
Regards,
Bita

 Administrator
 Posts: 72233
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Array formula is greyed out
Excel in Microsoft 365 supports dynamic arrays.
Excel in Office 2019 and earlier versions does not support dynamic arrays.
Excel in Office 2019 and earlier versions does not support dynamic arrays.
Regards,
Hans
Hans

 BronzeLounger
 Posts: 1427
 Joined: 01 Mar 2015, 02:03
Re: Array formula is greyed out
Hans & Ken,
Do you mean if someone has Microsoft office 2016, that person is not able to create array functions?
I entered some of my array functions with entering just enter for a dashboard in office 365 that I am going to distribute to others that might not have 365 or 2019. because I have 365. Should I go back and reenter the functions by ctrl shift enter as one might not have office 365 or 2019 version?
Please advise me.
Regards,
Bita
Do you mean if someone has Microsoft office 2016, that person is not able to create array functions?
I entered some of my array functions with entering just enter for a dashboard in office 365 that I am going to distribute to others that might not have 365 or 2019. because I have 365. Should I go back and reenter the functions by ctrl shift enter as one might not have office 365 or 2019 version?
Please advise me.
Regards,
Bita

 3StarLounger
 Posts: 239
 Joined: 15 Aug 2016, 11:23
Re: Array formula is greyed out
Yes, they can create array formula USING CTRL+SHIFT+ENTERDo you mean if someone has Microsoft office 2016, that person is not able to create array functions?
In 365 use only ENTER for using array formula.
No. It is not necessary cause excel will recognize.Should I go back and reenter the functions by ctrl shift enter as one might not have office 365 or 2019 version?

 BronzeLounger
 Posts: 1427
 Joined: 01 Mar 2015, 02:03
Re: Array formula is greyed out
Thanks for the response.
bittenapple
bittenapple