If you measure the central tendency of symmetrically distributed data, perform statistical analysis on population data, conduct random sampling of polls, or want to calculate the average interest rate on an investment, you need to calculate the mean.

As you’ve planned to do any of the above data analysis on Microsoft Excel, you might wonder, “how to calculate mean in Excel.” In this quick and easy Excel tutorial, I’ll show you various methods for finding mean with Excel supported by real-world datasets, images, and easy steps.

## What is a Mean?

Mean is the measure of central tendency obtained by summing up a set of values and dividing by the number of values. In practice, mean and average are often used interchangeably, and their usage can depend on the context. In statistics, the symbol for the mean is Î¼.

For example, if you have the numbers 2, 4, 6, 8, and 10, the mean would be calculated as follows:

`Î¼=(2+4+6+8+10)/5=6`

In statistics, mean is the more commonly preferred term. Statisticians typically use mean to describe the average or central value of a dataset. In mathematics, especially in more general contexts or when dealing with mathematical functions, the term average might be used more broadly. Another common terminology of mean in mathematics is the arithmetic mean.

ðŸ“’ **Read More**: 5 Ways to Find Interquartile Range in Microsoft Excel

## Calculate Mean in Excel Using AutoSum

Open an Excel worksheet and enter your data values in a column or row adjacent to the cell where you want the mean.

Navigate to the destination cell.

Go to the **Formulas** tab on the Excel ribbon.

Click on the **AutoSum** drop-down button in the **Function Library** toolbar. It looks like the Greek letter sigma (**Î£**).

Choose **Average** from the context menu of the AutoSum tool.

Excel will automatically select what it thinks is the range for the input dataset.

If the selection is correct, press `Enter`.

You shall see the mean in the highlighted cell on the worksheet.

ðŸ“’ **Read More**: 3 Ways to Calculate a Pearsonâ€™s Correlation Coefficient in Excel

## Calculate Mean in Excel Using Quick Analysis

Navigate to the Excel worksheet that contains your dataset. Highlight the range of cells for which you want to calculate the mean.

Once the data is selected, a small icon (a small square box) will show up at the bottom-right corner of the selected range.

Hover the cursor over the small box, and the **Quick Analysis** icon (a small lightning bolt) will appear.

Click on the **Quick Analysis** icon.

A menu will appear with various tabs and options.

Select the **Totals** tab on the Quick Analysis context menu.

Now, click on the **Average** button to the right of the **Sum** button.

ðŸ“’ **Read More**: 9 Ways to Count Distinct Values in Microsoft Excel

## Calculate Mean in Excel Using Excel Status Bar

If you’ve already selected the **Average** option for the Excel Status bar, you can find the mean for a dataset in just one click.

Go to your worksheet, find the dataset, and highlight the column or row for which you need to calculate the mean.

At the bottom right corner of the Excel app, you should see the average value of the dataset you’ve highlighted on the worksheet.

## Calculate Mean in Excel Using Total Row

Open your Excel spreadsheet and make sure your data is organized in a table format.

If it’s not already a table, convert it to a table by selecting your data and pressing `Ctrl` + `T`.

Click anywhere inside the table.

In the **Table Tools Design** tab that appears, ensure the **Total Row** option is checked inside the **Table Style Options** commands block.

The **Total Row** will appear below the table you’ve just created.

Go to the column in the table for which you need the mean value.

Now, click on the calculated total for the selected column.

A drop-down arrow will appear. Click on it.

Choose **Average** from the context menu.

You should have your mean for the highlighted dataset. However, you must edit the row header from **Total** to **Average**.

## Calculate Mean in Excel Using Data Analysis ToolPak

Open your Excel workbook and ensure that the **Data Analysis ToolPak** is enabled.

If not, go to the **File** tab, click on **Options**, select **Add-Ins**, choose **Excel Add-ins** in the **Manage** box, and click **Go**.

Check **Analysis ToolPak** and click **OK**.

After enabling the **ToolPak**, a new **Data Analysis** option will appear in the **Data** tab’s **Data Analysis** block.

Click on the **Data Analysis** button.

Select **Descriptive Statistics** from the list of available analysis tools in the **Data Analysis** dialog box and click **OK**.

In the **Descriptive Statistics** dialog box, specify the input range by selecting the data for which you want to calculate the mean.

Choose the output cell where you want the result to be displayed.

Check the **Summary statistics** option and click **OK** to run the analysis.

Excel shall calculate the mean (average) of your selected data and display it in the specified output range as **Column 1**.

## Calculate Mean in Excel Using SUBTOTAL Function

If you’ve got regional sales data by agents and want to see the region-wise average as well as the grand average, you can use the **Subtotal** tool in Excel.

Access your dataset in a worksheet, select the dataset range, and go to the **Data** tab.

Find the **Outline** block and click on the **Subtotal** button.

Subtotal dialog will show up and Excel should have already filled up the required entries for subtotal calculation.

If you want to manually enter the details, here’s how:

**At each change in**should be the column that you’ve sorted by name, for example,**Region**in the current dataset.**Use function**should be set to**Average**.- For the
**Add subtotal to**field, choose the values against which you want to calculate the mean. Here. it’s the**Sales**column.

Finally, hit the **OK** button to get regional and grand averages in a table.

Now, you can click on the **numerical 2** hierarchy button as indicated in the screenshot, Excel will hide granular details and only show the regional averages and grand averages.

## Calculate Mean in Excel Using an Array Formula

If there are multiple columns containing numerical values in your dataset and you would like to include all of those when calculating the mean, you can use an array formula.

In the above table, I’m trying to calculate what would be the mean cost to my business if I purchase a CPU in different quantities at different MSRP from different eCommerce stores.

So, instead of creating another column to calculate store-wise costs for the said units of CPUs and apply the **AVERAGE** formula, I can use the following formula to reduce the number of calculations I need to perform.

`={AVERAGE(C2:C9*D2:D9)}`

Do customize the cell references in the above formula according to your own dataset.

## Calculate Mean in Excel Using PivotTable

You might not need to use PivotTable to just calculate the mean but if you’re planning for a detailed data analysis of your Excel worksheet data using a PivotTable and wondering how to find mean in Excel PivotTables, this method is just for you.

Before beginning with this exercise, you may want to learn how to insert a PivotTable in Microsoft Excel to master your PivotTable game in Excel.

Now that you’ve created the **PivotTable**, click on the **Sum** column as shown in the above image.

Go to the **PivotTable Analyze** tab and click on the **Active Field** drop-down.

Click the **Field Settings** on the context menu, choose **Average** from the **Value Field Settings** dialog, and click **OK**.

The **Sum** column header will change to **Average** and you get your mean instantly.

## Calculate Mean in Excel Using Power Query

This method is suitable for you if you’re importing a gigantic dataset to Excel and want to get a quick look at the central tendency of the dataset or mean. However, you can also utilize this technique of mean calculation for existing worksheet datasets.

For external server data, go to the **Data** tab and click on the **Get Data** drop-down arrow. Hover the mouse cursor over appropriate data sources like **From Database** and click on **From Oracle Database**, **From SQL Server Database**, etc.

Follow on-screen instructions like **Server**, **Database**, etc., to import your dataset to Power Query.

If you need to add existing worksheet data to Power Query, highlight the dataset on your Excel worksheet and click the **From Table/Range** button on the **Data** tab > **Get & Transform Data** block.

Click **OK** on the **Create Table** dialog. You should now see your dataset on PowerQuery.

On the Power query, highlight the column that contains the value for average calculation.

Now, go to the **Transform** tab and click on the **Statistics** drop-down button inside the **Number Column** block.

Click **Average** on the context menu that pops open.

Power Query shall calculate the mean and show the value beneath the **Power Query formula bar**.

To export the mean to your Excel worksheet, click the **File** tab, and choose **Close & Load To** button.

Click on the **Existing worksheet** option and choose a cell range in the sheet. Click **OK** to complete the export process.

You should now see the mean in your worksheet.

## Calculate Mean in Excel Using Excel VBA

Are you okay to write a few lines of codes and convert that to a macro using Excel VBA? If yes, you’d definitely like this technique to calculate mean in Excel.

Use the following script to create a VBA macro in your workbook that’ll calculate the mean of the selected cell range and put the value in a cell designated by you. All these steps happens automatically. You just need to run the macro.

```
Sub CalculateAndPasteMeanWithLabel()
' Declare variables
Dim inputRange As Range
Dim outputRange As Range
Dim meanResult As Double
' Prompt user to select the input cell range
On Error Resume Next
Set inputRange = Application.InputBox("Select a range for mean calculation", Type:=8)
On Error GoTo 0
' Check if the user canceled the input box
If inputRange Is Nothing Then
MsgBox "Operation canceled by the user.", vbExclamation
Exit Sub
End If
' Prompt user to select the output cell range
On Error Resume Next
Set outputRange = Application.InputBox("Select a cell to paste the mean", Type:=8)
On Error GoTo 0
' Check if the user canceled the input box
If outputRange Is Nothing Then
MsgBox "Operation canceled by the user.", vbExclamation
Exit Sub
End If
' Check if a single column or row is selected for input range
If inputRange.Rows.Count > 1 And inputRange.Columns.Count > 1 Then
MsgBox "Please select a single row or column for input range.", vbExclamation
Exit Sub
End If
' Calculate the mean
meanResult = Application.WorksheetFunction.Average(inputRange)
' Write "Average" to the left adjacent cell of the output range
outputRange.Offset(0, -1).Value = "Average"
' Paste the mean into the output range
outputRange.Value = meanResult
' Display a success message
MsgBox "The mean has been calculated and labeled, then pasted to the selected cell.", vbInformation
End Sub
```

If you’re wondering how to use this VBA script, read the following article:

ðŸ“’ **Read More**: How To Use The VBA Code You Find Online

When you execute this VBA script, Excel will show an input box to highlight the cell range for mean calculation.

Then, you’ll see another input box that enables you to select a cell to print the result.

## Conclusions

That’s it! Calculating mean or arithmetic average in Excel is quick and effortless if you follow any of the methods mentioned above.

If you liked the techniques I’ve explained in this article or have a suggestion for me, comment below.

## 0 Comments