How to Average Only Visible Cells in Excel: 3 Quick Methods

Method 1 – Using SUBTOTAL Function to Average Visible Cells

1.1 For Filtered Data

Steps:

Filter the dataset to hide rows. To enable the Filter feature for the dataset, select any cell of the dataset.
Go to the Home tab >> Editing menu >> Sort & Filter option >> Filter.

Using Filter Feature to Average Only Visible Cells in Excel

Paste the formula in cell D15 to get the average of all cells in the range D5:D13.

=SUBTOTAL(1,D5:D13)

Using Subtotal Function to Average Only Visible Cells in Excel

Formula Explanation

It returns a list or database. It offers 11 functions to perform by inserting the corresponding argument.

Syntax:

SUBTOTAL(function_num,ref1,[ref2],…)

function_num = 1 to 11: Includes manually hidden rows but excludes filtered-out cells.
function_num = 101 to 111: Excludes both manually hidden rows and filtered-out cells.

To exclude filtered-out cells only, we have used 1 in the function_num argument, and selected the full range of the dataset D5:D13 to calculate the average. Before filtering, we got the average of the full column.

Filter the data to check the output. If you want to get the average sales of any region, then click on the Filter icon on the Region column header.
Mark any region where you want to calculate the average and unmark the rest.
Press OK.

Filtering Data

See that the Average cell shows the average sales for only the Alberta region.

Average Only Visible Cells in Excel using Subtotal Function

1.2 For Data with Hidden Rows

Steps:

Insert the following formula into cell D15.

=SUBTOTAL(101,D5:D13)

Get the Average of the full column before hiding any rows.

Average Only Visible Cells for Data with Hidden Rows

Hide any row from the dataset. Suppose you have hidden the 6th, 10th, and 12th rows.
See the Average cell, which gives the average value of the visible cells.

Average Only Visible Cells for Data with Hidden Rows using Subtotal Function

Method 2 – Using AGGREGATE Function in Excel

Steps:

Hide some rows from the dataset.

prepare dataset to Use AGGREGATE Function

Paste this formula into cell D15.

=AGGREGATE(1,1,D5:D13)

Formula Breakdown:

Syntax:

AGGREGATE(function_num, options, array, [k])

Insert function_num = 1 to calculate the average value of the selected data range.
Insert options = 1 to exclude the hidden rows from the calculations.
Select array = D5:D13 to calculate the average of this data range, excluding hidden rows.

You got the average of only the visible cells in the selected data range.

Using AGGREGATE Function to average only the visible cells

Hide one or more rows of the dataset to check whether the formula works or not. We hidden the 8th and 12th rows, and the average value is changed which is the average of only the visible cells.

Average Using AGGREGATE Function

Method 3 – Average Visible Cells Using Total Row Feature of Excel Table

Steps:

You have to convert the dataset into an Excel table.

Dataset for Using Table Total Row Feature

To make a table, select any cell of the dataset.
Go to the Home tab.
Select the Format as Table option and there will be a list of table designs.
Select any design from the options.

Steps to create Excel table

After selecting a design for the table, a pop-up window named Create Table will appear.
You will see here the range of table cells inserted already.
Press OK.

Create table window

The dataset will be converted into an Excel table.
Select any cell of the table, and a new tab named Table Design will be created in the top ribbon.
Go to the Table Style Options and mark the Total Row.

Using Total Row Feature

There is a new row created at the bottom of the table showing the Total Sales. You can change it to show the average of the sales values.
Click on cell D14 then a drop-down arrow will appear. Click on the arrow.
Select the Average option.

Find Average using Total Row feature

Click on the cell B14 and rename it as Average.
Cell D14 shows the average value of the visible cells.

Rename as Average

Hide some rows to check whether the average cell value changes or not.

Output Using Table Total Row Feature

Things to Remember

Use function_num 1 when you use the filtered dataset to exclude the filtered-out rows from the average calculation.
Use function_num 101 when you want to exclude the manually hidden rows from the average calculation.

Download Practice Workbook

You can download the practice workbook from here:

Average Only Visible Cells.xlsx