Problem
You want to perform a quick linear curve fit without using chart trendlines and without having to write the leastsquares formulas yourself.
Solution
Use Excel's LINEST function.
Discussion
LINEST computes statistics for a leastsquares straight line through a given set of data. The syntax for LINEST is {= LINEST(yvalue cell range, xvalue cell range, compute intercept, compute statistics)}. Note the braces surrounding this formula since it is an array formula. When you type this formula into a cell, you have to press CtrlShiftEnter to enter it. Further, you have to select a 2 x 5 grid of cells before typing and entering the formula. This is because LINEST returns an array of data containing the various statistics computed for the bestfit line.
The first argument in LINEST is a cell range containing the yvalues for the data to be fit, and the second argument is a cell range containing the xvalues. The third argument is a logical value (true or false) specifying whether or not to force the intercept of the fit line to pass through zero. If TRue, the intercept is calculated in the usual leastsquares manner. If false, the intercept is forced to zero, with the slope computed accordingly. The fourth argument is a logical value indicating whether or not to display extended statistics for the bestfit line. These extended statistics include such things as standard errors and residual sums. (See the "LINEST" help topic in Excel's online help for more information on these statistics. Also, take a look at Recipes 8.7 and 8.8.)
Consider the chemical reaction data discussed in Recipe 4.5. Instead of plotting concentration versus reaction time on a semilog scale, you can plot the log of the concentration versus reaction time on a linear scale. The plot should look like a straight line. Figure 86 shows the log of the concentration versus reaction time data, along with an XY scatter plot of the data using linear axes.
Figure 86. Log concentration versus reaction time
I also included a linear trendline on this chart (see Recipe 8.1). The linear trendline represents the data very well.
Figure 87 shows the results of using LINEST to perform a linear fit. The data returned by LINEST is contained in cells H5 to I9. The key to the right describes what each returned value represents. The three values in bold type are the slope, intercept, and Rsquared value. These values agree very well with those returned by the chart trendline.
Figure 87. Linear fit using LINEST
You'll also notice that I have cell H5 selected. This is the first cell in the range that I selected before entering the formula. The formula I entered is displayed in the formula bar. The actual formula is =LINEST(F5:F13,D5:D13,TRUE,TRUE). The yvalues are contained in the range F5:F13. These are the log of concentration values. The xvalues are the reaction times in the range D5:D13. I set the final two arguments to trUE so that LINEST computes the intercept and returns extended statistics.
Here are the steps for using LINEST as I did in this example:
That's all there is to it.

LINEST is also capable of performing multiple linear regression where the equation fit is of the form y = m1x1 + m2x2 + m3x3 + ... + mixi + b. See Recipe 8.4 for an example.

See Also
Read the help topic "LinEst" in Excel's online help guide for more details on the LINEST function.
Using Excel
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Charting
Statistical Analysis
Time Series Analysis
Mathematical Functions
Curve Fitting and Regression
Solving Equations
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations
Index