How to Calculate MSE in Excel

By Stephanie Ellen

Mean squared error (MSE) is used in statistics to give a numerical value to the difference between values indicated by an estimation and the actual value of the quantity. The larger the MSE, the further away the estimation is from the true data points. To calculate the MSE by hand, you would have to make several calculations that opens the process to error. Using a spreadsheet format, such as Microsoft Excel, cuts down on errors and allows for faster calculation.

Step 1

Type the data points in column A, starting in cell A1.

Step 2

Type the estimated data points in column B, starting in cell B2.

Step 3

Type "=A1-B1" in cell C1, then grab the fill handle, which is the little black square at the bottom right of the cell. Drag the fill handle down the column to match the last row of data you entered in columns A and B.

Step 4

Type "=C1^2" into cell D2 then grab the fill handle. Pull the fill handle down the column to match the last row you filled in columns A through C.

Step 5

Click the first empty cell in column D, then click the sigma symbol in the Ribbon. This action adds the sum of the square of the errors.

Step 6

Calculate the MSE from the entered data. Click cell E1, then type "=." Click the summation cell, then type "/." Type the number of data points that you entered in column A. Press Enter to get the MSE.