Drop Lines in Excel Charts: Ah, that's what I needed!
Ever had the feeling that you're missing something obvious? This blog highlights one particular chart option that you may well have overlooked.

Posted by Michael Allsop on 24 May 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Drop Lines in Excel Charts: Ah, that's what I needed!

One of the frustrating things about Excel, particularly when you're producing a chart, is the huge quantity of formatting options that are available. Despite this quantity, you can never find the one you want. This short blog highlights one specific example which you may find useful.

A Default 2D Line Chart

For this example, I wanted to create a multi-series line chart which would track two sets of data across a group of 45 people. Each person was tasked with dropping several slices of buttered toast and counting how many fell butter side down. An extract of the data is shown below:

Excel results table

It looks like a couple of Hobbits joined in the test.

After selecting the data I wanted to show on the chart, I pressed F11 (the default chart button) and changed the chart type to a 2D line chart. This is a cropped view of the result:

2D Line Chart

There are several things I don't like about this:

Along with the other changes mentioned above, I tried showing the vertical gridlines, both major and minor, but neither of these simplified analysing the chart:

Major vertical gridlines Major and minor gridlines
Major vertical gridlines fall
between each data point.
Showing both major and minor
gridlines gets too messy!

Just as I was starting to think I'd have to put up with one of these options, I discovered the Lines button in the Analysis group on the Layout tab:

Options on the Chart Tools Layout tab

Lines - I wonder what they do?

Always being one for trying something new, I clicked on the Lines button and, using the thumbnails as a guide, selected the Drop Lines option:

The Lines options

Drop Lines looks promising.

Curiosity paid off in this instance, and the chart now boasts lines that connect each person with their respective scores:

Chart showing Drop Lines

The drop lines here have been formatted to a medium grey colour.

This example was created using Excel 2010, but you'll find drop lines in the same place in Excel 2007. If you're still using Excel 2003, you will need to right-click on one of the data series lines, choose to Format Data Series... and then go to the Options tab where you will find a checkbox for Drop lines.

This blog has 0 threads Add post