Introduction to Real Statistics using Excel

Statistics Objectives

Statistics is a field of study that has two principal objectives:

  • Describing data (Descriptive Statistics)
  • Making inferences based on experimentally observed data (Inferential Statistics)

Descriptive Statistics involves calculating the mean, median, variance, standard deviation and other properties of the data, and presenting this information in ways that make the data more meaningful, such as histograms, boxplots, QQ plots, etc.

Inferential Statistics involves analyzing data and inferring characteristics of a general population based on the same properties of a sample taken from the population. This is what gives the field of statistics its power since with a relatively small amount of data you are able to make significant assertions, even though such inferences are not 100% certain, but probabilistic in nature.

Website Overview

This website provides a tutorial on statistics plus access to tools that let you calculate various statistical tests from within Excel, including the following:

  • Tutorial on basic statistical tests and analyses
  • Free access to supplemental Excel formulas and data analysis tools
  • Step by step procedures for carrying out these tests and analyses using Excel
  • Numerous examples
  • Some of the theory behind these analyses

Why Excel?

There are a number of commonly used, powerful tools for carrying out statistical analyses. The most popular of these are SPSS, SAS and R. We have chosen instead to use Excel as our analysis tool, even though it offers far fewer built-in statistical tools. The reasons for choosing Excel are as follows:

  • Excel is widely available and so many more people know how to use it
  • It is not necessary to incur the cost of yet another tool
  • It is not necessary to learn new methods of manipulating data and drawing graphs
  • Excel already contains some basic statistics functions and data analysis tools
  • It is much easier to see what is going on since unlike the more popular statistical analysis tools very little is hidden from the user
  • It provides the user with a lot of control and flexibility

Real Statistics Software

This makes Excel an ideal tool for learning statistical concepts, although often it is easier to use the standard statistical tools for carrying out more advanced statistical analyses. To address Excel’s shortcomings, therefore, we have created the Real Statistics Resource Pack which contains various supplemental tools that enable you to carry out a wide range of advanced statistical analyses without leaving the Excel environment. The Resource Pack can be downloaded for free by clicking on Download.

While everything in this website can be done with standard Excel, the tools in the Resource Pack make it easier to perform statistical analyses. The website focuses on Excel 365, 2021, 2019, 2016, 2013, 2011, 2010 and 2007, although much of what is described will work fine even with previous versions of Excel.

No Prerequisites

No advanced mathematics is necessary to use this website. In particular, calculus is not required. For those readers who are familiar with calculus and are interested in the derivation of some of the statistical concepts described in the website, from time to time you will have the opportunity to click on a link to access more advanced information.

Knowledge of Excel is also not required. Excel Environment contains an introduction to Excel and Excel Capabilities provides more detailed information. There are also many books and online resources that provide a wealth of information about how to use Excel.

Ongoing Process

In the past few years, we have added many new statistical capabilities. More new features are being all the time, so stay tuned.

References

Schmuller, J. (2009) Statistical analysis with Excel for dummies. Wiley
https://www.wiley.com/en-us/Statistical+Analysis+with+Excel+For+Dummies%2C+3rd+Edition-p-9781118464311

Howell, D. C. (2010) Statistical methods for psychology (7th ed.). Wadsworth, Cengage Learning.
https://labs.la.utexas.edu/gilden/files/2016/05/Statistics-Text.pdf

22 thoughts on “Introduction to Real Statistics using Excel”

  1. Hi Charles,

    while trying to download the add in, I am getting 504 time out gateway error. please help.

    Regards,
    Anil sahu

    Reply

    • Hello Anil,
      Downloading the Real Statistics software is just like downloading any other file. It is a function of your Internet connection and browser and is not related to the Real Statistics software. I suggest that you try again at a different time. Once you successfully download the file, make sure that you install the software as described on the webpage.
      Charles

      Reply

  2. Hi Charles,
    Thank you so much for your wonderful website and pack. I followed your detailing steps and installed it successfully. I just can’t wait to explore this brilliant tool!!

    Reply

  3. Hi Charles

    I am a new visitor to your wonderful site.

    I was wondering whether you have any idea why during a Kappa test very high agreement among raters leads to very low kappa values.

    I conducted a study of facemask use among persons in a market. In my facemask use videos I developed categories of use -full, partial, and misused facemask , and no mask. The two raters have very high similarity in ratings but the Kappa is very low. I read that there is what is called Kappa paradox but that won’t help address the issue.

    Do you have any solution?

    I will formally acknowledge your help.

    Regards

    Reply

    • Amanuel,
      This can happen and is one of the drawbacks of the Kappa measurement. If you email me an Excel file with your data and results, I can check to make sure that kappa has been calculated properly.
      Other metrics have been developed to give a truer picture in these types of circumstances. One of these is Gwet’s AC2. See
      Gwet’s AC2
      Charles

      Reply

  4. Charles, this site is brilliant – thank you so much! Whenever a textbook or online explanation fails to help me understand, I look up your excel worksheets and I can quickly see what is going on. It must have taken you many hours to put this all together, so I just wanted to express my gratitude.

    Reply

  6. I tried to use Real Statistics with Excel for Mac 2011. I downloaded the RealStats-Mac-2011.xlam, copied it to the MS Office folder. Upon opening Excel the Real Statistics appears now the menu bar. However, if I want to use it an error message appears, like that:
    T-test:
    Compile error in hidden module: Address
    Anova:
    Compile error in hidden module: AnovaAnalysis
    None of the functions starts. So I do not know what to do. I would appreciate your help. Regards, Laszlo

    Reply

    • Laszlo,
      What do you see when you enter the formula =VER() in any cell?
      When you press the key sequence Alt-TI does Solver appear on the list of add-ins with a check mark next to it?
      Charles

      Reply

  7. Thank you! This makes it so much easier for me to train new students and help them become more productive and efficient with less of my time.

    Reply

  8. Hello Charles,
    I’m Excel illiterate and even worse at statistics but part of my job entails correlating between similar groups of numbers in a hospital lab setting. I’ve stumbled around enough to be able to figure out slope, intercept and R2. plus some graphs as our regulatory groups love seeing graphs. What I would love to learn though is what do I really need to prove correlation. I have one ongoing problem that I cannot for the life of me figure how to solve. we have to correlate between automated white blood cell differentials and manual differentials. There are 5 types of WBC’s and they need to be correlated separately but the problem is the instrument counts 10,000 or more cells while a normal manual differential is only 100 cells but I have the technologists count 200 cells because more is better right. I realize the more cells that are counted the better the correlation might be however its very time consuming.. I found something in my search called Rumke tables which helps but I don’t know how to put this in use in excel. The instrument reports the differential as percentages and a print out is available. I make/stain a slide of the patients blood and the techs to look through the microscope and count each type of cell. once complete I evaluate each techs differential in comparison to the instrument automated differential. I just found your site today and haven’t made it very far through the reading but I’ve impressed with it and I wanted to say thanks for this undertaking. I will have more questions in the near future I’m about 110% sure.

    Reply

    • Miles,
      I am not familiar with Rumke tables, although I believe that they are related to the binomial distribution in some way. I am not familiar with the concepts in your comment, but when I enter Rumke Table in google, I see that there are quite a few webpages on the topic that you are referencing.
      Sorry that I can’t give you any more help on this topic.
      Charles

      Reply

  10. Hello Doug, my name is Manuela, i am a radiologist doing a pediatric fellowship in Toronto and fall into your site due to pure desperation… !! As part of my training I am expected to produce decent original research and the statistician support is very limited. As a lot of doctors my training in this matters is poor… which has led me to lose a looot of time trying to understand! I have discovered Excel but this page is going to help me a lot, thanks for your time in putting it together!

    Reply

  11. Hi Charles,

    I would like say “Thanks” because Real stats is really good! I had used in windows in office 2015, but now I have a macbook + office 2016. I followed the steps that you told. The add-in is there, but i can’t find the icon in excel 2016 on macbook as I used had in excel 2015 in windows laptop. So … i can’t do the Statistical analysis.

    I’ll appreciate your suggestion.

    Thanks.

    Reply

Leave a Comment