Leave a Reply

    Your email address will not be published.

    Four Stats Formulas that Every Spreadsheet User Should Know About

    Updated on: Dec 10th, 2019
    Data Analysis
    Excel screenshot of how to use formulas in Excel.

    I overheard a conversation at a conference lunch table recently. It went something like this:

    Smart, hardworking person #1: I love the idea of using data to drive decisions, but spreadsheets can be such a drag. It takes forever to finish all the monthly reports that my organization is required to submit.

    Person #2: Agreed. Spreadsheets totally suck. Just figuring out the ‘average’ scores takes too long.

    #1: I have to type all the numbers into my calculator to add them up…

    #2: … and then count how many people are included! Right! And divide by that number!  And then I have to do those same tallies again the next month.

    Oh noooooooooo!

    A tiny piece of my soul crumbles every time I hear these comments.

    You were put on this planet to do more than manual data analysis.

    There’s a better way.

    I love Excel, but that doesn’t mean I want to spend more time than necessary using it. Instead, I use these time-saving strategies to save my limited mental energy for tasks that are more important.

    Here are four statistical formulas that every spreadsheet user should know.

    (There are many, many, many additional formulas that you show know. But this is a good place to get started.)

    Statistical Formula #1: Calculating the Average or Mean

    Ten years ago, I was a consultant to Federal and state education agencies, so my brain is still packed full of education-related datasets. Humor me and pretend like you work on similar datasets for a minute. (The formula is the same regardless of your content area.)

    Let’s pretend you want to calculate the average (a.k.a. mean) score for students.

    Think back to high school math class: To calculate the average, you add up all the scores and then divide that sum by 5 because there are 5 students in the group. These are the steps that the poor conference-goers were doing by hand with their calculator.

    Let Excel’s =average() function handle the calculations for you.

    Type =average( and then click on the range of scores (so B2 through B6 for the reading scores). Add a ) to the end of the function and press Enter on the keyboard.

    Your function will say =average(B2:B6) and the result is an average score of 49.2.

    Once you calculate the average reading score in Column B, you can also drag that function over to the right to find the average math score, as I demonstrate below.

    Screenshot of an Excel sheet that outlines how to calculate the average (aka mean) score.

    This is a simple dataset with just 5 scores, but the formula works the same with huge datasets of thousands of entries, too. Sometimes people worry that data analysis gets more complicated with larger datasets. Not necessarily. Formulas just become more critical on larger datasets. It’s time-consuming to do manual calculations on small datasets, and it’s impossible to do manual calculations on large datasets.

    Statistical Formula #2: Median

    You remember the median from high school math class, too, right?

    Think of a median as a middle value. You sort the dataset from the smallest number to the largest number. The median is the number in the middle of that list.

    Medians aren’t just nice-to-have calculations—they’re critical for reporting on skewed distributions. But that’s a post for another day… unless you want to comment on this blog post and practice giving your best explain-it-to-your-kindergartener explanation of a normal vs. skewed distribution, and a mean vs. median? Go for it, brave friends.

    To calculate the median reading score, type =median(B2:B6) into cell B8 and press Enter.

    You’ll get a result of 41.

    Screenshot of an Excel sheet showing how to calculate the median or middle value.

    Over the years, I’ve heard a few of my workshop participants wonder if the median still works even if your spreadsheet isn’t sorted from smallest to largest first. Yep, it still works! Excel does the behind-the-scenes sorting for you. That’s built into the formula. Your computer scans that section of numbers… sorts it… and finds the middle value. Your numbers can be in any order whatsoever.

    Statistical Formula #3: Mode

    The mode is the most-occurring number in a set of numbers.

    To find the mode for this sample dataset, you would type =mode(B2:B6).

    The most common reading score is 41 and the most common math score is 68.

    Screenshot of an Excel sheet showing how to calculate the mode, or the most-occurring number in a set of numbers.

    Statistical Formula #4: Standard Deviation

    Remember the term standard deviation from your high school math class?

    The standard deviation basically measures how stretched out your set of numbers is.

    Is there a bunch of reallllllly small numbers along with a bunch of realllllly big numbers? Or are the numbers all fairly similar across the board?

    A higher standard deviation means the numbers really vary, while a small standard deviation means the scores are pretty similar to one another.

    To calculate the standard deviation for our sample numbers, you would type =stdev(B2:B6) and get a result of 20.8.

    Fellow data friends, I challenge you to comment on this blog post with your best explain-it-to-your-kindergartener definition. Phew! This is a hard term to describe!

    Screenshot of an Excel sheet showing how to calculate the standard deviation, or how to measure how stretched out a set of numbers is.

    Feeling Good??

    I try to vary my blog content between beginner and advanced topics. This post is purposefully towards the beginner end of the spectrum.

    Some of you work in spreadsheets all day every day like me, and you can do these formulas (and a million more) in your sleep. Other people are only in spreadsheets once in while because they’re busy leading organizations, writing grants, and managing teams of staff.

    Occasional spreadsheet users, this post is for you! I hope you got a confidence boost and realized that you’re already on the right track with your current spreadsheet knowledge.

    Your Turn

    There are so many useful spreadsheet formulas. Comment below with your favorite formula, especially the formulas you think the occasional spreadsheet users would get the most benefit from.

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.


      Leave a Reply

      Your email address will not be published.

      You Might Like

      Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.


      Visualizing 24 School Divisions’ Submissions with a Dashboard in Microsoft Excel

      Amadu Sidi Bah encountered a forgotten folder on the shared drive: “Stakeholder Submissions.” These text-heavy reports had been left to gather dust. He wanted to make sure the stakeholders’ recommendations were used in policy decisions, and he decided to do something about it. In this blog post, you’ll see the Excel-based dashboard that Amadu created from those 24 reports.

      More »

      Need some time-savers in Excel? Feel like all the formulas are taking forever? Learn how to clean, analyze, and tabulate your dataset.



      Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.