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


  • 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.

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.



  • 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


  • Insert the following formula into cell D15.
  • 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


  • Hide some rows from the dataset.

prepare dataset to Use AGGREGATE Function

  • Paste this formula into cell D15.

Formula Breakdown:


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


  • 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:

Related Articles

<< Go Back to Conditional Average | Calculate Average | How to Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF