Excel Statistics: Step by Step

If you’re using Microsoft Excel in your stat class, you may be surprised to learn that Excel is actually a very poor choice. Excel is a great spreadsheet package, but it was never intended to be used for statistical analysis. The Data Analysis package is an optional add-in. That should give you a clue that data analysis isn’t Excel’s strong point. You can easily make simple graphs and calculations. But you’ll run into some serious issues with more complex tasks: Including missing data.

Specific Issues with Excel for Statistics

According to the University of Massachusetts, Excel has several issues with statistical calculations. These include:

• Excel can only perform certain tests on one column at a time. In the real world, it’s unlikely that you’ll have a single column of variables; You’re much more likely to have a mix over several columns. This can cause issues with running t-tests and even simple frequency counts

• Excel doesn’t log what you are doing. So if you forget what you’ve done, or simply want to repeat a task, that may not be possible without starting from scratch

• If you want to perform a series of statistical tests on your data, you’re likely going to be doing a lot of copying, pasting, sorting, and rearranging. That’s because each test in Excel requires the data to be arranged in a certain way. For example, the One-Way ANOVA requires that data be arranged in separate and adjacent columns or rows for each group. The two-factor ANOVA with replicates requires a rectangular region with columns representing one factor and rows representing the other. With a statistical package such as SPSS, you won’t have to rearrange anything.

These are just a few of the pitfalls with Excel for statistics. Of course, statistical packages like SPSS are likely out of your budget; They cost thousands of dollars which is why the casual user chooses Excel. However, if your school or work offers a statistical package, you may want to learn it. In the long run your computations will be easier, less time-consuming and more accurate.

A note on the Content:

Excel has undergone many revisions from 2007-2013. Many of the original functions in Excel still work with 2013 – especially those that are included in the Data Analysis Toolpak. This book is segmented into two sections -- Excel 2013 and Excel 2007-2010. If the steps are significantly different, you’ll find a separate article. However, many functions work in ALL editions, so check the index (at the end of the book) if you’re looking for a specific topic.

The best place to start is by loading the Data Analysis Toolpak, as without it you won’t be able to perform anything more than calculating basic descriptive statistics and making charts.

How to Load the Data Analysis Toolpak in Excel 2013

The Data Analysis Toolpak is included with every copy of Excel. It gives you access to a wide variety of statistical functions including histograms, correlation, a range of z-test and t-test functions and a random number generator. Once you load the Data Analysis Toolpak you can access it by clicking Data Analysis in the Data tab.

Step 1: Click the File tab and then click Options. The Options window will open.

Step 2: Click Add-Ins. Add-Ins is listed at the bottom of the left-hand column,

Step 3: Click the Go button, next to Manage Excel Add-Ins. Manage Excel Add-Ins is at the very bottom of the pop up window, underneath the list of Add-In options.

Step 4: Check the Data Analysis Toolpak check box.

Step 5: Click OK. The Data Analysis Toolpak is now loaded into Excel.

Step 6: Click the Data tab and then click Data Analysis – located at the far right of the Toolbar in the Analysis group — to access the Toolpak.

How to Load the Microsoft Excel Data Analysis ToolPak: Excel 2007-2010

The Data Analysis ToolPak is added from the Office menu.

Step 1: Click the Office button at the top left (with the Microsoft logo) and then click Excel Options.

Step 2: Click Add-Ins, then select Excel Add-Ins in the Manage box.

Step 3: Click Go, then click the Analysis ToolPak check box.

Step 4: Click OK. The Data Analysis ToolPak is now ready for use in the Data tab.

Note: Excel has been known to be buggy with stats calculations, especially things like f-tests. These bugs are thought to be fixed with Excel 2013. So if you’re going to use Excel for stats, make sure you know the limitations of the ToolPak.

Excel 2013

Descriptive Statistics

Range in Excel 2013

A range, along with the mean, mode and median, is a measure of spread in statistics. The range is calculated by subtracting the largest point in your data set from the smallest point in your data set. For example, in the set 1,2,3,4,5,6,7,8,9,10, the range is: 10-1=9. Of course, real life data sets aren’t as small as this one, and they aren’t usually in numerical order, which can mean that finding the range poses serious challenges unless you use technology like Excel 2013. To find a range in Excel 2013, you have two options: you can use the MAX and MIN functions to find the largest and smallest numbers in a data set and then you can subtract the two. For example, if you had a data set in cells A1 to A10, you’d need three formulas in three blank cells. The format (assuming you put these formulas into cells B1:B3) would be:

B1 = MAX(A1:A10)

B2 = MIN(A1:A10)

B3 =(B1-B2)

A much easier way is to use Data Analysis. In a couple of clicks (with no entering formulas) you can display a variety of summary statistics, including the