Array formula is greyed out

BittenApple
BronzeLounger
Posts: 1427
Joined: 01 Mar 2015, 02:03

Array formula is greyed out

Post by BittenApple »

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

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Array formula is greyed out

Post by HansV »

I don't have Microsoft 365 so I cannot help you with this.
Regards,
Hans

BittenApple
BronzeLounger
Posts: 1427
Joined: 01 Mar 2015, 02:03

Re: Array formula is greyed out

Post by BittenApple »

Hi Hans,
Thanks for it!
BittenApple

User avatar
stuck
UraniumLounger
Posts: 6698
Joined: 25 Jan 2010, 09:09
Location: up North (but it's not that grim)

Re: Array formula is greyed out

Post by stuck »

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

BittenApple
BronzeLounger
Posts: 1427
Joined: 01 Mar 2015, 02:03

Re: Array formula is greyed out

Post by BittenApple »

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

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Array formula is greyed out

Post by HansV »

Excel in Microsoft 365 supports dynamic arrays.
Excel in Office 2019 and earlier versions does not support dynamic arrays.
Regards,
Hans

BittenApple
BronzeLounger
Posts: 1427
Joined: 01 Mar 2015, 02:03

Re: Array formula is greyed out

Post by BittenApple »

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

Toranaga
3StarLounger
Posts: 239
Joined: 15 Aug 2016, 11:23

Re: Array formula is greyed out

Post by Toranaga »

Do you mean if someone has Microsoft office 2016, that person is not able to create array functions?
Yes, they can create array formula USING CTRL+SHIFT+ENTER

In 365 use only ENTER for using array formula.
Should I go back and reenter the functions by ctrl shift enter as one might not have office 365 or 2019 version?
No. It is not necessary cause excel will recognize.

BittenApple
BronzeLounger
Posts: 1427
Joined: 01 Mar 2015, 02:03

Re: Array formula is greyed out

Post by BittenApple »

Thanks for the response.
bittenapple