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.