In many types of data analysis you will have a set of standards to compare you unknown sample to.  For example when you run an agarose or SDS PAGE gel.  You compare your unknown sample to the ladder of known sizes.  Another example is when you use a spectrophotometer to measure protein concentration (C) using absorbance (A) readings at 280 nm.  According to Beer’s law, A = C · l · ϵ, where A = absorbance, C = concentration, l = path length, and ϵ = the extinction coefficient specific for a particular molecule at a particular wavelength. As the concentration of protein increases so does the absorbance. The correlation is linear meaning if you double the concentration of protein the absorbance at 280 nm should also double.  

If you don’t know ϵ?  How could you determine the concentration of an unknown sample?

Suppose you have a sample of protein X of unknown concentration.  You also have 4 samples of protein X for which you do know the concentration.  We will call the four samples of known concentrations “standards.”  You determine the absorbance of each of these samples at 280 nm; the data are shown in Excel figure below.

Data Entry in Excel for Standard Curves
Enter data on Excel by placing the values for the independent variable (x) in the column to the left. Dependent variable data (y) should be placed in the column to the right. Remember to title your columns with the type of data and the units.

 

Make a Standard Curve Graph in Excel

If you make a graph of the data points you can determine the equation for the line of best fit.  You will use the equation of the line of best fit to determine the concentration of the unknown sample.  For the given dataset above, highlight cells A2 through B5.  Click on the Insert tab, then on Scatter (* in the figure below).  

Definition

line of best-fit: a straight line that best represents the data on a scatter plot. This line minimizes the difference between the line and the data points and may pass through some, none, or all of the points.

Make a scatter plot in Excel
The Excel sheet shows the highlighted dataset. Next click on the insert tab at the top of the Excel page. The red asterisk (*) shows where to select the scatter plot. Choose the option in which there are no lines connecting the dots

 

A graph with the plotted data will appear. The graph initially will not have any titles or axes labels, which is a bad thing.  To fix that, click on the Graph to open CHART TOOLS.  Click on the tiles above each of the three red asterisks in the figure below). Change the graph title (Standard Curve for Protein X), and axes (the labels provided on the table, including units).  The default arrangement in Excel is to use the  left column for the X-axis, and the right column for the Y-axis.  Click on and then delete the legend that says Absorbance at 280 nm on the bottom, as it’s not necessary for our purposes.

Excel Scatter Plot with Data and Chart Titles
The three red asterisks (*) show where to click to add the chart and axis titles and remove the unnecessary legend.  Axis titles should include the units of the values in parentheses. The graph below has the titles added and legend removed.