Skip to main content

Thank you for visiting nature.com. You are using a browser version with limited support for CSS. To obtain the best experience, we recommend you use a more up to date browser (or turn off compatibility mode in Internet Explorer). In the meantime, to ensure continued support, we are displaying the site without styles and JavaScript.

  • Protocol
  • Published:

Nonlinear least-squares data fitting in Excel spreadsheets

Abstract

We describe an intuitive and rapid procedure for analyzing experimental data by nonlinear least-squares fitting (NLSF) in the most widely used spreadsheet program. Experimental data in x/y form and data calculated from a regression equation are inputted and plotted in a Microsoft Excel worksheet, and the sum of squared residuals is computed and minimized using the Solver add-in to obtain the set of parameter values that best describes the experimental data. The confidence of best-fit values is then visualized and assessed in a generally applicable and easily comprehensible way. Every user familiar with the most basic functions of Excel will be able to implement this protocol, without previous experience in data fitting or programming and without additional costs for specialist software. The application of this tool is exemplified using the well-known Michaelis–Menten equation characterizing simple enzyme kinetics. Only slight modifications are required to adapt the protocol to virtually any other kind of dataset or regression equation. The entire protocol takes 1 h.

This is a preview of subscription content, access via your institution

Access options

Rent or buy this article

Prices vary by article type

from$1.95

to$39.95

Prices may be subject to local taxes which are calculated during checkout

Figure 1: ADD-INS window.
Figure 2: Worksheet after the completion of Step 5.
Figure 3: Worksheet in FORMULA AUDITING MODE after the completion of Step 6.
Figure 4: SOLVER PARAMETERS window with the appropriate values for Set Target Cell, Equal To, By Changing Cells and Subject to the Constraints.
Figure 5: SOLVER OPTIONS window with default selection of values appropriate for most fitting problems.
Figure 6: Worksheet after the completion of Step 10.
Figure 7: Confidence assessment of best-fit parameter values.
Figure 8: Circular dichroism (CD) melting curve of 20 μM RNAse A (Sigma–Aldrich, Steinheim, Germany) in 15 mM potassium acetate buffer (pH 5.5).
Figure 9: Confidence assessment of best-fit parameter values.

Similar content being viewed by others

References

  1. De Levie, R. Advanced Excel for Scientific Data Analysis 2nd edn. (Oxford University Press, New York, 2008).

  2. Johnson, M.L. Why, when, and how biochemists should use least squares. Anal. Biochem. 206, 215–225 (1992).

    Article  CAS  PubMed  Google Scholar 

  3. Press, W.H., Teukolsky, A.S., Vetterling, W.T. & Flannery, B.P. Modeling of data. In Numerical Recipes in C: The Art of Scientific Computing 2nd edn. 656–706 (Cambridge University Press, New York, 1992).

  4. Bevington, P.R. & Robinson, D.K. Least-squares fit to an arbitrary function. In Data Reduction and Error Analysis for the Physical Sciences 3rd edn. 142–167 (McGraw-Hill Higher Education, New York, 2009).

  5. Motulsky, H. & Christopoulos, A. Fitting Models to Biological Data Using Linear and Nonlinear Regression: A Practical Guide to Curve Fitting 2nd edn. (GraphPad Software, San Diego, 2003).

  6. Orvis, W.J. Excel for Scientists and Engineers 1st edn. (SYBEX, Alameda, 1995).

  7. Fylstra, D., Lasdon, L., Watson, J. & Waren, A. Design and use of the Microsoft Excel Solver. Interfaces 28, 29–55 (1998).

    Article  Google Scholar 

  8. Lasdon, L.S., Waren, A.D., Jain, A. & Ratner, M. Design and testing of a generalized reduced gradient code for nonlinear programming. ACM T. Math. Software 4, 34–50 (1987).

    Article  Google Scholar 

  9. Beck, A., Tsamaloukas, A.D., Jurcevic, P. & Heerklotz, H. Additive action of two or more solutes on lipid membranes. Langmuir 24, 8833–8840 (2008).

    Article  CAS  PubMed  Google Scholar 

  10. Tsamaloukas, A.D., Beck, A. & Heerklotz, H. Modeling the micellization behavior of mixed and pure n-alkyl-maltosides. Langmuir 25, 4393–4401 (2009).

    Article  CAS  PubMed  Google Scholar 

  11. Plouffe, L. Jr. & Luxenberg, S.N. Biological modeling on a microcomputer using standard spreadsheet and equation solver programs: the hypothalamic-pituitary-ovarian axis as an example. Comput. Biomed. Res. 25, 117–130 (1992).

    Article  PubMed  Google Scholar 

  12. Hargrove, J.L., Heinz, G. & Heinz, O. Modeling transitions in body composition: the approach to steady state for anthropometric measures and physiological functions in the Minnesota human starvation study. Dyn. Med. 7, 16 (2008).

    Article  PubMed  PubMed Central  Google Scholar 

  13. Stevens, P.W. & Kelso, D.M. Estimation of the protein-binding capacity of microplate wells using sequential ELISAs. J. Immunol. Methods 178, 59–70 (1995).

    Article  CAS  PubMed  Google Scholar 

  14. Kawamata, W. & Toyoshima, H. Estimation of T1 and T2 using general-purpose spreadsheet software. Nippon Hoshasen Gijutsu Gakkai Zasshi 65, 306–311 (2009).

    Article  Google Scholar 

  15. Rohatagi, S., Hochhaus, G., Möllmann, H., Barth, J. & Derendorf, H. Pharmacokinetic interaction between endogenous cortisol and exogenous corticosteroids. Pharmazie 50, 610–613 (1995).

    CAS  PubMed  Google Scholar 

  16. Dansirikul, C., Choi, M. & Duffull, S.B. Estimation of pharmacokinetic parameters from non-compartmental variables using Microsoft Excel. Comput. Biol. Med. 35, 389–403 (2005).

    Article  CAS  PubMed  Google Scholar 

  17. Meineke, I. & Brockmöller, J. Simulation of complex pharmacokinetic models in Microsoft Excel. Comput. Methods Programs Biomed. 88, 239–245 (2007).

    Article  PubMed  Google Scholar 

  18. Briones, A.M. Jr. & Reichardt, W. Estimating microbial population counts by 'most probable number' using Microsoft Excel. J. Microbiol. Methods 35, 157–161 (1999).

    Article  PubMed  Google Scholar 

  19. Sonnenberg, A. Special review: game theory to analyse management options in gastro-oesophageal reflux disease. Aliment. Pharmacol. Ther. 14, 1411–1417 (2000).

    Article  CAS  PubMed  Google Scholar 

  20. Ward, R., Schlenker, J. & Anderson, G.S. Simple method for developing percentile growth curves for height and weight. Am. J. Phys. Anthropol. 116, 246–250 (2001).

    Article  CAS  PubMed  Google Scholar 

  21. Zhang, F. & Roush, W.B. Multiple-objective (goal) programming model for feed formulation: an example for reducing nutrient variation. Poult. Sci. 81, 182–192 (2002).

    Article  CAS  PubMed  Google Scholar 

  22. Guevara, V.R. Use of nonlinear programming to optimize performance response to energy density in broiler feed formulation. Poult. Sci. 83, 147–151 (2004).

    Article  CAS  PubMed  Google Scholar 

  23. Kuo, P.C., Schroeder, R.A., Mahaffey, S. & Bollinger, R.R. Optimization of operating room allocation using linear programming techniques. J. Am. Coll. Surg. 197, 889–895 (2003).

    Article  PubMed  Google Scholar 

  24. Maurer, M., Kühleitner, M., Gasser, B. & Mattanovich, D. Versatile modeling and optimization of fed batch processes for the production of secreted heterologous proteins with Pichia pastoris . Microb. Cell Fact. 5, 37 (2006).

    Article  PubMed  PubMed Central  Google Scholar 

  25. Abdel-Fattah, Y.R. et al. Application of factorial experimental designs for optimization of cyclosporin A production by Tolypocladium inflatum in submerged culture. J. Mol. Microbiol. Biotechnol. 17, 1930–1936 (2007).

    CAS  Google Scholar 

  26. Burke, J.A. Two mathematical programming models of cheese manufacture. J. Dairy Sci. 89, 799–809 (2006).

    Article  CAS  PubMed  Google Scholar 

  27. Schrader, H. & Svec, A. Comparison of ionization chamber efficiencies for activity measurements. Appl. Radiat. Isot. 60, 369–378 (2004).

    Article  CAS  PubMed  Google Scholar 

  28. Brown, A.M. A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet. Comput. Methods Programs Biomed. 65, 191–200 (2001).

    Article  CAS  PubMed  Google Scholar 

  29. Brown, A.M. A non-linear regression analysis program for describing electrophysiological data with multiple functions using Microsoft Excel. Comput. Methods Programs Biomed. 82, 51–57 (2006).

    Article  PubMed  Google Scholar 

  30. Branco, T.J., Botelho do Rego, A.M., Ferreira, M.I. & Vieira Ferreira, L.F. Luminescence lifetime distributions analysis in heterogeneous systems by the use of Excel's Solver. J. Phys. Chem. B 109, 15958–15967 (2005).

    Article  CAS  PubMed  Google Scholar 

  31. Li, J. Comparison of the capability of peak functions in describing real chromatographic peaks. J. Chromatogr. A 952, 63–70 (2002).

    Article  CAS  PubMed  Google Scholar 

  32. Nikitas, P., Pappa-Louisi, A. & Papageorgiou, A. On the equations describing chromatographic peaks and the problem of the deconvolution of overlapped peaks. J. Chromatogr. A 912, 13–29 (2001).

    Article  CAS  PubMed  Google Scholar 

  33. Nikitas, P., Pappa-Louisi, A., Papageorgiou, A. & Zitrou, A. On the use of genetic algorithms for response surface modeling in high-performance liquid chromatography and their combination with the Microsoft Solver. J. Chromatogr. A 942, 93–105 (2002).

    Article  CAS  PubMed  Google Scholar 

  34. Karmarkar, S., Garber, R., Kluza, J. & Koberda, M. Gel permeation chromatography of dextrans in parenteral solutions: calibration procedure development and method validation. J. Pharm. Biomed. Anal. 41, 1260–1267 (2006).

    Article  CAS  PubMed  Google Scholar 

  35. Dasgupta, P.K. Chromatographic peak resolution using Microsoft Excel Solver. The merit of time shifting input arrays. J. Chromatogr. A 1213, 50–55 (2008).

    Article  CAS  PubMed  Google Scholar 

  36. van Dijk, J.W. Thermoluminescence glow curve deconvolution and its statistical analysis using the flexibility of spreadsheet programs. Radiat. Prot. Dosimetry 119, 332–338 (2006).

    Article  CAS  PubMed  Google Scholar 

  37. Walsh, S. & Diamond, D. Non-linear curve fitting using Microsoft Excel Solver. Talanta 42, 561–572 (1995).

    Article  CAS  PubMed  Google Scholar 

  38. Kane, P. & Diamond, D. Determination of ion-selective electrode characteristics by non-linear curve fitting. Talanta 44, 1847–1858 (1997).

    Article  CAS  PubMed  Google Scholar 

  39. Luther, G.W. III, Theberge, S.M. & Rickard, D. Determination of stability constants for metal-ligand complexes using the voltammetric oxidation wave of the anion/ligand and the DeFord and Hume formalism. Talanta 51, 11–20 (2000).

    Article  CAS  PubMed  Google Scholar 

  40. Comuzzi, C., Polese, P., Melchior, A., Portanova, R. & Tolazzi, M. SOLVERSTAT: a new utility for multipurpose analysis. An application to the investigation of dioxygenated Co(II) complex formation in dimethylsulfoxide solution. Talanta 59, 67–80 (2003).

    Article  CAS  PubMed  Google Scholar 

  41. Safavi, A., Maleki, N., Rostamzadeh, A. & Maesum, S. CCD camera full range pH sensor array. Talanta 71, 498–501 (2007).

    Article  CAS  PubMed  Google Scholar 

  42. Parsons, J.D. A high-throughput method for fitting dose–response curves using Microsoft Excel. Anal. Biochem. 360, 309–311 (2007).

    Article  CAS  PubMed  Google Scholar 

  43. Bárány-Wallje, E. et al. A critical reassessment of penetratin translocation across lipid membranes. Biophys. J. 89, 2513–2521 (2005).

    Article  PubMed  PubMed Central  Google Scholar 

  44. Keller, S., Böthe, M., Bienert, M., Dathe, M. & Blume, A. A simple fluorescence-spectroscopic membrane translocation assay. ChemBioChem 8, 546–552 (2007).

    Article  CAS  PubMed  Google Scholar 

  45. Keller, S., Tsamaloukas, A. & Heerklotz, H. A quantitative model describing the selective solubilization of membrane domains. J. Am. Chem. Soc. 127, 11469–11476 (2005).

    Article  CAS  PubMed  Google Scholar 

  46. Schmidt, M.F., El-Dahshan, A., Keller, S. & Rademann, J. Selective identification of cooperatively binding fragments in a high-throughput ligation assay enables the evolution of a picomolar caspase-3 inhibitor. Angew. Chem. Int. Ed. 48, 6346–6349 (2009).

    Article  CAS  Google Scholar 

  47. Keller, S. et al. Membrane-mimetic nanocarriers formed by a dipalmitoylated cell-penetrating peptide. Angew. Chem. Int. Ed. 44, 5252–5255 (2005).

    Article  CAS  Google Scholar 

  48. Keller, S., Heerklotz, H., Jahnke, N. & Blume, A. Thermodynamics of lipid membrane solubilization by sodium dodecyl sulfate. Biophys. J. 90, 4509–4521 (2006).

    Article  CAS  PubMed  PubMed Central  Google Scholar 

  49. Heerklotz, H., Tsamaloukas, A.D. & Keller, S. Monitoring detergent-mediated solubilization and reconstitution of lipid membranes by isothermal titration calorimetry. Nat. Protoc. 4, 686–697 (2009).

    Article  CAS  PubMed  Google Scholar 

  50. Keller, S., Heerklotz, H. & Blume, A. Monitoring lipid membrane translocation of sodium dodecyl sulfate by isothermal titration calorimetry. J. Am. Chem. Soc. 128, 1279–1286 (2006).

    Article  CAS  PubMed  Google Scholar 

  51. Geissler, D. et al. (Coumarin-4-yl)methyl esters as highly efficient, ultrafast phototriggers for protons and their application to acidifying membrane surfaces. Angew. Chem. Int. Ed. 44, 1195–1198 (2005).

    Article  CAS  Google Scholar 

  52. Hagen, V. et al. Coumarinylmethyl esters for ultrafast release of high concentrations of cyclic nucleotides upon one- and two-photon photolysis. Angew. Chem. Int. Ed. 44, 7887–7891 (2005).

    Article  CAS  Google Scholar 

  53. Cambridge, S.B., Geissler, D., Keller, S. & Cürten, B. A caged doxycycline analogue for photoactivated gene expression. Angew. Chem. Int. Ed. 45, 2229–2231 (2006).

    Article  CAS  Google Scholar 

  54. Gilbert, D. et al. Caged capsaicins: new tools for the examination of TRPV1 channels in somatosensory neurons. ChemBioChem 8, 89–97 (2007).

    Article  CAS  PubMed  Google Scholar 

  55. Sauer, I. et al. Dipalmitoylation of a cellular uptake-mediating apolipoprotein E-derived peptide as a promising modification for stable anchorage in liposomal drug carriers. Biochim. Biophys. Acta. 1758, 552–561 (2006).

    Article  CAS  PubMed  Google Scholar 

  56. Tsamaloukas, A.D., Keller, S. & Heerklotz, H. Uptake and release protocol for assessing membrane binding and permeation by way of isothermal titration calorimetry. Nat. Protoc. 2, 695–704 (2007).

    Article  CAS  PubMed  Google Scholar 

  57. Seelig, J. Titration calorimetry of lipid–peptide interactions. Biochim. Biophys. Acta. 1331, 103–116 (1997).

    Article  CAS  PubMed  Google Scholar 

  58. Seelig, J. Thermodynamics of lipid–peptide interactions. Biochim. Biophys. Acta. 1666, 40–50 (2004).

    Article  CAS  PubMed  Google Scholar 

  59. Forrest, S. Genetic algorithms: principles of natural selection applied to computation. Science 261, 872–878 (1993).

    Article  CAS  PubMed  Google Scholar 

  60. Motulsky, H.J. & Ransnas, L.A. Fitting curves to data using nonlinear regression: a practical and nonmathematical review. FASEB J. 1, 365–374 (1987).

    Article  CAS  PubMed  Google Scholar 

  61. Johnson, M.L. Evaluation and propagation of confidence intervals in nonlinear, asymmetrical variance spaces. Analysis of ligand-binding data. Biophys. J. 44, 101–106 (1983).

    Article  CAS  PubMed  PubMed Central  Google Scholar 

  62. Johnson, M.L. & Frasier, S.G. Nonlinear least-squares analysis. Methods Enzymol. 117, 301–342 (1985).

    Article  CAS  Google Scholar 

  63. Johnson, K.A., Simpson, Z.B. & Blom, T. FitSpace Explorer: an algorithm to evaluate multidimensional parameter space in fitting kinetic data. Anal. Biochem. 387, 30–41 (2009).

    Article  CAS  PubMed  Google Scholar 

  64. Michaelis, L. & Menten, M.L. Die Kinetik der Invertinwirkung. Biochem. Z. 49, 333–369 (1913).

    CAS  Google Scholar 

  65. Lineweaver, H. & Burk, D. The determination of enzyme dissociation constants. J. Am. Chem. Soc. 56, 658–666 (1934).

    Article  CAS  Google Scholar 

  66. Berg, J.M., Tymoczko, J.L. & Stryer, L. Biochemistry 5th edn. (W.H. Freeman & Company, New York, 2002).

  67. Wisniak, J. & Polishuk, A. Analysis of residuals—a useful tool for phase equilibrium data analysis. Fluid Phase Equilib. 164, 61–82 (1999).

    Article  CAS  Google Scholar 

  68. Greenfield, N.J. Using circular dichroism collected as a function of temperature to determine the thermodynamics of protein unfolding and binding interactions. Nat. Protoc. 1, 2527–2535 (2006).

    Article  CAS  PubMed  PubMed Central  Google Scholar 

  69. Pace, C.N., Grimsley, G.R., Thomas, S.T. & Makhatadze, G.I. Heat capacity change for ribonuclease A folding. Protein Sci. 8, 1500–1504 (1999).

    Article  CAS  PubMed  PubMed Central  Google Scholar 

Download references

Acknowledgements

We thank Professor Heiko Heerklotz (University of Toronto, Canada), Dr. Alekos Tsamaloukas (Rensselaer Polytechnic Institute, USA), Natalie Bordag (FMP) and Martin Kemmer (23karat GmbH, Berlin, Germany) for helpful discussions and constructive comments on the manuscript. This work was supported by grant KE 1478/1-1 from the Deutsche Forschungsgemeinschaft (DFG) to S.K.

Author information

Authors and Affiliations

Authors

Contributions

G.K. designed and performed experiments, analyzed and fitted data, and wrote the manuscript. S.K. designed experiments, analyzed data and wrote the manuscript.

Corresponding author

Correspondence to Sandro Keller.

Rights and permissions

Reprints and permissions

About this article

Cite this article

Kemmer, G., Keller, S. Nonlinear least-squares data fitting in Excel spreadsheets. Nat Protoc 5, 267–281 (2010). https://doi.org/10.1038/nprot.2009.182

Download citation

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1038/nprot.2009.182

This article is cited by

Comments

By submitting a comment you agree to abide by our Terms and Community Guidelines. If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Search

Quick links

Nature Briefing

Sign up for the Nature Briefing newsletter — what matters in science, free to your inbox daily.

Get the most important science stories of the day, free in your inbox. Sign up for Nature Briefing