How to Calculate Standard Error in Excel?

Microsoft Excel’s mathematical capabilities are usually beyond the scope of an average user. However, the addition of statistical functions in Excel makes it a must-have application for any individual that uses or manages any type of statistical data. One of the basic statistical requirements is to calculate the standard error or standard error of the mean (S.E.M).

How to Calculate Standard Error in Excel

But unlike many other functions, Excel is yet not equipped with a direct formula to calculate the standard error, which makes the process a bit difficult. But no need to worry, we will guide you step by step.

Definition and Importance of Standard Error

The standard error (S.E.) of a sample tells how accurate its mean is as compared to the true mean of the population as working with the entire population in the real-world data is usually not possible. So sampling is used. It also helps in identifying the variation or dispersion of different data samples and also helps in estimating your samples’ reliability.

Standard Error Formula

In Statistics, Standard Error is equal to Standard Deviation divided by the square root of the size of the sample i.e.,

Standard Error= Standard Deviation/√n

Where n is the sample size.

Standard Error Formula

Standard Error Formula in Excel

Calculating standard error from the above formula is a routine matter for many statisticians but using automatic calculations through Excel can greatly enhance your efficiency and accuracy. As discussed earlier, the standard error is equal to the standard deviation divided by the square root of your sample size, so, in Excel, we can calculate the standard deviation by the following formula

STDEV(number1,number2)

Keep in mind, that the STDEV function is the same as STDEV.S as it calculates the standard deviation of a sample, whereas, STDEV.P is different as it is applied to the whole population, not just the sample.

To count the sample size in Excel:

COUNT(number1,number2)

And to calculate the square root of the count, the formula would be

SQRT(COUNT(number1,number2))

So, the formula for Standard Error in Excel would be

STDEV(number1,number2)/ SQRT(COUNT(number1,number2))

For example, if your sample is located from B1 cell to G4 cell, then the formula for standard error would be

STDEV(B1:G4)/SQRT(COUNT(B1:G4))

Calculating Standard Error In Excel

Calculating standard error in Excel can be easily divided into the following steps:

Prepare Your Sheet and Enter your data

Firstly, you should prepare your sheet and enter your data. Also, it will be good to add descriptive labels as column headings. For this, you may use the sheet as shown in the image where B2 to B11 is the sample data.

Sample Data to Calculate Standard Error

There are two main ways you can calculate the Standard Error. One is through a series of separate formulas and one is to nest all the formulas together. First, we will discuss using separate formulas. 

1.1: Calculate Standard Deviation

  1. Click in the B12 cell of the Excel sheet and press the = sign.
  2. Now type STDEV( and then select the B2 to B11 range.
    Enter Standard Deviation Formula in the Excel Sheet
  3. Then press the ) key and hit the Enter key. Now the Standard Deviation of the sample data will be shown to you. 
    Standard Deviation of the Sample Data

Count the Sample Size

  1. Click in the B13 cell and press the = sign.
  2. Now type Count( and then select the B2 to B11 range.
    Count the Size of the Sample Data
  3. Then press the ) and hit the Enter key. Now the calculated size of the sample data will be shown to you. 
    Calculated Size of the Sample Data

1.2: Calculate the Square Root of the Sample Size

  1. Click in the B14 cell and press the = sign.
  2. Now type SQRT( and select the B13 cell (where the count of the sample size is present).
    Calculate Square Root of the Size of the Sample Data
  3. Then press the ) key and hit the Enter key. Now calculated value of the square root of the sample size will be shown to you. 
    Calculated Square Root of the Size of the Sample Data

1.3: Calculate Standard Error

  1. Click in the B15 cell and press the = key.
  2. Now select the B12 cell (where the standard deviation is calculated) and press the / key.
  3. Then select the B14 cell (where the square root of sample size is calculated) and press the Enter key.
    Calculate Standard Error of the Sample Data
  4. Now the standard error, calculated by Excel will be shown to you.
    Calculated Standard Error of the Sample Data

2: Use a Single Formula to Calculate Standard Error In Excel

Some of our readers may not feel comfortable with using 4 formulas to calculate Standard Error, no need to worry, you can calculate Standard Error by only using a single formula (basically, we split the formula to make the concept clear to newbies). As you have understood the process, you can use the single formula wherever you want to calculate standard error by using Excel. 

  1. Click in the B16 cell and press the = key.
  2. Now type STDEV( and select the range of B2 to B11.
  3. Then press the ) key and afterward, press the / key.
  4. Now type SQRT(Count( and select the range of B2 to B11.
  5. Then press the ) key twice and hit the Enter key. The complete formula will be:
    =STDEV(B2:B11)/SQRT(Count(B2:B11))
    Calculate Standard Error of the Sample Data by Using a Single Formula
  6. Ta-da, the standard error calculated by Excel is shown to you by using a single formula.
    Calculated Standard Error of the Sample Data by Using a Single Formula
  7. If you want to calculate the standard error of C2 to C11, simply drag the formula from B16 cell to C16 cell and the standard error of that range will be calculated within the blink of an eye. Just think, how much time and energy the manual calculations would have taken.
    Drag the Standard Error Formula to C16 to Calculate Standard Error of C2 to C11

3: Use Analysis Toolpak to Calculate Standard Deviation and Count In Excel

You can also use the data analyzing capabilities of Excel to calculate standard deviation and count, which can then be used to calculate Standard error. 

  1. Launch Excel and open its File menu.
  2. Now expand More and select Options.
    Open Excel Options in the File Menu
  3. Then, in the left pane, head to the Add-ins tab, and the right pane, select Analysis Toolpak.
    Select Analysis Toolpak in the Excel Add-ins and Click Go
  4. Now, near the bottom of the screen, click on Go, and then, in the pop-up shown, tick the checkbox of Analysis ToolPak.
    Install Analysis ToolPak Add-in in Excel
  5. Then click on the OK button and wait till the installation completes.
  6. Now, open the sample sheet (discussed above) and head to its Data tab.
    Open Data Analysis in the Data Tab of Excel
  7. Then click on Data Analysis and select Descriptive Statistics. Afterward, click on OK
    Select Descriptive Statistics in Excel’s Data Analysis
  8. Now select B1 to B11 (B1 contains label) as the Input Field and select the radio button of Columns.
  9. Then tick the checkbox of Labels in First Row and checkmark the checkbox of Summary Statistics.
  10. Now select the radio button of Output Range and select the output range like D2 to F20
    Use Descriptive Statistics to Calculate Standard Deviation and Count of the Sample Data
  11. Then click OK and you can easily find the standard deviation value along with count in the summary.
    Use Descriptive Statistics to Calculate Standard Deviation and Count of the Sample Data
  12. Now you can calculate standard error by using these values. 
    Calculated Standard Error of the Sample Data Through the Excel’s Analysis ToolPak

Dear readers, if you have any queries in calculating Standard Error in Excel, do not hesitate to ask in the comments. 

Kevin Arrows
Kevin is a dynamic and self-motivated information technology professional, with a Thorough knowledge of all facets pertaining to network infrastructure design, implementation and administration. Superior record of delivering simultaneous large-scale mission critical projects on time and under budget.

Expert Tip

How to Calculate Standard Error in Excel?

If the issue is with your Computer or a Laptop you should try using Restoro which can scan the repositories and replace corrupt and missing files. This works in most cases, where the issue is originated due to a system corruption. You can download Restoro by clicking the Download button below.

Download Now

I'm not interested