How to make a calibration curve and calculate sample concentrations using Excel – Video Tutorial

Working in the laboratory, there are a number of different ways that we can calculate the amount of an analyte present in a sample by comparing them to standards. You could use a single external standard, a calibration curve,  internal standard or use standard addition. Learning to use these different methods of calculation is essential to working in the laboratory and is a essential part of any laboratory training program

Excel Calibration Curve Video Tutorial  

This is a video tutorial for making an Excel sheet to create a calibration curve using six standards and using it to automatically back calculating unknown sample concentrations.

The sheet also includes a dilutions factor calculator using which the concentration of analyte in the undiluted samples can also be automatically calculated.

We also show you how to protect the Excel sheet so that the formulas cannot be altered by mistake and also you can further validate the Excel sheet for use in a regulated environment.

For best results see the video in HD, in full screen mode and use headphones for better sound clarity. 

From the video tutorial you will learn how to - 

  1. Enter data for a calibration curve in excel 
  2. Make a calibration curve with linear regression trend line
  3. Display the linear regression equation of the line on the graph
  4. Display rvalue of the regression line on the graph
  5. Calculate the slope and intercept for the regression line using formulas
  6. Calculate the concentration of unknown samples using the equation y = mx + c
  7. Calculate dilution factor for samples which are diluted prior to analysis
  8. Calculate undiluted sample concentrations
  9. Protecting the excel sheet so you can validate it in the future
  10. Formatting the excel sheet so it can be printed properly on one sheet of paper 

What can you use this sheet for?

You can use this sheet for calculating sample concentration from a standard calibration curve for any technique like HPLC, GC, UV, AAS or any other technique where linear regression is used. You can also use it in method validation to evaluate linearity of the response and establish the range of the method. 

Please leave your comments below!

Please share your feedback on this video and let us know what other topics you would like us to cover in the future by leaving your comments below and don’t forget to share it with your friends!

Comments

  1. Thank you very much for this nice video.
    Actually I am interested in knowing how can I calculate and represent in the chart the error of the result. When I calculate for instance a concentration by means of a calibration curve, I got a value. But I need to know how good is this value and a +/- around this value.
    Thank you,
    Fidor

    • I would like to thank you for this excellent video. I WOUNDER HOW I CAN COPY THE VIDEO SO I WOULD BE ABLE TO WATCH IT AGAIN IN CASE I LOST CONNECTION.

      • Saurabh Arora says:

        Hi, I am glad you liked the video, we do not have an option for downloading the video currently. we will check and see if it can be done.

        • Dr. Shiwali Sharma says:

          Hello Mr. Arora
          thanks a lot for uploading such a useful video….I also want to upload this video as it is very useful to the students who face the problem to prepare calibration curve in HPLC system software.

          Thanks and God bless you.

          DR. SHIWALI SHARMA

          • Dr. Saurabh Arora says:

            Hi Dr. Sahrma, let me know which site you want to upload it on and we will send you the link and permission. Glad you liked it!

  2. Tobias Clark says:

    Just wanted to express my gratitude at you uploading this clear and helpful video that has aided me in determining Sodium Nitrite concentrations, couldn’t have done it with out you.

    Best regards

  3. Any clue to calculate and represent the error of a calibration curve?
    Thanks,
    Fidor

    • Saurabh Arora says:

      Hi Fidor, the regression line that we make using the calibration data accommodates for the errors. We usually look at the r square value and test for non zero slope to evaluate the suitability of the calibration curve. The errors for the individual points can also be shown if we have replicates (minimum 3) for each of the calibration points, then we could add error bars to the values. Also there is a method to produce a 90 or 95% confidence regression line for the curve. These are all statistical methods, how ever in analytical applications the calibration range is thoroughly evaluated for accuracy and precision during method validation. I hope my longish answer makes some sense!

  4. Thank you for your presentation. It will be useful to who are working in AR@D and QC dept. it is good.

  5. Antigona says:

    Hi, Thank you for this useful video!I have question: how do you calculate the concentration of your samples when the calibrator concentrations fit a sigmoidal curve?Is the process similar to what you showed in this video?

    • Dr. Saurabh Arora says:

      Hi, the process will be the same, you just need to change to nonlinear regression to fit the sigmoidal curve.

  6. Hi, Thank you very much, I am glad to see the video, so much helpful for me , Could I downnloading the video currently ? , Thanks

  7. Could you pleeze send me this video to my email yuliani_tjoe@yahoo.com ?
    thanks you, very much

  8. rajasekhar says:

    very informative video. i would be grateful if you demonstrate how to calculate drug content in tablet using calibration curve .thank you

  9. This video has been very useful to me, thanks very much for your work.

  10. Hi. Thank you for sharing your knowledge with us, it is very informative. Could you do me a favour by sending this video (How to make a calibration curve and calculate sample concentrations using Excel – Video Tutorial) to my email? I want to download it but not able to. Thank you very much in advance.

  11. very informative..

  12. Robert R. Kerr, Ph.D. says:

    Well done. :)

  13. Abhishek aggarwal says:

    Hello Dr. Saurabh Arora sir,

    thank you so much for sharing very informative video with us regarding how to prepare a calulator on excel sheet.

    regards
    abhishek aggarwal
    M.Pharma ,PGDPRA

  14. thank you very much Dr. Saurabh Arora for sharing. it is very informative and helpful to me. Can you show us how you calculate inflection point from S- shape curve using excel? and thank you again.

    Radouane

  15. The video proved to be really useful for calculations!

    Thank you.

  16. Hi, Many thanks for your valuable compliments.It will be our constant endeavor to meet the expectations and improve further on the basis of comments and suggestions.

Speak Your Mind

*

Get Free and Instant Access to Lifelong Learning!

Lab-Training.Com provides you free ongoing learning on the fundamentals and latest developments in laboratory and analytical sciences. Come be part of our community of 7000 members!

  • Become an expert
  • Tips, Tricks and Troubleshooting
  • Just few minutes a week
  • Latest from the laboratory world
  • Brush up and strengthen your basics
  • Learn new techniques
  • Short and crisp articles