EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Excel Z Score

By Madhuri ThakurMadhuri Thakur

Home » Excel » Blog » Statistical Functions in Excel » Excel Z Score

Z Score in Excel

Excel Z Score (Table of Contents)

  • Z Score in Excel
  • How to Calculate Z Score in Excel?

Z Score in Excel

Z Score is used for statistical measurement. It is also known as a standard score. The value of the Z score is the measurement of the number of standard deviations a specific number is above or below a mean.

Start Your Free Excel Course

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

The below formula is used to calculate the Z score:

Z = (x-µ) / σ

Where the supplied arguments are as below:

  • Z = It denotes the Z score value.
  • X = The value to be standardized.
  • µ = Mean of the given data set values.
  • σ = Standard deviation of the given data set values.

How to Calculate Z Score in Excel?

Calculation of Z Score in excel is very simple and easy. Let’s understand how to calculate the Z score in excel with some examples.

You can download this Z Score Excel Template here – Z Score Excel Template

Excel Z Score – Example #1

We have given marks of some students as below:

Z Score Example 1-1

Now for calculating Z Score, we need to find out the Mean and standard deviation of the given dataset in excel.

Mean (or Average) calculation:

For finding the average, follow the below steps:

Step 1 – Go to the Formulas tab. Click on More Functions options under the Functions Library section.

Z Score Example 1-2

Step 2 – Now click on the Statistical functions category from the drop-down list. It will again open a list of functions. Click on the AVERAGE function as shown below.

Popular Course in this category
Sale
Excel Advanced Training (16 Courses, 23+ Projects)16 Online Courses | 23 Hands-on Projects | 140+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.8 (11,066 ratings)
Course Price

View Course

Related Courses
Excel for Marketing Training (8 Courses, 13+ Projects)

Z Score Example 1-3

Step 3 – It will open a Function Arguments dialog box. Enter the Range from Cells B4:B13 under field Number1 and click on OK.

Z Score Example 1-4

  • It will give you the Average or Mean value.

Z Score Example 1-5

Standard Deviation calculation:

For finding the standard deviation, follow the below steps:

Step 1 – Go to the Formulas tab. Click on More Functions under the Function Library section.

Z Score Example 1-6

Step 2 – Click on the Statistical Function category from the drop-down list. It will open a list of functions. Click on STDEVPA from the list as shown in the below screenshot.

Z Score Example 1-7

Step 3 – It will open a Function Arguments dialog box. Enter the cells range from B4:B13 under field Value1 and click on OK.

Z Score Example 1-8

  • It will give you the standard deviation value.

Z Score Example 1-9

Now we have to calculate the Z score values in excel. For this, follow the below screenshots:

  • Go to the FORMULAS tab. Click on the More Functions option under the Function Library section.

Z Score Example 1-10

  • Then a drop-down list of functions will open. Click on Statistical functions from the list. It will open a list of functions. Click on STANDARDIZE function from the list as per the below screenshot.

Z Score Example 1-11

  • Then it will open a Function Arguments dialog box. Enter the Cell value B4 under field X.

Z Score Example 1-12

  • Enter the Mean value in the second field, Mean, which is mentioned under Cell B15.

Z Score Example 1-13

  • Enter the Standard Deviation value in the third field, Standard_dev, which is mentioned under Cell B16. Click on OK.

Z Score Example 1-14

  • The result will be as given below.

Z Score Example 1-15

  • Drag this formula for the rest values, and it will pop up the Z score values in excel as shown below:

Z Score Example 1-16

Explanation

  • If we analyze the data, the highest Z score value is 2.082778, which is the Z score value of Nick Brown, who has achieved the highest score in the exam.
  • The smallest Z score value is -0.98521, which is the lowest Z score value of Adrian Steve, who has achieved the lowest score in the exam.
  • There can be positive and negative values in Z scores.
  • Students who have achieved scores more than the mean value get positive Z scores.
  • Students who have achieved scores less than the mean value get negative Z scores.
  • If the Z score is zero, that means the student’s score is the same as the mean value.

Excel Z Score – Example #2

We have given the below data values.

Example 2

To calculate the Z score or standard score, we need to determine the first mean and standard deviation in excel.

  • Let’s apply the AVERAGE formula for calculating the mean of the given dataset.

Calculation of Mean

  • It will give you the Average or Mean value.

Result of Mean

For calculating standard deviation, let’s apply the STDEVPA function for the given data values as per the below screenshot.

Calculation of Standard Deviation

  • It will give you the standard deviation value.

Result of Standard Deviation

  • Now we will calculate Z score values in excel. For this, apply the STANDARDIZE function for the given data values as per the below screenshot.

Calculation of STANDARDIZE function

  • The result will be as given below.

Result of STANDARDIZE function

  • Drag this formula for the rest values. The Final result is given below:

Result of Example 2

As we can see, the positive value of Z scores are higher than the mean value, and the negative value of Z scores are lower than the mean value.

Things to Remember

  • The Z score tells us a number of standard deviations that are away from the mean of the distribution or dataset.
  • The data values which are higher than the mean Z score will be a positive value.
  • Data values which are below the mean Z score will be a negative value.
  • The z score value is probably used for statistical analysis.

Recommended Articles

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

  1. Excel AVERAGE Function
  2. Standard Deviation in Excel
  3. Z TEST in Excel
  4. Excel Frequency Distribution

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

2 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • 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 Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course
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

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

Let’s Get Started

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

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

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

EDUCBA

Download Z Score Excel Template

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.

4th of July Offer - EXCEL ADVANCED Training Certification Learn More