Questions & Answers: June

by David Moss on Jul 4, 2007

Charting my anger
Q Where has the Floating Bars chart type been put in Excel 2007? I use floating bars to display sound ranges, such as the difference between sounds made and heard by a variety of creatures and humans. Not all creatures start at 0 like humans or cats, so you need floating bars to display results correctly. The standard chart types firmly nail the bar on the 0 point of the Y axis and that just isn’t right.
Annoyed

A For anyone unaware, before Microsoft Excel 2007, there was a custom chart type called Floating Bars that displayed 3D bars that were more than happy to ‘float’ away from the vertical axis, their starting point being dictated by a value on the horizontal axis. This was a very useful chart type, as were some of the other custom chart types. They are no more. Excel 2007 doesn’t actually have a custom chart types section at all.

Notice how the standard stacked bar chart fills the area between 0 and the actual start of the data series with a different coloured section. It sees the single piece of data as two separate series.

The floating bars fix is complete. The spurious data points are rendered invisible, and each series has its own colour code. Add a dash of chart and axis titles to garnish.

I really like Office 2007, but charting in Excel, while gaining enormously in some areas, has really taken a bad swipe in others, the loss of floating bars being a point in question. Now, I’m well aware that you can fake up floating bars, which is just as well as it forms the basis for my answer, but it really shouldn’t have been necessary to resort to this method.

Perhaps someone at Microsoft might like to consider the re-introduction of floating bars and, while you’re at it, fix the standard bar chart so that it doesn’t stagger negative and positive value bars on either side of a central column. Let’s have the nice, fat, directly opposing bars of old back as an option at least, please. In the meantime, here’s how to create fake floating bars in Excel 2007.

Once you’ve entered your data and selected the items you wish to appear in the chart, click on the Insert tab and then on the Bar button on the Ribbon.

Choose a chart style that suits what you want. The default stacked 2D bar style works pretty well.

When you create your chart, you’ll see that for any values that don’t start at 0, Excel has created a different coloured section of bar that starts on the vertical axis and changes colour when it reaches the point that the bar should really start.

What you need to do now is highlight these short bar sections (just click on one), then right-click and select Format Data Series from the pop-up menu.

When the Format Data Series dialog appears, click on Fill and then on the ‘No fill’ option button in the right pane. Next, click on Border Color and select the ‘No line’ option. Click Close and you’ll see that the short sections of bar have vanished, and the bars that should be floating now appear to be doing that.

Once you’ve done that, you’ll need to do a little work to tidy up your chart. The Series 1 and Series 2 legend needs to be removed, and you need a chart title and a horizontal axis title too. You can then change the colour of individual data points.

Parasite spammer
Q When I access my email, my inbox fills up with returned undeliverable emails that are being sent using my domain name. None of them are from me and are returned with random user names @ my domain (such as lkpb@iliffe.net). I’ve pasted an example in below that came from MAILER-DAEMON@americanwebservices.com:

Failed to deliver to ‘arrow@rcip.com’ LOCAL module (account arrow@rcip.com) reports: account is full (quota exceeded).

All sorts of reasons can be given for the failure to deliver. Some such emails are very long and may have attachments (that I’ve never opened). My AVG virus scanner says they don’t contain viruses. So, is someone using my domain name to generate spam, or are these just plain spam emails that I should filter out?
A Iliffe

A My guess is that someone is indeed trying to get your domain to generate spam. Your mail server is rejecting the emails and as admin, yours is the account that has been nominated to get undeliverable email notifications. Emails with viruses/trojans attached are often sent out with the ‘Undeliverable Email’ tag on a totally random basis, but you won’t usually get loads each day, and they’re aimed more at inexperienced users who might open them worried that an email hasn’t been sent. I’d recommend a spot of upstream filtering.

Talk to your ISP about its anti-spam services, or to someone like MessageLabs. If MessageLabs, your ISP or whoever you choose were to handle your incoming mail queue, you could then restrict the incoming SMTP address range to only allow mail from that one source. That should sort out your problem.

This article appeared in the June, 2007 issue of PC Authority.