
Creating a standard curve in Excel is a fundamental skill for scientists, researchers, and analysts who need to determine unknown values based on a set of known standards. A standard curve is a graph that plots known concentrations of a substance against a measured response (such as absorbance in spectrophotometry).
This curve helps estimate unknown values by interpolating data points along the trendline.
In this guide, we’ll walk you through how to make a standard curve in Excel step by step, ensuring accuracy and proper visualization.
What is a Standard Curve?
A standard curve is a graphical representation of the relationship between known concentrations and their corresponding measured values. It is commonly used in:
- Biological and chemical assays (e.g., ELISA, spectrophotometry).
- Calibration of laboratory instruments (e.g., determining DNA or protein concentration).
- Quality control in manufacturing (e.g., verifying substance concentrations in products).
By plotting the known values and fitting a trendline, you can determine the concentration of unknown samples based on their measured response.
How to Make a Standard Curve in Excel (Step-by-Step Guide)
Step 1: Enter Your Data in Excel
Begin by organizing your data in two columns:
- Column A: Known concentrations (independent variable, X-axis).
- Column B: Measured values (dependent variable, Y-axis).
| Concentration (mg/mL) | Absorbance (OD) |
| 0.1 | 0.05 |
| 0.2 | 0.12 |
| 0.5 | 0.30 |
| 1.0 | 0.60 |
| 2.0 | 1.20 |
This table represents a typical dataset where absorbance values correspond to known concentrations.
Step 2: Insert a Scatter Plot
- Select your data (both columns).
- Go to the Insert tab.
- Click on Scatter Chart and choose Scatter with Markers.
- A basic scatter plot will appear, showing the relationship between concentration and absorbance.

Step 3: Add a Trendline to Your Standard Curve
- Click on any data point in your scatter plot.
- Right-click and select Add Trendline.
- Choose an appropriate trendline type:
- Linear Trendline – If your data follows a straight-line relationship.
- Polynomial Trendline – If the data has a curved relationship.
- Check the box for “Display Equation on Chart” to generate the equation of the line.
- Check “Display R² value on chart” to assess the trendline’s accuracy.
Pro Tip: An R² value close to 1 indicates a strong correlation between the concentration and the measured response.
Step 4: Use the Equation to Determine Unknown Values
Once the equation appears on the chart (e.g., y = 0.6x + 0.02), you can use it to calculate unknown concentrations:
Manual Calculation
If your unknown sample has an absorbance of 0.75, substitute it into the equation:
0.75 = 0.6x + 0.02
x = (0.75 – 0.02) / 0.6
x ≈ 1.22 mg/mL
The unknown sample concentration is 1.22 mg/mL.
Using Excel’s Formula
- If your trendline equation is in the format y = mx + b, enter this formula in a new cell:
=(B2 – b) / m
- Replace B2 with the absorbance value, b with the y-intercept, and m with the slope.
Step 5: Customize Your Standard Curve
To make your graph more readable:
- Add Axis Titles – Click on the chart, go to Chart Elements (+), and enable Axis Titles. Label them appropriately:
- X-axis: Concentration (mg/mL)
- Y-axis: Absorbance (OD)
- Modify the Trendline Style – Change the color and thickness for better visibility.
- Adjust the Scale – Right-click the axis > Format Axis > Modify the range if needed.
Key Considerations for Creating a Standard Curve in Excel
- Use at least 5-6 data points – More data points improve accuracy.
- Ensure proper spacing of concentration values – Avoid clustering at one end.
- Use an appropriate trendline – A linear relationship is ideal, but always check for non-linearity.
- Check the R² value – If R² < 0.98, consider re-measuring or adjusting data.
- Avoid extrapolation – Only determine unknowns within the tested range.
Final Thoughts
Now that you know how to make a standard curve in Excel, you can easily analyze and interpret data in scientific and analytical applications. Whether you’re measuring substance concentrations, performing lab calibrations, or analyzing experimental data, mastering standard curves is essential for accurate results.
By following these steps and best practices, you’ll ensure that your Excel-generated standard curves are reliable and precise. Try it out and optimize your data analysis today!
