Add trendlines and error bars to a chart

Add a trend line to summarize the relationship between numeric X and Y values, and add Y error bars only when you can explain what their size represents. A close fit and small error bars are different claims.

Prepare measurements, not categories

Use Apache OpenOffice Calc 4.1 desktop, English menus, with a disposable ODS workbook. This procedure is documentation-qualified; no native chart execution is claimed. It extends basic chart creation rather than replacing that introduction.

Enter A1:B5 as follows:

Time (s)	Position (cm)
0	1
1	3
2	5
3	7

For this synthetic exercise, assume each position measurement has a known absolute uncertainty of 0.5 cm in either direction. That number is supplied as part of the exercise, not estimated from four observations. Do not present it as a calculated confidence interval.

Select the table and use Insert > Chart to create an XY (Scatter) chart. In the wizard choose columns as series, the first row as labels, A2:A5 for X and B2:B5 for Y. Label both axes with their units and finish. The points should be (0,1), (1,3), (2,5), and (3,7).

Fit and display the relationship

1. Double-click the chart to enter edit mode and select the Position data series, not one individual marker or the chart wall.
2. Choose Insert > Trend Lines and select Linear. Confirm the dialog.
3. Select the trend line and use its context menu to insert the trend-line equation. The guide also documents Insert R2 and Trend Line Equation for showing both.
4. Check that the displayed relationship agrees with y = 2x + 1. If the display rounds coefficients, interpret the rounded text accordingly.

These four points lie exactly on a straight line, so the expected R² is 1. R² measures the fraction of variation explained by this fitted model; it is not evidence that time causes position to change or that extrapolation will be reliable. Do not label R² as the signed correlation coefficient.

Avoid a category-based Line chart for this exercise. On category charts Calc uses X positions 1, 2, 3 and so on for trend calculations. That would change the intercept and could make a seemingly sensible equation describe category positions instead of measured seconds.

Show the declared uncertainty

With the Position series selected in chart edit mode, choose Insert > Y Error Bars. Choose Constant Value, enter 0.5 for positive and negative values, and display both directions. Confirm.

The first point should span 0.5–1.5 cm; the last should span 6.5–7.5 cm. These bars express the same absolute uncertainty at every measurement. Percentage error would mean something different: 10% would produce 0.1 cm at the first point and 0.7 cm at the last. Do not switch options merely to make the chart look balanced.

Change one observation and diagnose

Change B5 from 7 to 9. With ordinary least-squares fitting, the expected line becomes y = 2.6x + 0.6 and R² is approximately 0.965714. The last error bar now spans 8.5–9.5. The constant error amount should not grow just because the fit is less exact.

If the equation stays unchanged, verify the chart’s Y range still includes B5 and that you changed the cell feeding the chart. If only one marker has bars, reselect the whole series. If bars are hard to see, inspect axis bounds before increasing their size. Changing their size changes the claim.

Restore B5 to 7, save the ODS, close and reopen it, and verify the equation, error-bar direction and values. To remove a mistaken trend line, select the line and press Delete, taking care not to select the chart object. Keep a copy of the workbook before modifying chart interpretation for a real report.

Official reference

Apache OpenOffice 4.1 Calc Guide, Chapter 3, Trend Lines and Y Error Bars. Recovered official guide retrieved 19 October 2026; expected coefficients here are independently calculated, not native-test results.