Home About us Editorial board Search Ahead of print Current issue Archives Submit article Instructions Subscribe Contacts Login 

 Table of Contents  
Year : 2017  |  Volume : 44  |  Issue : 2  |  Page : 117-118

Calculation of Cronbach's alpha in spreadsheet: An alternative to costly statistics software

1 Department of Physiology, MKCG Medical College, Ganjam, Odisha, India
2 Department of Physiology, Medical College and Hospital, Kolkata, West Bengal, India

Date of Web Publication11-Oct-2017

Correspondence Address:
Himel Mondal
Department of Physiology, MKCG Medical College, Ganjam - 760 004, Odisha
Login to access the Email id

Source of Support: None, Conflict of Interest: None

DOI: 10.4103/jss.JSS_18_17

Rights and Permissions

How to cite this article:
Mondal H, Mondal S. Calculation of Cronbach's alpha in spreadsheet: An alternative to costly statistics software. J Sci Soc 2017;44:117-8

How to cite this URL:
Mondal H, Mondal S. Calculation of Cronbach's alpha in spreadsheet: An alternative to costly statistics software. J Sci Soc [serial online] 2017 [cited 2020 Sep 22];44:117-8. Available from: http://www.jscisociety.com/text.asp?2017/44/2/117/216491


When investigators try to study some characteristics in population by asking some questions, they design a questionnaire. After initial drafting of the questionnaire, it is usually tested for content and construct validity. Next is the test for internal consistency of the questionnaire. The overall internal consistency of the questionnaire can be measured by Cronbach's alpha.[1] Researcher can easily calculate the α with some clicks in statistics software. However, costly statistics software package may not be accessible to researchers in smaller settings in developing countries. Hence, we provided an alternative way to calculate Cronbach's alpha, backed by theoretical knowledge.

OpenOffice™ Calc is free spreadsheet software which is a part of Apache OpenOffice™ software package (The Apache Software Foundation, Los Angeles, California, USA). It can be used as an alternative to Microsoft Excel.

We drafted a set of four questions with response option in 5-point Likert-type scale ranging from 0 for lowest score and 5 for highest score. We took filled up questionnaire of only six respondents for convenience and scored it in spreadsheet as shown in [Figure 1].
Figure 1: Calculation of Cronbach's alpha in cell E13 with the calculated value of B12, E12, and B13 in OpenOffice™ Calc spreadsheet

Click here to view

First, we calculated the standard deviation (SD) of the data of each column. For calculation of SD of column B data, we wrote B10 cell formula as “= STDEV (B2:B7)”, and the SD was calculated as 0.752773. Similarly, we calculated the SD of other columns. In column F, we calculated the total score of four questions of each subject. For calculation of total score of subject 1, we wrote formula for F2 cell as “= SUM (B2:E2)” and the sum score was calculated as 11. Then, we calculated the SD of sum score in cell F10.

The formula for calculation of α is:[2],[3]

Where, k = number of items (question/statement) in questionnaire

Si = SD of ith item

St = SD of sum score

We wrote A12 cell text as for ease of identification and the right adjacent cell (B12) equation was written as “= COUNT (B2:E2)/(COUNT (B2:E2)-1)”. Then, we wrote D12 cell text as and the right adjacent cell (E12) formula was written as “= SUMSQ (B10:E10)”, and the sum square of all SD was calculated in the cell E2. The A13 cell text was written as and the right adjacent cell (B13) formula was written as “= F10^2”. The α was calculated in cell E13 when we put the cell formula as “= B12* (1-(E12/B13))”. The calculated value of α in E13 was 0.835.

Those who have access to Microsoft Excel, they can carry out the same calculation in an Excel ® spreadsheet as described above. We had tested all the formulae in Excel and found it compatible. In addition, we calculated α for the above example in IBM SPSS Statistics (New Orchard Road Armonk, New York, USA) and it showed same result.

It was possible to calculate α of a questionnaire with the help of free computer software. Cronbach's alpha for any number of questions and responses can be calculated in similar fashion. This article would help researchers in resource-poor setting for calculation of Cronbach's alpha.

Declaration of patient consent

The authors certify that they have obtained all appropriate patient consent forms. In the form the patient(s) has/have given his/her/their consent for his/her/their images and other clinical information to be reported in the journal. The patients understand that their names and initials will not be published and due efforts will be made to conceal their identity, but anonymity cannot be guaranteed.

Financial support and sponsorship


Conflicts of interest

There are no conflicts of interest.

  References Top

Hulley SB, Cummings SR, Browner WS, Grady DG, Newman TB. Designing Questionnaires and Interviews. In: Designing Clinical Research, 3rd ed. Philadelphia, PA: Lippincott Williams and Wilkins; 2001.p.249-54.  Back to cited text no. 1
Bland JM, Altman DG. Cronbach's alpha. BMJ. 1997;314:572.  Back to cited text no. 2
Machin D, Campbell MJ, Walters SJ. Reliability and method comparison studies. In: Medical Statistics, 4th ed. England: John Wiley and Sons Ltd; 2007.p.209  Back to cited text no. 3


  [Figure 1]


Similar in PUBMED
   Search Pubmed for
   Search in Google Scholar for
Access Statistics
Email Alert *
Add to My List *
* Registration required (free)

  In this article
Article Figures

 Article Access Statistics
    PDF Downloaded896    
    Comments [Add]    

Recommend this journal