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?

Advertisements

7 thoughts on “Excel Bug in Office 365

  1. jonpeltier

    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.

    Reply
  2. Jordan G Post author

    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.).

    Reply
  3. Jordan G Post author

    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.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s