How to Calculate Basic Statistics in Excel: A Beginner’s Guide
Pinterest Whatsapp
Advertisement

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 Power Up Excel with 10 Add-Ins to Process, Analyze & Visualize Data Like a Pro Power Up Excel with 10 Add-Ins to Process, Analyze & Visualize Data Like a Pro Vanilla Excel is amazing, but you can make it even more powerful with add-ins. Whatever data you need to process, chances are someone created an Excel app for it. Here's a selection. Read More ).

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.)

how to calculate basic statistics in excel

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 60 Essential Microsoft Office Keyboard Shortcuts for Word, Excel, and PowerPoint 60 Essential Microsoft Office Keyboard Shortcuts for Word, Excel, and PowerPoint Hundreds of keyboard shortcuts are available in Office, but these are the most important ones you should know because they will save you a lot of time. Read More 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.

how to calculate basic statistics in excel

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).

Tip: Learn how to create dropdown lists for Excel cells How to Create a Dropdown List in Excel How to Create a Dropdown List in Excel Excel supports versatile dropdown list options. Use them to control user input. We'll show you how to add dropdown lists to your Excel spreadsheets. Read More .

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:

how to calculate basic statistics in excel

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 16 Excel Formulas that Will Help You Solve Real Life Problems 16 Excel Formulas that Will Help You Solve Real Life Problems The right tool is half the work. Excel can solve calculations and process data faster than you can find your calculator. We show you key Excel formulas and demonstrate how to use them. Read More 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)

how to calculate basic statistics in excel

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 with these free resources for learning statistics Learn Statistics for Free with These 6 Resources Learn Statistics for Free with These 6 Resources Statistics has a reputation of a subject that's difficult to understand. But learning from the right resource will help you understand survey results, election reports, and your stats class assignments in no time. Read More (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.

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:

how to calculate basic statistics in excel

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.

how to calculate basic statistics in excel

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.

how to calculate basic statistics in excel

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:

=STDEV.S(array1)/SQRT(COUNT(array1))

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:

How to Calculate Basic Statistics in Excel: A Beginner's Guide standard error excel correct

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. You can also use Excel’s Goal Seek feature to solve equations even faster How to Use Excel's Goal Seek and Solver to Solve for Unknown Variables How to Use Excel's Goal Seek and Solver to Solve for Unknown Variables Excel can solve for unknown variables, either for a single cell with Goal Seeker or a more complicated equation with Solver. We'll show you how it works. Read More .

Don’t forget to put your data into aesthetically pleasing and informative graphs How to Create Powerful Graphs & Charts in Microsoft Excel How to Create Powerful Graphs & Charts in Microsoft Excel A good graph can make the difference between getting your point across or leaving everyone dozing off. We show you how to create powerful graphs in Microsoft Excel that will engage and inform your audience. Read More before you show it to your colleagues! And it would also help to master IF statements in Excel How to Use IF Statements in Microsoft Excel How to Use IF Statements in Microsoft Excel Whether you're a seasoned expert or a spreadsheet beginner, you'll want to check out this guide to IF statements in Excel. Read More .

 

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Bill Voss
    December 27, 2017 at 12:32 am

    Image in How to Calculate the Percentage Increase in Excel does not match the text. Equation in image should be =(C4-B4)/B4.

  2. Jon O. Skarphedinsson
    December 23, 2017 at 1:30 am

    To calculate standard error you divide standard deviation by sqrt(count(B6:B16))

    • Dann Albright
      December 26, 2017 at 4:24 pm

      Thanks for pointing that out! Fixing now.

  3. Chomeeaung
    December 15, 2017 at 11:12 pm

    Thanks
    I want to know cumulative frequency for size at first maturity of animal

  4. riz
    December 15, 2017 at 7:34 pm

    Don't forget linear regression!
    There are some nice built in commands like INTERCEPT and SLOPE you can use to calculate a linear regression.