{"id":21869,"date":"2017-06-05T16:13:40","date_gmt":"2017-06-05T10:43:40","guid":{"rendered":"https:\/\/trumpexcel.com\/?p=21869"},"modified":"2023-11-20T14:44:30","modified_gmt":"2023-11-20T09:14:30","slug":"standard-deviation","status":"publish","type":"post","link":"https:\/\/trumpexcel.com\/standard-deviation\/","title":{"rendered":"How to Calculate Standard Deviation in Excel"},"content":{"rendered":"<p>Excel is used extensively for statistics and data analysis. Standard deviation is something that is used quite often in statistical calculations.<\/p>\n<p>In this tutorial, I will show you <strong>how to calculate the standard deviation in Excel<\/strong> (using simple formulas)<\/p>\n<p>But before getting into, let me quickly give you a brief overview of what standard deviation is and how it&#8217;s used.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">This Tutorial Covers:<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/trumpexcel.com\/standard-deviation\/#What-is-Standard-Deviation\">What is Standard Deviation?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/trumpexcel.com\/standard-deviation\/#Calculating-Standard-Deviation-in-Excel\">Calculating Standard Deviation in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/trumpexcel.com\/standard-deviation\/#Using-STDEVS-Function-in-Excel\">Using STDEV.S Function in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/trumpexcel.com\/standard-deviation\/#Example-%E2%80%93-Calculating-the-Standard-Deviation-for-Weight-Data\">Example &#8211; Calculating the Standard Deviation for Weight Data<\/a><\/li><\/ul><\/nav><\/div>\n\n<h2><span class=\"ez-toc-section\" id=\"What-is-Standard-Deviation\"><\/span>What is Standard Deviation?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A standard deviation value would tell you how much the data set deviates from the mean of the data set.<\/p>\n<p>For example, suppose you have a group of 50 people, and you are recording their weight (in kgs).<\/p>\n<p>In this data set, the average weight is 60 kg, and the standard deviation is 4 kg. It means that most of the people&#8217;s weight is within 4 kg of the average weight (which would be 56-64 kg).<\/p>\n<p>Now let&#8217;s interpret the standard deviation value:<\/p>\n<ul>\n<li>A lower value indicates that the data points tend to be closer to the <a href=\"https:\/\/trumpexcel.com\/excel-average-function\/\">average<\/a> (mean) value.<\/li>\n<li>A higher value indicates that there is widespread variation in the data points. This can also be a case when there are many <a href=\"https:\/\/trumpexcel.com\/find-outliers-excel\/\" target=\"_blank\" rel=\"noopener\">outliers in the data set<\/a>.<\/li>\n<\/ul>\n<h2><img fetchpriority=\"high\" decoding=\"async\" class=\"size-full wp-image-21931 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Standard-Deviation-in-Excel-Bell-Curve.jpg\" alt=\"Standard Deviation in Excel- Bell Curve\" width=\"700\" height=\"466\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Standard-Deviation-in-Excel-Bell-Curve.jpg 700w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Standard-Deviation-in-Excel-Bell-Curve-451x300.jpg 451w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Standard-Deviation-in-Excel-Bell-Curve-50x33.jpg 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Standard-Deviation-in-Excel-Bell-Curve-250x166.jpg 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Standard-Deviation-in-Excel-Bell-Curve-150x100.jpg 150w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Standard-Deviation-in-Excel-Bell-Curve-300x200.jpg 300w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Standard-Deviation-in-Excel-Bell-Curve-225x150.jpg 225w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Standard-Deviation-in-Excel-Bell-Curve-694x462.jpg 694w\" sizes=\"(max-width: 700px) 100vw, 700px\" \/><\/h2>\n<h2><span class=\"ez-toc-section\" id=\"Calculating-Standard-Deviation-in-Excel\"><\/span>Calculating Standard Deviation in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>While it&#8217;s easy to calculate the standard deviation, you need to know which formula to use in Excel.<\/p>\n<p>There are six standard deviation formulas in Excel (eight if you consider database functions as well).<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-21965 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/there-are-eight-standard-deviation-functions-in-Excel.png\" alt=\"there are eight standard deviation functions in Excel\" width=\"103\" height=\"169\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/there-are-eight-standard-deviation-functions-in-Excel.png 103w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/there-are-eight-standard-deviation-functions-in-Excel-30x50.png 30w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/there-are-eight-standard-deviation-functions-in-Excel-61x100.png 61w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/there-are-eight-standard-deviation-functions-in-Excel-91x150.png 91w\" sizes=\"(max-width: 103px) 100vw, 103px\" \/><\/p>\n<p>These six formulas can be divided into two groups:<\/p>\n<ol>\n<li><span style=\"text-decoration: underline;\">Calculating the sample standard deviation:<\/span>\u00a0The formulas in this category are STDEV.S, STDEVA, and STDEV<\/li>\n<li><span style=\"text-decoration: underline;\">Calculating the standard deviation for an entire population:<\/span>\u00a0The formulas in this category are STDEV.P, STDEVPA, and STDEVP<\/li>\n<\/ol>\n<p>In almost all of the cases, you will use standard deviation for a sample.<\/p>\n<p>Again in layman terms, you use the term &#8216;population&#8217; when you want to consider all the datasets in the entire population. On the other hand, you use term &#8216;sample&#8217; when using a population is not possible (or it&#8217;s unrealistic to do so). In such a case, you pick a sample from the population.<\/p>\n<p>You can use the sample data to calculate the standard deviation and infer for the entire population. You can read a great explanation\u00a0of it <a href=\"https:\/\/stats.stackexchange.com\/questions\/269\/what-is-the-difference-between-a-population-and-a-sample\" target=\"_blank\" rel=\"noopener\">here<\/a> (read the first response).<\/p>\n<p>So. this narrows down the number of formulas to three (STDEV.S, STDEVA, and STDEV function)<\/p>\n<p>Now let&#8217;s understand these three formulas:<\/p>\n<ul>\n<li>STDEV.S &#8211; Use this when your data is numeric. It ignores the text and logical values.<\/li>\n<li>STDEVA &#8211; Use this when you want to include text and logical values in the calculation (along with numbers). Text and FALSE are taken as 0 and TRUE is taken as 1.<\/li>\n<li>STDEV &#8211; STDEV.S was introduced in Excel 2010. Before it, the STDEV function was used. It is still included for compatibility with prior versions.<\/li>\n<\/ul>\n<p>So, you can safely assume that in most of the cases, you would have to use STDEV.S function (or STDEV function if you&#8217;re using Excel 2007 or prior versions).<\/p>\n<p>So now let&#8217;s see how to use it in Excel.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Using-STDEVS-Function-in-Excel\"><\/span>Using STDEV.S Function in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>As mentioned, STDEV.S function uses numerical values but ignores the text and logical values.<\/p>\n<p>Here is the syntax of STDEV.S function:<\/p>\n<p><em>STDEV.S(number1,[number2],&#8230;)<\/em><\/p>\n<ul>\n<li><strong>Number1<\/strong> &#8211; This is a mandatory argument in the formula. The first number argument corresponds to the first element of the sample of a population. You can also use a <a href=\"https:\/\/trumpexcel.com\/named-ranges-in-excel\/\">named range<\/a>, single array, or a reference to an array instead of arguments separated by commas.<\/li>\n<li><strong>Number2, &#8230;<\/strong> [Optional argument in the formula] You can use up to 254 additional arguments. These can refer to a data point, a named range, a single array, or a reference to an array.<\/li>\n<\/ul>\n<p>Now, let&#8217;s have a look at a simple example where we calculate the standard deviation.<\/p>\n<pre>Also read:\u00a0<a href=\"https:\/\/trumpexcel.com\/interquartile-range-iqr-excel\/\">Calculate Interquartile Range (IQR) in Excel<\/a><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Example-%E2%80%93-Calculating-the-Standard-Deviation-for-Weight-Data\"><\/span>Example &#8211; Calculating the Standard Deviation for Weight Data<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Suppose you have a data set as shown below:<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-21928 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Weight-Data-to-Calculate-Standard-Deviation-in-Excel.png\" alt=\"Weight Data to Calculate Standard Deviation in Excel\" width=\"160\" height=\"292\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Weight-Data-to-Calculate-Standard-Deviation-in-Excel.png 160w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Weight-Data-to-Calculate-Standard-Deviation-in-Excel-27x50.png 27w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Weight-Data-to-Calculate-Standard-Deviation-in-Excel-137x250.png 137w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Weight-Data-to-Calculate-Standard-Deviation-in-Excel-55x100.png 55w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Weight-Data-to-Calculate-Standard-Deviation-in-Excel-110x200.png 110w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Weight-Data-to-Calculate-Standard-Deviation-in-Excel-82x150.png 82w\" sizes=\"(max-width: 160px) 100vw, 160px\" \/><\/p>\n<p>To calculate the standard deviation using this data set, use the following formula:<\/p>\n<pre>=STDEV.S(A2:A10)<\/pre>\n<p>In case you&#8217;re using Excel 2007 or prior versions, you will not have the STDEV.S function. In that case, you can use the below formula:<\/p>\n<pre>=STDEV(D2:D10)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-21929 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Using-STDEVS-function-to-find-deviation-in-weight.png\" alt=\"Using STDEVS function to find deviation in weight\" width=\"497\" height=\"336\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Using-STDEVS-function-to-find-deviation-in-weight.png 497w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Using-STDEVS-function-to-find-deviation-in-weight-444x300.png 444w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Using-STDEVS-function-to-find-deviation-in-weight-50x34.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Using-STDEVS-function-to-find-deviation-in-weight-250x169.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Using-STDEVS-function-to-find-deviation-in-weight-148x100.png 148w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Using-STDEVS-function-to-find-deviation-in-weight-296x200.png 296w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2017\/06\/Using-STDEVS-function-to-find-deviation-in-weight-222x150.png 222w\" sizes=\"(max-width: 497px) 100vw, 497px\" \/><\/p>\n<pre><\/pre>\n<p>The above formula returns the value of 2.81, which indicates that most of the people in the group would be within the weight range 69.2-2.81 and 69.2+2.81.<\/p>\n<p>Note that when I say &#8216;most of the people&#8217;, it refers to the normal distribution of the sample (that is 68% of the sample population is within one standard deviation from the mean).<\/p>\n<p>Also, note that this is a very small sample set. In reality, you may have to do this for a bigger sample data set where you can observe normal distribution better.<\/p>\n<p>Hope you found this Excel tutorial useful.<\/p>\n<p><strong>You May Also Like the Following Excel Tutorials:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/trumpexcel.com\/weighted-average-in-excel\/\">Calculating Weighted Average in Excel<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/calculate-cagr-excel\/\">Calculating CAGR in Excel<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/calculate-percentages-excel\/\">Calculate and Format Percentages in Excel<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/calculate-age-in-excel\/\">Calculate Age in Excel using Formulas<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/bell-curve\/\" target=\"_blank\" rel=\"noopener noreferrer\">Creating a Bell Curve in Excel<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/compound-interest-calculator\/\" target=\"_blank\" rel=\"noopener noreferrer\">Calculating Compound Interest in Excel<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/calculate-square-root-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">How to Calculate Square Root in Excel<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/calculate-coefficient-of-variation-excel\/\" target=\"_blank\" rel=\"noopener\">Calculate the Coefficient of Variation (CV) in Excel<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/correlation-coefficient-excel\/\" target=\"_blank\" rel=\"noopener\">How to Calculate Correlation Coefficient in Excel<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/descriptive-statistics-excel\/\" target=\"_blank\" rel=\"noopener\">How to Get Descriptive Statistics in Excel?<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Excel is used extensively for statistics and data analysis. Standard deviation is something that is used quite often in statistical calculations. In this tutorial, I will show you how to calculate the standard deviation in Excel (using simple formulas) But before getting into, let me quickly give you a brief overview of what standard deviation &#8230; <\/p>\n<p class=\"read-more-container\"><a title=\"How to Calculate Standard Deviation in Excel\" class=\"read-more button\" href=\"https:\/\/trumpexcel.com\/standard-deviation\/#more-21869\" aria-label=\"Read more about How to Calculate Standard Deviation in Excel\">Read more<\/a><\/p>\n","protected":false},"author":80,"featured_media":21931,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-21869","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-tips"],"_links":{"self":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts\/21869","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/users\/80"}],"replies":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/comments?post=21869"}],"version-history":[{"count":0,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts\/21869\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/media\/21931"}],"wp:attachment":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/media?parent=21869"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/categories?post=21869"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/tags?post=21869"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}