This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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. You can see a full list of our Excel training courses here.

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:

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:

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 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:

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:

Drop Lines looks promising.

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

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.

Some other pages relevant to the above blog include: