How To Calculate Mean in Excel in 4 Steps (Plus Its Uses)

Updated August 8, 2022

Excel is a spreadsheet program used to organize and analyze large amounts of data quickly. One of the most important functions for analyzing data is finding the mean. The mean is the average number when all of the data is added and divided by the number of data points.

In this article, we describe what the mean is often used for, how to calculate it in Excel and how to specify what data to use in the calculation.

Uses for mean

There are many uses for understanding the mean of a data set. The mean is found by adding up all of the numbers in a set of data and dividing by the number of points that are added together. This formula tells you what the typical value is in a given set of information. Here are a few ways you can use the mean in analyzing data:

To find a standard midpoint for comparing individual data points.
To compare historical data.
To determine quotas and key performance indicators (KPIs).
To guide project management and business strategy.

Find a standard midpoint for comparing individual data points

The mean allows you to find a fair, central number to act as a basis of comparison, rather than comparing individual data points to each other without a standardized basis for comparison. For example, if you find the mean for historical sales per hour on a certain day, you can see how individual employees compare to that standard.

Compare historical data

You can use the mean to compare how your data sets change over time. This is particularly important if you are tracking sales, productivity, employee hours and performance management. You can measure the mean of individual employees, certain departments or overall company data. Understanding historical data can help you develop budgets, gauge employee performance over time, build sales strategies and other important business decisions.

Determine quotas and key performance indicators (KPIs)

You can use the mean to determine what the average employee should be able to accomplish. This means that you can use this calculation to set performance expectations, such as sales quotas or call time expectations. These quotas and KPIs will help you manage employee productivity and provide a measurable standard for every employee to meet. You can also use these quotas and KPIs to present to current and potential clients as a way for them to determine if you can meet their needs.

Guide project management and business strategy

You can use the mean to determine what to expect when you are developing budgets, schedules, teams and man-hours. The mean can give you an idea of what resources were required to complete similar projects in the past and what kinds of costs you can expect to incur as you go forward. The mean is an important analytical tool for building strategies for individual projects and broader business decisions.

How to use Excel to calculate mean

There are several ways you can calculate the mean of a data set, depending on what you are trying to find in the data. Here is how you calculate the mean of an entire data set:

1. Enter the data into your Excel spreadsheet

You start by getting all of the data you are analyzing into your spreadsheet. You can enter it by hand by opening the Excel spreadsheet and typing each piece of information into a column. If you are entering your data set by hand, you will have to design your spreadsheet so that you can find the mean based on columns or rows. Many programs also allow you to export information that has been collected into a spreadsheet. This second option is particularly useful if you have a lot of information that would be very time-consuming or difficult to enter by hand.

2. Reorganize your Excel spreadsheet as needed

You won't need to complete this step if you design the spreadsheet and enter the data by hand. However, if you are importing data from another program, you might have information you don't need or you will need to find which columns have the data you will be using to find the average. Whether you delete the information you don't need for the average or just select the data you need for the calculation will depend on what other analysis you will be doing later.

3. Select an empty cell to enter the mean formula

The mean will be calculated in the same spreadsheet as the data you are analyzing. You will want to select an empty cell so that you do not lose any of the data you are analyzing. You can use a cell at the bottom of the column you are finding the mean for or a cell to the right of the filled cells.

4. Enter the formula to find the mean

To find the mean in Excel, you start by typing the syntax =AVERAGE or select AVERAGE from the formula dropdown menu. Then, you select which cells will be included in the calculation.

Syntax: =AVERAGE(array of numbers)

For example: Say you will be calculating the mean for column A, rows two through 20. Your formula will look like this: =AVERAGE(A2:A20).

When you hit enter, Excel will automatically calculate the mean, but will not include any cells that are empty or have a value of zero.

Get interview-ready with tips from Indeed

Prepare for interviews with practice questions and tips

Specifying mean calculation criteria

You will often have to find the mean of data that meets certain criteria. Here are some situations where you will need to use more specific mean formulas to determine if the information should be included in the calculation:

You want to include cells with values of zero or text. If you want to include cells with any data in your calculations, you would use the formula AVERAGEA.
You want to include cells that meet specific criteria. If you only want to find the mean of certain information that is based on one criterion, you would use the formula AVERAGEIF. You might use this if you are trying to find the mean of every cell that doesn't meet a quota or the mean salary over a certain pay grade.
You want to include cells that meet several specific criteria. If you are trying to find the mean of information that meets two or more requirements, you would use the formula AVERAGEIFS. You might use this if you are trying to find the mean of cells that are within a certain number range.