How to Average a Column in Excel (7 Easy Methods)

Sometimes we may need to average numbers from a column in Excel. Average means the mean value of the input numbers. We have to sum the numbers first and then divide the sum by the number of inputs to get an average. This article will discuss methods to average a column in Excel.


Download Practice Workbook

You can download the practice workbook from here.


7 Methods to Average a Column in Excel

We can find the average of numbers using different methods in Excel. 7 such simple and efficient methods to average a column are given here.


Method 1: Using AVERAGE Function for Column Average

Excel has an in-built AVERAGE function to do the average. Here we will discuss the method in steps.

Step 1: We have to select the cell where we want to put the average.

how to average a column in Excel

Step 2: We have to enter the cell and write the formula given below.

=AVERAGE(C6:C9)

Here, C6:C9 is the range for input numbers.

Step 3: We need to enter the formula in the cell and use Fill Handle to copy the formula to the cells besides.

average a column Excel

We will see the results for each column.

average a column result

This method also works for percentage input.

average a column percentage

Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)


Method 2: Column Average with Blanks and Texts

If we have text as input, we can use the AVERAGEA function instead of the AVERAGE function. Here the text input will be counted as 1 and blank cells will be ignored. We are showing the steps below.

Step 1: We have to select the cell where we’ll average the inputs.

average a column blanks and text

Step 2:  We need to write the formula given below in the cell.

=AVERAGEA(C6:C9)

Here, C6:C9 is the range for input.

Step 3: We have to enter the formula in the cell and use Fill Handle to copy the formula to the cells besides.

We can see, the results are different than the previous method.

Read More: How to Calculate Class Average in Excel (6 Easiest Methods)


Similar Readings


Method 3: Average of Top Numbers in a Column

We may need to average the top few numbers of the input range. In that case, we will use the LARGE function in addition to the AVERAGE function. The steps are given below.

Step 1: We have to select the cell where we want to calculate the result.

average a column top numbers

Step 2: We have to write the formula given below to the cell.

=AVERAGE(LARGE(C6:C9, {1,2,3}))

Here, C6:C9 is the range for input, and {1,2,3} denotes we will take the top 3 numbers.

Step 3: We enter the formula in the cell and use Fill Handle to copy the formula in the cells besides.

Read More: How to Calculate Average of Multiple Columns in Excel (6 Methods)


Method 4: Average of Bottom Numbers in a Column

Instead of the average of top numbers, we may want the average of a few bottom numbers. We will use the SMALL function additionally. We will show the steps below.

Step 1: We need to select the cell where we will show the result.

average a colum bottom number

Step 2: We have to write the formula given below in the cell.

=AVERAGE(SMALL(C6:C9, {1,2,3}))

Here, C6:C9 is the range for input, and {1,2,3} denotes we will take the bottom 3 numbers.

Step 3: We need to enter the formula into the cell and use Fill Handle to copy the formula to the cells besides.

We can see the result in all cells.

Read More: Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)


Method 5: Using Excel AVERAGEIF to Average with Criteria

We can also average numbers if certain criteria are fulfilled. Here we will use the AVERAGEIF function. We will show the steps to do that below.

Step 1: We have to select the cell where we will show the result.

average a column with criteria

Step 2: We have to enter the cell and write the following formula.

=AVERAGEIF(C6:C9, ">70")

Here, C6:C9 is the range, and “>70” is the criteria.

Step 3: We need to enter the formula in the cell and use AutoFill to copy the formula to the cells besides.

average column criteria result

We can see the result in all cells.

Read More: How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)


Similar Readings


Method 6: Using MEDIAN Function for Column Average

Instead of arithmetic mean we can also find the median of a few numbers as average. We will use the MEDIAN function in this case. We have to follow the steps below to find the median.

Step 1: We have to select the cell where we’ll show the result.

average column median

Step 2: We need to write the formula given below to the cell.

=MEDIAN(C6:C9)

Here, C6:C9 is the range for input.

Step 3: We have to enter the formula in the cell and copy the formula to the cells beside by using Fill Handle.

average column in excel

We can see the result in all cells.

Read More: How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)


Method 7: Column Average Using MODE Function

The mode can be a replacement for average sometimes. We will use the MODE function in Excel to do that. We will show the steps below.

Step 1: We have to select the cell to display the result.

average column mode

Step 2: We have to enter the cell and write the formula given below.

=MODE(C6:C9)

Here, C6:C9 is the range for input.

Step 3: We have to enter the formula in the cell and use Fill Handle to copy the formula to the cells besides.

average column result

We can see the result in all cells. There will be no mode if there is no repetition of the input number.

Read More: How to Average Every Nth Row in Excel (3 Ways)


Conclusion

Average of numbers is a very useful tool for mathematical calculation. In Excel, we can find the average of a column using different methods. In this article, we have shown 7 such simple methods to average a column in Excel. Hope the article will help you. If you have any suggestions regarding the methods, please let us know by comment.


Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo