Doing so copies the data down the rest of the column until it reaches the last data entry.Įnter “=B1-C1” without quotes in cell D1 to calculate the residual, or the predicted value’s deviation from the actual amount.Ĭlick the cell again and double-click the “Fill Handle” to copy the formula and display the residuals for each data entry. This calculates the predicted value.Ĭlick the cell again and double-click the small, black “Fill Handle” at the bottom right of the cell. Leave “Linear” selected and check “Display Equation on Chart.” Close the “Format Trendline” side panel.Įnter the equation that appears on the chart in cell C1, but replace “X” with “A1.” As an example, if the displayed equation is “y = 362.46x + 26259, enter “=362.46*A1+26356” without quotes in cell C1.
In the example, highlight cells A1 through B15.Ĭlick the “Insert” tab, choose “Insert Scatter (X,Y) or Bubble Chart” from the Charts group and select the first “Scatter” option to create a scatter chart.Ĭlick “Add Chart Elements” from the Design tab, “Trendline” and then “More Trendline Options.” Click and drag your mouse across both data sets to highlight all the values.
