0

I've spent the last day searching on Google and YouTube. The closest I found was a tutorial on stacked bar charts, but it does not completely answer my question.

I'm trying to do the following: Conceptual Excel Chart

For example, let's say I have the following:

  1. Radio 1 (Blue) - 200 to 300
  2. Radio 1 (Red) - 350 to 400
  3. Radio 2 - 300 to 400

What I would like to show are the two entries for Radio 1 and Radio 2 on the y-axis. On the chart, I would like a bar showing the 200 to 300 range for Radio 1 (Blue) and the 350 to 400 range for Radio 1 (Red). Both entries should be aligned to the Radio 1 label. Preferably, the Blue bar should have "Blue" label and the Red bar should have "Red" label. Below Radio 1, I want a Radio 2 entry that shows a bar from 300 to 400. The bars should be aligned to the x-axis.

My Excel version is Version 1705 (Build 8201.2193).

I would appreciate any help and additional tips you can throw my way.

Thank you!

2 Answers 2

0

Excel is really the wrong application for this kind of plot.

But one thing you can try is to add "spacer" data and make it invisible after the chart is created.

Set up the data table as shown using "fillers" wherever you want blank space. Doing it this way allows you to choose different colors for all the bars at the end.

Note that the graph makes the data cumulative. i.e. the data point for the end of the Google bar has to be 110, not 210 because the Filler has already contributed 100.

Select all the data (including headers) and choose Stacked Bar Graph from the appropriate tab or menu. The default chart may be backwards (mine was). If it is, go to Chart > Source Data... and click the Switch Row/Column button.

enter image description here

Finally, format (CTRL-click) each of the "Filler" series and set "No Fill", "No Line" and deselect "Shadow". I've left the line here for visibility.

PS: The overlap between the two Msft bars is a separate series. You can probably find a way to format those 10 MHz better. Also, some versions of Excel will let you add data labels on the bars.

1
  • Thank you for the tips and comments! This is a helpful technique. However, as you mentioned, I have decided to evaluate Tableau as an alternative to visualizing the data.
    – Charles M.
    Sep 21, 2017 at 17:50
0

To "shift" a bar to the middle of the chart, you can use a stacked bar chart, with two series:

  1. From 0 to Start
  2. The difference between Start and End

The apparent shift is handled by changing the Fill on the first series to No Fill

Your first example might something like this: enter image description here

Note: I have selected the first series in the chart so you can see it. When not selected it's invisible.The second series is based on the Size column.

The graphic from your second chart is a little harder, particularly because of the overlap. To accomplish this, you will need to add additional series for each segment.

enter image description here

Remember in a stacked bar chart, you are setting the initial value (Start) and then plotting incremental changes thereafter. In other words, GOOG Start and End values of 10 and 210 are plotted as one bar from 0 to 10, made invisble, and a second bar from 10 to 210 (size = 200).

1
  • Thank you for the tips and comments! I will keep this in mind for future activities. At this point, I'm going to try evaluating another tool to see if it accomplishes what I need.
    – Charles M.
    Sep 21, 2017 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.