Bar Chart of Start and End point on a horizontal line

[neim0003]

I'm trying to make a chart that will show where each name starts and ends on a horizontal line. The path is 1 through 5, but each name will start and end at different places. I believe a Bar Chart will show this well, where it is white for the stops that are outside the limits and colored for the area inside the limits, but not sure how to do that. Below is an example of the data and a drawing of what I want to create. Is there an easy way to do this I am missing in Excel 2010?

Name	Start Location	End Location
Paul	1	5
John	2	4
George	1	3
Ringo	2	5

example.jpg

[dflak]

The way to do this is with a helper column. Create a new column that measures the length of the bar which is end point - start point.

The create a stacked bar chart with series 1 as the start and series 2 as the length. Then assign no fill to series 1.

[neim0003]

Fantastic! I knew it had to be simple and couldn't wrap my head around it. Thank you!

[neim0003]

Follow-up ... is there an easy way to show a single line if the journey where start location and end location are the same? (e.g. Pete starts at 5 and ends at 5)

[dflak]

In a word, "CHEAT!"

I changed the formula in Column D to: =C3-B3+0.05*(B3=C3). What this does is add 0.05 to the length if it otherwise would have been zero. So it produces a thin bar at the value. With additional helper columns. I could get it to center on the line instead of being on the right side of it.

[neim0003]

that is perfect, thank you again!