If you have dates that you want to convert into a quarter, you may have…
Do you also want to create a multiple timeline bar chart in Excel? Maybe you want to make what is essentially a Gantt chart, with floating bars representing time durations. It involves that you have to create a stacked bar chart. You have read and tried everything, but it seems as if you are missing something. This article will explain how to create a bar chart in Excel with start time and duration.
Table of contents
Steps to create a bar chart in Excel with start time and duration
Let’s say that you have data of three work shifts on each day, and you want to enter it in an Excel sheet. This example only covers three days, from the 6th of February until the 8th of February. It should display a stacked bar chart with three rows for each day. Then each day would have three separate floating bars representing the three-time duration of that day. Below, you can see the start and end time of every shift and each shift’s duration.
1 – Arrange the data in Excel
First, you want to enter the data the right way. In the following steps, we will show you how to do this.
- Write the three dates underneath each other in rows, cell range A2:A4.
- Then type the start time of each shift next to each date in columns, cell range B2:B4.
- The duration of all the shifts should be in the cell ranges C2:C4, E2:E4, G2:G4.
- Calculate the time between each shift and write it under the break. So if the first shift on the 6th of February ends at 08:35 and the second shift start at 10:00, then the break is 1 hour and 25 minutes long. Write 01:25 under the break, which is highlighted in the example below. Do this for every break time between all the shifts.
Then the data in your Excel sheet should look like the picture below.
2 – Create a stacked bar chart
Now we are going to create a 2d stacked bar chart with this data.
1 – First, you have to select the entire data, ranging from cell A1:G4.
2 – Then go to Insert in the top bar.
3 – Click on the bar chart figure.
4 – Now select the 2d stacked bar chart.
Look at the picture below to see the steps in order.
3 – Create multiple timeline bar chart
The 2d stacked bar chart you have created will appear on your Excel sheet, but it does not look like the one in your mind. You have to make a few changes to create multiple timeline bar chart in Excel with start time and duration.
So, the stacked bar chart you made from the previous data will look like this picture.
Now you have to select the entire chart by clicking on it once. Then you have to go to Design in the top bar > Click on Switch Row / Column. Now the times are on the horizontal axis, and the dates are on the vertical axis. The stacked bar chart will change into the one below.
4 – Make the series invisible in chart
Now we have to make the “start” series look invisible in the chart. First, you have to select one of the blue-colored bars in your chart > Then right-click on it and choose Format Data Point. A new pane appears on the right side of your Excel sheet.
In the Format Data Point pane, you have to click on the symbol and go to Fill > Then choose No fill. Now you can click on the other blue bar and choose No fill as well. Please do this for all three “start” series to make them invisible.
The chart has changed a little bit and should look like the example below.
If you want to make the “break” series disappear, you can follow the same procedure as you did before. Or you can right-click on one of the grey colored series > All three “break” series are selected > Click on Fill > Then select No fill. The “break” series will automatically look invisible.
Do this for the light blue colored series also. Just right-click on one of the series, and it will automatically select all three. The picture below will show you how to create multiple timeline bar chart in Excel.
5 – Format axis in the chart
This step requires to format the horizontal axis in the bar chart, which will change the time values. First, you have to click on the horizontal axis > Then you can right-click on the horizontal values and select Format Axis.
Or you can double-click on the horizontal (values) axis, and the Format Axis pane will appear on the right side of your Excel sheet.
In the pane Format Axis, you have to select the Axis Options on the right. Change the number of Bounds in Minimum to 0,25 and Maximum to 0,8. Now the chart will change the time values from 06:00 until 18:00.
6 – Change chart title in Excel
The last step is to change the chart title in Excel, as it shows the chart’s meaning. First, you have to select the Chart Title in your stacked bar chart. Then you can type in your customized title.
Read more: How to create a bar chart in Excel »
You have achieved to create a bar chart in Excel with start time and duration. It did involve a few steps to get there, but at least you can do it yourself. Now you can create a multiple timeline bar chart in Excel. You know how to change the chart title in Excel to make it look more customized.