Using Excel 2016 - Add Max/Min Lines to a Graph

(VIEW THIS TUTORIAL AS A VIDEO)

In some ways a preferred alternative to using regression to find errors in the gradient and intercept is to add max/min lines to a graph, since this determines the error in the gradient and intercept based on the measurement errors made during the data collection rather than just relying on the statistical distribution of the data.

Although there is no tool to create max/min lines in Excel, it is still possible to add them by plotting additional data based using the graph error bars/values.

1. The data (and plot generated from it) for the example used to illustrate this procedure are given below.
   
   
2. Max/Min lines are created using trendlines to connect the lower limit of the first data point (i.e y value minus the error) to the upper limit of the last data point and vice versa. To achieve this, some additional data must first be generated:
   
   

3. The two new tables of data can then be plotted on the graph as second and third series of data. First select the original graph and then click Select Data under the Design tab.
   
   

4. Click Add and input the Max/Min Line 1 x and y values, then click OK.
   
   




5. Repeat the process to add the Max/Min Line 2 data.

6. This will result in 4 new points (2 for each new data set) appearing on the graph, on the error bars of the highest and lowest x values. Trendlines can then be added for each of the new series of data to create the min/max lines.
   
   

7. The new data points on the graph should then be formatted to make the markers "disappear". First right click one of the new data points and select Format Data Series. Then select Marker Options followed by No fill and No line.
   
   

8. Repeat step for second Min/Max trendline to create a graph with min/max lines on it as shown below.
   
   

If your Min/Max lines do not pass between all the error bars: Go back to the data you selected for the line and adjust the y values until the line passes between all the error bars - the line will adjust automatically as you change the values in Excel:

The final graph for the example given (with valid Min/Max lines) should look like the one opposite.

ADDITIONAL NOTE: If x and y error bars are shown on the data, then you should assume that for each data point these error bars describe a box. In this case the Min/Max lines must pass through or touch every box to be valid.