How to Plot a Standard Curve in Excel for Spectroscopy
How to Plot a Standard Curve in Excel for Spectroscopy
Creating a standard curve in Excel for spectroscopy is a fundamental skill for anyone working in analytical chemistry or laboratory research. Whether you’re measuring protein concentrations, enzyme activity, or chemical absorbance, plotting a standard curve allows you to translate raw spectroscopic data into meaningful quantitative results. This guide walks you through the process step by step, providing clear examples, practical tips, and insights that ensure accuracy and reliability.
Introduction
When working in a lab, you might have noticed that raw absorbance values from a spectrophotometer are just numbers—they don’t directly tell you the concentration of your sample. That’s where a standard curve comes in. By preparing a series of known concentrations and measuring their absorbance, you can create a reference curve. Then, by comparing your unknown samples to this curve, you can accurately determine their concentrations.
Using Excel for this process makes it fast, precise, and visually clear. Unlike manual plotting, Excel handles calculations automatically and lets you apply linear regression, trendlines, and formula-based predictions effortlessly.

What is a Standard Curve?
A standard curve is a graphical representation of the relationship between known concentrations of a substance (x-axis) and their corresponding spectroscopic readings (y-axis). In most cases, this relationship is linear, allowing you to use a simple equation to calculate unknown concentrations.
Key benefits of a standard curve:
-
Converts raw absorbance data into meaningful concentrations
-
Improves accuracy and reproducibility
-
Validates experimental consistency
-
Enables identification of outliers and measurement errors
Step-by-Step Guide: Plotting a Standard Curve in Excel
Step 1: Collect Your Data
Before using Excel, prepare:
-
Known standard solutions (e.g., 0, 2, 4, 6, 8, 10 mg/mL)
-
Measured absorbance values from the spectrophotometer
-
A clear table structure for easy input
Example Table:
| Concentration (mg/mL) | Absorbance |
|---|---|
| 0 | 0.00 |
| 2 | 0.12 |
| 4 | 0.25 |
| 6 | 0.37 |
| 8 | 0.50 |
| 10 | 0.63 |
Step 2: Enter Data into Excel
-
Open Excel and input concentrations in Column A and absorbances in Column B.
-
Ensure the headings are clear (e.g., Concentration and Absorbance)—this helps Excel create meaningful chart labels.
Step 3: Insert a Scatter Plot
-
Highlight your data.
-
Go to Insert → Charts → Scatter → Scatter with only Markers.
-
Your chart will display points representing each standard solution’s absorbance.
Step 4: Add a Trendline
-
Click on any data point in the scatter plot.
-
Right-click and select Add Trendline.
-
Choose Linear for most spectroscopy standard curves.
-
Check the boxes for:
-
Display Equation on Chart
-
Display R-squared Value on Chart
-
This gives you the linear equation (e.g., y = 0.063x + 0.001) that describes the relationship between absorbance (y) and concentration (x).
Step 5: Use the Equation to Calculate Unknowns
With the trendline equation, you can now calculate unknown concentrations:
Example:
If a sample absorbance is 0.31 and your trendline is y = 0.063x + 0.001:
This is your sample’s concentration.
Step 6: Customize Your Chart for Clarity
-
Add axis titles: X = Concentration (mg/mL), Y = Absorbance
-
Include a chart title: “Standard Curve for Protein Assay”
-
Adjust marker colors and sizes for better visibility

Practical Tips for Accuracy
-
Always measure each standard in triplicate to account for variability.
-
Ensure blank subtraction: subtract the absorbance of a blank solution from all readings.
-
Avoid concentrations that produce absorbance >1.0, as spectrophotometers may become nonlinear at high absorbances.
-
Confirm linearity by checking that R² > 0.99 for a reliable standard curve.
Examples of Standard Curve Applications
-
Protein Quantification (Bradford Assay): Use BSA standards to determine unknown protein concentrations.
-
DNA/RNA Measurement: Plot absorbance at 260 nm versus known nucleic acid concentrations.
-
Enzyme Kinetics: Measure substrate absorbance to calculate enzyme activity.
FAQs
Q1: Can I use a non-linear trendline?
Yes, if your data shows curvature. Choose Polynomial or Logarithmic trendline, but ensure it fits your experimental system.
Q2: Why is the R² value important?
R² indicates how well the trendline fits your data. Values close to 1 (e.g., 0.995) show high reliability.
Q3: How many standards do I need?
At least 5–6 concentrations evenly spaced across your expected sample range provide the most reliable curve.
Conclusion
Learning how to plot a standard curve in Excel for spectroscopy is essential for translating raw data into actionable results. By following these steps—inputting your data, plotting, adding a trendline, and using the linear equation—you can determine unknown concentrations with confidence. Experimenting with different standards and checking your R² values ensures high accuracy and reproducibility. Excel makes this process simple, precise, and professional.
For further insights, explore these related guides:
