Share via

Fill Down is not working on Mac Excel 2016

Anonymous
2019-01-29T22:28:36+00:00

I am trying to put a formula into a cell and then fill down to apply that formula to each row in my sheet. I create a formula:

=IF(A2="xyz",0,1)

I highlight that cell and drag down to the bottom of the column. I then hit control-D and fill down. But the formula does not take - the IF doesn't fire as expected. But if I manually edit the specific cell where the formula is fulfilled (ie. the row where the condition is met), it works. So Fill isn't working but the formula works fine if I manually edit it.  This is happening consistently on any sheet where I try to use Fill Down.  It fails every time, no matter the formula/function, no matter the worksheet.

This used to work just fine. I can't isolate when it broke - some recent Excel update, apparently.

This is Excel 2016 for Mac, version 16.21.1 (190123).

Help?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Bob Jones AKA CyberTaz MVP 435.3K Reputation points
    2019-01-30T16:02:39+00:00

    This isn't an error - it sounds like you have the Calculation Options set to Manual...

    Go to the Formulas tab, click the Calculation Options tool in the Calculation group, then select Automatic.

    Another direction to produce the same result is to go to Excel> Preferences - Calculation to change the Calculation Option to Automatic.

    Was this answer helpful?

    10+ people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-01-30T16:16:58+00:00

    Holy smokes, you're right, Bob!  I had no idea that option existed. It must have been set to automatic and some Office update reset it to manual, b/c I guarantee you *I* didn't change it, seeing I didn't know about it.

    I set it to Automatic, created a new formula in another column, filled it down and the formulas refreshed.

    THANK YOU!  This has been bugging me for several weeks now.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-01-30T15:30:17+00:00

    NOTE: OOOPS - I did this response and used screenshots copy/pasted in, but they didn't save. Edited to add pictures...

    Thank you for the response, Neo.  Here's what I'm doing:

    Note the formula shown in the top right.  That's the formula in B1.  Now I fill down...I double-click the little box in the lower right corner of the cell, and now I see this:

    Note that all values are zero, even the ones that are NOT equal to "abc"

    Here you'll see what is in B4 ... note that the formula is correct but not the result in the cell:

    I get the same results if I drag down from B1 and then hit Control-D to fill down in that manner. It fails just as it does when I double-click the little square thingy.

    Now here's something I discovered later:  IF I save the sheet after making the change (which I often do NOT want to do), I see this:

    ...which is, of course, the desired result.  BUT - I don't WANT to save the sheet until I'm satisfied with the results. I don't want to have to save it to see the correct numbers.

    This error is consistent. Every sheet I have where I try to fill down (which is something I do virtually every day), it fails like this.

    Hopefully this helps...

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-01-30T06:24:26+00:00

    Hi Bill,

    Here are my test steps, please have a look:

    1. Launch Excel and create a blank worksheet.
    2. Type =IF(A2="xyz",0,1) in B3.
    3. Click B3 then drag down to some rows. then click control +D.

    After that all cells I selected display 1 and formulas in cells are different. e.g. B3 =IF(A2="xyz",0,1), B4 =IF(A3="xyz",0,1).

    1. Click B3 then click the formula bar>Select Formula and click Command +C.
    2. Click C3 and click Command +V.

    After that, C3 contains the same formula as B3 and they all display 1.

    Do you mean you can't fill down formula or the formulas are not the same as the first one? To have a better understanding of your scenario, please share with me a screenshot of your result after you fill down your formula. (Mask your personal info).

    Regards,

    Neo

    Was this answer helpful?

    0 comments