Despite not being as powerful as software specifically for statistics, Excel is actually quite adept at running basic calculations, even without add-ins (though there are some add-ins that make it even better). You probably know that it can do arithmetic, but did you know that it can also quickly get percentage change, averages, standard deviation from samples and populations, standard error, and student’s T-tests?
Excel has a lot of statistical power if you know how to use it. We’ll take a look at some of the most basic statistical calculations below. Let’s get started!
How to Calculate Percentage in Excel
Calculating percentages in Excel is as simple as it is anywhere else: just divide two numbers and multiply by 100. Let’s say that we’re calculating the percentage of 347 out of 521.
Simply divide 347 by 521 using the syntax =347/521. (If you’re not familiar with Excel, the equals sign tells Excel that you want it to calculate something. Just enter the equation after that and hit Enter to run the calculation.)
You now have a decimal value (in this case, .67). To convert it into a percentage, hit Ctrl + Shift + 5 on your keyboard (this is a very useful Excel keyboard shortcut to add to your arsenal).
You can also change the cell format the long way by right-clicking the cell, selecting Format Cells, choosing Percentage, and clicking OK.
Keep in mind that changing the format of the cell takes care of the “multiply by 100” step. If you multiply by 100 and then change the format to percentage, you’ll get another multiplication (and the wrong number).
How to Calculate Percentage Increase in Excel
Calculating the percentage increase is similar. Let’s say our first measurement is 129, and our second is 246. What’s the percentage increase?
To start, you’ll need to find the raw increase, so subtract the initial value from the second value. In our case, we’ll use =246-129 to get a result of 117.
Now, take the resulting value (the raw change) and divide it by the original measurement. In our case, that’s =117/129. That gives us a decimal change of .906. You can also get all of this information in a single formula like this:
Use the same process as above to convert this to a percentage, and you’ll see that we have a 91 percent change. Do a quick check: 117 is almost equal to 129, so this makes sense. If we would have calculated a change value of 129, the percentage change would have been 100 percent.
How to Calculate Mean (Average) in Excel
One of Excel’s most useful built-in functions calculates the mean (average) of a set of numbers. If you haven’t used an Excel function before, you’ll be impressed at how easy it is. Just type in the name of the function, select the cells you want to apply it to, and hit Enter.
In our example here, we have a series of measurements that we need the average of. We’ll click into a new cell and type =AVERAGE(, then use the mouse to select the relevant cells (you can also type in the cell range if you’d like). Close the parentheses with a ) and you’ll have a formula that looks like this: =AVERAGE(B4:B16)
Hit Enter, and you’ll get the average! That’s all there is to it.
How to Calculate a Student’s T-Test in Excel
A Student’s t-test calculates the chances that two samples came from the same population. A lesson in statistics is beyond this article, but you can read up more on the different types of Student’s t-tests on these sites with free resources for learning statistics (Statistics Hell is my personal favorite).
In short, though, the P-value derived from a Student’s t-test will tell you whether there’s a significant difference between two sets of numbers.
Let’s say you have two measurements from the same group and you want to see if they’re different. Say you weighed a group of participants, had them go through personal training, and then weighed them again. This is called a paired t-test, and we’ll start with this.
Excel’s T.TEST function is what you need here. The syntax looks like this:
=T.TEST(array1, array2, tails, type)
array1 and array2 are the groups of numbers you want to compare. The tails argument should be set to “1” for a one-tailed test and “2” for a two-tailed test (if you’re not sure which you have, check out this FAQ from UCLA).
The type argument can be set to “1,” “2,” or “3.” We’ll set it to “1” for this example because that’s how we tell Excel we’re doing a paired t-test.
Here’s what the formula will look like for our example:
Now we just hit Enter to get our result! It’s important to remember that this result is the P value. In most fields, a P value of less than .05 indicates a significant result.
The basics of the test are the same for all three types. As mentioned, a “1” in the type field creates a paired t-test. A “2” runs a two-sample test with equal variance, and a “3” runs a two-sample test with unequal variance. (When using the latter, Excel runs a Welch’s t-test.)
How to Calculate Standard Deviation in Excel
Calculating standard deviation in Excel is just as easy as calculating the average. This time, you’ll use the STDEV.S or STDEV.P functions, though.
STDEV.S should be used when your data is a sample of a population. STDEV.P, on the other hand, works when you’re calculating the standard deviation for an entire population. Both of these functions ignore text and logical values (if you want to include those, you’ll need STDEVA or STDEVPA).
To determine the standard deviation for a set, just type =STDEV.S() or =STDEV.P() and insert the range of numbers into the parentheses. You can click-and-drag or type the range.
At the end, you’ll have a number: that’s your standard deviation.
How to Calculate Standard Error in Excel
Standard error is closely related to standard deviation. And while Excel doesn’t have a function that will calculate it, you can quickly find it with minimal effort.
To find the standard error, divide the standard deviation by the square root of n, the number of values in your dataset. You can get this information with a single formula:
If you’re using text or logical values in your array, you’ll need to use COUNTA instead.
Here’s how we’d calculate the standard error with our dataset:
Using Excel for Statistics: Not Great but Workable
Can you use Excel for statistics and complex calculations? Yes. Will it work as well as dedicated statistical software like SPSS or SAS? No. But you can still calculate percentages, averages, standard deviations, and even t-tests.
When you need a quick calculation, and your data is in Excel, you don’t need to import it into different software. And that’ll save you time. Don’t forget to put your data into aesthetically pleasing and informative graphs before you show it to your colleagues!
What else would you like to know about doing statistics in Excel? Share with us in the comments!