10 Ways to Find the Mean in Microsoft Excel

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

Enter data
Enter data

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.

Go to AutoSum
Go to AutoSum

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 highlights dataset
Excel highlights dataset

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

If the selection is correct, press Enter.

Find mean in Excel using AutoSum
Find mean in Excel using AutoSum

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

Highlight dataset
Highlight dataset

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

Quick analysis tool icon
Quick analysis tool icon

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.

Menus in QAT
Menus in QAT

Click on the Quick Analysis icon.

A menu will appear with various tabs and options.

Mean with Excel using QAT
Mean with Excel using QAT

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.

Average on Excel status bar
The average on the Excel status bar

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

Convert to table
Convert to table

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.

Table design tab
Table Design tab

Click anywhere inside the table.

Total row
Total row

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.

Click on total
Click on total

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

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

Choose average
Choose average

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

Choose Average from the context menu.

Average using total row in Excel
Average using total row in Excel

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

Excel add-ins manage
Excel add-ins manage

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

Enabling analysis toolpak
Enabling analysis toolpak

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.

data analysis toolpak
data analysis toolpak

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

Descriptive statistics
Descriptive statistics

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.

Setting up descriptive statistics
Setting up descriptive statistics

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.

Mean with Excel using Analysis ToolPak
Mean with Excel using Analysis ToolPak

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.

Select data
Select data

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.

Setting up subtotal dialog
Setting up the subtotal dialog

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.
Mean with Excel by Subtotal
Mean with Excel by Subtotal

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

Filter subtotals
Filter subtotals

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.

Example data for array formula
Example data for 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.

Using an array formula
Using an array formula

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.

Sum of cost
Sum of cost

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

Field settings
Field settings

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

Value field settings
Value field settings

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

Mean with Excel by PivotTable
Mean with Excel by PivotTable

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.

From sql server database
From SQL server database

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.

Setting up server connection
Setting up server connection

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

Create table for Power Query
Create a table for 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.

Highlight value
Highlight value

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

Choose Average on Statistics
Choose Average on Statistics

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.

Average in Power query
Average in Power query

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

Close and load to
Close and load to

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

Import data
Import data

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

Mean with Excel via Power Query
Mean with Excel via Power Query

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.

The VBA script
The VBA script
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

Select a cell range for input dataset
Select a cell range for input dataset

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

Select a cell range for result
Select a cell range for result

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

John MacDougall

I’m John, and my goal is to help you Excel!

You’ll find a ton of awesome tips, tricks, tutorials, and templates here to help you save time and effort in your work.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃