Calculate Unknown Concentration from Standard Curve in Excel

Method 1 – Generating a Standard Curve to Calculate Unknown Concentration in Excel

  • Insert a scatter chart for our dataset.
  • Select the range of the cells C5:D10.
  • In the Insert tab, click on the drop-down arrow of the Insert Scatter (X, Y) or Bubble Chart from the Charts group.
  • Choose the Scatter chart.

generate Standard Curve in Excel

  • You will get the following chart.

  • Select the points in the scatter chart, and right-click on them. Select the Add Trendline option.

add trendline in curve

  • You will get the following standard chart.

  • Add graph elements. In Quick Elements, some elements are already added or removed. But you can manually edit the graph to add or remove any elements of the chart by using the Add Chart Element option.
  • After clicking on the Add Chart Element, you will see a list of elements.
  • You have to click on them one by one to add, remove, or edit.
  • Find the list of chart elements by clicking the Plus (+) button from the right corner of the chart.
  • Mark the elements to add or unmark the elements to remove.
  • Find an arrow on the element, where you will find other options to edit the elements.
  • Check Axis Titles and uncheck Gridlines.

modify the chart elements

  • The standard curve will look like this.

  • Modify the chart style, select Chart Design and then, select your desired Style 8 option from the Chart Styles
  • Click on the chart, select the Chart Styles icon, and select the chart you want.

  • The standard curve with the dataset will look like this.

show Standard Curve to Calculate Unknown Concentration in Excel

Method 2 – Obtain Unknown Concentration from the Standard Curve

  • Input the absorbance in cell C12.
  • Use the following formula in cell C13:


C5:C12 = known_y’s, dependent y-values.

D5:D12 = known_x’s, independent x-values.

C12 = new_x’s, new x-values to calculate the TREND value for.

  • Press Enter.

Calculate Unknown Concentration from Standard Curve

  • Calculate an unknown concentration from the standard curve as shown below.


Whenever the known X and Y values differ in length, the TREND function returns a #REF error.

It is necessary for the known values – known_x’s, known_y’s – to be linear data. It is possible for the predicted values to be inaccurate otherwise.

Things to Remember

✎ Before inserting a scatter chart, you must select anywhere in the dataset. The rows and columns will need to be manually added otherwise.

✎ When the given values of X, Y, and new X are non-numeric, and when the const argument is not a Boolean value (TRUE or FALSE), then the TREND function returns  #VALUE! Error.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.

<< Go Back to Excel Standard Curve | Excel Charts | Learn Excel




Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF