What is Real Statistics Using Excel?
Real Statistics Using Excel is a practical guide for how to do statistical analysis in Excel plus free statistics software. This software package extends Excel’s built-in statistical capabilities. In particular, it will enable you to more easily perform a wide variety of statistical analyses in Excel.
What does Real Statistics Using Excel consist of?
Real Statistics Using Excel is comprised of the following four components:
Real Statistics Resource Pack
An Excel add-in that extends Excel’s standard statistics capabilities. It provides you with advanced worksheet functions and data analysis tools. This will enable you to more easily perform a wide variety of practical statistical analyses. The software supports Excel 2007, 2010, 2013, 2016, 2019, 2021, and 365 for Windows. It also supports Excel 2011, 2016, 2019, 2021, and 365 for the Mac. The current version is Rel 9.2.2 (released 21 October 2024). There is also limited support for Excel 2002 and 2003.
Real Statistics Website (i.e. this site)
- Lets you download a free copy of the Real Statistics Resource Pack
- Provides tutorials and descriptions of how to perform a variety of statistical analyses using built-in Excel capabilities. You will also learn how to use the supplemental capabilities provided by the Real Statistics Resource Pack.
- Presents numerous examples in the form of Excel worksheets which you can download to your computer
For the student and the novice, the Real Statistics website is an excellent tutorial for learning the basic concepts of statistics and how to do statistical analysis. For all users, it provides a step-by-step guide for how to do statistical analysis in the Excel environment and the tools necessary to carry out these analyses.
You can use this website to learn how to perform statistical analyses in Excel even without using the Real Statistics Resource Pack. But we recommend that you download the resource pack so that you can have access to its powerful capabilities.
Real Statistics Examples Workbooks
Consists of fourteen Excel files that contain all the examples shown on the website. These example files can be downloaded for free (click here). Each example focuses on a specific statistical concept and has been designed to demonstrate simple concepts before moving on to more complicated topics.
Over time, the webpages on the Real Statistics website are being updated so that you will be able to download an Excel file with any of the examples found on that particular webpage.
Real Statistics Community
Each webpage has a Comments section where you can make suggestions, identify errors, ask questions to others in the community, or request their advice.
You can also get updates about new releases and other information via the @Real1Statistics twitter feed.
How do I get started?
Step 1: If you elect to use the Real Statistics Resource Pack, click on the following icon. You will be given the opportunity to download and install for free the Real Statistics Resource Pack.
Once you have downloaded and installed the Real Statistics Resource Pack, you will be able to use the supplemental capabilities with your copy of Excel. This is described throughout the rest of the website and is summarized in Using Real Statistics Functions and Real Statistics Data Analysis Tools.
If you choose not to download the resource pack or examples now, you can do so later at any time.
Step 2: Browse through the website to learn how to perform a wide range of statistical analyses in Excel using standard built-in as well as supplemental Real Statistics capabilities. We suggest that you begin by clicking on the Website Introduction (and especially Organization of the Website). These will explain how to navigate the website to get the information you need to run any specific statistical test or learn about any particular topic.
Why do statistical analysis in Excel?
The reasons for choosing Excel are as follows:
- It is widely available and many people already know how to use it
- It is not necessary to incur the cost of yet another tool
- There is no need to learn new methods of manipulating data and drawing graphs
- It 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
- Excel provides the user with a lot of control and flexibility
This makes Excel an ideal tool for learning statistical concepts and performing some basic statistical analyses. Unfortunately, its built-in statistics capabilities are limited. As a result, many prefer to use statistical tools such as R, SPSS, or SAS for carrying out more advanced statistical analyses.
We created the Real Statistics Resource Pack to address these shortcomings. This software package contains various supplemental tools that enable you to carry out a wide range of advanced statistical analyses without leaving the Excel environment. You can download the Real Statistics Resource Pack free of charge from this website.
Hello Charles –
Your free statistics excel add in, tutorials, and examples is beyond helpful and I am so grateful to you for making statistics more understandable to someone that does not have that background. I have a question that is tying my brain in knots. I have a dataset with rolling annual medians calculated on a monthly basis for a period of approximately 10 years. I am tasked with finding an upper confidence limit to set a discharge concentration that can be met most of the time. My inclination is to treat the data set as if they are discreet data points and analyze based on a mean value. However, because the data points are rolling annual medians, I am wondering if I should calculate use a “median” method for calculating the confidence limits. Any insight or recommendations would be greatly appreciated. Thank you!
Hello Caroline,
I am very pleased that the Real Statistics capabilities have been useful to you.
What sort of confidence intervals do you have in mind? Are these confidence intervals for each forecasted value?
Charles
Hello Charles – Yes, I would like to understand the confidence intervals for each forecasted value. Thank you!
Caroline,
How to calculate this sort of confidence interval depends on the specific time series predictive tool that you use. I address this issue in many places on the website. See the following:
https://real-statistics.com/time-series-analysis/basic-time-series-forecasting/simple-exponential-smoothing/exponential-smoothing-confidence-interval/
https://real-statistics.com/time-series-analysis/basic-time-series-forecasting/holt-linear-trend/holts-linear-trend-confidence-interval/
https://real-statistics.com/time-series-analysis/basic-time-series-forecasting/holt-winters-additive/holt-winters-additive-confidence-interval/
https://real-statistics.com/time-series-analysis/arma-processes/forecasting-arma/
Charles
Thank you. I will look at these.
To find the median 95th percent confidence limit that would be used as a discharge concentration limit, I ended up isolating the maximum value in the rolling medians and bootstrapped the underlying data using 1000 sets of resampled values. After calculating the median of each set, I applied the method used in your bootstrapping for order statistics file to find the 95th percent confidence intervals.
To find the 95th percentile discharge limit, I used the underlying data for the maximum rolling 95th percentile value in the data set and bootstrapped the underlying data to return 1000 sample sets. I then calculated the 95th percentile for each resampled set. The 95th percentiles were then used to calculate the 97.5th percentile which would, I think, be equivalent to the upper 95th percentile confidence limit.
I’m hoping these methods are legitimate…. Thank you!
Hello Caroline,
These are legitimate approaches, but I can’t say for sure whether these are the right approaches for your problem. This is not to say that they are wrong, only that I haven’t tried to verify them.
Charles
Hello, can you make a Chinese version? English is not good.
My Chinese is non-existent.
Charles
Hello, can this plugin perform Cochran’s Q test? I couldn’t find this option in non parametric testing.
It is an option on Real Statistics’ Other Nonparametric data analysis tool (on the Misc tab).
Charles
Dear Professor Charles, could you please provide more specific steps for the Cochran’s Q Test? A person who has never studied mathematics and statistics finds it very difficult to learn. If there were specific steps for operation, it would be much easier to understand. I look forward to your reply.
Are you looking for more information about how to use the Real Statistics data analysis tool to perform Cochran’s Q Test or do you want to understand how to perform the test without the software tool? In any case, see
https://real-statistics.com/anova-repeated-measures/cochrans-q-test/real-statistics-cochrans-q-test/
Charles
Oh no, how do I perform multiple comparisons while conducting Cochran’s Q Test?
See https://real-statistics.com/anova-repeated-measures/cochrans-q-test/
Charles
Real Statistics Using Excel is a game-changer for data analysis! This powerful tool helps users unlock insights and make informed decisions. What’s your favorite feature or application of Real Statistics
Is it possible to add solving functions for ordinary differential equations and partial differential equations
I have thought about this previously, and I will add these to my list of future enhancements.
Charles
Buenas noches Dr. Zaiontz.
Estoy muy interesado en descargar e instalar en mi equipo los archivos del Real Statistics Resource Pack, pero el link de descarga no me permite hacerlo. Como debo proceder para acceder a la descarga? Les agradezco si me pueden facilitar este proceso.
Feliz noche.
Hello Jairo,
You can click on the link on the following webpage:
https://real-statistics.com/free-download/real-statistics-resource-pack/
Charles
I’m studying for Lean Six Sigma as a job requirement. As you undoubtedly know, it’s heavy on statistics. I’m looking for an add-in that can provide me with many of the charts and tools Six Sigma requires (such as run charts, control charts, data stability calculations, and so forth) and also teach me statistics in more depth than I’m getting from my Six Sigma courses, which are, so far, mostly rote and repetitive application of statistical processes and formulas without a strong grasp of statistical theory. Can RealStatistics meet my needs as described?
Hello,
Real Statistics has a wealth of information about statistics but I can-t claim that it provides all the capabilities that you describe.
Charles
Okay, thanks. After looking over, I should be able to learn the statistical theory. You offer a lot of information. I’ll work on other ways to create run and control charts in Excel.
If you come up with some charts that might be useful to others, I would consider adding them.
Charles
Legend
Dr Zainonitz good afternoon, I hope you are well, Dr, have you thought about relating Real Statisticistics with Copilot?
Thank you so much
Dr Zainonit buenas tardes, espero se encuentre muy bien, Dr, ha pensado en relacionar Real Stattististics con Copilot?
Muchas gracias
Hello Gerardo,
I haven’t looked into this possibility.
Clearly you can already use Copilot on the output from Real Statistics. I don’t know how to integrate Copilot with the input to Real Statistics.
Charles
Ok Sr Thank you very much.
Thank you very much to the authors for building a very good website for users about data analysis.
Hello Dinh,
Glad I could help.
Charles
Hi Charles
Thank you wholeheartedly for putting so much time, effort and hard work in providing all of us with an amazing website and a free stats software.
You are doing an awesome job and I am grateful of you.
Thank you again
Sincerely
Mukesh
Hi Mukesh,
Thank you very much. I am trying to do my part.
Charles
Dear Charles,
In your Polynomial Regression Excel-package, is it possible to force a quadratic regression to lie at a certain specified fixed point on the x-axis (where y=0)? For instance, in regular Excel it is possible to set the y-intercept (where x=0) to a value of one’s own choice.
Hello Terence,
No, I haven’t implemented this feature in the analysis tool.
Charles
Dear Charles
I am using Excel 2021 and I had finished all the adjustment in “Trust center”.When I key in a formula like”=QCRIT”, there’s formula bar appearing to remind other ones like”QDIST”,”QINV”,etc.(Is this meaning I have successfully installed it?)But when I press “enter”,”#NAME?” appears. Could you please advise what is going wrong here? Thanks!(and apologize for my poor English)
What do you see when you enter the formula =VER() in any cell?
What do you see when you press the key sequence Ctrl-m ?
Charles
Charles,
I am not able to get past Step 1:…
Step 1: If you elect to use the Real Statistics Resource Pack, click on the following icon. You will be given the opportunity to download and install for free the Real Statistics Resource Pack. Also, click on the icon if you would like a copy of the examples used throughout the website.
When I try to download nothing happens. I tried downloading other software and was successful with no problem. I contact IT at my college for assistance and they were unsuccessful also. If you could help me with this download I would greatly appreciate it.
Hi Roger,
I am not sure why you had this problem. I was able to perform Step 1 without any problems. In any case, please go to the following webpage:
https://real-statistics.com/free-download/real-statistics-resource-pack/
Charles
Dear Charles
I am using Excel 2019. I am getting an error message “This File Type is not supported in the protected view. Request your help. Thanks Tushar
Hell Tushar,
What Real Statistics capability (specific data analysis tool or worksheet function) are you using when you received this message?
If you email me an Excel spreadsheet with your data and explain what you were doing when you got this message, I will try to figure out why you are getting this message.
Charles
The problem is that Microsoft protects you from using addd-ins from the web. You have to navigate through the “trust Center” to find the Real Statistics file and thll excel that you trust it. Then the spreadsheet will not be opened in the “protected mode”
Gerhard
Thanks Gerhard for your comment.
This is useful information.
Charles
Hi,
About to buy a new computer – are there any issues with windows 11 (either home or pro – I am leaning toward pro) and excel (365 or the regular office suite)?
Thanks – I love the software!!
Hi Mark,
I am using windows 11 home without any problems. I like office 365 because its version of excel supports dynamic arrays.
Charles
Dear Doc Charles, Thanks, thanks and thanks, by the new Version.
Hello Charles,
Initially, I was able to open Real Statistics through ctrl+m in my Excel. Later, I downloaded Microsoft 365 to get box plots which were not available on my previous Excel. Since then I have not been able to open Real Statistics. It shows it in the add-ins but does not show up on ctrl+m. Can you please advise what’s going wrong here? Thanks!
Hello Sonia,
What do you see when you insert the fomula =VER() in any cell?
Charles
Hello, Charles
A quick question that I have asked before: how does one determine the version currently installed on a particular computer? Thanks.
Regards,
Chris Victoria
Hello Chris,
You can use the formula =VER() to find out what version of Real Statistics you have installed.
You can use the formula =ExcelVER() to find out what version of Excel you have installed.
Charles
Thanks, Charles!
Thanks professor Ziaontz for sharing your material.
Hi Charles,
Thanks for your package, it is awesome!
I have tried the functions LAMB and EVALS, and I have found that the EVALS function returns a VALUE! error when the number is formatted with a comma as a decimal separator. I tried with two different Office versions (2013 and 2019) and both of them give the same problem. The function runs well when fed with integers, but with decimals it only gives the correct result when the number is introduced as a text string, with a point as a decimal separator or when changing the format of decimal separator to a point.
Is this a bug or it’s supposed to work this way?
Hello Marc,
Let’s suppose that you place the formula =A1+1 in cell A2 and the formula =EVALS(A2;7,5) in cell C1. I’ll also assume that you are using a version of Excel where the decimal value 7.5 (in the English-language version) is expressed as 7,5
Are you saying that cell C1 contains an VALUE! error instead of the value 8,5 ?
In my version of Excel, the formula =EVALS(A2,7.5) does return the value 8.5. I would expect that you would get the value 8,5 (as long as you used a semi-colon as the separator between A2 and 7,5)
Charles
That’s exactly what happens, Charles.
The cell C1 gives a VALUE error when fed with a decimal comma separated value. The only way it gives result is changing 7,5 to 7.5 or writing the EVALS function this way: =EVALS(A2,”7.5″)
Marc
Marc,
If a comma is used as a decimal symbol, then the formula should be =EVALS(A2;”7.5″) and not =EVALS(A2,”7.5″). What am I missing?
Charles
My mistake Charles,
You’re right, =EVALS(A2;”7.5″) gives the correct result, while =EVALS(A2;7,5) gives VALUE error. (Using Excel with the decimal value as a comma).
Marc
Marc,
I will check this on my Italian-language version of Excel. Looks like I need to revise the LAMB and EVALS functions to handle this situation.
Charles
Hello,
I have researched this problem further.
1. Yes, I see the same problem when I use my Italian version of Excel.
2. I am using a specific feature of VBA that does not recognize a comma as a decimal symbol. I consider this to be a bug in Excel VBA.
3. You mentioned that everything works fine when you write the number as text (i.e. use “3,5” instead of 3,5). Do you see any reason not to use this approach as the solution to this issue? Can you find any situation where this doesn’t work?
Charles
It’s OK Charles,
I just posted this issue for you to know. I think I can manage to work with EVALS using text strings instead of numbers without a problem.
Thanks again for your amazing work!
Marc,
Thanks. I am going to check whether there are any problems with this approach. If not, I have a solution, but I would prefer to not to use it since it might make things more complicated.
Charles
Hello – I drop into the site (https://real-statistics.com/) occasionally to see if there’s new features or bug fixes that I might need or could use.
However, I always have a difficult time actually finding the download URL. There’s many “Free Download” boxes everywhere that take me to different pages, but the only link I’ve found is buried in a paragraph “… click here on Real Statistics Resource Pack for Excel 2010/2013/2016/2019/2021/365” on https://real-statistics.com/free-download/real-statistics-resource-pack/
I know that “Free Downloads” from the top of page banner, and then “Resource Pack” will get me to the above page, but IMHO it’s not intuitively obvious.
Can I suggest that you consider a site change to use an extremely bare downloads page and include version and update date for all your products?
Thanks
Thanks for your suggestion, Paul.
I will try to simplify the download page in a few days.
Charles
Charles,
I am looking for a table of KS2 critical values corresponding to alpha >0.20, such as 0.25, 0.30… up to 0.50. Most of the tabular data stop at alpha = 0.20. I am unable to download the functions in Real Statistics that compute these, and was hoping you might be able to advise.
Most appreciated
Hello Stephen,
If I understand correctly, Real Statistics’ KSINV worksheet function would address this issue. What sort of problem are you having in downloading Real Statistics?
Charles
Hola, Estimado Señor
He bajado los archivos de practica, pero encuentro que las funciones credas por Ud. No están activadas, es decir, al abrir cualquier libro de Excel donde hay una función del complemento se describe el Error ¿#Nombre?.
He buscado las funciones en el ambiente VBA y no las he podido encontrar.
Me ayuda por favor y muchas gracias.
You need to make sure that you have installed the Real Statistics software; otherwise, you will get NAME error values.
What do you see when you enter the formula =VER() in any cell?
Charles
Hi Charles,
I have downloaded and installed the Real Statistics Resource Pack into my Macbook air, but I could not find the extended statistic tools. Please advice. Thank you for your kind attention.
TeeNa
Hi TeeNa,
What do you see when you press the key sequence Ctrl-m ?
What do you see when you enter the formula =VER() in any cell?
Charles
Hi Charles,
I am using Macbook.
So I think your “Ctrl-m” meant to open a new excel page. I used “command-n” instead.
When I enter the formula =VER, (before typing ()) I saw a long list of functions (e.g., AVERAGE…) and user-defined functions (e.g., SolverAdd, SolverChange….).
However, when i click the “Data Analysis”, there are still only the original choices from excel, and no Wilcoxon Signed-rank test tool.
TeeNa
Hello TeeNa,
Ctrl-m should open the Real Statistics menu. You can’t use command-n.
You need to enter =VER() with the parentheses. If you get an error message, then this means that Real Statistics hasn’t been installed.
Charles
Hello! Thank you for your wonderful contribution. I have a question. What is the data limit that RealStatistics currently allows?
Hello Andres,
Do you mean the number of rows of data? This varies from test to test. For most tests, it is a huge number (say one million +). For some tests, it is limited by array size, namely about 65,000 rows.
Charles
Hello, is there a possibility that you will increase the kalman filter?
Hi Christian,
Are you asking me to add the kalman filter to the website and software?
Charles
Hi Charles,
yes, as a forecasting tool.
Cristian
Hello Cristian,
I will look into Kalman’s filter.
Charles
Thank You!
Are Bradley-Terry model included?
Hello Ernesto,
Real Statistics doesn’t currently support the Bradley-Terry model. It does provide support for Rasch model, which is similar, and for logistics regression.
I will add support for the Bradley-Terry model in the next release of the Real Statistics software. This should be available this month.
Charles
Hello Ernesto,
See https://www.real-statistics.com/multiple-regression/bradley-terry-model/
Charles
So great, Thanks sir!!!
Por favor me podrian proporcionar la direccion para descargar un paquete estadistico…
You can download the Real Statistics Pack at
https://www.real-statistics.com/free-download/real-statistics-resource-pack/
Charles
Los paquetes estadisticos son herramientas de mucha importancia y utilidad para aplicar en las invastigaciones
How do I uninstall it?
Hello Sagrario,
The uninstall instructions can be found at the bottom of the webpage
https://www.real-statistics.com/free-download/real-statistics-resource-pack/
i.e. the webpage from where you downloaded the software.
Charles
ewfwagreag
Respected sir charles
Please calculate degree of freedom for me
Factor A = Nitrogen having 4 levels x phosphorus having 3 levels
Replicated 3 times
Design RCB split plot
Factor A for main plot
It’s one factor
Sorry, Siddique, but I don’t understand your design.
Charles
Charles: I have been using your XRealStats add-in to calculate eigenvalues and eigenvectors. Thank you so very much for this. My computer has installed a Windows update. As a result, I can no longer use your add-in. The machine tells me that Microsoft has blocked the macros of the add-in because it does not recognize the certificate of the add-in (or words to this effect). I am using Excel 365. Any ideas for a work around?
Nicos,
I believe that all you need to do is change the trust setting for the XReakstats.xlam file. This is described in the Troubleshooting section of
https://www.real-statistics.com/free-download/real-statistics-resource-pack/
Charles
Charles, when I add the trust certificate, I cannot see the installation package when browsing my computer, but I have downloaded it and can see it through direct access. But I just can’t see it when I add trusted certificates and add ins. What is the solution?
Hello Estella,
You don’t need a trust certificate to use the Real Statistics software. You only need to adjust the trust setting as described in the Troubleshooting section of
https://www.real-statistics.com/free-download/real-statistics-resource-pack/
Charles
Hola, prueba utilizando excel 2016. Saludos.
Thanks, rethanks to Charles’s beautiful job
In Excel, now we have also access to the new LET and LAMBDA functions* … Imagine all the Charles’s tools coming rewritten with these functions, for young people, easier to read, faster …
*(only in Office insider until now)
Hello Mallard,
Yes, you could rewrite some of the capabilities using LET and LAMBDA, but not all.
I have also added a LAMBDA-like capability in Real Statistics that is available to all Excel users.
Charles
Dear Prof. Charles,
I am using this wonderful add-id with my university students. In this year many of them received an error when running any function of Real-Stat:
“Compile error in hidden module: frmHist”.
This is apparently due to the incompatibility between their Office version (64-bit) and the add-id (developped for 32-bit Office), as suggest by Office help. The Block has already been removed for all of them and no error occurs when installing the add-id, but just when running whatever function.
Is there a solution for that?
Many thanks again!!!
simone
Hello Simone,
Real Statistics should work for 64-bit Office. In any case, if you email me an Excel file with your data and results, I will try to figure out why you are getting a compile error message.
Charles
Hi Charles,
Thank you for your awesome tool. I used it some years ago to perform PCA, so I naturally had your tool in mind when looking for an easy way to unsmooth a series of quarterly returns from several alternative asset classes.
Unfortunately for me, it doesn’t seem that unsmoothing is one of the options in your tool. Is that correct, or am I just not able to find it?
Thanks again,
Jesper
Hello Jesper,
Real Statistics doesn’t address this subject.
I see on the Internet the following papers on the subject of unsmoothing asset classes
https://eres.org/eres2010/contents/papers/id235.pdf
https://www.cliffwater.com/Research/DownloadFile?path=docs%2FForecasting%20Risk%20for%20Illiquid%20Asset%20Classes.pdf&title=Forecasting%20Risk%20for%20Illiquid%20Asset%20Classes&title=Forecasting+Risk+for+Illiquid+Asset+Classes
I don’t know whether either of these papers is useful.
Charles
Is it possible to reach you through inbox for more explanation. I have a sample size of 29 and am worried on the possible tests ill need to run
Hello Shadrack,
You can send me an email. See Contact Us.
Charles
Thank you Charles.
Hi Charles,
i express my salute to you … you have done great in simply way . thank you so much for this website
is it possible to run real-statistics tools for multiple sku?
e.g time line is monthly basis from B2-P1
actual for SKU A is from B2-P2 need to run statistical tools to get result on Q2-V2 (6month)
actual for SKU B is from B3-P3 need to run statistical tools to get result on Q3-V3 (6month)
sorry time line should be from B1-P1..
Hello Didien,
Glad that you like the Real Statistics website.
What is a SKU? Stock Keeping Unit?
I don’t understand what B1-P1, etc. represent.
Charles
i am unable to dowload the pack, every time when Click the download button a new tab oens instead of dowloading the pack, could you please help me to solve this issue
Hi Paul,
If you are using Windows, then I suggest that you go to the Download section of the following webpage to download the pack
https://www.real-statistics.com/free-download/real-statistics-resource-pack/
Charles
It is a very good site, if you can insert conversion tab of different types of languages.
By languages, do you mean Spanish, Russian, etc.?
Charles
I would like to cite your work on this software package. What is the best citation for this work?
Hello Alix,
See https://www.real-statistics.com/appendix/citation-real-statistics-software-website/
Charles