Statistics In Excel

Publication Date :

Blog Author :

Download FREE Statistics In Excel Template and Follow Along!
Statistics In Excel Template

Table Of Contents

arrow

Key Takeaways

  • Statistics in Excel refers to conducting statistics analysis using Microsoft Excel.
  • Excel provides all the fundamental and analytical statistical analysis with formulas under the category “Statistical” formulas.
  • Excel also enables easy and accessible advanced statistical analyses such as T-TEST, Z-TEST, and Descriptive Statistics. It can be beneficial for professionals who are from statistical backgrounds.

What Is Excel Statistics?

In the modern data-driven business world, we have sophisticated software dedicatedly to working towards "Statistical Analysis." Amidst all these modern, technologically advanced excel software is not a bad tool to do your statistical analysis of the data. Of course, we can do all statistical analysis using Excel, but you should be an advanced Excel user. This article will show you some basic to intermediate-level statistics calculations using Excel.

How To Use Excel Statistical Functions?

#1: Find Average Sales Per Month

The average rate or trend is what the decision-makers look at when they want to make crucial and quick decisions. So finding the average sales, cost, and profit per month is a common task everybody does.

For example, look at the below data of monthly sales value, cost value, and profit value columns in Excel.

excel statistical functions Example 1.0

So, by finding the average per month from the whole year, we can see what per month numbers are.

Using the AVERAGE function, we can find the average values from 12 months, which boils down to per month on an average.

  • Open the AVERAGE function in the B14 cell.
Example 1.1
  • Select the values from B2 to B13.
excel statistical functions Example 1.2
  • The average value for sales is:
Example 1.3
  • Copy and paste cell B14 to the other two cells to get the average cost and profit. The average value for the cost is:
excel statistical functions Example 1.4
  • The average value for the profit is:
Example 1.5

So, on average, per month, the sale value is $25,563, the cost value is $24,550, and the profit value is $1,013.

#2: Find Cumulative Total

Finding the cumulative total is another set of calculations in excel statistics. Cumulative is nothing but adding all the previous month's numbers together to find the current total for the period.

The steps to find the cumulative total are as follows:

  1. First, look at the below 6 months sales numbers.


    Statistics in Excel Example 2.0

  2. Open the SUM function in the C2 cell.


    Statistics in Excel Example 2.1

  3. Select the cell B2 cell and make the range reference.


    Statistics in Excel Example 2.2

    From the range of cells, make the first part of the cell reference B2 an absolute reference by pressing the F4 key.

  4. Close the bracket and press the "Enter" key.


    Statistics in Excel Example 2.3

  5. Drag and drop the formula below one cell.


    Statistics in Excel Example 2.4

  6. Now, we have the first two months' cumulative total. At the end of the first two months, revenue was $53,835. Drag and drop the formula to other remaining cells.


    Statistics in Excel Example 2.5
    From this cumulative, we can find in which month there was a less revenue increase.

#3: Find Percentage Share

Out of twelve months, you may have got $1,000,000 in revenue. But, still, maybe in one month, you must have achieved the majority of the revenue, and finding the month's percentage share helps us find the particular month's percentage share.

For example, look at the below data of the monthly revenue.

excel statistical functions Example 3

To find the percentage share first, we need to see what the overall 12 months total is, so by applying the SUM function in Excel, find the overall sales value.

 Example 3.1

We can use the formula to find the percentage share of each month.

% Share = Current Month Revenue / Overall Revenue

To apply the formula as B2 / B14.

excel statistical functions Example 3.2

The percentage share for Jan month is:

 Example 3.3

Note: Make the overall sales total cell (B14 cell) an absolute reference because this cell will be a common divisor value across 12 months.

Copy and paste the C2 cell to the below cells as well.

Statistics in Excel Example 3.5

Apply the "Percentage" format to convert the value to percentage values.

 Example 3.7

So, from the above percentage share, we can identify that the "Jun" month has the highest contribution to overall sales value, i.e., 11.33%, and the "May" month has the lowest contribution to overall sales value, i.e., 5.35%.

Statistics in Excel Example 3.8.0

#4: ANOVA Test

Analysis of Variance (ANOVA) is the statistical tool in excel used to find the best available alternative from the lot. For example, if you are introducing four new kinds of food to the market. You gave a sample of each food to get the public's opinion and from the opinion score given by the public by running the ANOVA test. We can choose the best from the lot.

ANOVA is a data analysis tool available in Excel under the "DATA" tab. By default, it is not available. You need to enable it.

Statistics in Excel Example 4.0

Below are the scores of three students from 6 different subjects.

Example 4.1

Click on the "Data Analysis" option under the "Data" tab. It will open up below the "Data Analysis" tab.

excel statistical functions Example 4.2

Scroll up and choose “Anova: Single Factor.”

 Example 4.3

Choose "Input Range" as B1 to D7 and tick “Labels in first row.”

Statistics in Excel Example 4.4.0

Select the "Output Range" as any of the cells in the same worksheet.

 Example 4.5.0

We will have an "ANOVA" analysis ready.

excel statistical functions Example 4.6.0

Important Things To Note

  • All the basic and intermediate statistical analyses are possible in Excel.
  • We have formulas under the category of "Statistical" formulas.
  • If you are from a statistics background, it is easy to do fancy and important statistical analyses in Excel like T-TEST, Z-TEST, descriptive statistics, etc.

Frequently Asked Questions

Frequently Asked Questions (FAQs)

1

How to get regression statistics in Excel?

Arrow down filled
2

How do you track statistics in Excel?

Arrow down filled
3

How to graph statistics in Excel?

Arrow down filled