Tutorial on using Microsoft Excel to perform a linear regression on simple data. This tutorial will provide step-by-step instructions for using Excel to perform a least squares fit of a straight line to a data set. You will learn how to obtain the coefficients m, the slope, and b, the y-intercept, of the best linear fit of the form Y = mX + b. You will also see how to obtain the standard error in the coefficients.

I have written a short set of Notes on Linear Regression that outline a simple way to derive the equations for a linear regression. The notes also give a code fragment for programming a linear regression in Python using the SciPy modules.

The equations used (behind the scenes) by Excel in calculating the slope and intercept of the "best" straight line fitting a set of approximately linear data are usually derived in a course on probability and statistics. For Calculus level discussion with an emphasis on data analysis see Introduction to Error Analysis: The Study of Uncertainties in Physical Measurements (Second Edition, 1997) by John R. Taylor, University of Colorado, Boulder. Publisher: University Science Books. Taylor also discusses in detail the question of whether or not the fit is "good" which is closely related to the question of whether there is a correlation between the two variables. In physics this question is often answered for us on theoretical grounds. For example, velocity and time for a freely falling body are always going to be closely linear and well correlated unless it falls fast enough for air resistance to be important.

To begin the tutorial click Start

Back to Tutorials