How to Get Horizontal Bar Graph to Realign with Data

Question

Hello,

I am trying to display data on a horizontal bar graph showing surgery duration and times, I have each time labeled as "start 2" and "finish 2" for the starting and finishing times, expressed in number format (1 being 24 hours, or 0:00, 0.5 being 12 noon). I also have "start 1" and "finish 1" for overlapping times that continued beyond midnight, in order to display surgical times restarting at 0:00 on the left side of the graph (the graph axis moved from midnight to midnight). The following pictures should clarify.

My problem is that the times, as seen in the pictures (bars are labeled with their correct corresponding value, for clarity), is that the values for each bar (which are correctly labeled) do not line up with the x-axis. So, for example, 0.4 is shown on the graph more around 0.35, and "1" will go off the graph and end about at 2.8 or so. Can someone please clarify how to get the data to just line up with the x-axis?

Other context: this is a 100% stacked bar chart.

I have tried:

• manually setting the axis minimum and maximum values
• changing the axis to display values in reverse order
• adjusting the bar width
• ensuring data is mapped to the correct axis ("primary axis")
• ensuring the number format of the cells the data is coming from is "number" instead of "text."

Also included among these pictures are the formulas I used, just for context (don't worry about it).

If this works correctly, the blue bars, "finish 2" should not all move out to "1," instead, most should stop before reaching 1 because their values are, as indicated, less than 1.

Answer 1

Hi @Samuel Wolfe

I want the graph to be able to stratify the two groups, ie, one group's cases on the top half of the graph, and then the second era's cases displayed on the bottom half of the graph

Based on my tests, here is a workaround. If needed, please refer to following steps, some Scatter lines, you can skip if you don't need them.

• Combine the two groups time data together. Put the data of the second month in the same column as the data of the first month.
• To mark the bars of different months in the chart later, you can create a Scatter with Smooth lines. Please use "Count" function to calculate the number of surgeries performed in the first month, and the number of surgeries performed in these 2 months.

• In My example, I get 19 for the first month and 37 for 2 months. Then please add 2 columns data of X and Y like following image.

• Select Data from A1 to B4, insert a Scatter with lines chart. Right click the chart > Select Data > Add, add a new series of Scatter, the X values are from A2 to A3, the Y values are from B2 to B3.

• Select the Vertical Axis and format it, change the Bounds to 0 and the numbers of total number, set Units to be 1.

• Then right click the chart again > Select Data > Add, add the Finish2 data as a new series like following image. And then right click the chart again > Change Chart Type > Combo. Set the correct chart types for these 3 series. In my example, X and Y are Scatter with Smooth lines chart, Finish2 is Clustered Bar chart. The Secondary Axis will be ticked for Finish2 automatically.

• Follow the previous step to add Start 2 and Finish1 series.

• Then follow the steps I suggested earlier to set the overlap ratio, change the Start2 bar solid fill color to white, adjust the Axis Bounds values, etc.

• At last, according to the Bounds and Units values that you set for Axis, and the number for first month, you may add following data to create a new Scatter with Smooth lines, this line is used to separate two months of bars.

• For the clarity of the chart, it is recommended that you go to the "Select Data" option and check to display the X and Y series. Via Select Data and Add options, create this new line. Additionally, this line uses primary axis like X and Y Series.

Please make sure the Axis Bounds and Units values are not changed.

• If you need data labels, you can add them. If you don't want to display the 0 for Start1, you can customize its format to be #,##0.000;(#,##0.000);. The final effect is shown in the figure below.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Hi @Samuel Wolfe

I find a way to set the different colors. But it still needs to add extra bars series.

• Add the first month data into this chart, so there are 3 new series.

• In Change Chart Type, please make sure these 3 series are clustered bar charts.

• For clarity of the chart, temporarily check these three new series and Scatter with smooth lines in Select Data to show in chart.
• Then set overlap percentage 100% and solid fill color white for the "ExtraS 2" (Extra Start 2 series)

• Go back to Select Data, and check the box of other previous series to show.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 3

I presume this is a follow-up question on https://learn.microsoft.com/en-us/answers/questions/2223399/how-to-display-time-duration-on-a-horizontal-bar-g

Don't really understand why you are now using a 100% stacked bar chart as that will indeed plot stacked bars from 0 to 1 (that is 0% to 100%). So if you have values like 0.15 and 0.45 these will be plotted as 25% [ 0.15 / (0.15+0.45) ] and 75% [ 0.45 / (0.15+0.45) ] for a total of 100%. That's how such charts work. You should use a regular stacked bar chart instead and you have two solutions in the linked thread.

Answer 4

Hi,

I suggest you use Clustered Bar chart.

For example, if you add the "Start 2“and "Finish 2” 2 series, then you will get following chart.

The Finish2 bar is longer than Start2, if Finish2 is located on top of Start2, please right click on chart > Select Data > Reverse their order, click OK.

Right click on one bar > Format Data Series > Series Overlap: 100%. By the way, please change the Bounds values of X-axis.

And then, you will get following chart.

Please set the Start2 bar to white fill color and no border line.

Then please add the just Finish1 Series in chart via right clicking chart > Select Data > Add, add the data source. But you do not need add Start1, because the Start1 starts with 0:00.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---