EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel Statistics in Excel
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

Statistics in Excel

By Madhuri ThakurMadhuri Thakur

Statistics in Excel

Excel Statistics (Table of Contents)

  • Introduction to Statistics in Excel
  • Examples of Statistics in Excel

Introduction to Statistics in Excel

In this modern era where business solutions in a layman language are all people are thinking of, different dedicated software is developed and used for Statistical Analysis. It is a major part of the decision-making and finding out adequate solutions for your business problems. Despite the fact that it is not as powerful as the software designed dedicatedly for the Statistical Analysis, Excel still holds some of the power games to be able to do most of the Statistical Analytical tasks on its own and that too in a pretty simple manner. You need to be an advanced user of Excel, though, in order to be able to work on Statistical Analysis through Excel.

Examples of Statistics in Excel

We will see some examples using which we can calculate the statistics under Excel.

You can download this Statistics Excel Template here – Statistics Excel Template

Example #1 – Find Average

Suppose we have Country-wise Sales and Margin data as shown below:

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Statistics in Excel 1-1

We wanted to capture the average sales value for our company throughout these countries. The standard formula for Average is as below:

Average/Mean = Sum of All Values/ Number of Values

However, in Excel, you have a built-in AVERAGE function that does this task for you.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)

Step 1: In cell B9, start typing the formula =AVERAGE()

average in excel 1-2

Step 2: Use B2:B7 (all sales values) as a reference under the AVERAGE function.

average in excel 1-3

Step 3: Close the parentheses to complete the formula and press Enter key to see the output as shown below:

average Sales

Step 4: Copy cell B9 and paste (Ctrl + V) it under cell C9 to get the average value for Margin. Well, this is one of the nicest Excel features of all time. You can copy the formulas and paste them to different cells so that you get the formulated results for the other column.

average Sales 1

Example #2 – Margin Percentage for Each Country

Suppose we wanted to capture the Margin % we have acquired through business with each country. Does percentage show it all in a nice way, you know? Let’s see how this can be done.

The general formula for calculating Margin% is as follows:

Margin% = Margin/Sales

Step 1: In column D, under cell D2, use the formula as C2/B2 (Since C2 has Margin and B2 has Sales value for UAE).

Statistics in Excel 2-1

Step 2: Press Enter key to see the Margin% value we have acquired for UAE through our trade.

Statistics in Excel 2-2

Step 3: Now, Drag down this formula across the rows to see the Margin% we have acquired for different countries through trade. You can use the keyboard shortcut Ctrl + D to achieve the result. Select all the necessary sales, including D2 and press Ctrl + D.

Statistics in Excel 2-3

We need to change the number formatting for column D to be seen in a percentage format.  Follow the step below to achieve the result.

Step 4: Select entire column D. Go to Home tab, under which navigate towards Number group.

Statistics in Excel 2-4

You can see the result as below:

Statistics in Excel 2-5

Example #3 – Find Standard Deviation

We can also be very interested in knowing the degree to which the data points are deviating from our data. We can find out the Standard Deviation, which gives us a good idea about the spread of the data. It is very easy to calculate Standard Deviation under Excel. We have two functions to achieve the result.

STDEV.P and STDEV.S. STDEV.P works well when you have an entire population to cover, and STDEV.S is the one that can be used to figure out the Standard Deviation for a sample. Follow the steps below to be able to find the standard deviation.

Step 1: Start typing the formula under cell D10 as =STDEV.S( to initiate the formula for sample standard deviation.

Standard deviation 3-1

Step 2: Now, use the range of cells you wanted to capture the standard deviation. I will use the sales values spread across B2:B7 as a reference to the STDEV.S function. It will give me a single value, which represents the standard deviation between the Sales Values.

Standard deviation 3-2

Step 3: Use closing parentheses to complete the formula and press Enter key. You’ll get a Standard Deviation value as shown in the screenshot below:

Standard deviation 3-3

Example #4 – Regression Analysis

Regression Analysis is a widely used statistical technique to determine a relationship between two or more variables and predict the future (forecasting) based on the model fitted. It assumes that there is some kind of relationship (termed as correlation) between two variables.

Suppose we have data of Height (in cm) and Weight (in kg) as shown below, and we are keen to know whether there is any relationship between both. If so, can we predict one based on the other?

regression analysis 4-1

This is a problem with Regression. Follow the steps below to run a regression analysis for the same.

Step 1: Navigate towards the Data tab and click on the Data Analysis button under the Analyze section.

data analysis

Step 2: Once you click there, the Data Analysis toolbox will pop up. Scroll down to navigate towards and select Regression. Click OK.

regression analysis 4-2

Step 3: Use B2:B11 as Input Y Range and A2:A11 as Input X Range under the Regression window that pops up.

regression analysis 4-3

Step 4: Tick the Labels option, select Output Range as E2 of the current worksheet, and tick on Residuals to show the residuals for the data.

regression analysis 4-4

Step 5: Click the OK button once you are satisfied with the output options you chose. It is customizable; feel free to add some more residuals if you want.

regression analysis 4-5

You can see a regression output as shown in the image below under the same worksheet where data is present.

summary output

This is how we can use the statistical techniques in Excel to extract more analytical insights through our data. This article ends here.

Things to Remember

  • There are more Statistical functions than the ones we have used in this article. Most of the formulae could be found under More Functions > Statistical functions encapsulated under Formulas sections.
  • All basic Descriptive Statistics can also be calculated at once using Data Analysis Descriptive Statistics tool. This will capture Mean, Mode, Median, Range, Quartiles, Quartile Deviations, etc., for you at a single click.
  • Working with advanced statistical techniques such as Regression, ANOVA, T-Test, F-Test, etc., is really very easy within Excel. I mean, come on! All you need to do is select an appropriate technique and do some clicking.’

Recommended Articles

This has been a guide to Statistics in Excel. Here we discuss How to use Statistics in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Linear Interpolation in Excel
  2. Excel Regression Analysis
  3. Regression Line Formula
  4. Excel GROWTH Formula
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Statistics Excel Template

EDUCBA

Download Statistics Excel Template

EDUCBA

डाउनलोड Statistics Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more