#Conditional formatting excel 2016 mac full
In fact, your green one should be an "equal-to", not a "contains", unless you can guarantee the entry will never have a "yes" anywhere in it.ġ I generally like to use full ranges ( Applies to of D:D) for my conditional formatting, and suppress it in the heading row with either ROW() > 1 in the specific conditional formatting, or a separate conditional format that just covers the heading row and uses the "Stop if true" functionality to suppress later conditional formats.Ģ I mention this, because when highlighting cells as you're doing, I often like to tie in an ISBLANK reference to a column elsewhere that should always be populated, so the empty cell highlighting doesn't extend off into infinity.ģ Based on your comment, Mac Excel may be lacking the full set of choices available in Windows. You should be able to do that one with cell-value-is-equal-to 3. That said, your red one should show similar problems to the yellow one. For instance, if you used $D2 instead of D2 and an Applies to that extended into Column E, it would reference D2 when evaluating for E2, because the column reference is locked/static. You can even do things like highlight multiple cells based on a single column using static references 2. Sadly, the column widths aren't adjustable in this window, but I assure you that the yellow formula is logically equivalent.
#Conditional formatting excel 2016 mac windows
In this example, it's the Windows version and we're starting with A1 instead of D2, but the logic is the same. That means whichever formula comes last is the one that will take precedence. The conditional formats are applied in order.
You need to use the Stop If True function or arrange your formulas in the right order. The formula you're using tries to evaluate the entire range, shifted down once for each row. If the above is combined with an Applies to that starts with cell D2, it will check D2 to highlight D2, D3 to highlight D3, and so on. When working with conditional formatting, Excel will evaluate them relative to the "Applies to" range 1. Your formulas should not have ranges in them. You want to make sure you don't have any competing/erroneous conditions that could be messing up the ones you're working with. Start by changing Show formatting rules for to let you see everything. That said, I can't think of any reason why conditional formatting would function differently across platforms. Note that my experience is entirely in the Windows-based version, so if the Mac version has any inexplicably different behaviors, it may just be Mac-version-strangeness.