Forum Discussion
Incapable of Creating a Manual Line (Threshold) on a Box and Whisker Chart
I am seeking information on how to manually add a line (a visual threshold line for my need) to a box and whisker charts. To summarize my data, I have 4 independent variables with 2 metrics that are measured on a percentage scale (Figure 1 below). I am trying to add threshold lines to visualize a pass/fail (Figure 2). I have used several suggestions about combining charts and copying and pasting scatter plots. Nothing is working as intended and the simplest solution is simply to insert a line on top of the chart. However, this does not integrate it into the chart and I cannot add it to the legend. Is this realistically the only way to get what I want? I am not familiar with macros or VBA, but this seems to be a function that other users could use.
I would use this function on other box and whisker plots with varying number of variables, too. This chart style was chosen for the most efficient way to present the data. I included a glimpse of how the data is setup (Table 1). Any ideas are welcomed.
Figure 1.Figure 2.Table 1.
4 Replies
I am not sure I know what it is about but this is a variant based upon Patrick2788 's work
To do this natively in Excel with that chart type you could plot the entire data set then modify the thresholds to show as mean lines:
The results:
The downside being the threshold lines do not run the width of the chart.
You could use Python in Excel to create a Box chart with the Seaborn library.
This is what I made with your data:
If you want to explore the Python route, I recommend loading the Anaconda Toolbox office addin. It helps a lot in creating these charts. It will give you a menu to work with:
I tabled your data and built the initial box chart. I then made revisions to match the desired result.
May consider this as workaround:
1. Add a Threshold Series
You can simulate a threshold line by adding a new data series:
• Create a new row or column with your threshold value repeated for each category.
• Add this series to your chart as a line chart.
• Format it to appear as a horizontal line across the box plots.
• Right-click the chart → “Select Data” → Add your threshold series.
• Then format the new series to use a line style and add it to the legend.
This method works well and keeps the line part of the chart, not just floating on top.
2. Use Error Bars
If your threshold is tied to a specific metric, you can use custom error bars to simulate a line:
• Add a dummy series with values at the threshold.
• Apply error bars that span across the chart.
• Format the bars to look like a line.
3. Overlay with Combo Chart
• Create a combo chart: box and whisker + line chart.
• Use the line chart to plot your threshold.
• Make sure both series share the same axis.
• Format the line to match your desired threshold style.
This method allows legend integration and dynamic updates.A box and whisker plot cannot be used in a combo chart...
