Written and maintained by CASRAI Editorial Board
Last updated
A calibration graph in Excel is an XY (scatter) chart of instrument response against known standard concentrations, with a linear trendline fitted through the points. The trendline gives you a slope and an intercept; the same two numbers are available as worksheet formulas (LINEST, RSQ), and those formulas, not the chart label, are what you should use to calculate unknowns. This guide shows both routes and how to back-calculate a sample concentration from the fitted line.
What a calibration curve in Excel is used for in research
Many measurements are indirect: a spectrophotometer reports absorbance, an HPLC reports peak area, a plate reader reports a signal. You measure standards of known concentration, fit a line relating response to concentration, then use the line to convert the response of an unknown sample into a concentration. Excel can do the fit with a chart trendline or with worksheet functions. For an instrument example, see the HPLC chromatogram guide, where peak area is converted to concentration through a calibration curve.
Set up the data
Put the standard concentrations in one column (x values) and the measured responses in the next column (y values), one row per standard. Keep the unknowns in a separate block so they are not included in the fit. The convention used throughout this page is y = response, x = concentration, so the fitted line is y = mx + b, where m is the slope and b is the intercept.
How to make a calibration curve in Excel: step by step
- Enter the concentrations in column A and the responses in column B, with a header row.
- Select both columns and create an XY (scatter) chart.
- Select the data series, open the Chart Design tab, choose Add Chart Element, then Trendline, then Linear. Microsoft notes that the Forward and Backward fields project the trendline beyond your data; leave them at zero for a calibration line, since results should stay within the range of the standards.
- Label the axes with the quantity and unit (for example concentration in mg/L and response in absorbance units) so the graph can be read without the worksheet.
- Calculate the fit in cells, as described in the next section, so the numbers you use are stored in the workbook rather than only drawn on the chart.
Get the slope, intercept and r² with formulas
Microsoft documents LINEST(known_y's, [known_x's], [const], [stats]), which fits the relationship y = mx + b and returns an array of values. With the data in A2:B8 (x in A, y in B):
- Slope:
=INDEX(LINEST(B2:B8,A2:A8),1) - Intercept:
=INDEX(LINEST(B2:B8,A2:A8),2) - Coefficient of determination:
=RSQ(B2:B8,A2:A8).RSQ(known_y's,known_x's)returns the square of the Pearson product moment correlation coefficient.
If you set the optional stats argument to TRUE, LINEST returns additional regression statistics, including the standard errors of the slope and intercept and the coefficient of determination (Microsoft Support, “LINEST function”). Setting const to FALSE forces the intercept b to 0; only do that if your method specifies a line through the origin. Microsoft notes that because LINEST returns an array of values, it must be entered as an array formula; wrapping it in INDEX, as above, returns a single value.
Microsoft’s trendline article also notes that Excel adjusted how it calculates R² for linear trendlines with the intercept set to zero, so that it matches LINEST. Treat the worksheet formulas as the reference values.
Calculate an unknown concentration from the curve
Because the fitted line is y = mx + b, solve for x to convert a measured response to a concentration:
x = (y − b) / m
In Excel, if the response of the unknown is in B12 and the slope and intercept are in E2 and E3, enter =(B12-E3)/E2. This is the same back-calculation described in the HPLC chromatogram guide as concentration = (A − I)/m.
Calibration curve in Excel vs LINEST
The chart trendline and LINEST fit the same straight line, but they serve different purposes. The trendline is a visual check: you can see curvature, an outlier or a flattening at high concentration. LINEST and RSQ put the numbers in cells where they can be referenced, copied down a column of unknowns and audited. Use the chart to look, and the formulas to calculate.
An algebra cross-check with FORECAST.LINEAR
FORECAST.LINEAR(x, known_y's, known_x's) predicts a y from an x. If you swap the arguments, regressing concentration on response, it returns a concentration directly: =FORECAST.LINEAR(B12,A2:A8,B2:B8). This fits a different line from the response-on-concentration fit, so the result will differ slightly from (y − b)/m unless the points lie exactly on a line. Use it as a sanity check, and use whichever direction your method or SOP prescribes.
Read the output
- Slope is the change in response per unit of concentration. A larger slope means a more sensitive response.
- Intercept is the predicted response at zero concentration.
- r² reads as the share of the variance in y explained by x. A high value does not by itself show the line is appropriate; check the pattern of the points and the residuals for curvature, and review the assumptions of simple regression.
- Range: only report concentrations for responses that fall within the span of your standards. Extrapolating beyond the highest or lowest standard is not supported by the fit.
Acceptance criteria belong to your method or regulator, not to Excel. For example, the ICH M10 expectations for bioanalytical calibration standards are summarised in the guide on setting the LLOQ of a bioanalytical assay.
Troubleshooting
| Symptom | Documented cause |
|---|---|
#N/A from RSQ or FORECAST.LINEAR |
The x and y ranges are empty or have different numbers of data points. |
#DIV/0! from RSQ |
Only one data point was supplied. |
#DIV/0! from FORECAST.LINEAR |
The variance of the known x values is zero (all standards have the same x). |
#VALUE! from FORECAST.LINEAR |
The x you are predicting from is nonnumeric. |
Only part of the LINEST output shows |
LINEST returns an array; use INDEX to pick single values, as above. |
| Trendline option missing in the Chart Elements button | Microsoft notes that Excel shows the Trendline option in the Chart Elements button only for a chart with more than one data series; use Chart Design, Add Chart Element, Trendline instead. |
Per Microsoft, RSQ ignores text, logical values and empty cells but includes zeros, so a stray 0 entered for a missing reading will distort the fit.
FAQ
How do I make a calibration curve in Excel?
Enter concentrations and responses in two columns, make an XY (scatter) chart, add a linear trendline through Chart Design, Add Chart Element, Trendline, Linear, and calculate the slope, intercept and r² with LINEST and RSQ.
What is the difference between a calibration graph and a calibration curve?
In practice the terms are used interchangeably: both describe the plot and fitted line of response against known standard concentration.
How do I calculate an unknown from the calibration curve?
Rearrange y = mx + b to x = (y − b)/m and apply it to the unknown’s response.
Sources
- Microsoft Support, LINEST function (checked 2026-10-29).
- Microsoft Support, RSQ function (checked 2026-10-29).
- Microsoft Support, FORECAST and FORECAST.LINEAR functions (checked 2026-10-29).
- Microsoft Support, Add a trend or moving average line to a chart (checked 2026-10-29).
Related: how to find a slope in Excel, the TREND formula in Excel, linear regression scatter plot.
