Line Chart With Tolerance Lines
Here is an easy trick to set up a line chart with tolerance lines. This chart will help you see whether values are in or out of given tolerances at a glance.
To setup a line chart with tolerance lines, you need to have one or more series and a set of tolerances to compare data series with.
Here is the data set I will use for this tutorial:
You need to set your tolerance numbers as a data series (like C and D columns in the picture above) to be able to present them on the chart. This will allow you to draw tolerances as lines.
Select data table and insert a line chart. A default line chart like the one below will be created.
Clear unnecessary chart junk (vertical axis and gridlines) and leave lines only. And edit chart title like shown below:
Little tip: You can edit chart title by simply selecting it and typing the new title. You can also use different fonts and font sizes for each line or character of your title.
Now comes the creative part: select your chart and press “Change Chart Type” button from Design ribbon (you can also do this by right-clicking and selecting Change Chart Type Option). Select Combo Chart from left side menu and set tolerance series as “Area Chart”. Leave weight series as line, change it back to line if Excel changes it to any other chart type.
Your chart will look like the one below at this stage:
We need to rearrange our data series to get the setup we want. To do this, open chart source data selection interface by selecting “Select Data” from right-click menu. Arrange series from top to bottom as Weight-Max-Min with this order. This will ensure all data series will be visible on the chart.
Now select min series on chart and set its fill color to white and shape outline to “no outline” from Design Ribbon. Do the same for Max series but set fill color as light orange. You have successfully created a line chart with tolerance lines.
Here is how your chart looks after you add data labels to Weight series: