How To Add Asterisks To Correlation Table In MS-Excel
This article clearly illustrates using asterisks to indicate statistical significance in correlation tables in 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 about 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 comprises, the n-value. In the case of our sample data set, n = 9.
Step 2: Calculate the r critical value using the formula:
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 of 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.
Our illustration shows that none of our variables are significantly correlated at 5% probability level.
Step 7: Repeat step 5 again to get correlation values at 1% alpha. Put two asterisks instead of one to flag significant correlations at p<0.01.
The results above show that only OC and Av.P are significantly correlated at 1% probability level (p<0.01).
The correlation coefficient is positive, which indicates that as the value of OC increases, a similar trend of increase is expected for AV.P.
I hope this article has shown you how to flag significant correlations in MS Excel. Ensure to practice repeatedly to gain mastery of it.
Do not forget to drop your questions and comments in the comment box below.
Subscribe to this blog to receive more do-it-yourself (DIY) training on data analysis. Share on your social media platforms, too!
