September 20

Excel Bug in Office 365

I’ve found a rather obnoxious bug in the version of Excel 2013 that comes with Office 365.

As you know, I’m a big fan of doing stuff with charts. One of my favorite things is to make a dynamic series that disappears based on some user functionality like in the animation below.

graph

So far so good. Now look what happens when I change the Y-axis scale to a much larger range. Below, I’ve given the Y-axis a range of [0, 10,000]. Now see what happens when I plot a series of –1’s.

image

That’s not right. I shouldn’t be seeing the line! And if I change the values to plot to –10, I still see the line…

image

But if I change them to –100, the line disappears…that is, until I choose a larger scale for the Y-Axis.

image

For good measure, I tried these three exact same scenarios in Office 2010 and had no issues. The line disappeared as expected.

I used Microsoft’s feedback form for Office 365 and let them know what I found. However, I have feeling the feedback form has the same effectiveness as writing your congressmen. Oh well.

—– Edit:

Jon’s comment made me think I should include a way to get around this bug. He mentioned using NA(), which some might argue I should be using anyway. Here’s how to do it. Below, I’ve added an IF formula between the checkbox result and the values I want to display.

image

If the checkbox is checked, the IF function will return a 1 which is then multiplied by the values across the top making the line reappear. If not, it will become an #N/A which will make the rest of the values become #N/A in turn.

Here’s a challenge:  can anyone thank of a way to do this without using IF?


Tags


You may also like

51: Oz du Soleil & the Global Excel Summit 2021

51: Oz du Soleil & the Global Excel Summit 2021

Global Excel Summit 2021

Global Excel Summit 2021
  • Use NA() and the line won’t be drawn.
    But yeah, it’s a pain. I just reproduced in in Excel 2103, not the one that comes with 365.

  • Ha! Take a look at the addendum to the blog article. I’m wondering if there’s a way to use NA – with the same checkbox functionality designed above – without also using an IF formula (or another conditional formula e.g. CHOOSE, IFERROR… etc.).

  • Eh, there’s nothing wrong with using IF() here. I’m just always interested if there’s an alternative way to do the same things with Boolean operations. I can’t think of a way for this one.
    But I would love it if Microsoft fixed this bug – and, as a bonus, provided the option (in Excel itself) to stop chart animations (would be great if I could save that option to the file). Watching the line animate to the bottom of the chart just amplifies my annoyance with this bug.

  • A follow-up… After this discussion, I looked into the problem a bit more, and it seems to happen because on a scale of 0 to 10000, a 3 pixel line at -1 is thick enough to appear below and above the axis. Excel is smart enough not to draw the markers that fall off-scale, but it still draws the edge of the line.
    This still happens in Excel 2016.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free Power User Quick Guide

    Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

    With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

    >