How to Calculate Standard Deviation in Excel, and Why It Matters for Marketers

How to Calculate Standard Deviation in Excel, and Why Every Marketer Should Know This Statistic

If the phrase "standard deviation" gives you nightmarish flashbacks to high school statistics class, you‘re not alone. This complex-sounding metric is actually a powerful tool that provides insights into data that basic averages cannot — and Microsoft Excel makes it surprisingly simple to calculate.

As a marketer, you‘re likely very familiar with using averages to summarize data and gauge performance. The average conversion rate, average order value, average number of website visitors — these metrics are marketing 101.

However, while averages are certainly useful, they don‘t tell the full story. Two datasets can have the exact same average but vastly different spreads between the highest and lowest values. That‘s where standard deviation enters the picture.

In this post, I‘ll give you a crash course in standard deviation — what it means, why it matters to marketers, and most importantly, how to easily calculate it in Excel. We‘ll also walk through examples of how you can start applying this handy statistic to your marketing analyses today.

What is standard deviation?
Standard deviation is a measure of how spread out a set of numbers are from their average value. It tells you, on average, how far each value lies from the mean.

A low standard deviation indicates that most of the numbers are clustered close to the average. A high standard deviation shows that the data points are spread out over a wider range.

Here‘s a simple way to think about it: Standard deviation measures the typical "deviation" (distance) from the average.

Let‘s say you‘re comparing the monthly sales of two stores. Both have an average of $10,000 per month. However, the first store has a low standard deviation of $500 while the second store has a high standard deviation of $5,000.

This tells you that while both stores end up with around the same sales each month, the first store has very consistent sales hovering between $9,500 and $10,500. The second store sees much larger swings, with some months as low as $5,000 and others as high as $15,000.

So while the $10,000 monthly sales average sounds great by itself, the standard deviation reveals how volatile those sales numbers actually are, which is crucial information.

Why standard deviation matters for marketers
As a marketer, you‘re awash in data and metrics. Open rates, click-through rates, time on page, bounce rate, leads, conversion rate, customer acquisition costs — the list goes on. When analyzing all these numbers, it‘s easy to just zero in on the averages.

However, looking at standard deviations in addition to averages gives you a much more comprehensive picture. Here are a few reasons why:

1. Identify consistency (or lack thereof).
   A low standard deviation tells you that a metric is steady and reliable, while a high standard deviation points to unpredictability. A stable conversion rate with a low standard deviation means you can depend on generating a similar number of customers from month to month. A conversion rate with a high standard deviation is prone to peaks and valleys.

2. Uncover hidden issues.
   Averages can sometimes mask underlying problems. Let‘s say your average customer acquisition cost looks great. However, if you dig deeper and see that the standard deviation of those costs is very high, it reveals that some months you‘re spending way more than others to get customers. There may be issues with certain campaigns, channels, or strategies that you need to investigate.

3. Make better projections.
   When forecasting metrics like website traffic, leads, or revenue, considering the standard deviation and not just the average will result in more realistic projections. If your leads per month have a high standard deviation, your projections should incorporate that variability rather than assuming you‘ll hit the average every time.

4. Assess risk.
   In general, the higher the standard deviation, the riskier a data set is because it‘s more unpredictable. For example, if you‘re deciding how to allocate your ad budget between two platforms, the one with the lower standard deviation in key metrics is probably the safer bet. Of course, with higher risk can also come higher reward — but it‘s important to understand the level of risk you‘re taking on.

5. Measure the impact of changes.
   When you make a change to your marketing strategy or tactics, calculating the standard deviation of your results before and after the change can help you gauge its impact. If the change reduces the standard deviation of an important metric like conversion rate or revenue, that‘s a sign that it‘s working to improve consistency.

How to calculate standard deviation in Excel
Now that you understand the importance of standard deviation, let‘s get into the nitty gritty of how to actually calculate it in Excel. Don‘t worry — it‘s much easier than you might expect.

Excel provides a few different functions for calculating standard deviation:

• STDEV.S: For a sample of data that doesn‘t include text or logical values
• STDEVA: For a sample that includes text and logical values
• STDEV.P: For an entire population of data that doesn‘t include text or logical values
• STDEVPA: For an entire population that includes text and logical values

In most cases, you‘ll want to use STDEV.S or STDEVA since you‘ll typically be working with a sample of data rather than an entire population.

Here‘s how to calculate standard deviation in Excel step-by-step using STDEV.S:

1. Enter your data into a single column or row in Excel. Remove any header rows or columns.

2. In an empty cell, type =STDEV.S(

3. Highlight the range of cells containing your data.

4. Type a closing parenthesis, then press Enter.

5. Excel will return the standard deviation of your selected data.

It‘s that simple! You can also access the standard deviation functions by clicking the Formulas tab, then More Functions > Statistical. Scroll down and you‘ll see all the STDEV options.

Now let‘s try an example. Say you have the monthly website traffic for your site over the past year:

January: 10,500
February: 9,800
March: 10,200
April: 11,000
May: 10,700
June: 9,500
July: 10,400
August: 10,900
September: 10,100
October: 9,900
November: 10,600
December: 10,800

To calculate the standard deviation, enter those numbers in a single column in Excel (let‘s say A1 through A12). Then click into any blank cell and type:

=STDEV.S(A1:A12)

Press Enter and Excel returns a standard deviation of 420.79.

The average monthly traffic is 10,367. The standard deviation of 420.79 tells you that most months, the traffic was within about 420 visits of that average. Some quick mental math shows that most months had between 9,947 and 10,787 visitors.

Examples of using standard deviation in marketing
Equipped with this understanding of what standard deviation is and how to calculate it in Excel, let‘s explore a few common use cases for marketers.

1. Analyzing email marketing performance
   You send a weekly email newsletter to your subscribers. The average open rate is 25%. However, when you calculate the standard deviation of the open rates, you get 5%.

This means that most weeks, the open rate is within 5% of 25% — so it typically falls between 20% and 30%. That‘s a fairly significant swing that the average of 25% doesn‘t fully capture.

There are several ways you could act on this insight:

• Test ways to make your open rates more consistent, such as always sending on the same day and time or using a standard subject line format.
• Investigate why some weeks perform much better or worse than average. What subject lines, send times, or content types correlate with the high and low open rates?
• When setting goals or projections for your email performance, use a range (e.g., 20-30%) rather than assuming you‘ll always hit 25%.

2. Comparing landing page conversion rates
   Let‘s say you have two landing pages for the same offer. Landing page A has an average conversion rate of 10% with a standard deviation of 0.5%. Landing page B has a slightly lower average of 9.5% but a higher standard deviation of 2%.

While landing page A looks better if you just consider the averages, factoring in the standard deviations may lead you to a different conclusion. Landing page A‘s conversions are extremely steady, consistently hitting very close to 10%.

However, landing page B‘s conversions are much less predictable. Some weeks it may only convert at 7.5% while other weeks it hits 11.5%. If your goal is just to maximize total conversions, that 2% swing may be worth it for the chance at those higher peaks.

3. Forecasting sales revenue
   You‘re creating projections for next quarter‘s sales revenue. Over the past year, your average monthly revenue was $100,000 with a standard deviation of $15,000.

When building your forecast, it would be unrealistic to assume you‘ll bring in exactly $100,000 each month. The $15,000 standard deviation shows that your typical monthly revenue is somewhere between $85,000 and $115,000.

Therefore, your projections should use a range for each month, not just the average. You may also want to create best case and worst case scenarios using the high and low ends of the standard deviation to help with planning.

Tips for visualizing standard deviation
In addition to calculating standard deviation in Excel, you can also create charts and graphs to visualize what the standard deviation represents. Here are a few options:

1. Line chart with average and standard deviation lines
   Create a line chart with your data. Add two more data series: one for the average plus the standard deviation, and one for the average minus the standard deviation. Format these series as dashed lines.

You‘ll end up with three lines on your chart: the actual values, the average + standard deviation, and the average – standard deviation. Most of your data points should fall between the + and – standard deviation lines.

2. Column chart with error bars
   If you‘re comparing the averages of multiple data sets, you can use a column chart with error bars to show the standard deviations.

First, create a column chart with your average values. Then, click on one of the columns and go to Format > Current Selection. In the Format Data Series pane, click the Chart Options icon and check the box next to Error Bars. Set the Error Amount to Standard Deviation.

Now each column will have an error bar showing how much the typical values deviate from the average. The shorter the error bar, the lower the standard deviation and the more consistent the data set.

3. Box and whisker plot
   Also known as a box plot, this chart type is designed to show the distribution of a data set. It displays the minimum, first quartile, median, third quartile, and maximum of a data set.

To create one in Excel, you‘ll need to use the Data Analysis ToolPak add-in. Once enabled, go to Data > Analyze > Descriptive Statistics. Select your input range, grouped by columns, with labels in the first row. Check the box for Summary statistics and Charts. Then click OK.

In the output, look for the Box Plot chart. The size of the box represents the standard deviation. A compact box means a low standard deviation, while a stretched out box means a high standard deviation.

Limitations of standard deviation
While standard deviation is undeniably a valuable tool for marketers, it‘s not without its limitations. Here are a few things to keep in mind:

1. It doesn‘t tell you the shape of the distribution.
   Standard deviation gives a sense of how spread out the data is, but it doesn‘t tell you if that spread is even. The data could be normally distributed in a bell curve shape, or it could be heavily skewed to one side. Always plot your data in a histogram or other chart to visualize the actual distribution.

2. It‘s sensitive to outliers.
   A few extreme outliers can inflate the standard deviation, even if most of the data is fairly close to the average. If your data set has significant outliers, consider using the interquartile range (IQR) instead, which is less affected by extreme values.

3. It should be used in conjunction with other statistics.
   Standard deviation is a fantastic companion to averages, but it doesn‘t replace other important stats marketers should track. Continue monitoring metrics like median, minimum, maximum, and percentile ranks to get a robust picture of your data.

4. It‘s not applicable to all types of data.
   Standard deviation is only relevant for quantitative, numerical data like website visits, conversion rates, revenue, and so on. It can‘t be used with categorical data like color, source, or region. For those types of data, use pivot tables or charts to analyze the distribution instead.

The bottom line
As a marketer, it‘s easy to get caught up in averages. We‘re taught to focus on bumping up the average conversion rate, lowering the average cost per lead, increasing the average order value.

However, averages are only one piece of the puzzle. Calculating standard deviation reveals the consistency (or lack thereof) hiding behind those averages. A metric with a low standard deviation is far more dependable and less risky than one with a high standard deviation.

Getting in the habit of calculating standard deviation for your key marketing metrics will help you make better, more data-driven decisions. You‘ll be able to spot potential issues, create more accurate projections, and ultimately, generate more consistent results.

And with Excel‘s built-in standard deviation functions, you don‘t need an advanced statistics degree to harness the power of this important calculation. The next time you‘re reviewing marketing data in Excel, take a few extra seconds to calculate the standard deviations. Your future self (and your boss) will thank you.