5 Ways To Run Shapiro Wilk Test In Excel

In statistical analysis, it's essential to determine whether a dataset follows a normal distribution. One widely used method for this is the Shapiro-Wilk test. Excel doesn't have a built-in function for the Shapiro-Wilk test, but there are several workarounds to run this test in Excel.

In this article, we'll explore five different methods to run the Shapiro-Wilk test in Excel, including using add-ins, VBA macros, and online tools. By the end of this article, you'll be able to determine whether your dataset is normally distributed using the Shapiro-Wilk test in Excel.

Method 1: Using the Real Statistics Add-in

One of the easiest ways to run the Shapiro-Wilk test in Excel is by using the Real Statistics add-in. This add-in is a free Excel statistics software that provides a wide range of statistical functions, including the Shapiro-Wilk test.

To use the Real Statistics add-in, follow these steps:

Download and install the Real Statistics add-in from the official website.
Select the dataset you want to test for normality.
Go to the "Real Statistics" tab in the Excel ribbon.
Click on "Normality Tests" and select "Shapiro-Wilk Test".
The results will be displayed in a new worksheet, including the test statistic, p-value, and a conclusion about normality.

Method 2: Using VBA Macro

Another way to run the Shapiro-Wilk test in Excel is by using a VBA macro. This method requires some programming knowledge, but it's a great way to automate the test.

To use a VBA macro for the Shapiro-Wilk test, follow these steps:

Open the Visual Basic Editor in Excel by pressing "Alt + F11" or by navigating to "Developer" > "Visual Basic" in the Excel ribbon.
Create a new module by clicking "Insert" > "Module" in the Visual Basic Editor.
Paste the following code into the module:

Function ShapiroWilkTest(data As Range) As Double
    Dim n As Long
    Dim i As Long
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim d As Double
    Dim e As Double
    Dim W As Double
    
    n = data.Rows.Count
    ReDim X(n) As Double
    
    For i = 1 To n
        X(i) = data(i, 1).Value
    Next i
    
    a = WorksheetFunction.Average(X)
    b = WorksheetFunction.StDev(X)
    
    c = 0
    d = 0
    e = 0
    
    For i = 1 To n
        c = c + (X(i) - a) ^ 3
        d = d + (X(i) - a) ^ 2
        e = e + X(i) - a
    Next i
    
    W = (c / (n * b ^ 3)) / Sqr(d / (n * b ^ 2))
    
    ShapiroWilkTest = W
End Function

Save the module by clicking "File" > "Save" in the Visual Basic Editor.
Return to Excel and select the dataset you want to test for normality.
Go to a new cell and type "=ShapiroWilkTest(A1:A100)", replacing "A1:A100" with the range of your dataset.
Press "Enter" to get the test result.

Method 3: Using Analysis ToolPak

If you have Excel 2013 or later, you can use the Analysis ToolPak add-in to run the Shapiro-Wilk test. This method is similar to using the Real Statistics add-in, but it's a built-in Excel feature.

To use the Analysis ToolPak add-in, follow these steps:

Select the dataset you want to test for normality.
Go to the "Data" tab in the Excel ribbon.
Click on "Data Analysis" in the "Analysis" group.
Select "Normality Test" from the list of available tools.
Choose the Shapiro-Wilk test and select the dataset range.
Click "OK" to run the test.

Method 4: Using R

If you're familiar with R programming, you can use the "shapiro.test()" function to run the Shapiro-Wilk test. This method requires some knowledge of R, but it's a great way to automate the test.

To use R for the Shapiro-Wilk test, follow these steps:

Install and load the necessary R packages, such as "stats".
Import the dataset into R using the "read.csv()" or "read.xlsx()" function.
Run the Shapiro-Wilk test using the "shapiro.test()" function.
The results will be displayed in the R console, including the test statistic, p-value, and a conclusion about normality.

Method 5: Using Online Tools

Finally, you can use online tools to run the Shapiro-Wilk test. This method is great if you don't have access to Excel or R, or if you want to quickly test a small dataset.

To use online tools for the Shapiro-Wilk test, follow these steps:

Search for "Shapiro-Wilk test online" or "normality test online" in your favorite search engine.
Choose a reputable online tool, such as GraphPad or MedCalc.
Enter the dataset into the online tool.
Run the test and view the results, including the test statistic, p-value, and a conclusion about normality.

Gallery of Shapiro Wilk Test in Excel

Shapiro Wilk Test in Excel Image Gallery

Shapiro Wilk Test in Excel using Real Statistics

Shapiro Wilk Test using Analysis ToolPak

Shapiro Wilk Test for Normality in Excel

Final Thoughts

In conclusion, running the Shapiro-Wilk test in Excel is a straightforward process that can be accomplished using various methods. Whether you prefer using add-ins, VBA macros, or online tools, there's a method that suits your needs.

By following the steps outlined in this article, you'll be able to determine whether your dataset is normally distributed using the Shapiro-Wilk test. Remember to always verify the assumptions of the test and interpret the results correctly.

If you have any questions or need further clarification on any of the methods, feel free to ask in the comments section below.

5 Ways To Run Shapiro Wilk Test In Excel

Intro