Array formula on Excel for Mac

Question

I need to call LINEST in Excel 2011 for Mac, I would say - this was a detective story. My laziness, my curiosity, all was here! I had a homework from university course to use LINEST.

I even stopped trying to do so on MAC, because after googling I could not find any solution how to do it. (I have tried EACH AND EVERY combination of Fn Ctrl Alt Cmd Return!)

Finally, I found a solution here on stack overflow! I want to mention it again, as it was a long time I spent looking for the solution. I have spent 4 days outside of home, to use just windows PC where it is easy as a pie - F2, CTRL+SHIFT+ENTER.

If you want to check if I tell truth - go to HELP in Excel for Mac, type array formula, find LINEST and look at the solution combination!

The solution: select the range, press CONTROL+U and then press ⌘+RETURN.

Here is the link to the original post: Excel formulas giving #VALUE! error when ported to Mac

A detective novel indeed could be written about this non-sense. Ah if only documentation was well written... — David, Mar 14 '16 at 14:31

score 33 · Answer 1 · answered Nov 05 '15 at 17:59

33

This doesn't seem to work in Mac Excel 2016. After a bit of digging, it looks like the key combination for entering the array formula has changed from ⌘+RETURN to CTRL+SHIFT+RETURN.

answered Nov 05 '15 at 17:59

damien_papworth

339
3
2

Yes I've seen this work in Mac Excel 2016 also, just not sure about older versions of Excel. – David Mar 14 '16 at 14:33
1

Looks like this no longer works. At least, it is not working for me on version 15.32 – PhiloT Jul 08 '18 at 12:02
Works for me version 16.15 – GreenAsJade Aug 14 '18 at 06:21

score 7 · Accepted Answer · edited May 18 '15 at 17:40

7

Select the range, press CONTROL+U and then press ⌘+RETURN.

edited May 18 '15 at 17:40

ZygD

22,092
39
79
102

answered Oct 28 '14 at 09:53

Ievgenii

477
1
5
13

I think this answer can and should be improved. **a)** The described actions do not lead to reasonable results if executed in the given order (you'll end up editing a cell that's part of the selected range) **b)** It misses important information, specifically that editing an array formula has to be finished by pressing ⌘ + RETURN **always** otherwise the formula will be interpreted as a simple formular again (and likely result in an #VALUE! error). – Daniel K. Jun 30 '16 at 09:51

score 2 · Answer 3 · answered Jul 24 '21 at 11:58

2

This works 100%.

Select the cells you want to populate with the formulated data.
Press Control + U.
Add your array formula on any cell (preferably first).
Press Control + Shift + Return.

Remember Control and Command are different keys on Mac!!

answered Jul 24 '21 at 11:58

Shubham Khare

21
1

This is confirmed working for Excel 2019 version 16.44 ! – AndiAna Dec 08 '21 at 08:32

score 1 · Answer 4 · answered Apr 21 '16 at 05:43

Found a solution to Excel Mac2016 as having to paste the code into the relevant cell, enter, then go to the end of the formula within the header bar and enter the following:

Enter a formula as an array formula Image + SHIFT + RETURN or CONTROL + SHIFT + RETURN

score 1 · Answer 5 · answered Mar 10 '20 at 11:35

CTRL+SHIFT+ENTER, ARRAY FORMULA EXCEL 2016 MAC. So I arrive late into the game, but maybe someone else will. This almost drove me nuts. No matter what I searched for in Google I came up empty. Whatever I tried, no solution seemed to be in sight. Switched to Excel 2016 quite some time ago and today I needed to do some array formulas. Also sitting on a MacBook Pro 15 Touch Bar 2016. Not that it really matters, but still, since the solution was published on Youtube in 2013. The reason why, for me anyway, nothing worked, is in the Mac OS, the control key by default, for me anyway, is set to manage Mission control, which, at least for me, disabled the control button in Excel. In order to enable the key to actually control functions in Excel, you need to go to System preferences > Mission Control, and disable shortcuts for Mission control. So, let's see how long this solution will last. Probably be back to square one after the coffee break. Have a good one!

score 0 · Answer 6 · answered Mar 17 '21 at 13:42

0

This solution worked for me:

When you’ve chosen range of cells in formula press control+u
Then press shift+enter/return

answered Mar 17 '21 at 13:42

Anna

1

score -3 · Answer 7 · answered Jun 06 '17 at 10:37

-3

Select the desired range of cells
Press Fn + F2 or CONTROL + U
Paste in your array value
Press COMMAND (⌘) + SHIFT + RETURN

answered Jun 06 '17 at 10:37

Wild Woz

1
1

1

COMMAND (⌘) + SHIFT + RETURN just adds a new line. Maybe it worked once, but it certainly doesn't work now. – Jayden Lawson Aug 07 '19 at 01:35

Array formula on Excel for Mac

7 Answers7

Linked