How To Add Asterisks To Correlation Table In MS-Excel

This article clearly illustrates how to use asterisks to indicate statistical significance in correlation table on MS-Excel.

It also illustrates how to calculate the correlation coefficient (r) critical value and how to identify statistically significant correlations at 5% (p<0.05) and 1% (p<0.01) levels of statistical significance.

In this article, I also illustrated how to conditionally format the correlation table to add single asterisks (for p<0.05) and double asterisks (for p<0.01) for significant correlations.

In my previous article, I described how to run a basic correlation analysis on MS-Excel. To read more on that, click here.

Steps In Adding Asterisks To Correlation Table In Excel.

To add asterisks, the following steps should be taken:

Step 1: Identify the number of data sets your correlation table is composed of, that is, the n-value. In the case of our sample data set, n = 9.

Step 2: Calculate the r critical value using the formular:

r critical value = T.INV (1-alpha/2, n-2)/SQRT (T.INV (1-alpha/2, n-2) ^2+n-2)

Where:

T.INV= t-distribution inverse

Alpha value = 0.05 and 0.01

n= number of observations

SQRT= Squareroot

The calculation is done for the two alpha values of 0.05 and 0.01.

Step 3: Proceed to conditional formatting the correlation table. Click conditional formatting on the home menu and select new rules.

Step 4: Select “format only cells that contain”. The cell value should be the r critical values of 0.05 and 0.01.

Step 5: Click on format. Go to number formatting and set it to three decimal places. Open and close quotation marks and type in an asterisk. This will enable it flag significant correlations at 5% level of probability.

Step 6: Click OK.

From our illustration, we can observe that none of our variables are significantly correlated at 5% level of probability.

Step 7: Repeat step 5 again to get correlation values at 1% alpha value. Put two asterisks instead of one to flag significant correlations at p<0.01.

From the results above, we can observe that only OC and Av.P are significantly correlated at 1% level of probability (p<0.01).

As the correlation coefficient is positive, it indicates that as the value of OC increases, similar trend of increase is expected for AV.P.

I hope this article has shown you how to flag significant correlations on MS-Excel. Ensure to practice repeatedly to gain mastery of it.

Do not forget to drop your questions and comments on the comment box below.

Also subscribe to this blog to get more do-it-yourself (DIY) trainings on data analysis. Share across your social media platforms too!