How to Make a Standard Curve in Excel

Q: How do I calculate an unknown concentration from a standard curve?

Rearrange the curve equation y = mx + b to x = (y − b) / m, where y is the unknown's measured signal. Or skip the rounding and use a formula: =(signal - INTERCEPT(knowny, knownx)) / SLOPE(knowny, knownx), with signal as y and concentration as x.

Q: What is a good R-squared value for a standard curve?

Aim for 0.99 or higher for quantitative assays; 0.98 is usually acceptable. Below about 0.95 suggests a pipetting error, an outlier, a reading outside the linear range, or a non-linear response. Always inspect the plotted points, not just the R² number.

To make a standard curve in Excel, type your known concentrations and their measured signals into two columns, plot them as an XY Scatter chart, then add a linear trendline with the equation and R-squared displayed. That equation, y = mx + b, lets you convert any measured signal from an unknown sample back into a concentration.

A standard curve (also called a calibration curve) is the backbone of quantitative lab work: ELISA, BCA and Bradford protein assays, spectrophotometry, and HPLC all rely on one. You run a dilution series of known concentrations, measure the signal each produces, fit a line, and read concentrations off unknown samples. Excel handles the whole linear workflow without add-ins, and the steps below work on both Windows and Mac.

Step 1: Set up your data in two columns

Put the known concentration of each standard in the first (left) column and its measured signal (absorbance, optical density, fluorescence, peak area, etc.) in the second. The X axis is always the variable you control, so concentration goes left and signal goes right.

Concentration (µg/mL)	Absorbance (OD)
0	0.04
2	0.19
4	0.33
6	0.51
8	0.66
10	0.80

Include your blank (the zero standard) so the curve has a real intercept. If you ran replicates, average them first or list every point — Excel fits a line through all of them either way.

Step 2: Insert an XY Scatter chart (not a Line chart)

Select both columns of data including the headers. Then:

Windows: Insert tab → Charts group → the Scatter icon → choose Scatter (markers only, no connecting lines).
Mac: Insert tab → X Y (Scatter) → choose Scatter (markers only). In older Excel for Mac, use the Charts ribbon tab → Scatter.

Choose a Scatter (XY) chart, never a Line chart — this is the single most common mistake. A Line chart treats your X-column values as evenly spaced text categories, ignoring their actual numeric spacing. Standards at 0, 2, 4, 8, 16 would be drawn as if equally spaced, distorting the curve and producing a meaningless trendline. A Scatter chart plots true X-versus-Y numeric pairs, which is exactly what regression needs. For a refresher, see our guide to creating a scatter plot in Excel or the alternate scatter plot walkthrough; the complete guide to Excel charts covers the wider menu.

Add axis titles so the chart reads clearly — concentration on X, signal on Y. Here’s how to add axis titles in Excel and label your axes.

Step 3: Add a linear trendline with equation and R-squared

Now fit the line:

Windows: click any data point in the chart to select the series → right-click → Add Trendline. The Format Trendline pane opens on the right. Choose Linear, then scroll down and tick Display Equation on chart and Display R-squared value on chart.
Mac: click a data point to select the series → Chart Design tab → Add Chart Element → Trendline → More Trendline Options (or right-click → Add Trendline). In the Format Trendline pane choose Linear and tick the same two boxes.

Excel draws the best-fit line and prints something like:

y = 0.0757x + 0.0405 R² = 0.9989

That’s your standard curve. Our adding a trendline tutorial covers trendline types, and finding the trendline equation explains the displayed text.

Step 4: Read the equation, y = mx + b

The trendline equation is a straight line:

y is the measured signal (absorbance/OD).
x is the concentration you want to know.
m is the slope (0.0757) — how much signal each unit of concentration adds. This is the sensitivity of your assay.
b is the y-intercept (0.0405) — the signal at zero concentration, essentially your background or blank reading.

To find an unknown concentration, you measured its signal (y) and need to solve for x. Rearrange y = mx + b to:

x = (y − b) / m

If an unknown sample reads an absorbance of 0.45:

x = (0.45 − 0.0405) / 0.0757 = 5.41 µg/mL

Step 5: Calculate unknowns with formulas (more accurate than the chart)

Reading numbers off the chart label rounds them. For reproducible work, pull the slope and intercept straight from your data with worksheet functions. Assume concentrations are in B2:B7 and signals in C2:C7, and the unknown’s measured signal sits in cell E2.

Option A — SLOPE and INTERCEPT. Note the argument order: known_y’s come first, known_x’s second. For a standard curve, signal is y and concentration is x:

=SLOPE(C2:C7, B2:B7)        ' the slope m
=INTERCEPT(C2:C7, B2:B7)    ' the intercept b
=(E2 - INTERCEPT(C2:C7,B2:B7)) / SLOPE(C2:C7,B2:B7)

That last formula returns the unknown concentration directly. Our guides on finding the slope in Excel and reading slope from a graph go deeper on those functions.

Option B — FORECAST.LINEAR. FORECAST.LINEAR predicts a y from an x along the regression line. To predict concentration from a measured signal, swap the ranges so concentration is treated as the value being predicted:

=FORECAST.LINEAR(E2, B2:B7, C2:C7)

Here E2 is the measured signal, B2:B7 (concentration) is known_y’s, and C2:C7 (signal) is known_x’s — so it returns concentration from signal. (In Excel 2013 and earlier the function is simply FORECAST, which still works today.) See our FORECAST function guide for syntax and limits.

Option C — LINEST for slope, intercept, and statistics. LINEST is the most complete tool, returning the regression coefficients plus error statistics in one array. Enter:

=LINEST(C2:C7, B2:B7, TRUE, TRUE)

In Excel 365 it spills automatically across a 2-column, 5-row block. In older versions, select a 2×5 range first, type the formula, and press Ctrl+Shift+Enter (Windows) or ⌘+Return / Ctrl+Shift+Enter (Mac) to enter it as an array. The output is:

Top-left: slope (m)	Top-right: intercept (b)
Std error of slope	Std error of intercept
R²	Std error of y estimate
F statistic	Degrees of freedom
Regression sum of squares	Residual sum of squares

So =INDEX(LINEST(C2:C7,B2:B7),1,1) grabs the slope and =INDEX(LINEST(C2:C7,B2:B7),1,2) the intercept. The R² in row three, column one is the same value the chart displays. For more, see linear regression in Excel and the full regression analysis walkthrough.

What R-squared means and what’s acceptable

R² (the coefficient of determination) measures how much of the variation in your signal is explained by the straight-line relationship with concentration. It ranges from 0 to 1: an R² of 1.0 means every point sits exactly on the line, while 0 means the line explains nothing.

For most assays an R² of 0.98 or higher is expected, and 0.99+ is the practical target for quantitative work like ELISA or protein assays. An R² below about 0.95 usually signals a pipetting error, a contaminated standard, a reading outside the linear range, or a genuinely non-linear response. R² is not a substitute for inspecting the plot — a few well-placed points can inflate it — so always eyeball the curve too. For the calculation behind it, see finding the R-squared value in Excel, and for the related strength-of-association measure, finding the correlation coefficient (R, the square root of R²).

When a linear trendline isn’t enough

A linear fit only works where your assay responds in a straight line. Many immunoassays — ELISA in particular — are sigmoidal across their full range and follow a four-parameter logistic (4PL) or five-parameter logistic (5PL) curve. Excel’s built-in trendline cannot fit a 4PL/5PL model; that requires Solver-based curve fitting or dedicated software (GraphPad Prism, MyAssays, plate-reader software).

The common workaround is to restrict your standards to the linear portion of the response and quantify only unknowns inside that window. If your points clearly curve, a linear trendline gives a misleadingly good-looking fit in the middle while failing at the ends — don’t force it.

Worked example, end to end

Using the data above, Excel returns y = 0.0757x + 0.0405, R² = 0.9989 — an excellent fit. An unknown sample reads OD = 0.45. Solving x = (0.45 − 0.0405) / 0.0757 gives 5.41 µg/mL. The formula =(0.45-INTERCEPT(C2:C7,B2:B7))/SLOPE(C2:C7,B2:B7) returns the same value without rounding. If the sample was diluted 1:10 before reading, multiply by 10 for the original concentration (54.1 µg/mL).

Troubleshooting

Trendline looks wrong or points are evenly spaced. You inserted a Line chart instead of an XY Scatter. Delete it and re-insert as Scatter — a Line chart ignores the numeric value of your X data.

R² is too low (below ~0.95). Check for a mis-pipetted standard, a bubble in the cuvette/well, or a saturated reading at the top end. Plot the points and look for an outlier. If the whole series curves, your assay is non-linear over that range — trim to the linear region.

Unknown concentration is impossible (negative or huge). You’re extrapolating. A standard curve is only valid inside the range of your standards. Dilute the sample and re-measure so its signal lands within the curve.

SLOPE/INTERCEPT return odd numbers. Confirm the argument order: known_y’s (signal) first, known_x’s (concentration) second. Swapping them inverts the relationship.

Frequently Asked Questions

How do I make a standard curve in Excel?

Enter known concentrations and their measured signals in two columns, select the data, and insert an XY Scatter chart (markers only). Right-click a point, choose Add Trendline, pick Linear, and tick Display Equation and Display R-squared value. The resulting y = mx + b equation is your standard curve.

What is the difference between a standard curve and a calibration curve in Excel?

There is no practical difference — the terms are interchangeable. “Calibration curve” is common in analytical chemistry, “standard curve” in biochemistry (ELISA, protein assays). Both plot known standards against measured signal and fit a line you use to quantify unknowns.

Why should I use a scatter chart instead of a line chart for a standard curve?

A Scatter (XY) chart plots true numeric X-versus-Y pairs, respecting the actual spacing of your concentrations. A Line chart treats the X values as evenly spaced text categories, which distorts the curve and produces a meaningless trendline. Always choose Scatter for a calibration curve.

How do I calculate an unknown concentration from a standard curve?

Rearrange the curve equation y = mx + b to x = (y − b) / m, where y is the unknown’s measured signal. Or skip the rounding and use a formula: =(signal - INTERCEPT(known_y, known_x)) / SLOPE(known_y, known_x), with signal as y and concentration as x.

What is a good R-squared value for a standard curve?

Aim for 0.99 or higher for quantitative assays; 0.98 is usually acceptable. Below about 0.95 suggests a pipetting error, an outlier, a reading outside the linear range, or a non-linear response. Always inspect the plotted points, not just the R² number.

Can Excel fit a non-linear (4-parameter logistic) standard curve?

Not with its built-in trendline. ELISA and similar assays are often sigmoidal and need a 4PL or 5PL fit, which requires Solver-based curve fitting or dedicated software like GraphPad Prism. In Excel, restrict your standards to the linear portion of the response and quantify only unknowns within that window.