Stacked Bar Chart that shows multiple rows of data together for a single line on a chart

Question

I am working on a smaller data set to create a gantt chart that displays data for multiples rows on a single line chart. For example an activity A is started on a certain date on Line 1 for 2 days and then same activity is started again on a certain date for some other duration. I have tried to create a gantt chart but for each activity it creates another row. I have achieved the required result in Tableau but I am unable to create it in Excel.

The chart I created in excel.

The kind of chart I required..

Excel File

It would be really helpful if someone could tell me what I am doing wrong because I am unable to find out the issue.

Answer 1

With a lot of creativity you could achieve something close to what you want.

I want to highlight two things:

• It take some times as modification needs to be applied to each series,
• and you need to create a custom Y-axis as Excel own Y-axis will not be used.

Result will be a Timeline chart:

Step-by-Step guide:

First we need to modify the data table.

Add a new column which will represent our Y-axis values (I call it: "Y-Axis Position" located in column F). Those values will replace the "Line" column which had values PD-18, PD-19 etc (we will create them by hand later).. It will determine at which height the bar will be placed.

Time to create a Scatter Plot.

I would suggest to mark only one series (1) and then click the icon Scatter (2). In my example I choose serie "AX 1".

Then add your additional series.

I take one at each time (If you have done correct you will see that each new series will get each own color (in picture I get blue and orange). For X-values I use the "Date" and for Y-values I use "Y-Axis Position".

You could also add the Series Name, which will would be "AY 1" in this case. Then it will be correct in the chart "Legend".

Add the error bars to the Scatter plot.

Fastest way is to mark the chart and click on the "+" sign and then "Error Bars". (Otherwise click on Chart -> "Design" (at top) -> "Add Chart Elements" (to the very left) -> "Error Bars" -> "Standard Error".

Click on the upper/lower error bars and then delete those (click "Del" on your keybord).

(Picture below shows, deleted error bars on the right series. The left series is how I select my error bars before I delete them)

Time to modify the graph.

I start with the markers, because it will be easier to click at later on. Click on the markers (1) and go to "Fill & Line" (2) at the list to the right. Click on "Marker Options". I notice that the big square I have highlighted worked good visually. (If you don't get as many as in the picture, try to copy the chart. Use the one you copied and then go to marker options again, you should see more marker types now...).

I choose Size 10. I also change the "Fill" colour to a predestined colour and also the border colour so they match. I do the same procedure for both series.

Mark your error bars (vertically) and go to "Error Bar Options", the small green bars at the top. You see them at the right in the picture (below word Format Error Bars).

Click on the "Plus" (we only want positive error bars) and also choose "No Cap". Do it for both series.

Time to modify the length of the error bars.

Mark one series (1), then choose "Custom: Specify Value" at your right (2). A little window will pop-up (3). The error bars length will be determined by the duration values (4). All the duration for the specific series should be used (notice I only choose 3 values). Do it for Both "Positive" and "Negative" values.

In my example I have marked the blue serie (AY 1). So I will use the "Duration" values for "AY 1" for both positive and negative error bars.

Time to modify the visualization of the error bars.

Click on the error bar. Then go to the "Fill & Line" options (the little paint bucket), then I change my colour so it match with my marker colour. Last thing I do is to change the width to size 10 (same as marker size). Do it for both series.

Add Data Labels to your series.

Click on the marker (that's the reason I keep them) and choose "Add Data Labels" -> "Add Data Labels" as the picture below shows. Do for both series.

Customize the data labels.

Right click on the labels (notice all is highlighted for one series), then click "Format Data Labels". Go to "Label Options" (small green bars to the right). Untick "Y Value" and then click "Value From Cells". Choose the name that will be representive. I choosed the Batch name, i.e. AY 1 ,range C5:C7. Click ok. Do it for both series.

If you want, you could format the X-axis. Right click on the X-axis and choose “Format Axis”. Click on the “Axis Options” (green bars) and the choose a good start date. I added 1 date to reflect the numbers better. You can also choose "Units" -> "Major" and set 1,0. Then every date will be shown in your X-axis.

I also customized the date format to day-month by write: DD-MMM;@ in the “Type” field. (Notice I have Nordic setup/language.)

Modification of the Y-Axis.

I “modify” the Y-Axis as we don’t want the position number (they don't give any valuable information). I create a “Text Box”. Go to “Insert” -> “Shapes” -> “Text Box”.

Drag it and enter the Y-Values. Notice I do my Text Box rectangular and try to fit my values in same height as the original chart numbers which was 1,2,3 (Column F: "Y-Axis Position"). Then I mark my chart Y-axis that has numbers and delete it. Last I place my customized Y-axis and group the chart (when I move my chart I don't need to re-positioning my customized Y-Axis). The Text Box background color is “No Fill”.

I have also deleted the background grids by clicking on the vertical and horizontal lines and delete them.

Last thing I do is to mark the borderline of my chart and choose “No Line”. I do the same for my text box.